Author: Oscar Cronquist Article last updated on May 01, 2019

The XNPV function calculates the net present value for cash flows that may or may not be periodic. Net present value is used in investment planning and capital budgeting.

Formula in cell C12:

=XNPV(C2, B5:B10, C5:C10)

Excel Function Syntax

XNPV(rate, values, dates)

Arguments

rate Required. The discount rate.
values Required. A number of values representing cash flow, the first value is optional and may be a cost or payment. Make sure the first value is a negative value if it is a cost or payment. At least one value must be negative and one value must be positive.
dates Required. Dates that correspond to the cash flow values.

Comments

The XNPV function returns:

  • #VALUE! error if arguments are non-numeric.
  • #NUM! error if any number in dates precedes the starting date.
  • #NUM! error if values and dates don't have the same number of values.
  • #VALUE! error if any number in dates is not a valid date.

The formula looks like this:


di = the ith, or last, payment date.
d1 = the 0th payment date.
Pi = the ith, or last, payment.