MIRR(finance_rate, reinvest_rate, list)

The MIRR function calculates a modified internal rate of return where positive cashflows earn interest at a reinvest_rate and negative cash flows are financed at a finance_rate. The cashflows are contained in list and will usually begin with a negative figure, indicating that you start with a loan.

MIRR is calculated as follows:

image\ebx_585077774.gif

where net interest is the return on the reinvested positive cash flows, less the interest accrued on the negative cash flows.

For example, suppose you were loaned $1000.00 to invest in a business venture and you pay interest on this loan at 15%. You predict that at the end of each of the first four years the business will generate the following cash flow: $500, $700, $700, $900. Further suppose that you reinvest the earnings at a fixed rate of 10% and this will compound over the four years.

At the end of the first year, you’ll receive $500, which will be reinvested but you’ll also have to take into account the interest on the loan, which will be $150 at this time. Over the years, net interest will accumulate as follows:

Period (n)

Cash flow

Interest1

Interest2

Interest3

Interest4

å Interest

1

-1000

-150.00

-172.50

-198.36

-228.13

-748.99

2

500

 

50.00

55.00

60.50

165.50

3

700

 

 

70.00

77.00

147.00

4

700

 

 

 

70.00

70.00

5

900

 

 

 

 

0.00

 

 

 

 

 

 

-366.49

After four years, the original $1000 has generated earnings of 500+700+700+900=$2800 less net interest of $366.49 = $2433.51. This can be expressed as a rate of return, over four years, of 143.35% or [(1+ 143.35%)^(1/4) -1] = 24.9% per annum.

The same result can be obtained using the MIRR functions as follows:

MIRR(15%, 10%, -1000, 500, 700, 700, 900)

which Ability calculates as 24.90%.

See also:

Other financial functions