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!

 

[Dave Patton]

Without a cumulative row, you could build a User-Defined Function with VBA.
It may be possible with regular functions but why make it awkward?

With a cumulative row, you could use something like the following

Your text is in Column A
Cumulative is in row 3

=OFFSET(A1,0,MATCH(1,B3:E3,1),1,1)-(OFFSET(A1,2,MATCH(1,B3:E3,1),1,1))/OFFSET(A1,1,MATCH(1,B3:E3,1)+1,1,1)


The match part "MATCH(1,B3:E3,1)" repeats three times so you put that in a cell and reference the cell or use other methods to reduce the number of matches (repeats).

D4 =MATCH(1,B3:E3,1)

=OFFSET(A1,0,D4,1,1)-(OFFSET(A1,2,D4,1,1))/OFFSET(A1,1,D4+1,1,1)

 

[xenou]

I'd use rows of cumulative values as well. Another example is here:
http://finance.thinkanddone.com/using-ms-excel-to-find-payback-period.html

I don't think this actually can be a simple one-cell formula if you need the decimal fraction of the last month (but it may not be so bad a candidate for a user-defined formula in VBA).

 

[theconditioner]

Thanks, xenou. Yeah, it may be a good idea to make up my own VBA formula; doesn't seem too hard (though I've never made one up before). Just got to learn how to actually do it!

 

[xenou]

No problem. We'll keep this thread open if you want to try more ideas. I should start with a user-defined formula (UDF) to get just the month ... then work it out to the fractional portion of the month too. Post back if you run into any roadblocks.

 

[Fazza]

Maybe, in a code module

Function Payback(Periods As Range, CashFlow As Range) As Variant
 
  Dim i As Long
  Dim dblMySum As Double
 
  Payback = CVErr(xlErrNA)
  With Periods
    For i = 1 To .Cells.Count
      dblMySum = dblMySum + CashFlow.Cells(i)
      If dblMySum > 0 Then
        Payback = .Cells(i).Value - dblMySum / CashFlow(i) * (.Cells(i).Value - .Cells(i - 1).Value)
        Exit Function
      End If
    Next i
  End With
End Function

You can add application.volatile if you like

You could make it simpler if the periods are always 0, 1, 2, 3, 4, etc. Only one range would need to be input then and the formula shortened.

hth

 

[jim may]

Fazza, Neat Code. Why the inclusion of this line?

Payback = CVErr(xlErrNA)

 

[xenou]

Fazza, Neat Code. Why the inclusion of this line?

Payback = CVErr(xlErrNA)

I'll hazard a guess - its a "default" error return value that will be given if the function fails/errors during the calculation of the payback. This would show (on the excel worksheet itself) a recognizable "#/NA" in the cell in such cases. Nice touch.

 

[Fazza]

It is as xenou wrote. It is normal good practice; I don't write many functions for use by others - I normally just hack something together for my own use; so maybe it isn't the best way to write it. I learnt about this when starting VBA from the superb "Excel 2000 Power Programming With VBA" by John Walkenbach. regards

 

[barry houdini]

With a cumulative row in B3:G3 you could use this formula

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

Without that row you could use this version

=LOOKUP(0,SUBTOTAL(9,OFFSET(B2,,,,COLUMN(B2:F2)-COLUMN(B2)+1)),B1:F1-(SUBTOTAL(9,OFFSET(B2,,,,COLUMN(B2:F2)-COLUMN(B2)+1)))/(SUBTOTAL(9,OFFSET(B2,,,,COLUMN(B2:F2)-COLUMN(B2)+2))-SUBTOTAL(9,OFFSET(B2,,,,COLUMN(B2:F2)-COLUMN(B2)+1))))