# 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 i'th payment

Related Function:
NPV Function

## Function 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:

 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 outgoing payments;Positive values are treated as income.The first payment is optional and denotes a 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

ABC
1 5% - Discount rate
2Jan-01-2016-\$10,000 - Initial investment cost
3Feb-01-2016\$2,000 - Return from year 1
4May-01-2016\$2,400 - Return from year 2
5Jul-01-2016\$2,900 - Return from year 3
6Nov-01-2016\$3,500 - Return from year 4
7Jan-01-2017\$4,100 - Return from year 5
8
9  Net Present Value:
10  =XNPV( B1, B2:B7, A2:A7 )

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 Xnpv function, as shown in cell C10, is:

=XNPV( B1, B2:B7, A2:A7 )

which gives the result \$4,447.94

For further details and examples of the Excel Xnpv function, see 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 earlier than the start date. #VALUE! - Occurs if either:Any of the supplied rate or values arguments are non-numeric;Any of the supplied dates are not recognised as valid Excel dates.