The Museum of HP Calculators

HP Forum Archive 19

 HP17bII+ Solver - Payback Period formulaMessage #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)

 Re: HP17bII+ Solver - Payback Period formulaMessage #2 Posted by Katie Wasserman on 28 July 2009, 12:58 a.m.,in response to message #1 by RMIER 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)

 Re: HP17bII+ Solver - Payback Period formulaMessage #3 Posted by Marcus von Cube, Germany on 28 July 2009, 2:47 a.m.,in response to message #2 by Katie Wasserman 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.

 Re: HP17bII+ Solver - Payback Period formulaMessage #4 Posted by Katie Wasserman on 28 July 2009, 9:41 a.m.,in response to message #3 by Marcus von Cube, Germany 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)

 Re: HP17bII+ Solver - Payback Period formulaMessage #5 Posted by RMIER on 2 Aug 2009, 1:45 p.m.,in response to message #4 by Katie Wasserman 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.

 Re: HP17bII+ Solver - Payback Period formulaMessage #6 Posted by Marcus von Cube, Germany on 28 July 2009, 2:50 a.m.,in response to message #1 by RMIER 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.

 Re: HP17bII+ Solver - Payback Period formulaMessage #7 Posted by Peter A. Gebhardt on 2 Aug 2009, 5:36 p.m.,in response to message #1 by RMIER 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.

Go back to the main exhibit hall