(Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%

02062022, 10:59 AM
(This post was last modified: 02172022 12:42 PM by Werner.)
Post: #1




(Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Update: for the latest version see here.
My first try at writing equations! The following equation performs Cash Flow Analysis:  NPV: Net Present Value  IRR%: Internal Rate of Return : the interest rate that makes NPV=0  MIR%: Modified Internal Rate of Return: works with two interest rates. Negative cash flows are discounted back to Present Values using a safe rate SF%, positive cash flows are accumulated forward to Future Values with a Reinvestment rate RI%. The MIR% is then ((FV/PV)^(1/N)1)*100, with N the total number of cash flows, not counting CF0.  FMR%: Financial Management Rate of Return: like MIR%, but where possible, future negative cash flows will be removed by prior positive cash flows.  All cash flows CFj (including CF0!) must be placed in a column matrix CF, with the corresponding frequency Nj in an optional second column.  Since these are solver equations, IRR% can be found setting NPV to zero and solving for IRR%  all calculations are done using a single reverse sweep over the CF matrix  I am a beginner at these things, so I'd welcome improvement tips. example 1  from the HP30b manual pg 44, the only HP to feature FMRR Create the 8x1 matrix  including CF0: [[ 1250000 ] [ 300000 ] [ 200000 ] [ 450000 ] [ 200000 ] [ 700000 ] [ 300000 ] [ 500000 ]] CALC the equation, and press CF to store the matrix Then set NPV to zero and press IRR%=4.96 set SF%=5 and RI%=8, then MIRR%=5.94 FMRR%=5.86 example 2  from hpmuseum forum: https://www.hpmuseum.org/forum/thread13...#pid117980 Create the 7x2 matrix [[ 25000 1] [ 10000 1] [ 7000 1] [ 5000 8] [ 4000 12] [ 35000 2] [ 29000 2]] and determine  NPV with discount rate 5% (24688.29) (Set IRR% to 5 and press NPV)  IRR% (11.08%) (set NPV to 0 first)  MIRR% with safe rate 3.75% and reinvestment rate 8.35% (7.87%)  FMRR% with the same rates (8.47%) formatted equation: Code: CFA:0*( as an equation string: Code: CFA:0*(L(N:MROWS(CF))+IRR%+NPV+SF%+RI%+MIR%+FMR%+L(P:0))+IF(S(CF):0:IF(S(SF%):0:IF(S(RI%):0:IF(S(FMR%):0*(L(F:0)+L(SF:SF%/100)+L(S:1+G(SF))+L(R:1+RI%/100)+IF(MCOLS(CF)=1:L(NN:G(N))+Σ(I:G(N):1:1:IF(L(P:G(P)L(C:CF[I]))<0:L(F:G(F)G(P))+L(P:0):0)+L(P:G(P)/G(S))+L(F:G(F)/G(R))):L(NN:0)+Σ(I:G(N):1:1:L(NN:G(NN)+L(Nj:CF[I:2]))+L(N1:L(N2:L(N3:0)))+IF(L(C:CF[I:1])<0:L(N1:G(Nj)):IF(G(P)=0:L(N3:G(Nj)):L(N1:MIN(G(Nj):IP(LNP1(G(SF)*G(P)/G(C))/LNP1(G(SF)))))+L(N2:G(Nj)G(N1))+L(N3:G(N2)1)))+IF(G(N1)>0:L(F:G(F)*SPPV(RI%:G(N1)))+L(P:G(P)*SPPV(SF%:G(N1))G(C)*USPV(SF%:G(N1))):0)+ IF(G(N2)>0:L(F:(G(F)G(P)+G(C))/G(R))+L(P:0):0)+IF(G(N3)>0:L(F:G(F)*SPPV(RI%:G(N3))+G(C)*USPV(RI%:G(N3))):0))))+SPFV((FMR%RI%)/G(R):G(NN)1)G(F)*G(R)/G(P)/G(S):IF(S(MIR%):0*(L(F:0)+L(S:1+SF%/100)+L(R:1+RI%/100)+IF(MCOLS(CF)=1:L(NN:G(N))+Σ(I:G(N):1:1:IF(L(C:CF[I])<0:L(P:G(P)G(C)):L(F:G(F)+G(C)))+L(P:G(P)/G(S))+L(F:G(F)/G(R))):L(NN:0)+Σ(I:G(N):1:1:L(NN:G(NN)+L(Nj:CF[I:2]))+L(P:G(P)*SPPV(SF%:G(Nj)))+L(F:G(F)*SPPV(RI%:G(Nj)))+IF(L(C:CF[I:1])<0:L(P:G(P)G(C)*USPV(SF%:G(Nj))):L(F:G(F)+G(C)*USPV(RI%:G(Nj)))))))+SPFV((MIR%RI%)/G(R):G(NN)1)G(F)*G(R)/G(P)/G(S):0*(L(R:1+IRR%/100)+IF(MCOLS(CF)=1:Σ(I:G(N):1:1:L(P:(G(P)+CF[I])/G(R))):Σ(I:G(N):1:1:L(Nj:CF[I:2])+L(P:G(P)*SPPV(IRR%:G(Nj))+CF[I:1]*USPV(IRR%:G(Nj))))))+NPVG(P)*G(R)))))) Took me 2 days to write  far less than the corresponding 42S RPN program that is still not finished ;) The #1 cause for errors is mismatched parentheses  I would therefore like very much to have a () insert in one go, with the cursor in between the parentheses  a bit like the '48 does. Cheers, Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02062022, 01:37 PM
Post: #2




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
(02062022 10:59 AM)Werner Wrote: [snip] Impressive equation! Inserting parenthesis in pairs is a great suggestion Werner, which can seem awkward and unnecessary to a seasoned RPN user until one has used a system for a while (like RPL) which acts this way, and thereafter it becomes intuitive and even essential to using them, and especially for daunting equations like this. I hope this can be easily adopted. Bob Prosperi 

02062022, 02:13 PM
(This post was last modified: 02062022 02:24 PM by Didier Lachieze.)
Post: #3




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%  
02062022, 02:26 PM
(This post was last modified: 02062022 02:28 PM by Werner.)
Post: #4




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Hi Didier,
strange, I can copy the text from my post and import it without a problem. The only abnormal character in the equation is the sigma Σ, that appears 6 times, but not at that place? Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02062022, 05:37 PM
Post: #5




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
I get a bullet character, too, when I try to copy the equation and paste it into Plus42 on my phone. Some kind of whitespace, I'm guessing. I'll look into it later, need a computer...
UPDATE: It's U+200b, zerowidth space, which Plus42 didn't handle. I added code to deal with this, and other zerowidth and whitespace characters as well. 

02082022, 05:08 PM
Post: #6




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Well, nobody noticed, but there was a tiny error in the FMR% equation ;)
I changed the whole equation in the meantime, shortening it, not necessarily improving the readability though.. Here goes: Code: CFA:L(P:0)*( and without blanks: Code: CFA:L(P:0)*(L(N:MROWS(CF))+SF%+RI%+MIR%+FMR%+IRR%+NPV)+IF(S(CF) OR S(SF%) OR S(RI%):0:IF(S(MIR%) OR S(FMR%):0*(L(F:0)+L(S:1+L(SF:SF%/100))+L(R:1+RI%/100)+IF(MCOLS(CF)=1:L(NN:G(N))+IF(S(FMR%):Σ(I:G(N):1:1:IF(L(P:G(P)L(C:CF[I]))<0:L(F:G(F)G(P))+L(P:0):L(P:G(P)/G(S)))+L(F:G(F)/G(R))):Σ(I:G(N):1:1:IF(L(C:CF[I])<0:L(P:G(P)G(C)):L(F:G(F)+G(C)))+L(P:G(P)/G(S))+L(F:G(F)/G(R)))):L(NN:Σ(I:G(N):1:1:L(N1:L(Nj:CF[I:2]))+L(N2:L(N3:L(N4:L(N5:0))))*(IF(L(C:CF[I:1])≤0:L(N2:G(Nj)):L(N5:G(Nj))+IF(S(FMR%):IF(G(P)=0:L(N1:0)+L(N4:G(Nj)):L(N4:L(N5:L(N3:G(Nj)L(N1:L(N2:MIN(G(Nj):IP(LNP1(G(SF)*G(P)/G(C))/LNP1(G(SF)))))))1))):0))+IF(G(N1)>0:L(F:G(F)*SPPV(RI%:G(N1)))+L(P:G(P)*SPPV(SF%:G(N1))):0)+IF(G(N2)>0:L(P:G(P)G(C)*USPV(SF%:G(N2))):0)+IF(G(N3)>0:L(F:(G(F)G(P)+G(C))/G(R))+L(P:0):0)+IF(G(N4)>0:L(F:G(F)*SPPV(RI%:G(N4))):0)+IF(G(N5)>0:L(F:G(F)+G(C)*USPV(RI%:G(N5))):0))))))+IF(S(FMR%):FMR%:MIR%)((G(F)*G(R)/G(P)/G(S))^(1/(G(NN)1))*G(R)1)*100:0*(L(R:1+IRR%/100)+IF(MCOLS(CF)=1:Σ(I:G(N):1:1:L(P:(G(P)+CF[I])/G(R))):Σ(I:G(N):1:1:L(P:G(P)*SPPV(IRR%:L(Nj:CF[I:2]))+CF[I:1]*USPV(IRR%:G(Nj))))))+NPVG(P)*G(R))) What I have learned about equations..
Cheers; Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02092022, 03:39 AM
Post: #7




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
If you're not familiar with this book Werner, you definitely will want to read it:
StepbyStep Solutions: Technical Applications (27S/19B) Don't let the hohum title bore you, it's a treasure. You've figured out many of the subtleties, but the book will delight your exploration of the Pioneer Solver. Bob Prosperi 

02092022, 06:32 AM
(This post was last modified: 02092022 06:33 AM by Werner.)
Post: #8




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Thanks, Bob, wish I had known this one before, I browsed all the publications on that site that had ‘finance’ in their title, missing this one  the most important one of all!
Cheers, Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02092022, 10:01 AM
Post: #9




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
(02092022 03:39 AM)rprosperi Wrote: If you're not familiar with this book Werner, you definitely will want to read it: The author of that book, Steve Sabin, is a member of this forum. Here is a post where he mentions this and another book he wrote for HP. https://www.hpmuseum.org/forum/thread15...#pid149679 

02092022, 10:54 AM
Post: #10




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Read the book, and indeed, I learned a few things more, namely the rules governing the use of the direct vs. iterative solver. So my question..
Take this simplified equation performing NPV and IRR% only: Code: NPVIRR:0*( If I understood the rules correctly, it should use the direct solver for NPV and the iterative one for IRR%, but how can I tell? Cheers, Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02092022, 01:04 PM
(This post was last modified: 02092022 01:13 PM by Thomas Okken.)
Post: #11




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
(02092022 10:54 AM)Werner Wrote: If I understood the rules correctly, it should use the direct solver for NPV and the iterative one for IRR%, but how can I tell? Hmmm... that's something I haven't really dealt with yet. The numerical solver puts several items on the stack: the solution in X, the last approximation before the solution in Y, the final function value in Z, and the exit status in T. The direct solver only puts the solution in X, and nothing else, so there's no reliable way of distinguishing the two cases. There are workarounds, like putting something like L(COUNT:G(COUNT)+1) in the equation, to see how often it is being called. Or use TRACE mode: if the function is being called repeatedly, the numerical solver is at work; if it runs only once and the equation source code isn't shown, the direct solver is at work, evaluating a transformed equation; and if it runs only once and the equation source code is shown, the direct solver is at work, running an untransformed equation, which happens when solving for a variable that is already isolated in the original equation, e.g. solving for X in X=Y+Z. (Or use NSTK mode, when you can tell which solver was used by how much stuff there is on the stack when the solver returns.) 

02092022, 02:36 PM
Post: #12




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
(02092022 01:04 PM)Thomas Okken Wrote: like putting something like L(COUNT:G(COUNT)+1) in the equationThought of that, too, so I did Code: NPVIRR:0*( And of course, NPV is also solved for iteratively, even if it is referenced only once. The equation can only be directly solved if NPV can be isolated, which is not the case here. When I remove the line at the beginning setting the order of the variables (+IRR%+NPV), and change the equation at the end to use NPV instead of G(NPV), it works. In this case it doesn't matter, but how then to both use direct solving and your preferred order of variables (eg in the much larger CFA equation, where only IRR% needs to be solved iteratively)? I will probably have to choose ;) Cheers, Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02092022, 04:28 PM
Post: #13




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
(02092022 02:36 PM)Werner Wrote: but how then to both use direct solving and your preferred order of variables (eg in the much larger CFA equation, where only IRR% needs to be solved iteratively)? I will probably have to choose ;) Not at all, the best of both worlds can be had. Specify a parameter list after the equation name, et voilà. Code: FOO(X:Y:Z):<function using X, Y, Z, and maybe other variables as well> The named parameters will be shown first in the VARMENU, in the order in which they appear in the parameter list; any remaining referenced variables will show up in the order in which they are found in the equation, and shown in black on white to set them apart. Note that a named equation with parameter list, as above, may not have a toplevel = sign, so if you have a =0, just leave it off, and otherwise, change it to a subtraction. 

02092022, 04:51 PM
Post: #14




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Thanks, Thomas! Will try that out ;)
Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02172022, 12:41 PM
Post: #15




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
My current version:
Code: CFA(CF:IRR%:NPV:SF%:RI%:MIR%:FMR%): CutandPaste: Code: CFA(CF:IRR%:NPV:SF%:RI%:MIR%:FMR%):IF(S(CF) OR S(SF%) OR S(RI%):0:L(P:0)*(IF(S(MIR%) OR S(FMR%):L(F:0)+L(S:1+L(SF:SF%/100))+L(R:1+RI%/100):L(S:1+IRR%/100))+L(NN:1+Σ(I:SIZEC(CF):0:1:L(Nj:#T(CF:I))+0*(L(C:FLOW(CF:I))+IF(S(FMR%):IF(G(Nj)=1:IF(L(P:G(P)G(C))<0:L(F:G(F)G(P))+L(P:0):L(P:G(P)/G(S)))+L(F:G(F)/G(R)):L(N1:L(N2:L(N3:0)))+IF(G(C)>0:IF(G(P)=0:L(N3:G(Nj)):L(N1:MIN(G(Nj):IP(LNP1(G(SF)*G(P)/G(C))/LNP1(G(SF)))))+L(N2:G(Nj)G(N1))+L(N3:G(N2)1)):L(N1:G(Nj)))+IF(G(N1)>0:L(F:G(F)*SPPV(RI%:G(N1)))+L(P:G(P)*SPPV(SF%:G(N1))G(C)*USPV(SF%:G(N1))):0)+IF(G(N2)>0:L(F:(G(F)G(P)+G(C))/G(R))+L(P:0):0)+IF(G(N3)>0:L(F:G(F)*SPPV(RI%:G(N3))+G(C)*USPV(RI%:G(N3))):0)):IF(S(MIR%):IF(G(Nj)=1:IF(G(C)<0:L(P:G(P)G(C)):L(F:G(F)+G(C)))+L(P:G(P)/G(S))+L(F:G(F)/G(R)):L(P:G(P)*SPPV(SF%:G(Nj)))+L(F:G(F)*SPPV(RI%:G(Nj)))+IF(G(C)<0:L(P:G(P)G(C)*USPV(SF%:G(Nj))):L(F:G(F)+G(C)*USPV(RI%:G(Nj))))):IF(G(Nj)=1:L(P:(G(P)+G(C))/G(S)):L(P:G(P)*SPPV(IRR%:G(Nj))+G(C)*USPV(IRR%:G(Nj))))))))))+IF(S(MIR%) OR S(FMR%):((G(F)*G(R)/G(P)/G(S))^(1/G(NN))*G(R)1)*100IF(S(MIR%):MIR%:FMR%):G(P)*G(S)NPV)) Cheers, Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02172022, 01:19 PM
Post: #16




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Really nice Werner , thanks for sharing this, though I can't say I've read and understand the equations. Yet...
Sharing it in this dual format is great, both useful to follow and understand, but also readily usable asis via copy/paste. Hopefully this will become a standard for complex Plus42 equations going forward, I suggest we ask folks sharing them in the future to look here as an example of coding style and documentation. Bob Prosperi 

02172022, 01:22 PM
Post: #17




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
My simpler version, that only deals with IRR%, NPV, NUS, NFV and TOTAL, as in the 17BII.
I used to have a separate CF0 variable, now that FLOW() and #T() are 0based, I put it as the first element of the matrix (index 1 in the MATRIX editor). It expects a Cash Flow matrix (N*2) named CF. Cheers CFLOW:IF(S(IRR%):0:L(IRR%:I%)I%)+L(Z:FLOW(CF:0)+Σ(J:1:SIZEC(G(CF)):1:FLOW(CF:J)×USPV(IRR%:#T(CF:J))×SPPV(IRR%:IF(J=1:0:Σ(L:1:J1:1:#T(CF:L))))))G(Z)+IF(S(IRR%):G(Z):NPVG(Z)+L(N:1+Σ(J:1:SIZEC(G(CF)):1:#T(CF:J)))G(N)+L(NFV:NPV×SPFV(I%:G(N)))NFV+L(NUS:NPV÷USPV(I%:G(N)))NUS+L(TOTAL:FLOW(CF:0)+Σ(J:1:SIZEC(G(CF)):1:FLOW(CF:J)×#T(CF:J)))TOTAL)=0 

02172022, 02:40 PM
Post: #18




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Aha, someone else's equation and coding style ;)
Thanks for the example, Werner 41CV†,42S,48GX,49G,DM42,DM41X,17BII,15CE,DM15L,12C,16CE 

02172022, 03:56 PM
Post: #19




RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR%
Hi Werner,
(02172022 02:40 PM)Werner Wrote: [*] I see you keep each 'segment' zero by subtracting the result again.Yes, I prefer it to the 0*, because 0* can sometimes trigger direct solving with undesirable side effects.... Quote:[*] A thing I noticed in all Cash Flow equations I've seen so far is that noone adopted the Horner scheme to obtain the result, as I did. It's a lot simpler... (eg you don't have to keep track of the partial sums of the Nj's)All I did was read the 17BII manual "equations used" Appendix, and translate it into the language Quote:[*] All your variables will be solved iteratively. With Plus42's blinding speed, this probably does not make a difference ;) Quote:Thanks for the example, WernerYou're most welcome. It is always a pleasure to discuss different approaches [/quote] 

« Next Oldest  Next Newest »

User(s) browsing this thread: 2 Guest(s)