MIRR (Modified IRR): The Return Metric That Fixes IRR’s Biggest Problems

Quick Answer

MIRR (Modified Internal Rate of Return) is an annualized return metric that modifies IRR by making two assumptions explicit: (1) a finance rate for cash outflows (what it costs you to fund negative cash flows) and (2) a reinvestment rate for interim cash inflows (what you can realistically earn on distributions before the end). MIRR often produces a more realistic return than IRR when IRR is inflated by early cash flows or when cash flows create multiple IRR solutions.

Why IRR Can Mislead (The Two Problems MIRR Fixes)

Problem #1: IRR assumes reinvestment at the IRR

IRR treats interim cash flows like they can be reinvested at the same rate as the deal’s IRR. If IRR is 35%, that assumption means you can repeatedly earn ~35% on distributed cash until the end of the project. In many real-world situations, that’s not realistic.

This is why some deals with early cash-outs or refinancing can show very high IRRs: the math rewards early cash return because it implicitly assumes you can reinvest it at the same high rate.

Problem #2: IRR can have multiple answers (or none)

When cash flows change sign more than once (negative → positive → negative → positive), IRR can produce multiple solutions, and spreadsheets may return one based on guess/convergence. That’s not “a bug” so much as a mathematical reality: the equation can have multiple roots.

What MIRR Is (Plain English)

MIRR asks a simpler, more realistic question than IRR:

If I fund all negative cash flows at a specified finance rate, and I reinvest all positive cash flows at a specified reinvestment rate, what constant annual return connects the total money I put in to the total money I end up with at the end?

MIRR “moves” all cash flows to either the start or the end of the project:

• Negative cash flows are discounted back to the start at the finance rate.
• Positive cash flows are compounded forward to the end at the reinvestment rate.
• Then MIRR solves for the annual growth rate that links those two totals over the project duration.

This creates a return metric that matches a more realistic reinvestment story: you don’t assume you can reinvest distributions at some sky-high IRR—only at what you think you can actually earn.

MIRR Inputs: Finance Rate and Reinvestment Rate

Finance rate

The finance rate is the rate used for funding negative cash flows. Think of it as the cost of money for cash out:

• If you borrow to fund the project, the finance rate might be your borrowing rate.
• If you pay cash, the finance rate might be your opportunity cost (what that cash could earn elsewhere at low risk).
• In corporate finance, the finance rate is often related to the company’s cost of capital for funding investments.

Reinvestment rate

The reinvestment rate is what you can realistically earn on interim positive cash flows until the end of the project. Examples:

• A conservative reinvestment rate (e.g., a modest market return or safe reinvestment assumption)
• Your hurdle rate (required return) if you believe you can redeploy cash into similar-return opportunities
• A lower “parking cash” return if distributions would mostly sit in cash

How MIRR Works (Intuition Without Math Pain)

MIRR is easiest to understand as a three-step process:

Step 1: Bring all negatives to “today”

Add up all negative cash flows, discounting any later negatives back to the start at the finance rate. This gives you a single “present value of money invested.”

Step 2: Bring all positives to the end

Add up all positive cash flows, compounding them forward to the end at the reinvestment rate. This gives you a single “future value of money returned.”

Step 3: Solve for the annual growth rate

MIRR is the constant annual rate that turns the Step 1 total into the Step 2 total over the project duration. Conceptually it behaves like: “If I invested X today and ended with Y in N years, what’s the annualized rate?”

That’s why MIRR often feels more “real” than IRR. It behaves like a disciplined compounding story with explicit reinvestment assumptions.

MIRR vs IRR vs XIRR vs NPV: When Each Metric Wins

IRR

Best for: simple, periodic cash flows with one sign change (negative upfront, positives later). Weakness: reinvestment at IRR assumption and multiple IRRs in complex cash flow patterns.

XIRR

Best for: irregular dates (real estate, private deals, irregular contributions/withdrawals). Weakness: still inherits IRR’s reinvestment logic and still can face multiple solution issues when sign changes occur multiple times.

MIRR

Best for: when you want a single annualized rate but with realistic reinvestment assumptions. Strong when: early distributions inflate IRR, or cash flows are complex. Weakness: requires choosing two rates (finance and reinvest), which adds subjectivity—but also transparency.

NPV

Best for: making a decision at a specified required return (hurdle rate). Robust with weird cash flows. Weakness: depends on the discount rate choice—but that’s often a feature.

How to Choose Finance Rate and Reinvestment Rate (Practical, Not Academic)

Finance rate: what does it cost you to fund outflows?

Ask: if I need money for negative cash flows, where does it come from?

• If you borrow: use an approximate borrowing cost (or blended rate if multiple funding sources).
• If you use cash: use your opportunity cost baseline (what that cash could earn with similar risk and liquidity).
• If this is a corporate project: finance rate often ties to the cost of capital for funding.

Reinvestment rate: what will you actually do with distributions?

Many deals assume distributions can be reinvested at the IRR itself. MIRR forces you to choose:

• If you can redeploy into similar deals reliably: reinvestment rate might be near your hurdle rate.
• If distributions will sit in cash: use a low reinvestment rate.
• If you’ll invest in public markets: use a conservative expected return assumption.

Use ranges (because reality isn’t one number)

Like discount rates, reinvestment rates can be estimated. Run MIRR with a conservative reinvest rate and a base reinvest rate. If the “headline story” changes wildly, the deal’s attractiveness depends heavily on reinvestment opportunity.

Examples (Simple + Real Estate)

Example 1: Why MIRR is often lower than IRR when distributions are early

