MIRR (Modified Internal Rate of Return) fixes a key flaw in standard IRR: the
assumption that all cash inflows are reinvested at the IRR itself — which is usually
unrealistically high.
MIRR formula
MIRR = (FV of positive cash flows at reinvestment rate / PV of negative cash flows at finance rate) ^ (1/n) − 1
Where n = number of periods.
MIRR vs. IRR
Aspect
IRR
MIRR
Reinvestment assumption
At the IRR rate (often too high)
At your specified reinvestment rate
Multiple IRR problem
Can have multiple solutions
Always single solution
Typical result
Higher
Lower, more realistic
Use case
Quick screening
More rigorous capital budgeting
When to use MIRR
Use MIRR when:
- Project cash flows alternate positive and negative (multiple IRR problem)
- The reinvestment rate is significantly different from IRR
- You need to compare projects with different lifespans
- Lenders or sophisticated investors require it
Typical MIRR thresholds: 15%+ for startup/VC investments, 12–18% for PE,
8–12% for real estate, 6–10% for infrastructure.
MIRR vs IRR: Which Should You Use for Capital Budgeting?
A practical comparison of MIRR and IRR for capital budgeting decisions — when each is appropriate and why MIRR gives more realistic results.
IRR is more commonly used, but MIRR is more realistic. Here is when and why to use
each one.
The reinvestment rate problem with IRR
IRR implicitly assumes that all positive cash flows can be reinvested at the IRR
itself. If a project has a 35% IRR, it assumes you can reinvest each year's returns
at 35% — which is usually impossible.
MIRR fixes this by separating the finance rate (cost of borrowing) from the
reinvestment rate (your actual reinvestment opportunities).
When to use MIRR over IRR
Multiple sign changes in cash flows — projects that require capital injections
mid-way can produce multiple mathematically valid IRRs. MIRR always returns a
single value.
Comparing projects of different scale — MIRR is better when you need to
compare a $500K project to a $5M project with different cash flow patterns.
Sophisticated lenders or board members require it — PE firms and sophisticated
investors often prefer MIRR for realistic return modelling.
When IRR is fine
For simple, conventional cash flow patterns (one initial outflow followed by
inflows), IRR and MIRR give similar results and IRR is more widely understood.
Use the MIRR calculator to compute MIRR with your
choice of finance and reinvestment rates.
MIRR in Excel: Formula, Examples, and Common Mistakes
How to calculate MIRR in Excel using the built-in MIRR function — with examples, argument explanations, and common mistakes to avoid.
Excel's built-in MIRR function makes the calculation straightforward — but there
are a few gotchas worth knowing before you use it in a model.
Excel MIRR syntax
=MIRR(values, finance_rate, reinvest_rate)
values: a range of cells containing the cash flows (Year 0 through Year N).
The first value must be negative (initial investment).
finance_rate: the interest rate you pay on negative cash flows (cost of
borrowing). Use your WACC or cost of debt.
reinvest_rate: the interest rate you earn on reinvested positive cash flows.
Use a conservative rate — Treasury yield, savings rate, or opportunity cost.
Example
Cash flows: Year 0 = −$100,000, Year 1–4 = $30,000 each.
Finance rate = 10%, Reinvest rate = 8%.
With standard IRR: =IRR({-100000,30000,30000,30000,30000}) = 7.71%
MIRR is lower here because the reinvest rate (8%) is lower than the IRR (7.71%...
wait, that seems backwards. Let me correct: MIRR can be higher OR lower than IRR
depending on the rates used. When reinvest rate < IRR, MIRR < IRR.)
Common mistakes
Not including Year 0: the values array must include the initial outflow
at position 0. If you start from Year 1, the function gives wrong results.
Using the same rate for both arguments: this reduces MIRR to a simple
IRR approximation and defeats the purpose.
Mixing periods: MIRR assumes annual periods unless you adjust. For
monthly cash flows, divide annual rates by 12.
Use the MIRR calculator for a web-based version
without needing Excel.