The calculation of an Internal Rate of Return is very appealing…at first glance.
When you know the IRR of a prospective investment, you seem to know all you need. A high IRR is a good thing; a low IRR is a bad thing.
But it’s not that simple.
In August, 2004, The McKinsey Quarterly printed an article by John C. Kelleher and Justin J. MacCormack about the dangers of using the IRR. CFO magazine republished the article in October of that year.
One problem with the IRR calculation concerns its assumption about cash generated during an investment. The calculation assumes that this cash will be reinvested at the rate calculated by the IRR. For projects expected to generate a lot of cash during the course of an investment, the IRR calculation therefore can overstate the financial benefits significantly.
Another problem with the IRR is that it can produce multiple results. Each time your cash flows change from negative to positive, or from positive to negative, the calculation generates an additional solution. To illustrate, the cash flows shown at the right produce three completely different IRRs.
These three tables illustrate the three possible results. All three can be seen as a combination of investments and borrowings. All produce the same Net Cash Flows shown above.
In the first example, the returns on each investment, and the repayment for the cash borrowed are the same. From this point of view, the IRR equals 0%.
In the second example, the returns and repayment are double each of the initial transactions. The IRR is therefore 100%.
In the third example, the returns and repayment are triple each of the initial transactions. The IRR is therefore 200%.
Which version is correct? They all are.
The McKinsey consultants offer the best advice about the IRR: Avoid it. However, the idea of an IRR is so widely accepted that this is difficult to do in many companies. Therefore, Excel offers a different solution, the MIRR (Modified IRR) function.
Excel’s MIRR function is an alternative to the IRR function. It has this form:
=MIRR(values, finance_rate, reinvest_rate)
The values argument is the same as the values argument for the IRR. It is an array or cell reference to the cash flows for which the MIRR is to be calculated.
The finance_rate argument is the annual interest rate that you would pay to cover any negative cash flows incurred during the life of the investment.
The reinvest_rate argument is the interest rate that you would earn on cash that the investment generates during its life.
To illustrate, if the Finance Rate is 8% and the Reinvest Rate is 3%, the MIRR of the four cash flows shown above would be:
=MIRR(NetCashFlows, .08, .03) = 5.84%
It’s always dangerous to treat an Excel function like magic. This is particularly true of a calculation on which you might base an investment decision. Luckily, the logic that supports the MIRR is easy to understand:
- Find the present value of negative cash flows incurred in any year during the course of the investment, discounting them at the Finance Rate.
- Find the future value of positive cash flows incurred in any year during the course of the investment, growing them at the Reinvestment Rate.
- Find the average interest rate that grows your adjusted investment (step 1) into your adjusted return (step 2).
For further study, MIRR uses logic that’s related to two other financial measures:
- The Profitability Index is the present value of future cash flows divided by the investment.
- The Financial Management Rate of Return (FMRR) is nearly identical to the MIRR, and has been used by real estate investors for decades.
You can learn more about these measures and the MIRR by using your favorite search engine.