Consider a deal with a big early distribution. IRR often jumps because cash comes back quickly. But can you reinvest that cash at the same high IRR? Maybe not.

MIRR typically reduces the implied return by assuming reinvestment at your chosen reinvestment rate (say 8% or 10%), not at a 30%+ implied IRR.

The lesson is not “IRR is wrong.” The lesson is: IRR’s reinvestment assumption is hidden; MIRR’s reinvestment assumption is explicit.

Example 2: Real estate with refinance cash-out (IRR inflation pattern)

A common real estate pattern:

• Big negative upfront: down payment + renovation
• Stabilize rents
• Refinance and pull cash out early (positive mid-hold cash flow)
• Hold for cash flow, then sell later

IRR can look extremely high because some capital returns early. But that doesn’t mean the deal is “as good as” a true 30% compounding machine. MIRR can be more realistic because it assumes the refinance cash-out is reinvested at a reasonable rate, not at the high IRR.

Example 3: Multiple IRR scenario (MIRR gives one answer)

If a project has:

• negative upfront
• positive distributions
• a large negative later (major CapEx or balloon payment)
• positive exit

IRR can have multiple solutions. MIRR often produces a single answer because it structures the problem as “present value of negatives” vs “future value of positives.”

How to Calculate MIRR in Excel (Step-by-Step)

Step 1: Put periodic cash flows in a column

MIRR in Excel assumes cash flows are periodic (e.g., yearly or monthly). If you have irregular dates, you typically build a dated DCF, use XIRR for timing, and use NPV checks. MIRR is still useful as a summary when your model uses periods.

Step 2: Use the formula

=MIRR(values, finance_rate, reinvest_rate)

Step 3: Example

If cash flows are in A1:A10:
=MIRR(A1:A10, 0.07, 0.09)

• 0.07 = finance rate (7%)
• 0.09 = reinvestment rate (9%)

Step 4: Interpret

MIRR is an annualized return. If MIRR is 11%, it means “given my finance and reinvest assumptions, this project behaves like an 11% annual compounder.”

How to Calculate MIRR in Google Sheets

Google Sheets supports MIRR with the same structure:
=MIRR(values, finance_rate, reinvest_rate)

Common Sheets issue: periodic assumption

Like Excel, MIRR in Sheets assumes equal spacing between cash flows. If your deal is irregular by dates, MIRR can still be used if you bucket into months/years consistently, but XIRR is the timing-accurate metric.

Common Mistakes (That Make MIRR Useless)

1) Using unrealistic reinvestment rates

If you set reinvestment rate equal to a very high IRR, MIRR loses its purpose. Pick a rate you can actually earn on interim distributions.

2) Confusing finance rate with discount rate

Finance rate is specifically about outflows and funding cost. Discount rate (for NPV) is about required return for the project’s risk. They can be related, but they’re not identical.

3) Using gross sale proceeds in real estate cash flows

MIRR is still only as good as your cash flows. Sale should be net of selling costs and loan payoff if you’re modeling equity returns.

4) Treating MIRR as a decision metric without NPV

MIRR is a summary return. For decision-making across different sized projects, NPV at a hurdle rate often ranks projects better.

5) Ignoring scenario sensitivity

If your deal’s exit assumption changes a lot, MIRR can swing too. Run conservative/base/optimistic cases for exit price, vacancy, and expenses.

Multiple IRRs: The Classic Case Where MIRR Shines

If a cash flow stream has multiple sign changes, IRR can produce:

• multiple IRR answers
• a weird answer that depends on guess
• or a failure to converge

Why MIRR helps

MIRR effectively forces cash flows into one negative “bucket” (present value of costs) and one positive “bucket” (future value of returns). That creates a single growth rate linking them, which is why MIRR tends to be stable.

How Pros Use MIRR (Without Overcomplicating It)

In practice, professionals rarely rely on MIRR alone. They use it as one view in a “return dashboard”:

• XIRR (or IRR) as the headline rate
• MIRR as the “realistic reinvestment” rate
• NPV as the value creation metric at a hurdle rate
• Equity multiple to sanity-check total dollars returned

A clean workflow

1. Build realistic cash flows (net sale proceeds, fees, real CapEx timing)
2. Compute IRR/XIRR
3. Compute MIRR with grounded finance + reinvest assumptions
4. Compute NPV at a hurdle rate range
5. Run conservative scenarios

Checklist

• ✅ Use MIRR when interim cash flows are large or early (IRR inflation risk)
• ✅ Use MIRR when IRR can have multiple answers (multiple sign changes)
• ✅ Choose a realistic reinvestment rate (often lower than IRR)
• ✅ Finance rate reflects funding cost/opportunity cost for outflows
• ✅ Keep cash flows realistic (net sale proceeds, fees included)
• ✅ Use MIRR alongside XIRR and NPV (don’t rely on one metric)
• ✅ Run conservative and base scenarios (exit value matters)

Related links

Frequently Asked Questions

Bottom Line

MIRR exists because IRR is easy to quote but easy to misinterpret. IRR’s reinvestment assumption is often unrealistic, and complex cash flow patterns can produce multiple IRRs. MIRR fixes both by using explicit finance and reinvestment rates, producing a more stable and often more realistic annualized return—especially for real estate and private deals. Use MIRR as a companion metric: compute XIRR for timing, MIRR for realistic reinvestment, and NPV for decision-making at your hurdle rate.

Methodology and assumptions

Educational only. MIRR depends on chosen finance and reinvestment rates and assumes periodic cash flow spacing in common spreadsheet implementations. For irregular dated cash flows, use XIRR and NPV checks; use MIRR as a complementary, assumption-explicit summary metric.