Related Function:

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

Syntax

=XNPV(rate,values,dates)

Arguments

Argument Description
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

Examples

  A B C D
1 Values Dates    
2 -$15,000.00 01-Jan-2013 Initial investment  
3 $3,500.00 01-Feb-2013 Return – period 1  
4 $4,000.00 01-May-2013 Return – period 2  
5 $3,225.00 01-Jul-2013 Return – period 3  
6 $2,600.00 01-Nov-2013 Return – period 4  
7 $3,125.00 01-Jan-2014 Return – period 5  
8 9%   Discount Rate  
9        
10 Formula Result Notes
11 =XNPV(0.09,A2:A7,B2:B7) $739.72 The net present value for an investment with the above cost and returns. The cash flows are discounted at 9 percent

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  any of the supplied rate or values arguments are non-numeric
  any of the supplied dates are not recognized as dates
#NUM! Occurs if either:

  the values and dates arrays have different lengths
  any of the other dates are before the start date
#NAME? Occurs when Analysis ToolPak add-in is not enabled

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

    \[  \text{XNPV} =  \sum_{i=1}^N \frac{P_i}{(1 + rate)^\frac{(d_i \ d_1)}{365} }   \]

where:
  • P1 = the ith, or last, payment
  • di = the ith, or last, payment date
  • d1 = the 0th payment date