Microsoft KB Archive/96075

= Microsoft Knowledge Base =

Excel: XIRR with Large Cash Flows May Return #NUM! Error
Last reviewed: September 12, 1996

Article ID: Q96075

- --- The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0 and 4.0a
 * Microsoft Excel for Macintosh version 4.0

SYMPTOMS
The XIRR function in Microsoft Excel returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. In some cases, when some of the cash flow values are extremely large and the return percentage extremely small, a #NUM! error may result, even if the exact guess is specified.

MORE INFORMATION
XIRR takes 3 arguments: values, dates, and guess. The first two are required arguments; the guess argument is optional. The &quot;Microsoft Excel Function Reference&quot; details several cases in which the #NUM! error is returned. If you have ruled out each of these possible explanations and if you expect a relatively small return from a series containing large cash flows, try dividing each of your cash flow values by 10. An example appears below.

Note: If the XIRR function is not available, you must install the Analysis ToolPak add-in macro. For more information, see &quot;Managing Add-in Commands and Functions&quot; in chapter 4 of the &quot;Microsoft Excel User's Guide 2.&quot;

WORKAROUND
  Enter the following in a worksheet: A1: 2/1/93  B1: -100000000   C1: =B1/10 A2: 7/1/93  B2:    1000000   C2: =B2/10 A3: 1/1/94  B3:    1200000   C3: =B3/10 A4: 7/1/94  B4:    -700000   C4: =B4/10 A5: 1/1/95  B5:  100100000   C5: =B5/10  In cell B6, enter the formula &quot;=XIRR(B1:B5,A1:A5,.0084)&quot; (without the quotation marks). A #NUM! error results despite the exact guess. In cell C6, enter the formula &quot;=XIRR(C1:C5,A1:A5)&quot; (without the quotation marks). This will return the correct result, .0084.

STATUS
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. We are researching this problem, and will post more information here in the Microsoft Knowledge Base as it becomes available.