|
Home >
Excel
Solutions >
An Excel Tutorial:How to Fix Excel's IRR
The Internal Rate of Return calculation has
very real problems.
Excel offers a practical solution.
by Charley Kyd
October, 2004
 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.
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 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
years.
You can learn more about these measures and the MIRR by using your
favorite search engine.
|
