Wauvel

Excel like a finance pro.

← All functions

MIRR

Finance

IRR's honest cousin — the return when you say what you'd really earn on the cash coming back.

Difficulty

Advanced
Excel file

1What is it?

IRR has a hidden assumption: that every dollar the project throws off gets reinvested at the IRR itself. If a project shows a 40% IRR, that assumes you have somewhere else earning 40% to put the proceeds. You don't. MIRR splits the assumption in two — one rate for the money you borrow to fund the outflows, another for what the inflows actually earn sitting in the bank — and the answer is always more conservative and usually more defensible. It also can't produce the multiple answers IRR gives when the cash flows change sign more than once.

2What it looks like

MIRR(values, finance_rate, reinvest_rate)
values
The cash flows in order, evenly spaced, with at least one negative and one positive.
finance_rate
What the money costs you — your borrowing rate, or the cost of capital.
reinvest_rate
What the inflows actually earn once received. Be honest here: it's usually close to your deposit rate, not your hurdle rate.

3When you use it

  • Re-state a flattering IRR at a reinvestment rate you could actually achieve.
  • Compare projects of different sizes without the reinvestment assumption distorting the ranking.
  • Get one answer where IRR gives several, on a project with a later outflow.

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

IRR assumes you reinvest at the IRR. Set what the proceeds really earn and watch the answer fall.

E2
fx
=MIRR(A2:D2, B6/100, B7/100)IRR says: 19.4% — assuming you reinvest at that rate
ABCDE
1TodayYr 1Yr 2Yr 3Result
2IRR says: 19.4% — assuming you reinvest at that rate
3MIRR says: 13.9% — reinvesting at 3%
4The difference is the assumption: 5.5 points

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

5Common errors

#DIV/0!The values contain no negative, or no positive.

Fix: MIRR needs both directions, same as IRR — one outflow at minimum.

Much lower than IRR, and that's alarmingNothing is wrong. The gap between them IS the reinvestment assumption, made visible.

Fix: Report both, and say which rate you assumed.

#VALUE!Text or logical values sit in the range.

Fix: Empty cells are ignored; type a 0 for a period with no cash flow.

6Better functions & alternatives

  • IRR — Simpler and universally recognised, but assumes you reinvest at the IRR.
  • XIRR — Use when the flows carry real dates. There's no dated equivalent of MIRR.
  • NPV — Sidesteps the reinvestment argument entirely by answering in currency instead of a rate.

Want MIRR already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.