MIRR Function

Calculates a rate of return using separate financing and reinvestment rates.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A rate of return

Want to practice MIRR in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

IRR quietly assumes cash received is reinvested at the IRR itself — often unrealistic. MIRR lets you set what it costs to fund the outflows and what the inflows really earn.

The result is usually lower and more conservative than IRR, and there's always exactly one answer.

Syntax

=MIRR(values, finance_rate, reinvest_rate) Explain

Arguments

ArgumentRequired?What it does
valuesYesEvenly spaced cash flows, with at least one negative and one positive.
finance_rateYesThe interest rate paid on money used (outflows).
reinvest_rateYesThe rate earned on cash received (inflows).

Examples

Example 1 — A realistic return on the store

Northstar borrows at 8% but can only earn 5% on spare cash.

B7fx=MIRR(B2:B6,8%,5%)
AB
1YearCash flow
20 (today)-$150,000
31$40,000
42$50,000
53$60,000
64$55,000
7MIRR9.98%

Still above 8%, but well below the 12.96% IRR. With both rates at 8%, MIRR would be 11.10%.

Common mistakes

  • #DIV/0! error. The values need at least one negative and one positive number.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.