DEV Community

Cover image for XNPV Function

Posted on

XNPV Function

Get the Net Present Value (NPV) for a series of cash flows that may not be periodic.

XNPV uses specific dates that correspond to each cash flow being discounted in the series, whereas the regular** NPV Function** automatically assumes all the time periods are equal.

Image description


=XNPV(Rate, Cash Flows, Dates of Cash Flow)

Rate: The discount rate to be used over the length of the period

Values: This is an array of numeric values that represent the payments and income where;

  • Negative values are treated as outgoing payment.

  • ** Positive values** are treated as *income *

Dates: It is an array of dates corresponding to an array of payments.


Assumptions in the XNPV Example

  • The discount rate is 10

  • Start date is June 30, 2018

  • Cash Flow are received on the exact date they correspond to

  • The time between the start date and first cash flow is only 6 months.

Image description

Image description

Image description

The difference between the XNPV and NPV formulas is that XNPV recognizes that time period between the start date and first cash flow is only 6 months, while the NPV function treats it as a full-time period.

Things to remember about XNPV

  1. Numbers in dates are indicated as integers
  2. XNPV doesn't discount the initial cash flow
  3. #NUM! error occurs when either;
  • The values and date arrays are of different length

  • Any of the other dates are earlier than the start date

  • #VALUE error - occurs when either;

  • The values or rates arguments are non-numeric; or

  • The given dates are not recognized by Excel as valid dates.

Discussion (0)