NPV tells you what a stream of future cash flows is worth in today’s money, given a discount rate — the core test for whether an investment pays off. The one gotcha: NPV discounts every value you give it, so the initial outlay is handled separately.
The example
Invest $1,000 today; receive $300/year for 4 years; discount at 8%.
| A | B | |
|---|---|---|
| 1 | Discount rate | 8% |
| 2 | Initial (today) | -$1,000 |
| 3 | Year 1-4 each | $300 |
| 4 | NPV | -$6.04 |
The formula
The project’s NPV (a hair negative — just misses an 8% return):
How it works
The key is what NPV discounts and what it doesn’t:
NPV(B1, B3:B6)discounts each future cash flow back to today at 8% and sums them — here, ~$993.96.- NPV assumes the first value arrives one period in the future, so the initial investment (which happens now, at time 0) must be excluded from the NPV call.
- Add it back separately:
+ B2, where B2 is-1000. Result:993.96 - 1000 = -6.04. - A positive NPV means the investment beats the discount rate; negative means it falls short.
The #1 NPV mistake: including the time-0 investment inside the NPV range. That wrongly discounts it by one period. Keep it outside and just add it.
Try it: interactive demo
Adjust the discount rate and annual cash flow.
Variations
Find the break-even rate (IRR)
IRR is the discount rate where NPV = 0. List the flows with the negative initial first:
Irregular dates? Use XNPV
When cash flows aren’t evenly spaced, XNPV takes a dates column:
Initial flow already in the range
If you must include time 0 inside, discount the rest and add it raw — simplest to keep it outside NPV.
Pitfalls & errors
Discounting the initial investment. Putting the time-0 cost inside the NPV range understates NPV. Exclude it and add it with +.
NPV assumes equal periods. Each value is one period apart. For real, irregular dates use XNPV.
Rate must match the period. Annual flows need an annual rate; monthly flows need a monthly rate.
Practice workbook
Frequently asked questions
How do I calculate NPV in Excel?
Why shouldn't I include the initial investment in the NPV range?
What if my cash flows are on irregular dates?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class