# The Excel XNPV Function

XNPV Function Equation

The Excel XNPV Function uses the following equation to calculate the net present value of an investment:

where,

di = the i'th payment date
d1 = the of 0'th payment date
Pi = the ith payment

Related Function:
NPV Function

## Basic Description

The Excel XNPV function calculates the Net Present Value for a schedule of cash flows that is not necessarily periodic.

The syntax of the function is:

XNPV( rate, values, dates )

Where the arguments are listed in the table below:

 rate - The discount rate to apply to the cash flows. values - An array of numeric values, representing payments and income, where:Negative values are treated as payments;Positive values are treated as income.The first payment is optional and denotes a cost or payment at the beginning of the investment. dates - An array of dates corresponding to the array of payments. This array must be the same length as the supplied values array.

## XNPV Function Example

In the above spreadsheet on the right the Xnpv function is used to calculate the net present value of a series of cashflows.

The discounted rate is shown in cell B1 of the spreadsheet, the dates of the returns are stored in cells A2-A7 and the values of the returns are stored in cells B2-B7.

The syntax of the Xnpv function is shown in cell C10.

This function gives the result \$4,449.60

Further information and examples of the Excel Xnpv function can be found on the Microsoft Office website.

## XNPV Function Errors

If you get an error from the Excel Xnpv function this is likely to be one of the following:

Common Errors
 #NUM! - Occurs if either:The values and dates arrays have different lengths;any of the other dates are before the start date #VALUE! - Occurs if either:Any of the supplied rate or values arguments are non-numericany of the supplied dates are not recognised as dates by Excel #NAME? - Occurs when Analysis ToolPak add-in is not enabled in your Excel. You will need to enable the add-in if you want to use the Excel Xnpv function.