We have covered Pay Back Period in great detail. The process is not difficult and usually takes hardly 05 minutes. But, there are friends who want to know the exact years with decimal values. For them, we are sharing the Excel formula which you can use to find out PBP. You just need to enter initial investment amount and the remaining cash flows and our formula will do the rest of the work.
Example
ABC is considering investing in a capital budget which requires initial investment of $ 400,000. The cash flows are as follows:
| Year | Amount |
| 0 | 400,000 |
| 1 | 125,000 |
| 2 | 200,000 |
| 3 | 145,000 |
| 4 | 150,000 |
We have developed the following Excel sheet:
From the above sheet, we can easily tell that project will recover its initial investment in the 3rd year. However, in order to calculate exact figure with decimals, we will usre this
Excel formula:
=(SUM($B$1:B4)>0)*(C3=0)*(ROW()-2-SUM($B$1:B3)/B4)
You can see in the above screen shot that initial investment of $ 400,000 is getting repaid in 2.51724 years.