Multiple IRRs When More than One Sign Change in the Cash Flow

SUMMARY

The Internal Rate of Return (IRR) method used by Excel can produce a different solution for each change of sign in the cash flow. You must try different guesses for the rate to find the most accurate solution.

MORE INFORMATION

The following is an example:

1. Type the following information onto a spreadsheet in Excel:

         A1:    500
         A2:  -1100
         A3:    600
   
   

2. Type the following formula into cell A4 on the spreadsheet:

         A4: =IRR(A1:A3,10%)
   

   The answer will be 20%.

3. Substitute 5% for the guess value in the IRR function in cell A4. The formula will look like:

         A4: =IRR(A1:A3,5%)
   

   Now the answer is 0%.

They both result in zero values because that is the relationship between IRR and NPV. The internal rate of return is the rate that sets the net present value to zero.

When there are multiple IRRs, there's no good way to tell which result makes more sense. For instance, in the example above, 20% makes sense if the cash flows are annual but probably doesn't make sense if the cash flows are monthly.

The outline below illustrates how many IRR values you should expect, according to the number of cash flow sign changes that occur.

1. If there is not a change of sign in your cash flow values, you will not get an answer.

2. If there is one change of sign in your cash flow data, you will get one answer. This is probably the norm, where it costs a certain amount of money to buy into the investment and then you receive money back over the next couple of years.

3. If there are multiple changes in the sign of your cash flow data, there can be multiple IRR values depending on your guess.

REFERENCES

"Using Excel," Ron Person and Mary Campbell. Page 241. Que Corporation, 1988.

"Microsoft Excel Function Reference." Version 3.00, pages 134-135 and 162-163.

"Microsoft Excel for Windows Functions and Macros." Version 3.00, pages 63-64 and 92-94.