The Excel XNPV Function uses the following equation to calculate the net present value of an investment:
where,
d_{i} = the i'th payment date
d_{1} = the of 0'th payment date
P_{i} = the i'th payment
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:
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:

dates    An array of dates corresponding to the array of payments. This array must be the same length as the supplied values array. 
A  B  C  

1  5%   Discount rate  
2  Jan012016  $10,000   Initial investment cost 
3  Feb012016  $2,000   Return from year 1 
4  May012016  $2,400   Return from year 2 
5  Jul012016  $2,900   Return from year 3 
6  Nov012016  $3,500   Return from year 4 
7  Jan012017  $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 A2A7 and the values of the returns are stored in cells B2B7.
The Xnpv function, as shown in cell C10, is:
which gives the result $4,447.94
Further details and examples of the Excel Xnpv function are provided on the Microsoft Office website.
If you get an error from the Excel Xnpv function this is likely to be one of the following:
#NUM!    Occurs if either:

#VALUE!    Occurs if either:
