Excel MIRR Function

by Obaidullah Jan, ACA, CFA

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:

Year1-Jan-1731-Dec-1730-Nov-1815-Jan-2031-Jan-2131-Dec-21
Net Cash Flows2,0002,4003,2003,0005,000
Terminal Value2,000
Project Cash Flows−10,0002,0002,4003,2003,0007,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.

Excel MIRR Function

You can see that lower reinvestment rate assumption has reduced the internal rate of return estimate.