NPV function
Quick answer
NPV discounts a series of future cash flows back to today at a given rate, so you can judge whether an investment beats your cost of capital.
- Syntax
=NPV(rate, value1, [value2], ...)- Most common use
=NPV(D2,B3:B6)+B2returns $8,565 (Discount years 1 to 4, then add the year-0 outlay)
The catch: NPV assumes the first value arrives one period from now. An upfront investment at time zero must be added outside the function, not included in it.
- Available in
- Every current version
- Category
- Financial functions
- Returns
- The present value of the cash flows. A positive NPV (after adding the upfront cost) means the project earns more than the rate.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=NPV(rate, value1, [value2], ...)
| Argument | Required? | What it is |
|---|---|---|
rate | Required | The discount rate per period, such as your cost of capital. |
value1 | Required | The first future cash flow, one period from now, or a range of them. |
value2 | Optional | More cash flows, one period apart, in order. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =NPV(D2,B3:B6)+B2 | $8,565 | Discount years 1 to 4, then add the year-0 outlay |
| E3 | =NPV(D2,B2:B6) | $7,786 | Wrong: including year 0 discounts the outlay by a year |
| E4 | =NPV(D2,B3:B6) | $58,565 | Present value of the future cash flows alone |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Year | Cash flow | Rate | Result | |
| 0 | -$50,000 | 10% | $8,565 | |
| 1 | $15,000 | $7,786 | ||
| 2 | $18,000 | $58,565 | ||
| 3 | $20,000 | |||
| 4 | $22,000 |
Practice NPV
From lesson 41, NPV & IRR. Graded instantly, then re-checked on changed data.
Net present value
E4, calculate the project's NPV at the discount rate in E2. Year 0 is an upfront investment.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| NPV is a bit too high or too low | The year-0 investment was included in the range, so NPV discounted it as if it happened a year later. Add it separately: =NPV(rate, B3:B6)+B2. |
| Ignoring the order of cash flows | NPV assumes the values are evenly spaced and in time order. For cash flows on irregular dates, use XNPV, which takes the actual dates. |
| A rate in the wrong units | Use an annual rate for yearly cash flows and a monthly rate for monthly ones. |
Tips
- Check your NPV with IRR: at a discount rate equal to the IRR, the NPV is zero.
Questions
How do I calculate NPV in Excel?
Use =NPV(rate, year1:yearN) + year0. The upfront cost at time zero goes outside NPV because the function assumes the first value is a year away.
Why is Excel's NPV different from my finance calculator?
Most often the year-0 cash flow was included inside NPV. Excel discounts every value in the function by at least one period.
What does a positive NPV mean?
The investment returns more than the discount rate, so it adds value at that cost of capital.
Lessons that use NPV
- Lesson 41: NPV & IRR (Pro)
Answers that use NPV
In other languages
NPV is VNA in Spanish Excel and NBW in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .