Microsoft Knowledge Base

WINDOWS              | MACINTOSH

The information in this article applies to:

• Microsoft Excel for Windows, versions 4.0, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
• Microsoft Excel for NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

When you use the XNPV() function to calculate the Net Present Value, an incorrect value may be returned.

CAUSE

If the first value of the Values argument contains a fraction, the fractional part of the value is ignored in the calculation.

For example, the Value series (1, 2, 3) will produce the same result as the Value series (1.9, 2, 3).

The second and subsequent values in the Values argument are treated correctly in the calculation.

WORKAROUND

To obtain the correct result, add the fractional amount of the first value in the Values series to the XNPV() formula result. For example:

If you enter the following formula in a worksheet cell

   =XNPV(0.1,{1.9,2,3},{30000,31000,32000})

To obtain the correct result for the second formula, add the fractional amount of the first value in the Values series (".9" in this case) to the formula as shown below:

   =XNPV(0.1,{1.9,2,3},{30000,31000,32000})+0.9

STATUS

Microsoft has confirmed this to be a problem in the Microsoft Products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The XNPV() function contains three arguments:

   Rate: The discount rate to apply to the cash flows.

   Values: A series of cash flows that correspond to a schedule of
   payments in dates.

   Dates: A schedule of payment dates that corresponds to the cash flow
   payments.