# How to calculate NPV in Excel

Discount the future cash flows with NPV and add the upfront amount separately: `=NPV(E1,B3:B7)+B2`.

**Excel:** `=NPV(E1,B3:B7)+B2` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Year | Cash flow |  | Discount rate | 0.08 |
| 0 | -50000 |  | NPV |  |
| 1 | 12000 |  |  |  |
| 2 | 15000 |  |  |  |
| 3 | 18000 |  |  |  |
| 4 | 16000 |  |  |  |
| 5 | 14000 |  |  |  |

Formula in E2: `=NPV(E1,B3:B7)+B2`

Result: $9,548.82

## How it works

NPV discounts every value it is given as if it arrives at the end of a period: the first by one year, the second by two, and so on. That fits the yearly cash flows, but the $50,000 investment happens today and shouldn't be discounted at all, so it is added outside the function. A positive NPV means the project earns more than the 8% discount rate.

## Other ways

- `=SUMPRODUCT(B2:B7/(1+E1)^A2:A7)`: Discount every cash flow by its own year, year 0 included. Same result, and it shows the mechanism.
- `=NPV(E1,B2:B7)`: The common mistake: putting the investment inside NPV discounts it by a year too. (Too low by about $700: every flow is discounted one year too many.)

## Common mistakes

- Putting the year-0 investment inside NPV is the most common mistake. Keep it out and add it.
- NPV assumes equal periods. For cash flows on specific dates, Excel has XNPV, which takes a range of dates.
- Blank cells in the range are skipped, which shifts later flows a year earlier. Put 0 in years with no cash flow.

## Questions

**What does a negative NPV mean?**

The project returns less than the discount rate, so on these numbers it destroys value.

**How do I use monthly cash flows?**

Use a monthly rate: `=NPV(E1/12,B3:B62)+B2`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-npv