Microsoft KB Archive/140700

= Microsoft Knowledge Base =

XL: XNPV Function Appears to Truncate First "Values" Argument
Last reviewed: March 26, 1997

Article ID: Q140700

4.00 5.00 5.00c 7.00 | 4.00 5.00 5.00a

WINDOWS             | MACINTOSH kbprb

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}) the result is 4.3..., 0.9 less than the correct result.

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 The result of this formula is 5.2.

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.