The Modified Internal Rate of Return indicates the profitability of an investment and therefore
is commonly used in business, when choosing between investmentments.
This calculation uses a schedule of payments (including an initial investment, along with the
net income payments), to calculate the compounded return, assuming the Net Present Value of the
investment is zero.
The difference between the Modified Internal Rate of Return (MIRR) and the Internal Rate of Return
(IRR) is that, in its calculation, the MIRR considers the initial cost of the investment and also
the interest received on the reinvestment of cash, whereas the IRR does not consider these.
Further information on the Modified Internal Rate of Return can be found on the
Wikipedia MIRR page(link opens in a new window)
Search this site:
Custom Search
Basic Description
The Excel MIRR function returns the Modified Internal Rate of Return for a supplied series of periodic cash flows
(ie. a set of values, which includes an initial investment value and a series of net income values).
The format of the function is :
MIRR( Values, Finance_rate, Reinvest_rate )
Where the arguments are as follows :
Values
-
A reference to a range of cells containing the series of cash flows (investment and net income values)
that occur at regular periods
These must contain at least one negative value (representing payment) and
at least one positive value (representing income)
Finance_Rate
-
The interest rate paid on the money used in the cash flows
Reinvest_Rate
-
The interest rate paid on the reinvested cash flows
Example
In the spreadsheet below, the cashflow for an investment is shown in cells B2 - B7.
The initial investment of $100 is shown in cell B2 and the net income over 5 periods
is shown in cells B3 - B7.
The MIRR function in cell D2 shows the calculation of the Modified Internal Rate of Return after 3 years
and the function in cell D4 shows the Modified Internal Rate of Return after 5 years. The formulas for the
equations are shown in the spreadsheet on the left and the results are shown in the spreadsheet
on the right.
Formulas
Results
Further information and examples of the Excel Mirr function can be found on the
Microsoft Office website.
Common Errors
If you get an error from the Excel Mirr function this is likely to be one of the following :
#DIV/0!
-
Occurs if the supplied Values array doesn't contain at least one negative and at least one positive value
#VALUE!
-
Occurs if any of the supplied arguments are not recognised as numbers by Excel
