Excel MIRR Function
MIRR is an Excel function that calculates the modified internal rate of return—a variant of internal rate of return which lets us specify a reinvestment rate lower than the internal rate of return.
Internal rate of return (IRR) is calculated such that the cash flows of a project are assumed to be reinvested at the IRR. This is problematic because a company might not be able to find appropriate investment opportunities paying at least as much as the IRR right on the day the cash flows occur. Modified internal rate of return is calculated to account for a lower reinvestment rate.
Syntax
MIRR syntax is:
MIRR(values, finance_rate, reinvest_rate)
Values refers to the schedule of cash flows including the initial investment with a negative sign at the start of the stream.
Finance_rate is the cost of money used to finance the initial investment.
Reinvest_rate is the rate of return that can be earned on net cash flows that become available over the course of the project
Example
Let's say you have a project with the following cash flows:
| Year | 1-Jan-17 | 31-Dec-17 | 30-Nov-18 | 15-Jan-20 | 31-Jan-21 | 31-Dec-21 |
|---|---|---|---|---|---|---|
| Net Cash Flows | 2,000 | 2,400 | 3,200 | 3,000 | 5,000 | |
| Terminal Value | 2,000 | |||||
| Project Cash Flows | −10,000 | 2,000 | 2,400 | 3,200 | 3,000 | 7,000 |
Please note that we receive $2,000 on 31 December 2017. Now, what do we do with that money? Do we have another investment opportunity at hand to use that money and earn a return as high as 17.87%? In many situations is unlikely to get a reinvestment opportunity so quickly and that too at the high return the money was already earning. This exposes the biggest problem with IRR tool—that it inherently overstates the return by incorporating a higher reinvestment rate.
But don’t worry, modified internal rate of return or MIRR attempts to fix this problem.
You can see that lower reinvestment rate assumption has reduced the internal rate of return estimate.
by Obaidullah Jan, ACA, CFA and last modified on
