XNPV Function in Excel

In this article, you will learn about the XNPV function, the formula syntax and usage of the function in Microsoft Excel

XNPV Function in Excel

The XNPV function in Excel is a financial function. It calculates the net present value (NPV) of an investment using a discount rate and a series of cash flows that occur at irregular intervals.

Syntax

=XNPV (rate, values, dates)

Arguments 

• rate – Discount rate to apply to the cash flows.
• values – Values representing cash flows.
• dates – Dates that correspond to cash flows.

Usage Notes and Possible Errors

• As we know Excel stores dates as sequential series of numbers, i.e., January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 as it is 39,448 days after January 1, 1900.
• Numbers in dates are truncated to integers.
• When a nonnumeric argument is entered, XNPV returns the #VALUE! error value.
• In case of the date is not a valid date, XNPV returns the #VALUE! error value.
• If the given date precedes the starting date, XNPV returns the #NUM! error value.
• If values and dates contain a different number of values, XNPV returns the #NUM! error value.
• XNPV is calculated as follows:

where:

• di = the ith, or last, payment date.
  • d1 = the 0th payment date.
  • Pi = the ith, or last, payment.

How to use the XNPV function in Excel?

Using this function in a WS is simple; all you need to do is enter the function as a formula of the cell in the formula bar.

Take a look at the given example

To find out the net present values, Enter the given values in column A, enter the dates in Column B, and in Column C, enter the following formula

Formula: =XNPV (.15, A2:A6, B2:B6)

Here, the rate of discount is given as 15% or 0.15 and A2 refers to the cell name or the cell address.