The formula
Correct NPVExcelGoogle Sheets
=NPV(B8,B3:B6)+B2In D2 → $4,017.99
Common mistake (discounts year 0)ExcelGoogle Sheets
=NPV(B8,B2:B6)In D3 → $3,720.36
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Cash flow | ||
| 2 | 0 | -50,000 | $4,017.99 | |
| 3 | 1 | 15,000 | $3,720.36 | |
| 4 | 2 | 18,000 | ||
| 5 | 3 | 20,000 | ||
| 6 | 4 | 12,000 | ||
| 7 | ||||
| 8 | Discount rate | 8% |
How it works
- B8
- Discount rate per period.
- B3:B6
- Cash flows for years 1–4, evenly spaced.
- +B2
- The year-0 cash flow (negative), not discounted.
Common errors and fixes
NPV smaller than expected?
You included year 0 inside NPV. Move it outside.
Blank cells ignored?
NPV skips empty cells, which shifts later years. Enter 0 instead of leaving blanks.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.