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.
XNPV FORMULA
=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.
XNPV IN Excel
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.
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
- Numbers in dates are indicated as integers
- XNPV doesn't discount the initial cash flow
#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.