This is a discussion on Payback Period Calculation within the Excel Questions forums, part of the Question Forums category; Is there a formula in excel that will calculate the exact payback period for an investment, and a series of ...
Is there a formula in excel that will calculate the exact payback period for an investment, and a series of cash flows, for example:
Year 0: -275,000 (initial investment)
Year 1: 125,000
Year 2: 125,000
Year 3: 145,000
Year 4: 145,000
I know I will get the payback in year 3, but would like a more exact figure like 3.17 and/or in years and weeks/days...
Appreciate your help! db
No rate required, just looking for "simple" payback period -- e.g., not seeking NPV, IRR, etc... just how long it's taking me to recover the initial investment.
Each year is comprised of 12 equal payments at the beg. of each month to arrive at the year end total outlined in the original post. (example: rent payments).
Thank you, db
The first column would be a running total (using sum($a$2..a2) copied down). The second column would be a "boolean" check to see if the rt is > then the absolute value of the investment (which I assume happens one month before the rent starts?) in cell a1. The first TRUE tells you the month.
I'm sure some of the formula wizards here can do better - but at least that can get you started.
With your data in A1:B5
C1 is blank
In C2, enter the following and then copy or fill it down.
You can do that using COUNTIF and HLOOKUP functions. Here's how you do that:
Step 1: Open a new spreadsheet
Step 2: Enter the initial investment of 275000 in cell B1
Step 3: Enter Year numbers 1 to 4 in cells B3 to E3
Step 4: Enter the annual cash inflows 125k, 145k, 145k & 145k through years 1 to 4 in cells B4 to E4
Step 5: Calculate the cumulative cash inflows through years 1 to 4 in cells B5 to E5. The cumulative cash inflows would be 125k, 290k, 395k & 540k in the respective years.
Step 5: Enter (or copy and paste) the formula =COUNTIF(B5:E5,"<"&B1) in cell B7. This will return the value 2, indicating the year component of the payback period (not including Year 0, the year in which the initial investment was made)
Step 6: Enter (or copy & paste) the following formula in cell D7:
This will return the value 2.07, indicating the fraction of the 3rd year in months
The payback period will be computed as 2 years & 2.07 months or 2.17 years (not including year 0). If you were to include year 0 then it would be 3.17 years. To get to this value, modify the formula in cell B7 to =COUNTIF(B5:E5,"<"&B1)+1
Hope this helps.