FormulaCamp
XNPV with worked examples you can test in the live grid. The argument details are in the Excel guide.
The arguments and common errors are covered in the XNPV Excel guide; check the function help in Google Sheets for its own argument list.
| A | B | |
|---|---|---|
| 1 | Date | Cash flow |
| 2 | 1/1/2024 | -1000 |
| 3 | 7/1/2024 | 600 |
| 4 | 1/1/2025 | 600 |
Formula: D2: =ROUND(XNPV(0.1,B2:B4,A2:A4),2)
Result: 117.46
The first flow sits on the start date and is not discounted; the others are discounted by their day counts over 365.
| A | B | |
|---|---|---|
| 1 | Date | Cash flow |
| 2 | 1/1/2024 | -1000 |
| 3 | 1/1/2025 | 1100 |
Formula: D2: =ROUND(XNPV(0.05,B2:B3,A2:A3),2)
Result: 47.48
The 1,100 arrives 366 days later and is discounted slightly more than one year of interest.
| A | B | |
|---|---|---|
| 1 | Date | Cash flow |
| 2 | 1/1/2024 | -1000 |
| 3 | 7/1/2024 | 600 |
| 4 | 1/1/2025 | 600 |
Formula: D2: =XNPV(0,B2:B4,A2:A4)
Result: 200
At 0% nothing is discounted, so the result is simply the sum of the flows.
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =XNPV(10%,B2:B5,A2:A5)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Cash flow | Result | |
| 2 | 1/1/2026 | -10000 | 1344.63782042 | |
| 3 | 6/1/2026 | 3000 | ||
| 4 | 1/1/2027 | 4500 | ||
| 5 | 6/1/2027 | 5000 |
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.
Enter — commit · F2 — edit · F4 — $ in a reference · Ctrl+C/Ctrl+V — copy and paste · Ctrl+Z — undo
Esc or F6 — leave the sheet for the «Check» button · Shift+Tab in the first cell — back to the page