Modified Internal Rate of Return
Modified internal rate of return (MIRR) is a capital budgeting tool which allows a project cash flows to grow at a rate different than the internal rate of return.
Internal rate of return is the rate of return at which a project's net present value (NPV) is zero. MIRR is similar to IRR in that it also causes NPV to be zero. However, unlike IRR, it doesn't assume that net cash flows grow at the IRR. This is how MIRR addresses the most significant flaw with the IRR approach i.e. that it overstates the return on a project because the IRR 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
In case of independent projects, projects whose MIRR is greater the project's hurdle rate should be accepted.
In case of mutually exclusive projects, the project with higher MIRR should be preferred.
Formula
In order to calculate MIRR, we first need to find future value of all cash inflows at the end of the project using an appropriate reinvestment rate, calculate the present value of all cash outflows at the relevant discount rate and then use the following formula to work out MIRR:
MIRR = (FVCI/PVCO)(1/n) - 1
Where MIRR is the modified internal rate of return, FVCI is the sum of future values of all net cash flows at the end of the project, PVCO is the initial investment, and n is the number of periods
Understanding the Math
Let's learn how to derive the above equation. We start with the definition of MIRR. MIRR can be defined as the rate of return at which the future value of net cash inflows compounded at the the reinvestment rate equals the present value of cash outflows worked out at appropriate discount rate.
The definition can be written in algebraic form as follows:
PVCO × (1 + MIRR)n = FVCI
Dividing both sides by PVCO:
| (1 + MIRR)n = | FVCI |
| PVCO |
Taking n-th root of both sides and subtracting 1 from each, we get:
MIRR = (FVCI/PVCO)(1/n) − 1
Since cash outflows mostly occur at time 0, PV of cash outflows mostly equals initial investment. This simplifies the formula for MIRR:
MIRR = (FVCI/I)(1/n) − 1
Where I is the initial investment.
Calculation using Spreadsheet
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.
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:
| Year | Airport | Motorway |
|---|---|---|
| 0 | (12,000,000) | (18,000,000) |
| 1 | 6,000,000 | 8,000,000 |
| 2 | 8,000,000 | 10,000,000 |
| 3 | 4,000,000 | 10,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.
Formula Approach
The following table calculates the equivalent terminal cash flow for both projects:
| Airport Project | ||||
|---|---|---|---|---|
| Year | Cash Flows | FV Factor | Formula | Terminal Value |
| 0 | (12,000,000) | |||
| 1 | 6,000,000 | 1.16640 | =(1+8%)^(3-1) | 6,998,400 |
| 2 | 8,000,000 | 1.08000 | =(1+8%)^(3-2) | 8,640,000 |
| 3 | 4,000,000 | 1.00000 | =(1+8%)^(3-3) | 4,000,000 |
| 19,638,400 | ||||
| Motorway Project | ||||
|---|---|---|---|---|
| Year | Cash Flows | FV Factor | Formula | Terminal Value |
| 0 | (18,000,000) | |||
| 1 | 8,000,000 | 1.16640 | =(1+8%)^(3-1) | 9,331,200 |
| 2 | 10,000,000 | 1.08000 | =(1+8%)^(3-2) | 10,800,000 |
| 3 | 10,000,000 | 1.00000 | =(1+8%)^(3-3) | 10,000,000 |
| 30,131,200 | ||||
MIRR - Airport
= ($19,638,400/$12,000,000)(1/3) − 1
= 17.84%
MIRR - Motorway
= ($30,131,200/$18,000,000)(1/3) − 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.
by Obaidullah Jan, ACA, CFA and last modified on