Using the Excel XNPV Function
SummaryThe XNPV function is one of Excel's financial functions and returns the present value for a series of future cash flows. When using the XNPV function, the cash flows do not need to be periodic (i.e. they can occur at irregular intervals). XPNV does not discount the initial cash flow.
Use the NPV function when the cash flows are periodic.
Syntax=XNPV (rate, values, dates)
Required. Discount rate used in a period. Rate is provided as a percentage. For example, use .1 for 10%.
Required. A series of cash flows that corresponds to a schedule of payments provided as dates. The first payment is optional and normally represents a cost or payment that occurs at the start of an investment. If the first value will be a cost or payment, it should be entered as a negative value.
The series of payments must have at least one positive and one negative value.
Required. The dates that correspond to the cash flows in the values argument.
Enter date values using the DATE function to avoid errors that occur when dates are accidentally entered as text.
Usage NotesXNPV returns the net present value (NPV) of an investment by taking into consideration a discount rate and a series of cash flows that occur at irregular intervals. Cash flows must be listed with corresponding dates in chronological order.
Negative figures in the values argument represent cash paid out. Positive figures represent cash received.
The XNPV function will return the following errors:
- #NUM! error if the function doesn't contain at least one positive and one negative value;
- #NUM! error if any value in the date argument precedes the starting date;
- #NUM! error if the values and dates argument contain a different number of values;
- #VALUE! error if any value in the date argument isn't a valid date;
- #VALUE! error if any value is non-numeric