Modified internal rate of return

From CEOpedia
Modified internal rate of return
See also


Modified internal rate of return (MIRR) - is a measure of investment profitability that compounds all positive cash flows of a project forward to its final year at a stated reinvestment rate, discounts all negative cash flows back to the start at a stated finance rate, and then finds the single annual rate that turns the second amount into the first[1]. The result is one percentage per project, like the ordinary internal rate of return (IRR), but without the hidden assumption that cash is reinvested at the IRR itself.

Managers like percentages. A board member who struggles with "NPV of 2.65 million" will instantly grasp "this project earns 15% a year". That preference explains why IRR stayed so popular for decades, and why the modified version was invented: to keep the convenient percentage while removing the two defects that make plain IRR misleading. It's a patch, not a new theory. But a useful one.

Background

The trouble with IRR was spotted early. In 1955 James Lorie and Leonard Savage showed that a project whose cash flows change sign more than once can have two or more internal rates of return, or none at all[2]. Three years later Jack Hirshleifer, working from Irving Fisher's theory of interest, argued that the present value rule, not IRR, is the correct criterion when the two disagree[3].

Textbooks kept teaching IRR anyway. Practitioners kept using it. When John Graham and Campbell Harvey surveyed American chief financial officers at the end of the 1990s, 75.7% said they always or almost always used IRR, practically the same share as for net present value (74.9%)[4]. So the question was never "how do we get rid of IRR?" but "how do we make a percentage measure behave?"

One answer came from Steven Lin, who in 1976 published "The modified internal rate of return and investment criterion" in The Engineering Economist and set out the terminal-value logic that MIRR still follows today[5]. Similar terminal-value ideas had circulated in the literature before him. Spreadsheets did the rest. Once Microsoft Excel shipped a ready MIRR function, the measure moved from journals into everyday investment planning.

Formula of modified internal rate of return

The general formula is

\[ MIRR = \left( \frac{FV_{positive}}{PV_{negative}} \right)^{\frac{1}{n}} - 1 \]

where:

  • \( FV_{positive} \) - the future value, at the end of year n, of all positive cash flows compounded at the reinvestment rate r
  • \( PV_{negative} \) - the present value, at year 0, of all negative cash flows discounted at the finance rate f (taken as a positive number)
  • n - the number of periods in the project's life

Written out in full:

\[ MIRR = \left( \frac{\sum_{t=0}^{n} CF_t^{+} (1+r)^{n-t}}{\sum_{t=0}^{n} \left| CF_t^{-} \right| (1+f)^{-t}} \right)^{\frac{1}{n}} - 1 \]

Two rates, then. The finance rate is what the firm pays for the money it puts in, usually the cost of money or borrowing rate. The reinvestment rate is what the firm can realistically earn on the cash the project throws off before the end of its life. Many analysts simply set both equal to the firm's cost of capital or required rate of return; Excel lets them differ.

Steps of the calculation

  1. List the project's cash flows year by year, with outflows negative.
  2. Discount every negative flow to year 0 at the finance rate and add them up.
  3. Compound every positive flow to year n at the reinvestment rate and add them up (the "terminal value").
  4. Divide the terminal value by the present value of outflows.
  5. Take the n-th root and subtract 1.

That's it. No trial and error, no iteration. Ordinary IRR has to be found by searching for the rate that sets NPV to zero; MIRR has a closed-form solution, so it can be checked on a pocket calculator.

Why MIRR fixes the problems of IRR

The reinvestment assumption

