The Excel MIRR Function

Related Function:

Modified Internal Rate of Return

The Modified Internal Rate of Return indicates the profitability of an investment and therefore is commonly used in business, when choosing between investmentments.

This calculation uses a schedule of payments (including an initial investment and a series of net income payments), to calculate the compounded return, assuming the Net Present Value of the investment is zero.

The difference between the Modified Internal Rate of Return (MIRR) and the Internal Rate of Return (IRR) is that, as well as the initial cost of the investment, the MIRR also considers the interest received on the reinvestment of cash, whereas the IRR does not.

For further details, see the Wikipedia MIRR page

Function Description

The Excel MIRR function returns the Modified Internal Rate of Return for a supplied series of periodic cash flows (i.e. a set of values, which includes an initial investment value and a series of net income values).

The syntax of the function is:

MIRR( values, finance_rate, reinvest_rate )

Where the function arguments are as follows:

values	-	An array of values (or a reference to a range of cells containing values) representing the series of cash flows (investment and net income values) that occur at regular periods. These must include at least one negative value (representing outgoing payment) and at least one positive value (representing income).
finance_rate	-	The interest rate paid on the money used in the cash flows.
reinvest_rate	-	The interest rate paid on the reinvested cash flows.

Mirr Function Example

In the spreadsheet below, the cashflow for an investment is shown in cells B2-B7. The initial investment of $100 is shown in cell B2 and the net income over 5 periods is shown in cells B3-B7.

The MIRR function in cell C2 shows the calculation of the Modified Internal Rate of Return after 4 years and the function in cell C4 shows the Modified Internal Rate of Return after 5 years.

Formulas:

	A	B	C
1		Values	MIRR after 4 yrs:
2	Initial Investment:	-$100.00	=MIRR( B2:B6, 5.5%, 5% )
3	Year 1 Income:	$18.00	MIRR after 5 yrs:
4	Year 2 Income:	$22.50	=MIRR( B2:B7, 5.5%, 5% )
5	Year 3 Income:	$28.00
6	Year 4 Income:	$35.50
7	Year 5 Income:	$45.00

Results:

	A	B	C
1		Values	MIRR after 4 yrs:
2	Initial Investment:	-$100.00	2.54%
3	Year 1 Income:	$18.00	MIRR after 5 yrs:
4	Year 2 Income:	$22.50	10.00%
5	Year 3 Income:	$28.00
6	Year 4 Income:	$35.50
7	Year 5 Income:	$45.00

Note that, in the above example, the initial investment is a negative value, as this is an outgoing payment, and the income payments are represented by positive values.

For further information and examples of the Excel Mirr function, see the Microsoft Office website.

Mirr Function Errors

If you get an error from the Excel Mirr function this is likely to be one of the following:

Common Errors

#DIV/0!	-	Occurs if the supplied values array does not contain at least one negative and at least one positive value.
#VALUE!	-	Occurs if any of the supplied arguments is non-numeric.

Return to the Excel Financial Functions Page
Return to the List of All Built-In Excel Functions