Related Function:

The 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.

Syntax

=MIRR(values,finance_rate,reinvestment_rate)

Arguments

Argument Description
values A range of cells that represents the series of cash flows
finance_rate The interest rate that you pay on the cash flow amounts
reinvestment_rate The interest rate that you receive on the cash flow amounts as they are reinvested

Examples

  A B C
1   Data  
2 -$7,500 Initial investment  
3 $3,000 Income (yr 1)  
4 $5,000 Income (yr 2)  
5 $1,200 Income (yr 3)  
6 $4,000 Income (yr 4)  
7 $3,800 Income (yr 5)  
8 5.5% Annual interest rate  
9 8% Reinvestment rate  
10      
11 Formula Result Notes
12 =MIRR(A2:A7,A8,A9) 21.55% Investments modified rate of return after 5 years
13 =MIRR(A2:A5,A8,A9) 10.43% Modified rate of return after 3 years
14 =MIRR(A2:A7,A8,14%) 24.46% 5-year modified rate of return based on a reinvest_rate of 14 percent

Common Function Error(s)

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

The Modified Internal Rate of Return (MIRR) indicates the profitability of an investment and therefore is commonly used in business, when choosing between investments. MIRR is calculated as:

    \[ \left( \frac{-NPV (rrate,values[positive])*(1+rrate)^n}{NPV(frate,values[negative])*(1+frate)} \right)^\frac{1}{n-1} -1 \]

where:

  • n = number of cash flows in values
  • frate = finance_rate
  • rrate = reinvest_rate

This calculation uses a schedule of payments (including an initial investment, along with the 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, in its calculation, the MIRR considers the initial cost of the investment and also the interest received on the reinvestment of cash, whereas the IRR does not consider these.