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:
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: