Help with Payback Period formula!

[theconditioner]

Hello,

I am trying to make a payback period formula, but I can't figure out a simple formula to use. Payback period is essentially the time it takes for your total cash flows to equal 0 (assuming that cash flows are evenly distributed through time).

I am trying to avoid making a "cumulative sum" row too.

Basically, what formula should I use in cell B4?

The answer should be 3.2 if I'm not mistaken.

Thanks!

 

[Fazza]

BTW, looking at the code I posted, the conditional is if dblMySum > 0

I think that should be >= 0

 

[Fazza]

That is very neat, Barry. It assumes an interval of 1 on the years.

And that seems reasonable to me. The VBA works on the actual year entries (row A) of the sample. So the VBA and formula return different results if the year interval that payback occurs is other than 1.

The VBA can be shortened a bit and only one range input if the year steps are only ever 0, 1, 2, 3, 4, etc

 

[themev]

Barry (or anyone else) - the formula for a cumulative row works perfectly...except when the payback period is a whole number. In that case I believe the formula brings back an error of DIV/0 (at least that is what I am getting).

Is there any way to fix that?

 

[barry houdini]

It assumes an interval of 1 on the years.

Hello Fazza, Yes, I saw your initial comment and meant to address that in the formula......but forgot, you can amend the helper row formula like this to take account of any intervals

=LOOKUP(0,B3:F3,B1:F1-(B3:F3*(C1:G1-B1:F1))/(C3:G3-B3:F3))

The longer formula could be amended in the same way

....the formula for a cumulative row works perfectly...except when the payback period is a whole number. In that case I believe the formula brings back an error of DIV/0

I can't re-create that problem, what values are in the ranges?

 

[Dave Patton]

Hello Barry

The formula gives #Div/0 with a range of data like

-1000 250 250 250 250

Not a problem with

-1000 500 250 250 250

Dave

 

[mortgageman]

Hello,
<Big Snip>
Payback period is essentially the time it takes for your total cash flows to equal 0 (assuming that cash flows are evenly distributed through time).

I am trying to avoid making a "cumulative sum" row too.
<more Snip>

You are also avoiding pesky things like the time value of money.

Gene Klein

 

[mgirvin]

barry houdini,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thank you (and Dave Patton for sending me to this link) for the very efficient formula for calculating Payback. Here is the data, the formula I was using and an adapted version of your formula:<o></o><o></o><o>

Excel Workbook
	T	U	V
2	Period	Cash Flow For NPV	Cumulative CF for Payback
3	0	$ (1,115,000.00)	$ (1,115,000.00)
4	1	146,745.00	(968,255.00)
5	2	384,393.00	(583,862.00)
6	3	396,613.00	(187,249.00)
7	4	397,113.00	209,864.00
8	5	309,148.00	519,012.00
9	6	221,861.00	740,873.00
10	7	208,698.00	949,571.00
11	8	428,180.00	1,377,751.00
12
13	Payback	3.472
14	Payback	3.472
...

Cell Formulas
Range	Formula
U13	=IF(SUM(U4:U11)<=-U3,T11,COUNTIF(V4:V11,"<=0")-LOOKUP(2,1/(V4:V11<=0),V4:V11)/INDEX(U4:U11,MATCH(2,INDEX(1/(V4:V11<=0),))+1))
U14	=IF(SUM(U4:U11)<=-U3,T11,LOOKUP(0,V3:V11,T3:T11-V3:V11/U4:U11))

</o><o></o>
I like the logic of your formula because it follows the logic of how this is calculated on an individual basis (last cumulative cash flow before pay off divided by next cash flow plus the year). The logic I was using in my formula (year + lookup last negative divided by lookup position of last negative + 1) was as far as I could get.

I added the extra if to deal with the fact that it is possible (but unlikely) that payoff exceeds years.

Thanks very much!<o></o>

 

[mgirvin]

Yes, Payback avoids the time value of money, risk and whether value is added, but it is a common back of the envelope method that is often used.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If only everyone knew how to use Excel, then the back of the envelope methods could occur much less frequently!<o></o>
<o></o>

 

[Joyson]

How many pay periods left?

How many pay periods left?

We have 26 bi-weekly pay periods in a year, so how to calculate how many left?

=(12-MONTH(A3))*2+1+(DAY(A3)<16); where A3 is =today()

I use this formula, but it’s not correct,
the formula has to change its number after every period end day, which is Saturday.

 

[Legacy 165240]

Hello,

I am trying to make a payback period formula, but I can't figure out a simple formula to use. Payback period is essentially the time it takes for your total cash flows to equal 0 (assuming that cash flows are evenly distributed through time).

I am trying to avoid making a "cumulative sum" row too.

Basically, what formula should I use in cell B4?

The answer should be 3.2 if I'm not mistaken.

Thanks!

Hi Conditioner

It seems you visited my site with your original question and downloaded the Payback Period Template from us.

Since then I have made available a new template that eliminates the cumulative net cash flow row.

You can type in the following formula in cell C3 and then copy paste the formula in C3 to the cells D3 through G3. Remember to place the text N/A in cell B3

=IF(AND(SUM($B$2:C2)>0,B3="N/A"),B1+(C2-SUM($B$2:C2))/C2,"N/A")