HP17bII+ Solver - Payback Period formula

[RMIER]

Here is an accurate but long first attempt at a payback period formula for the HP17bII+ Solver.

If anyone has a shorter, more elegant version please feel free to share.

Background: Payback Period is number of years required to recover the invested capital in terms of free cash flows. By definition it ignores time value of money hence it is less useful than IRR and NPV, but it is sometimes used. http://www.investopedia.com/terms/p/paybackperiod.asp

For example, an investment of -$300,000 (Cash Flow 0) that generates free cash flows of $200,000 in Year 1 (Cash Flow 1) and $160,000 in year 2 has a payback period of 1.625 years (wish all my investments were like this).

In other words, it takes the entire first year and a fraction of the second year to recover the investment. The fraction of the second year is given by: (absolute value of accumulated cash flow Year 1/Cash Flow Year 2), or ($100,000/$160,000) = 0.625

Notes:

- Cash flow list has been named "CFLIS". FIN - CFLO - NAME - CFLIS
- This works for 16 cash flows, cash flow in year 0 is negative (the investment). For more cash flows you'll have to adjust the formula.
- Assumes cash is earned uniformly within each year (if I make $100,000 free cash flow in a year, by the end of month 9 of the same year I would have $75,000)
- For clarity I am including line breaks, but when entering the formula everything should be one single line of code without spaces.
- If for some reason the payback exceeds year 16, the answer will be 101 years, as in why bother with the project if it takes so long. The why bother limit of 16 years is of course at your discretion.
- The calculator takes a little to compute the answer. Hence the ask for a faster, more elegant solution.

Here we go:

PAYBK=L(S1:FLOW(CFLIS:0)+FLOW(CFLIS:1))*0+
L(S2:S1+FLOW(CFLIS:2))*0+
L(S3:S2+FLOW(CFLIS:3))*0+
L(S4:S3+FLOW(CFLIS:4))*0+
L(S5:S4+FLOW(CFLIS:5))*0+
L(S6:S5+FLOW(CFLIS:6))*0+
L(S7:S6+FLOW(CFLIS:7))*0+
L(S8:S7+FLOW(CFLIS:8))*0+
L(S9:S8+FLOW(CFLIS:9))*0+
L(S10:S9+FLOW(CFLIS:10))*0+
L(S11:S10+FLOW(CFLIS:11))*0+
L(S12:S11+FLOW(CFLIS:12))*0+
L(S13:S12+FLOW(CFLIS:13))*0+
L(S14:S13+FLOW(CFLIS:14))*0+
L(S15:S14+FLOW(CFLIS:15))*0+
L(S16:S15+FLOW(CFLIS:16))*0+

IF(S1>=0:1-(S1/FLOW(CFLIS:1)):
IF(S2>=0:2-(S2/FLOW(CFLIS:2)):
IF(S3>=0:3-(S3/FLOW(CFLIS:3)):
IF(S4>=0:4-(S4/FLOW(CFLIS:4)):
IF(S5>=0:5-(S5/FLOW(CFLIS:5)):
IF(S6>=0:6-(S6/FLOW(CFLIS:6)):
IF(S7>=0:7-(S7/FLOW(CFLIS:7)):
IF(S8>=0:8-(S8/FLOW(CFLIS:8)):
IF(S9>=0:9-(S9/FLOW(CFLIS:9)):
IF(S10>=0:10-(S10/FLOW(CFLIS:10)):
IF(S11>=0:11-(S11/FLOW(CFLIS:11)):
IF(S12>=0:12-(S12/FLOW(CFLIS:12)):
IF(S13>=0:13-(S13/FLOW(CFLIS:13)):
IF(S14>=0:14-(S14/FLOW(CFLIS:14)):
IF(S15>=0:15-(S15/FLOW(CFLIS:15)):
IF(S16>=0:16-(S16/FLOW(CFLIS16)):101)

[Katie Wasserman]

Wow, that's a long equation.

Disclaimer: I wrote and tested this on a 17bii not a 17bii+

Here's a simpler, more general solution that will work for any length cash flow list and follows your assumptions. It's doesn't stop at year 16 however.

PAYBK = 

0 x L(X:FLOW(CFLIS:0)) +

Sigma(I:1:SIZEC(CFLIS):1:

0 x L(Y:FLOW(CFLIS:I)) x L(X:G(X)+G(Y)) +

IF(G(X)<=0:1:IF(G(X)<G(Y):(G(Y)-G(X))/G(Y):0)))

- Sigma is the summation symbol on the 17BII

- G(..) simply gets the value of the variable but does it without trying to solve for it and without showing it as a variable in the menu.

Edited: 28 July 2009, 1:06 a.m.

[Marcus von Cube, Germany]

Katie, the problem is the case where the sum of cash flows is negative. The formula will spit out the number of cash flows but no error indication is given.

[Katie Wasserman]

That's correct, there's no check to see if total doesn't end up as a positive number and therefore it will just terminate showing the total number of cash flows. But it's simple to correct that:

PAYBK = 

0 x L(X:FLOW(CFLIS:0)) +

Sigma(I:1:SIZEC(CFLIS):1:

0 x L(Y:FLOW(CFLIS:I)) x L(X:G(X)+G(Y)) +

IF(G(X)<0:IF(I=SIZEC(CFLIS):9E499:1):IF(G(X)<G(Y):(G(Y)-G(X))/G(Y):0)))

The above formula will return a 9E499 if the sum of the cash flows never turns positive.

[RMIER]

Katie, thanks for the elegant and short solution, clever use of sigma, SIZEC and G(X) to get the job done. It works well on the 17bII+.

Marcus, thanks for your comment also, it resulted in an improvement to the solution.

Much appreciated, Rafael.

[Marcus von Cube, Germany]

Your formula doesn't check the total number of cas flows and will (probably, I haven't checked) error out if the sum of all cash flows is negative.

[Peter A. Gebhardt]

In addition to Katie's solution, here is my Discounted Payback Solution (from a 200LX) which takes into account that:

1) SAF% == 0 (Opportunity Cost per Period) results in Payback Periods instead of Discounted Payback Periods

2) Grouped CFs are possible

0*SAF%

+DPB

-G(D)                     ! see PS below !

=

0*(

    L(CF$:0)

   +L(DPB$:FLOW($:0))

   +L(N:1) 

   +L(D:9E99)             ! see PS below !

)

+

0*

SIGMA(G

     :1:SIZEC($):1

     :0*L(CF$:FLOW($:G))

      *SIGMA(T

            :1:#T($:G):1

            :IF((G(DPB$)+G(CF$)*SPPV(SAF%:G(N)))>=1E-15

                 :L(D

                   :G(N)-1

                    +IF(G(CF$)<>0

                       :ABS(G(DPB$))/(G(CF$)*SPPV(SAF%:G(N)))

                       :0

                       )                 

                   )

                 :L(DPB$

                   :G(DPB$)

                    +G(CF$)*SPPV(SAF%:G(N))

                   )

                  +0*L(N:G(N)+1)

                 )

           )

      )

To reduce the number of characters to be typed into your 17bII+ finetuning of the above code should be possible still. As examples: Somebody might reduce the code in the init section or might play around with some clever MIN/MAX usage instead of IF's ...

Best regards,

Peter A. Gebhardt

PS (3-Aug-2009,10:46 MESZ): The following code, which was thought of covering the case of sum of the CFs being negative, gave spurious erroneous results.

Therefore I replaced

-if(G(DPB$)<0:L(D:9E99):G(D))

with

-G(D)

and

+L(D:0)

with

+L(D:9E99)

Edited: 3 Aug 2009, 4:56 a.m.