The Excel XNPV Function

XNPV Function Equation

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

Excel XNPV Equation

where,

di   =   the i'th payment date
d1   =   the of 0'th payment date
Pi   =   the i'th 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

 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


Further details and examples of the Excel Xnpv function are provided 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 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 dates by Excel.