IRR is the discount rate at which NPV equals zero. Mathematically, that is equivalent to saying every interim cash flow earns the IRR until the project ends. For a project with an IRR of 40% this is fantasy. A firm whose cost of capital is 10% rarely finds a steady stream of new 40% opportunities in which to park the incoming cash. Kierulff makes exactly this point in arguing that MIRR is the better measure for practitioners[1]. (Some finance scholars dispute whether IRR truly "assumes" reinvestment, or whether the rate simply describes the project's own return on capital still tied up in it.)

Whatever the semantic debate, the practical effect is clear. IRR overstates the attractiveness of projects with large early inflows and high rates. MIRR replaces the implied rate with an explicit one that management chooses, which pulls inflated figures back towards reality.

Multiple IRRs

Take a project with cash flows of −100, +230 and −132 (years 0, 1 and 2). Some mining, construction and decommissioning projects look like this: an investment, a profitable period, then a big clean-up bill. NPV equals zero at both 10% and 20%. Which is the IRR? Both. Neither is meaningful on its own[2].

MIRR has a single answer. With a finance rate of 10% and a reinvestment rate of 12%:

  • PV of outflows = 100 + 132 / 1.10² = 100 + 109.09 = 209.09
  • FV of inflows = 230 × 1.12 = 257.60
  • MIRR = (257.60 / 209.09)^(1/2) − 1 = 0.1100, about 11.0%

One sign change in the "modified" stream (one negative lump at the start, one positive lump at the end) guarantees one root. Hazen proposed a different route out of the multiple-root trap, showing that each IRR can be interpreted against a particular investment stream[6], but MIRR remains the simpler fix for everyday use.

Ranking conflicts

IRR can rank mutually exclusive projects differently from NPV. MIRR, when both projects have the same size and life and the reinvestment rate equals the cost of capital, ranks them the same way as NPV. Not always, but in the situations most firms face, it removes the conflict.

Example of modified internal rate of return

The following example is hypothetical.

A manufacturer considers two mutually exclusive investment projects, each requiring an outlay of 10,000 at year 0. The firm's cost of capital is 10%, which it uses as the reinvestment rate. Its borrowing (finance) rate is 8%.

Year Project A cash flow Project B cash flow
0 −10,000 −10,000
1 2,000 6,500
2 3,500 4,000
3 4,000 2,000
4 4,500 1,000
5 3,000 500

Project A. Compound each inflow to year 5 at 10%:

  • 2,000 × 1.10⁴ = 2,928.20
  • 3,500 × 1.10³ = 4,658.50
  • 4,000 × 1.10² = 4,840.00
  • 4,500 × 1.10 = 4,950.00
  • 3,000 × 1 = 3,000.00

Terminal value = 20,376.70. The only outflow sits at year 0, so its present value is 10,000 whatever the finance rate (the 8% doesn't matter here; it would if the project needed more money later).

\[ MIRR_A = \left( \frac{20\,376.70}{10\,000} \right)^{\frac{1}{5}} - 1 = 0.1530 = 15.30\% \]

Project B. Same procedure: 6,500 × 1.10⁴ = 9,516.65; 4,000 × 1.10³ = 5,324.00; 2,000 × 1.10² = 2,420.00; 1,000 × 1.10 = 1,100.00; plus 500. Terminal value = 18,860.65, and MIRR_B = (1.886065)^(1/5) − 1 = 13.53%.

Now the comparison:

Measure Project A Project B Preferred
IRR 19.11% 20.24% B
MIRR (reinvestment 10%) 15.30% 13.53% A
NPV at 10% 2,652.33 1,710.98 A

IRR picks B. Why? B returns most of its money in the first two years, and IRR silently assumes that money keeps earning about 20%. Once the cash is reinvested at a realistic 10%, B's advantage evaporates. MIRR and NPV both pick A, and NPV says by how much: A creates roughly 941 more in present value.

Both MIRRs are also lower than the corresponding IRRs. That always happens when the IRR exceeds the reinvestment rate. A quick consistency check: discounting A's terminal value back five years at 10% gives 20,376.70 / 1.61051 = 12,652.33, and subtracting the outlay of 10,000 gives exactly A's NPV.

MIRR in Excel

Excel's syntax is

=MIRR(values, finance_rate, reinvest_rate)

According to Microsoft's documentation, values must contain at least one negative and one positive number (otherwise the function returns #DIV/0!), the finance rate is "the interest rate you pay on the money used in the cash flows" and the reinvestment rate is "the interest rate you receive on the cash flows as you reinvest them"[7]. The first value is treated as occurring at period 0 and the order of cells is taken as the order of periods. Text, logical values and empty cells are ignored, but zeros count. Leave a blank where a zero belongs and the timing silently shifts.

If k is the number of values in the range, Excel's exponent is \( \frac{1}{k-1} \), which is the same as 1/n in the formula above, because a five-year project has six cash flows.

For Project A typed into cells B2:B7, =MIRR(B2:B7, 8%, 10%) returns 15.30%, and =IRR(B2:B7) returns 19.11%. Google Sheets and LibreOffice Calc use the same function name and argument order.

Comparison of IRR, MIRR and NPV

Feature IRR MIRR NPV
Output Percentage Percentage Money amount
Reinvestment rate Implicitly the IRR itself Chosen explicitly Implicitly the discount rate
Number of solutions Can be several or none with non-conventional flows Exactly one Exactly one
Calculation Iterative search Closed formula Closed formula
Ranks mutually exclusive projects Can conflict with NPV Agrees with NPV for equal size and life The value-maximising benchmark
Reflects project scale No No Yes
Ease of communication High High Moderate

Advantages of modified internal rate of return

  • It states the reinvestment rate openly, so it can be challenged, stress-tested or set by company policy.
  • One project, one answer. The multiple-IRR problem disappears.
  • It's usually more realistic than IRR for projects whose IRR is far above the cost of capital.
  • With equal project size and life it ranks alternatives in line with NPV.
  • It still produces a percentage, which can be compared directly with the hurdle rate or with return on investment figures.
  • The arithmetic is transparent; no iterative solver is needed.
  • Two separate rates allow a firm to reflect the gap between what it pays for funds and what it earns on them.

Limitations of modified internal rate of return

  • It's still a rate, not an amount. A 30% MIRR on a 1,000 project adds less value than a 15% MIRR on a 1,000,000 project. For choosing between projects of different size, NPV or the profitability index is needed.
  • The result depends heavily on the reinvestment rate, and picking that rate is a judgement call. Change it and the ranking can change.
  • For projects of different lengths, MIRR can still disagree with NPV, because the compounding horizon differs.
  • Assuming one constant reinvestment rate for every year is itself a simplification.
  • Different conventions exist. Some authors discount only the outflows and compound only the inflows (as Excel does); others net flows within each year first. Two analysts can report slightly different MIRRs for the same project.
  • Like IRR and NPV, it is only as good as the cash flow forecasts behind it. Sensitivity analysis and scenario analysis remain necessary.
  • It is less familiar than IRR to many readers outside finance, which partly cancels its communication advantage.

Related approaches

MIRR belongs to the family of discounted cash flow investment appraisal techniques. Net present value remains the theoretical reference point; MIRR is best understood as a way of translating an NPV-consistent result into a rate. The profitability index divides present value of inflows by the outlay, which helps under capital rationing. Simple methods such as the payback period and the accounting rate of return ignore the time value of money or part of the cash flows, but are still used for quick screening and in feasibility studies.

In practice, a sound project evaluation uses several measures side by side: NPV for value, MIRR for a realistic rate, payback for liquidity risk. A conflict between them isn't a failure. It's information, and often the most useful part of the cash flow analysis.

References

Footnotes

  1. ↑ Kierulff H., MIRR: A better measure, Business Horizons, 2008
  2. ↑ Lorie J.H., Savage L.J., Three problems in rationing capital, The Journal of Business, 1955
  3. ↑ Hirshleifer J., On the theory of optimal investment decision, Journal of Political Economy, 1958
  4. ↑ Graham J.R., Harvey C.R., The theory and practice of corporate finance: evidence from the field, Journal of Financial Economics, 2001
  5. ↑ Lin S.A.Y., The modified internal rate of return and investment criterion, The Engineering Economist, 1976, pp. 237-247
  6. ↑ Hazen G.B., A new perspective on multiple internal rates of return, The Engineering Economist, 2003
  7. ↑ Microsoft, MIRR function, Microsoft Support
Modified internal rate of return — recommended articles
Net present value — Required rate of return — Profitability index — Payback period — Capital rationing — Investment appraisal techniques — Project evaluation
Retrieved from CEOpedia