How to calcultate MIRR with an hp solver?
* WHY
The MIRR or modified internal rate of return is useful when the project has multiple negative cash flows, and thus multiple IRRs. It prevents also from reinvesting the cash flows at the project internal rate of return.
Basically, the MIRR is equal to the future value of positive cash flows with a risky rate divided by the present value for negative cash flows with a safe rate, at the power one divided by the number of periods considered.
No MIRR calculation is provided on an hp17b, hp200lx or hp19b. But all include a powerful solver!
* WHAT
The following formula has been developed on an hp200lx and successfully used on an hp17b.
MIRR%/100+1=
(
-SIGMA(i;0;SIZEC(mirr);1;
SPFV(
Irisk%;SIGMA(j;i+1;SIZEC(trim);1;#T
(mirr;j))
)
*USFV(Irisk%;#T(mirr;i))
*IF(FLOW(mirr;i)>0;FLOW(mirr;i);0)
)/
SIGMA(i;0;SIZEC(mirr);1;
SPPV(
Isafe%;SIGMA(j;0;i-1;1;#T(mirr;j))-1*0
)
*USPV(Isafe%;#T(mirr;i))
*IF(FLOW(trim;i)<0;FLOW(mirr;i);0)
)
)
^
(
1/SIGMA(i;0;SIZEC(mirr);1;#T(mirr;i))
)
On an hp17b, SIGMA should be replaced by the special E and the separator [;] by [:].
Entering the formula on an hp17b is painful, but worth the patience. Be very cautious on the opening and closing brackets.
No problem should arise with an hp19b.
* VARIABLES
mirr is the NAME of the CFLO list (or the name of the file where the cash flows are saved on the hp200lx)
Isafe% is the rate to borrowing, or safe rate
Irisk% is the rate for re-investing, or risky rate
MIRR% is the modified rate of return, what we are looking for
* EXAMPLE
The example provided by hp is
-180, initial cash flow
100, 5 times
-100, 5 times
0, 9 times
200, 1 time
With Isafe%=8%/12, Irisk%=13/12, MIRR%=12.0/12 very close to 12.18%/12 but equal to 12.0%/12 given by Excel.
The example provided by hp is monthly based but the rates are year based, thus the conversions /12.
I hope this helps!