The XNPV function is a finance function which can calculate the net present value (NPV) of a series of non-periodic cash flows, based on a specified discount rate. In this guide, we’re going to show you how to use the XNPV function and also go over ome tips and error handling methods.
Supported versions
- All Excel versions
Syntax
Arguments
rate |
Discount rate over one period. |
values |
A series of cash flows. If the first value is cost or payment, it must be a negative value. |
dates |
Date series of cash flows. 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
Note that we’ve used named ranges in this example to make the formulas easier to read. This is not required.
Tips
- The XNPV doesn’t discount the initial cash flow.
- Negative values represent cash paid out, positive values represent cash received.
- If you want to calculate net present value for a series of periodic cash flows, use the NPV function
Issues
#VALUE!
- If an argument is nonnumeric you will get the #VALUE! error.
- An invalid date can give a #VALUE! error.
#NUM!
- Any date value entered that is before the starting date will give a #NUM! error.
- If references of values and date series have different sizes you will get a #NUM! error.