Practice free

XNPV function

XNPV returns the net present value of cash flows that fall on specific dates. It is better than NPV when the flows are not evenly spaced.

Example

Read it as a sentence: find the net present value at a 10 percent rate of the cash flows in N1:N5, dated in P1:P5, which gives 291.5747836.

Practice this in the app

Syntax

XNPV(rate, values, dates)

rate (required)
Annual discount rate.
values (required)
Cash flows.
dates (required)
Date for each cash flow; the first is the base date.

Returns: Net present value of dated cash flows.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=XNPV(0.1,{-100,110},{45292,45658}) -0.026108969 Array constants
=XNPV(0.1,Data!N1:N5,Data!P1:P5) 291.5747836 Dated flows
=XNPV(0.1,{-100,110},{"2024-01-01","2025-01-01"}) #VALUE! Text dates
=XNPV(0.1,{-100,110},{-1,365}) #NUM! Negative date
=XNPV(0.1,Data!N1:N5,Data!Q1:Q5) #NUM! First date not earliest
=XNPV(0.1,Data!N1:N5,Data!P1:P4) #NUM! Size mismatch
=XNPV(0,Data!N1:N5,Data!P1:P5) #NUM! Rate 0
=XNPV(-1,Data!N1:N5,Data!P1:P5) #NUM! Rate -1

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Unlike NPV, the first cash flow is not discounted, so include your upfront outflow here.
  • Rate is annual, using a 365-day year.

Where it works

Excel
XNPV is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .