HP Forums
(Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Printable Version

+- HP Forums (https://www.hpmuseum.org/forum)
+-- Forum: Not HP Calculators (/forum-7.html)
+--- Forum: Not quite HP Calculators - but related (/forum-8.html)
+--- Thread: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% (/thread-18003.html)

(Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-06-2022 10:59 AM

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 Re-investment 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/thread-13155-post-117980.html#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*(
  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))))
     )
  )+
  NPV-G(P)*G(R)
)))))

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))))))+NPV-G(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

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - rprosperi - 02-06-2022 01:37 PM

(02-06-2022 10:59 AM)Werner Wrote:  [snip]
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

Impressive equation! Smile

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.

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Didier Lachieze - 02-06-2022 02:13 PM

(02-06-2022 01:37 PM)rprosperi Wrote:  Impressive equation! Smile

+1 !!!

I’ve tried to copy/paste it in Plus42 on my phone and it says "Invalid Equation", due to some added black square character.

[Image: mini_MKIqNb-Screenshot-20220206-151748.jpg]

It would be nice if equations could be exported/imported as programs with raw files.

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-06-2022 02:26 PM

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

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Thomas Okken - 02-06-2022 05:37 PM

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, zero-width space, which Plus42 didn't handle. I added code to deal with this, and other zero-width and whitespace characters as well.

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-08-2022 05:08 PM

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)*(
  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))))
     )
  )+
  NPV-G(P)*G(R)
))

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))))))+NPV-G(P)*G(R)))

What I have learned about equations..
  • always remember you're writing an equation. This means you have to write it like
    0*(internal calculations) + equation
  • write the equation so that it can be solved directly - if possible.
  • S() tests which variable you're solving for. That way you can combine several equations into one.
  • L(var:expr) and G(var) are LET and GET respectively and are the local variable handling.
    So, to do
    X := 0; -> L(X:0)
    X := X+1; -> L(X:G(X)+1)
    The result of an L() instruction is the evaluation of the expression. So to set A, B and C to 0, you can write
    L(A:L(B:L(C:0)))
  • the order of the variables in the solver menu depends on the order in which they show up in the equation; hence the first line, which does nothing useful, except setting that order.
  • summing: I used Σ as a simplified FOR loop mostly, and since it is in the 0* section anyway it doesn't matter
for this equation specifically:
  • all Cash Flows are discounted back to Present Values using
    PV := PV*SPPV(%,Nj) + CFj*USPV(%,Nj)
    For Nj=1 this amounts to
    PV := PV/I + CF/I with I = 1+%/100
    Since CF0 is part of the CF matrix (and the corresponding Nj may be >1 !) this means that the calculated PV is off by a factor 1/I, and the true value is thus PV*I.
  • apart from this correction, Future Values must also be multiplied by I^N where N=(sum of Nj)-1
  • the actual equation for MIR% and FMR% is ((FV/PV)^1/N - 1)*100. Using the calculated PV and FV, that becomes
    ((FV*R^(N+1)/(PV*S))^(1/N)-1)*100
  • since NPV is written as a solver equation, you must explicitly set NPV to zero before calculating the Internal rate of Return IRR%

Cheers; Werner

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - rprosperi - 02-09-2022 03:39 AM

If you're not familiar with this book Werner, you definitely will want to read it:

Step-by-Step Solutions: Technical Applications (27S/19B)

Don't let the ho-hum 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.

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-09-2022 06:32 AM

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

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Steve Simpkin - 02-09-2022 10:01 AM

(02-09-2022 03:39 AM)rprosperi Wrote:  If you're not familiar with this book Werner, you definitely will want to read it:

Step-by-Step Solutions: Technical Applications (27S/19B)

Don't let the ho-hum 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.

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/thread-1580-post-149679.html#pid149679

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-09-2022 10:54 AM

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*(
  L(N:MROWS(CF))+
  L(M:MCOLS(CF))+
  IRR%+NPV+
  L(P:0)+
  L(R:1+IRR%/100)+
  IF(G(M)=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))))
  )
)+
G(NPV)-G(P)*G(R)

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

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Thomas Okken - 02-09-2022 01:04 PM

(02-09-2022 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 un-transformed 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.)

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-09-2022 02:36 PM

(02-09-2022 01:04 PM)Thomas Okken Wrote:  like putting something like L(COUNT:G(COUNT)+1) in the equation
Thought of that, too, so I did
Code:
NPVIRR:0*(
  L(N:MROWS(CF))+L(M:MCOLS(CF))+IRR%+NPV+
  L(P:0)+L(R:1+IRR%/100)+
  IF(G(M)=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))))
  )
)+
G(NPV)-G(P)*G(R)+0*L(D:D+1)
Initialize D to zero, and it will either show D=1 for direct solving, or D>1 for iterative calls.
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

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Thomas Okken - 02-09-2022 04:28 PM

(02-09-2022 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 top-level = sign, so if you have a =0, just leave it off, and otherwise, change it to a subtraction.

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-09-2022 04:51 PM

Thanks, Thomas! Will try that out ;-)
Werner

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-17-2022 12:41 PM

My current version:
  • NPV, IRR%, MIR%, FMR% without bracket amount (coming, but perhaps not soon..)
  • Compatible with legacy solvers (17B,19B), or so I think (if you take out the parameter list in the first line).
  • Direct solution for all but IRR%
  • Single sweep over the CashFlows
  • Separate code for Nj=1
  • CF0 may be positive, and N0 does not need to be equal to 1

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)*100-IF(S(MIR%):MIR%:FMR%)
:
  G(P)*G(S)-NPV
))

Cut-and-Paste:
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)*100-IF(S(MIR%):MIR%:FMR%):G(P)*G(S)-NPV))

Cheers, Werner

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - rprosperi - 02-17-2022 01:19 PM

Really nice Werner Smile, 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 as-is 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.

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Vincent Weber - 02-17-2022 01:22 PM

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 0-based, 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:J-1:1:#T(CF:L))))))-G(Z)+IF(S(IRR%):G(Z):NPV-G(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

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Werner - 02-17-2022 02:40 PM

Aha, someone else's equation and coding style ;-)
  • I see you keep each 'segment' zero by subtracting the result again.
  • A thing I noticed in all Cash Flow equations I've seen so far is that no-one 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 your variables will be solved iteratively. With Plus42's blinding speed, this probably does not make a difference ;-)

Thanks for the example, Werner

RE: (Plus42) Cash Flow Analysis: NPV, IRR%, MIR%, FMR% - Vincent Weber - 02-17-2022 03:56 PM

Hi Werner,

(02-17-2022 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 no-one 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 Smile
Quote:[*] All your variables will be solved iteratively. With Plus42's blinding speed, this probably does not make a difference ;-)
[/list]
Yes indeed, this is what I wanted as explained, and yes, with Plus42 you don't have to worry about speed Smile
Quote:Thanks for the example, Werner
You're most welcome. It is always a pleasure to discuss different approaches Smile[/quote]