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: