What is Modified Internal Rate of Return (MIRR)?
The Modified Internal Rate of Return (MIRR) is a financial metric used to evaluate the profitability and attractiveness of investments or projects. Unlike the traditional Internal Rate of Return (IRR), MIRR addresses some fundamental problems with IRR by using more realistic assumptions about cash flow reinvestment.
MIRR assumes that positive cash flows are reinvested at the reinvestment rate (typically the firm's cost of capital or a realistic market return) and that the initial outlays are financed at the financing cost. This provides a more accurate reflection of an investment's true profitability.
The MIRR Formula
The Modified Internal Rate of Return is calculated using the following formula:
Where:
FV = Future Value of positive cash flows at reinvestment rate
PV = Present Value of negative cash flows at finance rate
n = Number of periods
FV = Sum of [Positive CF x (1 + reinvestment rate)^(n - period)]
PV = Sum of [Negative CF / (1 + finance rate)^period]
Why Use MIRR Instead of IRR?
The traditional IRR has several limitations that MIRR addresses:
1. Unrealistic Reinvestment Assumption
IRR assumes that all positive cash flows are reinvested at the IRR itself. If a project has a 30% IRR, it assumes you can reinvest all returns at 30%, which is often unrealistic. MIRR uses a specified reinvestment rate that reflects actual market opportunities.
2. Multiple IRR Problem
Projects with alternating positive and negative cash flows can produce multiple IRRs, making interpretation difficult. MIRR always produces a single, unambiguous result.
3. Scale Independence
IRR doesn't account for the scale of investment. A small project with 50% IRR isn't necessarily better than a large project with 20% IRR. MIRR, when used alongside NPV, provides better decision-making capability.
How to Calculate MIRR: Step-by-Step Example
Let's walk through a detailed example:
Investment Scenario:
- Initial Investment: $100,000 (Year 0)
- Year 1 Return: $30,000
- Year 2 Return: $35,000
- Year 3 Return: $40,000
- Year 4 Return: $45,000
- Finance Rate: 10%
- Reinvestment Rate: 12%
Step 1: Calculate Future Value of Positive Cash Flows
- Year 1: $30,000 x (1.12)^3 = $42,147.84
- Year 2: $35,000 x (1.12)^2 = $43,904.00
- Year 3: $40,000 x (1.12)^1 = $44,800.00
- Year 4: $45,000 x (1.12)^0 = $45,000.00
- Total FV = $175,851.84
Step 2: Calculate Present Value of Negative Cash Flows
- Year 0: $100,000 / (1.10)^0 = $100,000
- Total PV = $100,000
Step 3: Apply MIRR Formula
- MIRR = ($175,851.84 / $100,000)^(1/4) - 1
- MIRR = (1.7585)^0.25 - 1
- MIRR = 1.1512 - 1
- MIRR = 15.12%
MIRR vs IRR vs NPV: When to Use Each
| Metric | Best Used For | Limitations |
|---|---|---|
| NPV | Absolute value creation, comparing different-sized projects | Doesn't show return rate, requires discount rate assumption |
| IRR | Quick comparison, when reinvestment at IRR is realistic | Multiple solutions possible, unrealistic reinvestment assumption |
| MIRR | Comparing projects with different cash flow patterns | Requires two rate assumptions (finance and reinvestment) |
Interpreting MIRR Results
A project or investment is generally considered acceptable if:
- MIRR > Cost of Capital: The investment earns more than it costs to fund
- MIRR > Hurdle Rate: The investment meets the minimum required return
- Higher MIRR = Better: When comparing mutually exclusive projects of similar scale
Common Mistakes When Using MIRR
- Using the same rate for both: If finance rate equals reinvestment rate, MIRR reduces to a simpler geometric mean return. The power of MIRR comes from using different, realistic rates.
- Ignoring project scale: A 20% MIRR on $1 million is more valuable than 25% MIRR on $100,000 in absolute terms.
- Not considering NPV: MIRR should complement NPV analysis, not replace it.
- Arbitrary rate selection: Finance and reinvestment rates should reflect actual costs and opportunities.
Frequently Asked Questions
What reinvestment rate should I use?
Use a rate that reflects realistic reinvestment opportunities. Common choices include the company's weighted average cost of capital (WACC), the risk-free rate plus a premium, or the expected return on the company's typical investments.
What finance rate should I use?
Use your actual cost of borrowing or the company's cost of debt. If the project is funded by equity, you might use the cost of equity or WACC instead.
Can MIRR be negative?
Yes, MIRR can be negative if the future value of positive cash flows is less than the present value of negative cash flows. This indicates an unprofitable investment.
Why is my MIRR lower than IRR?
MIRR is typically lower than IRR when the reinvestment rate is lower than the IRR. This is the more realistic scenario, as IRR often implies unrealistically high reinvestment returns.
Is MIRR used in Excel?
Yes! Excel has a built-in MIRR function: =MIRR(values, finance_rate, reinvest_rate). This makes it easy to calculate MIRR for complex cash flow scenarios.