HP17bII+ Solver  Payback Period formula Message #1 Posted by RMIER on 27 July 2009, 3:24 p.m.
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)
