Many organisations rely on the IRR as a measure of the success of a project. There are, however, some technical issues with using the IRR function in Excel, as well as some general issues that the user should be aware of.
Problems with the IRR in Excel
1. Multiple Solutions
The Problem:
- If the first cashflow isn’t negative, the IRR will have more than one solution, and Excel will only display the first one which it finds.
- Similarly, each time the cashflow changes from negative to positive, another possible solution for the IRR is created and Excel will only display the first one which it finds.
Workarounds:
- Use the “guess” feature to try and guide Excel to the ‘correct’ result.
- Another option is to use the MIRR function in Excel, which requires assumptions for the reinvestment rate, and finance rate.
- For more information: http://www.exceluser.com/solutions/irr.htm
2. No Solution
The Problem:
- If Excel has to go through more than 20 iterations to find the IRR, it will come up with #NUM! error value.
- The IRR function expects at least one positive cash flow and one negative cash flow; otherwise, it returns the #NUM! error value.
3. False Solution
The Problem:
- Even if net cashflows are negative, Excel can produce a positive IRR.
If cashflows don’t occur in the ‘correct’ order, i.e. negative followed by positive, then the IRR produced can be false. For example, if cashflows at the beginning of a project are small and positive, and at the end of the project are large and negative, the IRR will necessarily need to be high to reduce the net present value of the later cashflows. - For more information: http://www.datadynamica.com/fincalc/FalseIRR.htm
- General Issues with the IRR as a measure of investment value
1. Assumed re-investment rate = IRR
The Problem:
- The IRR calculation assumes that any income generated will be reinvested to get the same return (i.e. the IRR). Therefore, IRR overstates the annual equivalent rate of return for a project whose interim cash flows are reinvested at a rate lower than the calculated IRR.For more information: http://www.cfo.com/article.cfm/3304945/1/c_3348836
- Workarounds:
– Use the MIRR function in Excel, which requires assumptions for the reinvestment rate, and finance rate.
2. The IRR doesn’t take into account the size of investments
The Problem:
- An investment of £1 returning £2 next year, and an investment of £1,000 returning £2,000 next year would have exactly the same IRR of 100%.
- An investment of £100 returning £250 next year has an IRR of 150%, and investment of £200 returning £400 next year has a lower IRR of 100%, yet the first investment makes the least money.
- For more information: http://www.experiglot.com/2006/08/09/how-to-calculate-an-internal-rate-of-return-irr-and-when-not-to-use-it/
