Function: XNPV

by | Jan 29, 2019 | Excel Formulas

XNPV Function

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

XNPV(rate, values,dates)

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.

=XNPV(rate,cash_flow,time_periods)
formula returns value of 14,862.15 by calculating NPV for each period and adding them up. The mathematical formula for the XNPV function is NPV = F / [ ( 1 + rate ) ^ ( di - d1 ) ] where F, rate, di and d1 stand for future payment (cash flow), rate, the i'th payment date, and the 0'th payment date respectively.

XNPV Function

Download Workbook

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.