The Excel XNPV function is a financial function that calculates the net present value (NPV) of an investment using a discount rate and a series of cash flows that occur at irregular intervals.
Syntax:= XNPV (rate, values, dates)
The XNPV function syntax has the following arguments:
- Rate Required. The discount rate to apply to the cash flows.
- Values Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value.
-
Dates Required. A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.
Example: Let’s look at some Excel XNPV function examples and explore how to use the XNPV function as a worksheet function in Microsoft Excel:
Syntax: =XNPV(D2,B2:B7,C2:C7)
Result:
Note:
Below is a helpful list of points to remember:
- Numbers in dates are truncated to integers.
- XNPV doesn’t discount the initial cash flow (it brings all cash flows back to the date of the first cash flow). Subsequent payments are discounted based on a 365-day year.
- #NUM! error – Occurs when either:
- The values and dates arrays are of different lengths; or
- 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.
- XNPV is calculated as follows:
where:
- di = the ith, or last, payment date.
- d1 = the 0th payment date.
-
Pi = the ith, or last, payment.
