The calculation of Modfified Internal Rate of Return (MIRR) is similar to the technique adopted for calculating Internal Rate of Return (IRR.) The MIRR of a project with a single terminal payment is same as the IRR.
Assume a two-year project with an initial outlay of $195 and a cost of capital of 12 percent providing returns of $121 in the first year and $131 in the second year.
The IRR of the project is = 18.66%
MIRR assumes that positive cash flows find reinvestment at the 12 percent cost of capital. So the future value of the positive cash flows in this example is:
Future Value of $121+$131 at 12 percent annual interest = $121*(1+12%)+$131 = $266.52
Dividing this future value of the cash flows with the present value of the initial outlay ($195) provides the geometric return for the period. The root of two of the result (since the number of years is two. If the number of years is three, the calculation takes the root of 3 or cube root) provides the MIRR.
=sqrt($266.52/195) -1 = 16.91% MIRR
The MIRR of 16.91 percent is materially lower than the IRR of 18.66 percent.
MIRR can also be calculated using a built-in formula in MS-Excel.
=MIRR( cell range that lists the cash flow, rate of cost of capital, reinvestment_rate )
- Enter the periodic cash flows (-195, 121, 131) in column A.
- Enter the cost of capital (12%) in Column B,
- Enter the Reinvested Rate of Return in Column C. The Reinvested Rate of Return is the expected returns expected when investing the amount elsewhere. Assume this as same as cost of capital (12%).
- In any other cell, type the formulae –MIRR(A1:A3,B1,C1).
The answer lists MIRR as 16.91 percent, which is lower that the IRR of 18.66 percent.
IRR very often gives too optimistic picture of the potential of the project, while the MIRR gives a more realistic evaluation of the project.
Image Credit: N Nayab