MIRR Function Excel (Formulas, Example) | How to use MIRR Function

PMT Function in Excel (Table of Contents)

MIRR Function Excel
MIRR Formula in Excel
How to Use?

MIRR Function Excel

MIRR function is an in-build financial function used in Excel to calculate the modified internal rate of return for the cash flows supplied with a period. This function takes the set of initial investment or loan values and a set of net income values with the interest rate paid on initial amount including the interest earned from reinvestment of earned amount and returns the MIRR (modified internal rate of return) as output.

Here the cash flow is a variable amount of time and considered after regular interval of time.

In a simple language, MIRR excel considers the investment cost and interest received on reinvested money and provide the modified return rate with the time period.

MIRR Formula in Excel

Recommended Courses

Explanation of MIRR Function Excel

There are three parameters which are used in MIRR formula in Excel.

The details of the parameters used in MIRR formula in Excel are as follows:

Values: Here values are an array of cash flow representing a series of payment amount or range of references or set of income values including the initial investment amount.
Finance_rate: Finance rate is a rate of interest paid on the amount used during cash flow.
Reinvest_rate: Reinvest rate refers to the interest rate earned from the reinvested profit amount during cash flow.

How to use MIRR function Excel?

MIRR Excel function is very simple and easy to use. Let’s understand the working of this function by considering some simple MIRR formula examples.

MIRR Function Excel Example #1

Consider an initial loan amount of 25,000 as initial investment amount (loan amount) with an interest rate of 5% yearly and you have earned an interest rate of 8% from the reinvested income. In MIRR excel the loan amount or initial investment amount is always considered as (-ve) value.

Below are the table shows the details of income after regular interval of time. Considering the cash flow of income for 1^st, 2^nd, 3^rd, 4^th, and 5^th years are as follows: 10,911, 14,716, 19,635, 18,700, and 18,477.

Now calculate MIRR in Excel (Modified internal return rate) after 2 years:

=MIRR(B4:B6,B10,B11) and output MIRR is 3%.

Similarly, calculate MIRR in Excel (Modified internal rate of return) after 3 and 5 years:

MIRR after three years will be =MIRR(B4: B7, B10, B11) and output is 25%.

MIRR after five years will be=MIRR(B4: B9, B10, B11) and output is 31%.

MIRR Function Excel Example #2

Consider an initial loan amount of 10,000 as an initial investment with an interest rate of 5% yearly and you have earned an interest rate of 8% from the reinvested income.

Below are the table shows the details of income after regular interval. Considering the cash flow of income for 1^st, 2^nd, 3^rd, 4^th, and 5^th years are as follows: 7,505, 5,338, 9,465, 5,679, and 6,004.

Now calculate MIRR in Excel after 2 years:

=MIRR(B15:B17,B21,B22)and output MIRR is 16%.

Similarly, calculate MIRR in Excel after 3 and 5 years:

MIRR after three years will be =MIRR(B15: B18, B21, B22) and output is 34%.

MIRR after five years will be =MIRR(B15:B20,B21,B22) and output is 32%.

Things to remember about the MIRR Function Excel

Loan amount is always considered as negative value.
The modified internal return rate is always calculated on variable cash flow after regular interval.
Error handling:

#DIV/0!: MIRR excel will return #DIV/0! Exception when the supplied error does not contain at least one negative value of one positive value.

#VALUE!: MIRR excel will return this kind of exception when any of the supplied value is non-numeric.

You can download this MIRR function in Excel template here – MIRR Function Excel Template

MIRR Function