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

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:

  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%.

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.

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.


KBCategory: kbother
KBSubcategory:

Additional reference words: noupd


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.