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.
Excel shows 291.5747836
N
P
1
-1000
2024-01-01
2
300
2024-03-01
3
400
2024-10-30
4
500
2025-02-15
5
200
2025-04-01
Data
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.
| Formula | Excel shows | Case |
|---|---|---|
=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.
N
P
Q
1
-1000
2024-01-01
2024-01-01
2
300
2024-03-01
2023-03-01
3
400
2024-10-30
2024-10-30
4
500
2025-02-15
2025-02-15
5
200
2025-04-01
2025-04-01
Data
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 .