Microsoft KB Archive/69571

{| = Multiple IRRs When More than One Sign Change in the Cash Flow =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q69571

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:

  Type the following information onto a spreadsheet in Excel: A1:   500 A2: -1100 A3:   600   Type the following formula into cell A4 on the spreadsheet: A4: =IRR(A1:A3,10%) The answer will be 20%.   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%. 

Both of these answers are acceptable. It's up to you to decide which one of these values you want to use. If you use the NPV function on the same set of data and use both IRR values of 0% and 20% as the rate, you will see that both result in a value of zero, which they should. 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.