fx
FunctionPedia
Quick Answer

MIRR Function

Calculates the modified internal rate of return for a series of cash flows.

✓ Excel✓ Google SheetsExcel All versions

Syntax

MIRR
(values, finance_rate, reinvest_rate)

Parameters

ParameterDescriptionRequired
valuesArray or reference to cash flows, including the initial investment.Required
finance_rateInterest rate paid on the money used in the cash flows.Required
reinvest_rateInterest rate received on reinvested cash flows.Required

Basic Example

MIRR for a project with reinvestment assumption

=MIRR(B2:B6, 0.05, 0.08)
ResultMIRR value

Calculates the modified IRR for cash flows in B2:B6, assuming 5% financing cost and 8% reinvestment return.

Advanced Examples

Example 1: Compare MIRR and IRR

Investment analysis

MIRR usually gives a lower rate than IRR because it uses a conservative reinvestment rate.

=MIRR(B2:B6, 0.05, 0.07)
Result: Modified IRR
Uses 7% as the reinvestment rate for positive cash flows.

How MIRR Works

MIRR separates cash flows into negative and positive streams. Negative flows are discounted to the present at the finance rate, and positive flows are compounded to the end at the reinvest rate. The result is the rate that equates the present value of outflows to the future value of inflows.

1
Enter values
Type the values
2
Enter finance_rate
Type the finance_rate
3
Enter reinvest_rate
Type the reinvest_rate
4
Calculate
Press Enter to get the result.

Important Notes & Limitations

  • Requires at least one positive and one negative cash flow.

  • Assumes cash flows occur at regular intervals.

  • Generally more realistic than IRR because it uses explicit reinvestment and finance rates.

Common Errors & Fixes

#DIV/0! errorAll cash flows have the same sign.

Fix: Ensure there is at least one positive and one negative cash flow.

#NUM! errorInvalid finance or reinvest rate.

Fix: Use positive decimal rates.

Download Practice File

Practice MIRR with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the MIRR function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

What is the difference between IRR and MIRR?
MIRR uses explicit finance and reinvestment rates, while IRR assumes reinvestment at the IRR itself.
Why is MIRR lower than IRR?
Because MIRR typically assumes a lower reinvestment rate than the IRR.