Syntax. MIRR(Values,Finance_rate,Reinvest_rate)
Definition. This function calculates the internal yield of an investment while evaluating the negative period surpluses with a different interest rate as positive (qualified internal capital yield).
Arguments
Values (required) The (actual or expected) surpluses from disbursements and deposits, arranged without gaps in a column. Each value represents the end of a period (usually one year) in ascending order and without gaps. Negative surpluses have a minus sign. At least two values are expected, of which at least one is positive and one negative.
Finance_rate (required) The interest rate with which the negative amounts (disbursements) are reduced (discounted) to their cash value.
Reinvest_rate (required) The interest rate with which the positive amounts (deposits) are added to make up the end value.
If the cells of the Values argument do not contain numbers or are empty, Excel calculates as if these cells didn’t exist. This is also the case if cell references in the argument refer to such cells.
Background. The method of internal capital yield for evaluating investments is used for dynamic investment calculation. There are advantages and disadvantages when this function is used as described for the functions IRR() and NPV(). One advantage of capital value creation by discounting all future numbers is the open time horizon, which does not require any considerations regarding reinvestment options. A disadvantage is the interest rate for evaluation, which is always fixed.
The method of the qualified internal interest rate limits the time horizon but leaves some room for the evaluation interest rates. All disbursements are evaluated at the borrowed capital interest rate by discounting them at their current value, which is the cash value. All deposits are treated as investments. The result is the future value (calculated by adding interest) at the end of the investment.
The qualified internal interest rate is the one that currently leads to the future value of the deposits through disbursements by adding interest to the cash value. It therefore tells you at which interest rate the invested money is returned at the end.
Example. Assume that an investor purchases a medium-term government bond that is paid interest for five years at a rate of 4.25 percent per year. However, for certain reasons, the interest can be reinvested at only 2 percent.
Though the internal capital yield (calculated via IRR()) is 4.25 percent in this case, the mixed interest via MIRR() is only 4.08 per year.
Figure 15-6 shows the interpretation: An expenditure of $100.00 has an end result of $122.12 (calculated by adding interest).