MIRR

Modified internal rate of return (MIRR) is an improved version of the internal rate of return (IRR) which allows the net cash flows to grow at a different (i.e. lower) reinvestment rate. It addresses the most significant flaw with the IRR approach i.e. that it overstates the return on a project because the calculation inherently assumes that the project net cash flows are reinvested at the IRR which is rarely the case because alternate reinvestment opportunities are not readily available.

Decision rule: projects with MIRR greater the project's hurdle rate should be accepted; while in case of mutually exclusive projects, the project with higher MIRR should be preferred.

Formula

We can calculate MIRR using either Excel MIRR function or the manual calculation approach.

Excel MIRR syntax is: MIRR(values, finance_rate, reinvest_rate).

Values represent the array of the project's cash flows, finance_rate is the relevant cost of capital, and reinvest_rate is the rate of return at which the project's cash flows are expected to be reinvested.

In the manual approach, we need to find future values of all project net cash flows excluding the initial investment at the end of the project based on the reinvestment rate and then use the following equation to solve for MIRR:

$$ Modified\ Internal\ Rate\ of\ Return\ (MIRR) \\ =\sqrt[n]{\frac{FV\ of\ Net\ Cash\ Inflows\ at\ r_r}{−PV\ of\ Net\ Cash\ Outflows\ at\ r_f}} −1 $$

Where n is the number of periods, rr is reinvesting rate and rf is financing rate.

Let's learn how to derive the above equation. MIRR is the rate that equates the project net cash flows with initial investment. Let’s say the project’s terminal date is our point of comparison. The value of initial investment at the project end date is given as follows:

$$ FV\ of\ Initial\ Investment=Initial\ Investment\times{(1+MIRR)}^n $$

This must equal the future value of net cash flows calculated based on reinvestment rate. This can be expressed mathematically as follows:

$$ FV\ of\ Initial\ Investment=FV\ of\ Net\ Cash\ Flows $$

$$ Initial\ Investment\ \times{(1+MIRR)}^n=FV\ of\ Net\ Cash\ Flows $$

By rearranging the above equation, we get the equation for MIRR:

$$ {(1+MIRR)}^n=\frac{FV\ of\ Net\ Cash\ Flows}{Initial\ Investment} $$

$$ 1+MIRR=\sqrt[n]{\frac{FV\ of\ Net\ Cash\ Flows}{Initial\ Investment}} $$

$$ MIRR=\sqrt[n]{\frac{FV\ of\ Net\ Cash\ Flows}{Initial\ Investment}}-1 $$

Example

You are an assistant to Gökhan Erdogan, the corporate finance director at BTC, a Turkish civil engineering firm. Two of the company's recent bids are accepted. The first relates to construction of a new airport in Izmir. The second relates to construction of a motorway connecting Izmir with Ankara, the capital. Both the projects are expected to take 3 years. The applicable finance rate is 10% and the project's cash flows in Turkish Lira are given below:

YearAirportMotorway
0(12,000,000)(18,000,000)
16,000,0008,000,000
28,000,00010,000,000
34,000,00010,000,000

The company submitted bids for both projects because both had positive net present values.

Alev Toprak, the CEO, has asked Gökhan to recommend which project the company should accept. Alev is a fan of the IRR approach. Gokhan, on the other hand, is worried about the shortcomings of the IRR approach. He believes that the economy might slow down a little in next few years and a lower reinvestment rate should be factored in. He asked you to calculate MIRR for both the projects.

You double-check whenever and wherever possible: so you decided to calculate the MIRR using the manual formula approach and then verify the results using MS Excel MIRR function.

Manual approach

The following table calculates the equivalent terminal cash flow for both projects:

Airport Project
YearCash FlowsFV FactorFormulaTerminal Value
0(12,000,000)
16,000,0001.16640=(1+8%)^(3-1)6,998,400
28,000,0001.08000=(1+8%)^(3-2)8,640,000
34,000,0001.00000=(1+8%)^(3-3)4,000,000
19,638,400
Motorway Project
YearCash FlowsFV FactorFormulaTerminal Value
0(18,000,000)
18,000,0001.16640=(1+8%)^(3-1)9,331,200
210,000,0001.08000=(1+8%)^(3-2)10,800,000
310,000,0001.00000=(1+8%)^(3-3)10,000,000
30,131,200

$$ MIRR - Airport \\ =\sqrt[3]{\frac{19,638,400}{12,000,000}} −1 = 17.84\% $$

$$ MIRR - Motorway \\ =\sqrt[3]{\frac{30,131,200}{18,000,000}} −1 = 18.74\% $$

The motorway project should be preferred based on MIRR approach.

MIRR in Excel

You can calculate the modified internal rate of return using the Excel MIRR function. In the above example, we should enter the complete stream of cash flows inclusive of the initial invesetment in the value argument and use 10% and 8% in the finance rate and reinvest rate arguments.

Written by Obaidullah Jan, ACA, CFA and last modified on