MIRR Calculator
Calculate the Modified Internal Rate of Return. Uses specific reinvestment and finance rates for a more realistic profitability measure.
%
%
%
%
Period (Year)
Cash Flow Amount
Del
👨🏫
By Prof. David Anderson
Finance Professor | CFA Charterholder
“If standard IRR (Internal Rate of Return) is the optimistic salesperson who promises you the moon, then MIRR (Modified Internal Rate of Return) is the strict auditor who brings you back to earth. In 20 years of analyzing corporate projects and real estate deals, I’ve seen countless bad investments approved because managers relied on high IRRs that were mathematically impossible to achieve in the real world. Today, we will learn why MIRR is the only metric you should trust for robust Capital Budgeting.”
The Ultimate MIRR Calculator & Guide: Why Modified IRR is the “True” Return Metric
Exposing the Reinvestment Rate Fallacy: A Deep Dive into Capital Budgeting & Excel Modeling
1. The Problem: The “Big Lie” of IRR
Before we calculate MIRR, we must understand why standard IRR is flawed. The standard IRR formula implies that all interim cash flows are reinvested at the same rate as the IRR itself.
⚠️ The Reinvestment Fallacy
Imagine a project with a calculated IRR of 30%.
The math implicitly assumes that when you receive cash in Year 1, you can instantly find another amazing project paying 30% to put that money into.
Reality: You probably put that cash into a bank account earning 4% or pay off debt at 8%.
Consequence: IRR massively overstates your true wealth creation.
2. The Solution: Dual-Rate Logic
MIRR (Modified Internal Rate of Return) fixes this flaw by splitting the rates. It tracks the money flow more realistically using two distinct interest rates.
- Finance Rate ($r_{finance}$): The interest rate you pay to borrow money (used to discount negative cash flows). Typically your WACC (Weighted Average Cost of Capital).
- Reinvestment Rate ($r_{reinvest}$): The interest rate you earn on positive cash flows (used to compound future gains). Usually a conservative “Safe Rate” like 4-5% (e.g., Treasury Yields).
3. The Mathematical Engine: MIRR Formula
The MIRR formula does not require iteration like IRR. It is a direct geometric mean calculation that moves money to two distinct points in time: $T=0$ (Start) and $T=n$ (End).
$$ MIRR = \sqrt[n]{ \frac{FV(\text{Positive Flows})}{PV(\text{Negative Flows})} } – 1 $$
The 3-Step Process:
1. Discount all cash outflows (investments) to Present Value (PV) using the Finance Rate.
2. Compound all cash inflows (profits) to Future Value (FV) using the Reinvestment Rate.
3. Calculate the Compounded Annual Growth Rate (CAGR) required to get from that PV to that FV over $n$ years.
4. Case Study A: The “Unicorn Trap” (50% vs 15%)
Let’s look at a Venture Capital (VC) scenario to see how dangerous IRR can be. You invest in a startup that has a huge “Early Win” but then stalls.
The Cash Flows:
- Year 0: -$1,000,000 (Initial Investment)
- Year 1: +$1,500,000 (Early Exit/IPO)
- Year 2: +$0
- Year 3: +$0 (Money sits in the bank)
| Metric | Assumption | Result | Reality Check |
|---|---|---|---|
| Standard IRR | Assumes the $1.5M in Year 1 grows at 50% for remaining years. | 50.0% | The Trap. It implies you ended up with huge wealth, but you didn’t. You just got cash early. |
| MIRR | Assumes the $1.5M sits in a safe bond yielding 4%. | 15.9% | The Truth. Your money worked hard for 1 year, then got lazy. The 3-year average return is dragged down. |
Professor’s Verdict: The entrepreneur will pitch you the 50% IRR. The prudent investor calculates the 15.9% MIRR. This is why MIRR is the “True Return.”
5. Case Study B: Real Estate “Fix and Flip”
Real Estate developers often have high borrowing costs but low safe returns.
Scenario: You borrow money at 10% (Finance Rate) to build condos. Profits are parked in savings at 3% (Reinvestment Rate) until the project ends.
| Year | Cash Flow | Action |
|---|---|---|
| 0 | -$2,000,000 | Construction Cost |
| 1 | +$1,000,000 | Phase 1 Sales (Reinvested at 3%) |
| 2 | +$1,500,000 | Phase 2 Sales (End of Project) |
Result:
• IRR: 15.1% (Looks decent).
• MIRR: 11.4% (Barely covers the 10% cost of debt!).
Without MIRR, you might accept a project that barely breaks even on risk-adjusted terms.
6. Developer’s Corner: Excel MIRR Function
Unlike manual math, Excel makes MIRR easy.
# Syntax: =MIRR(values, finance_rate, reinvest_rate)
# A1: -$1,000 (Investment)
# A2: +$500 (Year 1)
# A3: +$800 (Year 2)
# Finance Rate: 8% (Cost of borrowing)
# Reinvest Rate: 4% (Safe bond yield)
=MIRR(A1:A3, 0.08, 0.04)
>> Result: 11.24% (VS IRR of 17.5%)
7. Cheat Sheet: When to use what?
Don’t throw away IRR completely. Use both metrics together for a balanced view.
| Metric | Best Used For… | Weakness |
|---|---|---|
| NPV (Net Present Value) | Final Decision Making (Go / No-Go). | Hard to compare projects of different sizes. |
| Standard IRR | Initial Screening / Quick Comparisons. | Over-optimistic; Fails with unconventional cash flows. |
| MIRR | Detailed Auditing / Board Presentations. | Requires estimating two separate interest rates. |
8. Professor’s FAQ Corner
Q: Is MIRR always lower than IRR?
Usually, yes. Since the Reinvestment Rate (e.g., 4%) is typically lower than the project’s IRR (e.g., 20%), MIRR pulls the return downwards towards reality. However, if the Reinvestment Rate is higher than the IRR (rare), MIRR could be higher.
Q: Does MIRR solve the “Multiple IRR” problem?
Yes! Standard IRR can mathematically crash (giving 2 or 3 different answers) if cash flows switch between negative and positive multiple times. MIRR fixes this by moving all negatives to the start and all positives to the end, guaranteeing a single, unique solution.
Q: What rates should I use for MIRR?
Finance Rate: Use your WACC (Weighted Average Cost of Capital) or loan interest rate.
Reinvestment Rate: Be conservative. Use the yield on short-term government bonds or a high-yield savings account (approx 3-5%).
Reinvestment Rate: Be conservative. Use the yield on short-term government bonds or a high-yield savings account (approx 3-5%).
References
- Kierulff, H. (2008). “MIRR: A better measure of return”. International Journal of Applied Corporate Finance.
- Brealey, R. A., Myers, S. C. (2019). Principles of Corporate Finance. McGraw-Hill Education.
- Investopedia. “Modified Internal Rate of Return (MIRR) Definition”.
- CFA Institute. “Level II: Capital Budgeting & MIRR Analysis”.