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

[Werner]

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