Modified Internal Rate of Return

Modified internal rate of return (MIRR) is an improved version of the internal rate of return (IRR) approach to capital budgeting decisions. It does not require the assumption that the project cash flows are reinvested at the IRR; rather, it factors in a discrete reinvestment rate into the model.

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

There is built in function in Microsoft Exel which can be used to calculate the MIRR:

MIRR = 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.

The manual approach to calculation of MIRR involves finding the sum of terminal values of all the net cash flows (other than initial investment) and then using the following equation to solve for MIRR:

$MIRR = Sum of Terminal Cash Flows Initial Investment n - 1$

Where n is the number of periods.

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 = 19,638,400 12,000,000 3 - 1 = 17.84%$ $MIRR Motorway = 30,131,200 18,000,000 3 - 1 = 18.74%$

The motorway project should be preferred based on MIRR approach.

Microsoft Excel Function

MIRR function is used to calculate MIRR for both projects as shown in the spreadsheet below. It uses finance rate of 10% and reinvestment rate of 8%.

Written by Obaidullah Jan