The Excel XNPV Function

Home » Excel-Built-In-Functions » Excel-Financial-Functions » Excel-Xnpv-Function

Search this site:

Custom Search

Related Function :

NPV Function

XNPV Function Equation

The Excel XNPV Function uses the following equation :

where,

d_i = the i'th payment date
d₁ = the of 0'th payment date
P_i = the ith payment

Basic Description

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

The format of the function is :

XNPV( rate, values, dates )

Where the arguments are as follows :

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

Example

The spreadsheet on the right shows an example of the Xnpv function. The data used is in cells A1-B7 of the spreadsheet, with the discounted rate shown in cell B1, the dates of the returns are stored in cells A2-A7 and the values of the returns are stored in cells B2-B7.

The Xnpv function used to calculate the net present value of the investment with these terms is shown in cell C10.

This function gives the result $4,449.60

More examples of the Excel Xnpv function can be found on the Microsoft Office website

Trouble Shooting

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
or
	-	any of the other dates are before the start date

#VALUE!

Occurs if either:

	-	any of the supplied rate or values arguments are non-numeric
or
	-	any of the supplied dates are not recognised as dates by Excel

#NAME?

Occurs when Analysis ToolPak add-in is not enabled in your Excel.

To enable this add-in in Excel 2003 :

From the Tools drop-down menu, select the option Add-Ins ...
An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK

To enable this add-in in Excel 2007 :

Click the Microsoft button on the top left of your spreadsheet and select the Excel Options button
From the menu on the left hand side, select Add-Ins
In the 'Manage:' box, select Excel Add-ins and click Go...
An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK