Payback Period Calculation

[dentonblake]

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

 

[mortgageman]

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

Do you know the month that each cashflow happens?
Also, is there a rate somewhere here?

Gene Klein

 

[dentonblake]

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

 

[mortgageman]

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

I can do this with "helper columns" - not a formula though
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.

Gene Klein

 

[Dave Patton]

With your data in A1:B5
C1 is blank

In C2, enter the following and then copy or fill it down.

=(SUM($B$1:B2)>0)*(C1=0)*(ROW()-2-SUM($B$1:B1)/B2)

 

[murali_subramanian]

Hi db,

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:
=(B1-HLOOKUP(COUNTIF(B5:E5,"<"&B1),B3:E5,3,0))/(HLOOKUP(COUNTIF(B5:E5,"<"&B1)+1,B3:E5,2,0))*12
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.

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

 

[adamash]

Hi db,

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:
=(B1-HLOOKUP(COUNTIF(B5:E5,"<"&B1),B3:E5,3,0))/(HLOOKUP(COUNTIF(B5:E5,"<"&B1)+1,B3:E5,2,0))*12
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.

Sorry to bring up an old thread, but I thought it would be easier than creating a new one.

I'm using a slight adaptation of the above, but keeping the same formulas. I have my initial investment, then the 'saving' over each year (rather than cash inflows), but I'm finding that when the saving is made in the first year, e.g., after 0 years and 5 months I get an error in D7 as the months are 0.00 also.

Is there a way around this. I also have a text formula that reads:

="Initial investment of "&TEXT(B1,"£0,000")&" paid back after "&PaybackCalculation!B6&" year(s) and "&TEXT(PaybackCalculation!C6,0)&" months."

Ideally where payback is in the first year I'd like it to say 0 years and 2 months.

 

[Dave Patton]

Hello Adamash

Try converting suggested solution to months instead of years. The answer would be in months and fraction of month.
You could convert the months to the format that you require.

You could also post a sample of your data with the result that you require.
Please ensure that your post can be extracted by people interested in providing a solution.

Since your challenge may be quite different, you could start a new thread.

 

[adamash]

Hello Adamash

Try converting suggested solution to months instead of years. The answer would be in months and fraction of month.
You could convert the months to the format that you require.

You could also post a sample of your data with the result that you require.
Please ensure that your post can be extracted by people interested in providing a solution.

Since your challenge may be quite different, you could start a new thread.

Hi Dave, thank you for the reply. I'm not sure how I'd convert the suggested solution to months. I use this to calculate the months as suggested above:

=(B1-HLOOKUP(COUNTIF(B5:E5,"<"&B1),B3:E5,3,0))/(HLOOKUP(COUNTIF(B5:E5,"<"&B1)+1,B3:E5,2,0))*12

 

[Dave Patton]

See post #5