How to calculate NPV in Excel
Formula (Excel and Google Sheets)
=NPV(E1,B3:B7)+B2Discount the future cash flows with NPV and add the upfront amount separately: =NPV(E1,B3:B7)+B2.
- Result on the example
- $9,548.82 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=NPV(E1,B3:B7)+B2Result9548.816157
E18%The value in E1.
B3:B75 rows × 1 col: $12,000, $15,000, $18,000, $16,000, …A range: 5 cells in one column.
NPV(E1,B3:B7)59548.81616NPV: Net present value of future cash flows (first flow one period out).- rate
E18% - value1
B3:B75 rows × 1 col: $12,000, $15,000, $18,000, $16,000, …
- rate
B2-$50,000The value in B2.
NPV(E1,B3:B7)+B29548.816157The + operator adds.
In Google Sheets
The same formula works in Google Sheets: =NPV(E1,B3:B7)+B2. We ran it in Google Sheets mode on the same data and it returns $9,548.82.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(B2:B7/(1+E1)^A2:A7) | $9,548.82 | Discount every cash flow by its own year, year 0 included. Same result, and it shows the mechanism. |
| =NPV(E1,B2:B7) | $8,841.50Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate NPV
E2, calculate the net present value of the project: the investment in B2 is paid today, and the cash flows in B3:B7 arrive at the end of years 1 to 5. The discount rate is in E1.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.