*** Here they are: MIRR(Modified Internal Rate of Return) programs!! *** Message #3 Posted by W. Bruce Maguire II on 28 Nov 2001, 9:53 p.m., in response to message #1 by David Martin
David (and anyone else who might be interested):
Here are two programs. The first one is a program to calculate Modified
Internal Rate of Return (MIRR) and the second is a bigger program that
calculates Financial Management Rate of Return (FMRR) as well as MIRR and IRR
for comparison purposes. Both programs are my modifications to a program to
calculate FMRR and IRR that is listed in the Pocket Guide for the HP-19BII.
The HP-17BII has all the same programming capability as the
19BII, and therefore these programs work exactly the same on both machines.
For the MIRR program, you can enter the P/YR, the safe and risk interest rates
(APR), and then press "$IN" to see the NPV of the negative cash flows (at the
safe rate) and press "$OUT" to see the NFV of all the positive cash flows (at
the risk rate). Pressing the "MIR%" button will return (after a short while)
the MIRR.
For the FMRR program, the $IN and $OUT calculations are for the FMRR
calculation, not the MIRR values. In this program, FMRR,
MIRR, and IRR may be returned after hitting the "MORE" button to get to the
next page of menu buttons. Note, that if you need to give the IRR calculation
initial guesses, you can key in guess #1, hit the IRR key, key in guess #2, hit
the IRR key, and then hit the IRR one last time to start the search for a
solution (as explained in the manual in the solver section---I think).
In the following listings, there are three symbols that I use special notations to represent.
1) The multiplication symbol is "_mult_"
2) The division symbol is "_div_"
3) The summation symbol is "_sum_"
Note #1: The cash flow which is used for the calculations must be entered
prior to "CALC"ing this formula, and it must be named "$". If you really
prefer another name for some reason, you can always change it in the listing.
Wherever "FLOW($:...)" or "#T($:...)" or "SIZEC($:...)" occurs (the cash flow
operations), just change the "$" to your preferred name.
Note #2: There is a variable used for the number of periods per
year---"P/YR"---the "/" symbol is found in the misc. menu; it is
not the divide key! Also, the risk and safe interest rates are
entered as percent per year (not per period), which is why the number of
periods per year is needed.
Note #3: This listing is "exploded" (using my own technique for formatting)
for ease of understanding and reading. Spaces should not be used when typing
the formula in---except in one important case. For "OR" (and "AND")
operations, you must have a space on either side of the
operator, or the calculator will be very confused (and wrong!).
Oh, one last thing. The bigger (FMRR) program assumes that the first cash flow
is negative for the MIRR calculation. In the smaller program, since I was
making more modifications, I handle (I think) the case of the initial cash flow
being positive (which would be a strange investment!).
-------------------------------------------------------------------
MIRR%:
L(N:
L(MP:
L(MF:
0
)
)
)_mult_
(
IF(P/YR=0:
L(P/YR:
1
)
:
0
)+
L(S:
SAF%_div_P/YR
)+
L(R:
RSK%_div_P/YR
)+
_sum_(
G:
SIZEC($):
1:
-1:
L(C:
FLOW($:G)
)+
L(T:
#T($:G)
)+
L(MP:
G(MP)_mult_SPPV(G(S):G(T))
)+
IF(G(C)<=0:
L(MP:
G(MP)+
G(C)_mult_USPV(G(S):G(T))
)
:
L(MF:
G(MF)+
SPFV(G(R):G(N))_mult_
G(C)_mult_USFV(G(R):G(T))
)
)+
L(N:
G(N)+G(T)
)
)+
L(C:
FLOW($:0)
)+
IF(G(C)<0:
L(MP:
G(MP)+G(C)
)
:
L(MF:
G(MF)+
SPFV(G(R):G(N))_mult_
G(C)
)
)
)+
IF(S($IN):
$IN-G(MP)
:
IF(S($OUT) OR S(P/YR):
$OUT-G(MF)
:
G(MP)_mult_
SPFV(MIR%_div_P/YR:G(N))+
G(MF)
)
)
-------------------------------------------------------------------
FMRR%:
L(N:
L(F:
L(M:
L(W:
L(MP:
L(MF:
0
)
)
)
)
)
)_mult_
(
IF(P/YR=0:
L(P/YR:
1
)
:
0
)+
L(I:
G(IRR%)_div_P/YR
)+
L(S:
SAF%_div_P/YR
)+
L(R:
RSK%_div_P/YR
)+
_sum_(
G:
SIZEC($):
1:
-1:
L(C:
FLOW($:G)
)+
L(T:
#T($:G)
)+
L(X:
G(M)_mult_SPPV(G(S):G(T))+
G(C)_mult_USPV(G(S):G(T))
)+
L(MP:
G(MP)_mult_SPPV(G(S):G(T))
)+
IF(G(C)<=0:
L(MP:
G(MP)+
G(C)_mult_(G(S):G(T))
)+
L(M:
G(X)
)
:
L(MF:
G(MF)+
SPFV(G(R):G(N))_mult_
G(C)_mult_USFV(G(R):G(T))
)+
IF(G(M)+G(C)>0:
L(W:
G(W)+
SPFV(G(R):G(N))_mult_
(
G(M)+
G(C)_mult_USFV(G(R):G(T))
)
)+
L(M:
G(X)
)
:
IF(G(X)<=0:
L(M:
G(X)
)
:
L(P:
IP(
LN(
-G(M)_mult_G(S)_div_100_div_G(C)+1
)_div_
LN(
1+G(S)_div_100
)
)
)+
L(W:
G(W)+
SPFV(G(R):G(N)+G(P))_mult_
(
G(C)_mult_USFV(G(R):G(T)-G(P))+
G(M)_mult_SPPV(G(S):G(P))+
G(C)_mult_USPV(G(S):G(P))
)
)+
L(M:
0
)
)
)
)+
L(F:
G(F)+
G(C)_mult_
USFV(G(I):G(T))_mult_
SPFV(G(I):G(N))
)+
L(N:
G(N)+G(T)
)
)
)+
IF(S($IN):
$IN-G(M)-FLOW($:0)
:
IF(S($OUT) OR S(P/YR):
$OUT-G(W)
:
IF(S(FMR%) OR S(SAF%) OR S(RSK%):
(G(M)+FLOW($:0))_mult_
SPFV(FMR%_div_P/YR:G(N))+
G(W)+
0_mult_(SAF%+RSK%)
:
IF(S(MIR%):
FLOW($:0)+
G(MP)+
G(MF)_mult_SPPV(MIR%_div_P/YR:G(N))
:
FLOW($:0)_mult_
SPFV(IRR%_div_P/YR:G(N))+
G(F)
)
)
)
)
Let me know if there are any bugs in my listing, or if you have any problems.
Enjoy,
Bruce.
|