Quick Navigation

MIRR Function

Excel 2007+

Summary

The MIRR function calculates the modified internal rate of return for an investment, accounting for both the financing cost of the investment and the reinvestment rate of cash inflows. This provides a more accurate return measure than the standard IRR by using different rates for cash outflows and inflows.

Syntax

MIRR(values, finance_rate, reinvest_rate)

Parameters

Parameter	Type	Required	Description
values	`Array/Reference`	Yes	Cash flow series with proper signs (negative outflows, positive inflows)
finance_rate	`Number`	Yes	Cost of borrowing money for investments
reinvest_rate	`Number`	Yes	Rate earned when reinvesting positive cash flows

Using the MIRR Function

MIRR is crucial for investment analysis when the cost of capital differs from the reinvestment rate. Use negative values for initial investments and cash outflows, positive values for returns. The function compounds outflows at the finance rate to the end period and inflows at the reinvest rate to present value, then finds the rate balancing these terminal values.

Common MIRR Examples

5-Year Investment Analysis

=MIRR(A2:A7, A8, A9)

Calculates 13% MIRR for 5-year project with 10% finance rate and 12% reinvestment rate.

Short-Term Project Evaluation

=MIRR(A2:A5, A8, A9)

Shows -5% MIRR for 3-year period, indicating poor performance.

Alternative Reinvestment Scenario

=MIRR(A2:A7, A8, 14%)

Tests sensitivity with 14% reinvestment rate, still yields 13% MIRR.

Frequently Asked Questions

#DIV/0! error - MIRR requires at least one positive and one negative value.

Yes, text, logical values, and empty cells are ignored, but zeros are included.

IRR assumes same rate for all cash flows; MIRR uses separate finance and reinvest rates.

Common Errors and Solutions

#DIV/0!

Cause: No positive and negative values in cash flow series

Solution: Ensure cash flows include both outflows (negative) and inflows (positive)

#VALUE!

Cause: finance_rate or reinvest_rate not numeric

Solution: Use decimal rates (10% = 0.1, not 10)

Unexpected results

Cause: Incorrect cash flow signs or order

Solution: Negative for outflows first, positive returns after; chronological order

Notes

Cash flows must be in chronological order
Zero values are included in calculation
MIRR formula: balances future value of outflows at finance_rate vs. present value of inflows at reinvest_rate
Perfect for capital budgeting and project appraisal

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2003 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+