NPV calculates the net present value of a series of future cash flows by discounting each one back to today using a given rate. A positive result means the investment is expected to add value; a negative result means it is expected to lose value.
=NPV(rate, value1, [value2], …)What does NPV do?
The NPV function in Excel returns the net present value of an investment based on a discount rate and a series of future cash flows. In plain terms, it answers a simple question: if money today is worth more than money later, what is this stream of future payments or receipts worth right now? Excel discounts each cash flow by the rate you supply and adds them together, giving you a single number you can compare against the cost of the investment.
This matters because office workers and students routinely face decisions that involve money spread over time: buying equipment, launching a project, or choosing between two savings plans. Comparing raw totals ignores the time value of money. NPV puts every option on the same footing by expressing future cash flows in today's money, so a project that looks profitable on paper may turn out to be a poor choice once the cost of capital is considered.
A typical situation: your team can spend 10,000 on a new tool that is expected to save 3,000 a year for five years. Is that a good deal? With NPV you enter the annual savings and a discount rate that reflects your organisation's cost of funds. If the result is positive, the savings are worth more than the upfront cost; if negative, the tool destroys value at that rate. The function turns a vague judgement into a defensible number.
When to use NPV
- Deciding whether to buy equipment or software based on expected annual savings.
- Comparing two projects that have different cash flow timings and sizes.
- Evaluating a savings or investment plan that pays out over several years.
- Checking whether a lease or a purchase is cheaper once time value is included.
- Teaching or studying capital budgeting and discounted cash flow concepts.
- Screening a business case before presenting it to management.
NPV syntax
=NPV(rate, value1, [value2], …)| Argument | Required? | What to enter |
|---|---|---|
rate | Required | The discount rate for one period, as a decimal or percentage. For example, 0.08 or 8% means future cash flows are discounted at 8% per period. |
value1 | Required | The first cash flow. It can be a number, a cell reference, or a range of cells. Cash outflows are negative, inflows positive. |
value2 | Optional | Additional cash flows, one per period, in order. You can list them individually or supply a range such as B2:B6. |
What it returns: A single number: the net present value of the cash flows at the given discount rate. A positive value means the cash flows are worth more than their nominal total after discounting; a negative value means they are worth less.
NPV examples
1 Example 1: One future payment
This is the simplest possible case: a single payment of 1,100 received one year from now, discounted at 10%. The formula =NPV(B2,B3) discounts 1,100 by 10%, giving 1,000. That tells you the future payment is worth 1,000 in today's money. If you could buy that future payment for less than 1,000, it would be a good deal.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Discount rate | 0.1 | 1000 | ||
| 3 | Cash flow in year 1 | 1100 | |||
| 4 |
2 Example 2: Three years of equal savings
Here a project saves 2,000 at the end of each of the next three years, and the discount rate is 10%. The formula =NPV(B2,B3:B5) discounts each year separately and adds the results. Year 1 is worth 1,818.18, year 2 is worth 1,652.89, and year 3 is worth 1,502.63, for a total of 4,973.70. This is less than the undiscounted 6,000 because later money is worth less today.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash flow | |||
| 2 | Rate | 0.1 | 4973.703982 | ||
| 3 | Year 1 | 2000 | |||
| 4 | Year 2 | 2000 | |||
| 5 | Year 3 | 2000 | |||
| 6 |
3 Example 3: Project with an upfront cost
A project costs 5,000 today and returns 2,000, 3,000, and 4,000 over the next three years. The discount rate is 8%. Because NPV discounts the first value by one period, the upfront cost must be added outside the function: =B2+NPV(B3,B4:B6). The discounted returns total 7,722.97, and subtracting the 5,000 cost gives 2,722.97. A positive result means the project adds value at an 8% cost of capital.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Amount | |||
| 2 | Initial cost | -5000 | 2599.197277 | ||
| 3 | Discount rate | 0.08 | |||
| 4 | Year 1 return | 2000 | |||
| 5 | Year 2 return | 3000 | |||
| 6 | Year 3 return | 4000 | |||
| 7 |
4 Example 4: Comparing two savings plans
Two plans each pay 10,000 in total over four years, but the timing differs. Plan A pays 2,500 each year; Plan B pays 1,000, 2,000, 3,000, and 4,000. At a 6% discount rate, Plan A has an NPV of 8,662.54 and Plan B has an NPV of 8,674.19. Plan B is slightly better because more of its cash arrives earlier. The formulas =NPV(B2,B3:B6) and =NPV(B2,C3:C6) make the comparison direct.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Year | Plan A | Plan B | |||
| 2 | Rate | 0.06 | 8662.764032 | |||
| 3 | Year 1 | 2500 | 1000 | |||
| 4 | Year 2 | 2500 | 2000 | |||
| 5 | Year 3 | 2500 | 3000 | |||
| 6 | Year 4 | 2500 | 4000 | |||
| 7 |
5 Example 5: Five-year business case with a terminal value
A department considers a 20,000 system that saves 6,000 a year for five years and can be sold for 2,000 at the end of year 5. The discount rate is 9%. The formula =B2+NPV(B3,B4:B8) adds the initial cost to the discounted savings and resale value. The discounted inflows total 24,975.89, so the NPV is 4,975.89. A positive NPV supports the purchase, and the size of the number helps rank it against other proposals.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Amount | |||
| 2 | Initial cost | -20000 | 4637.770353 | ||
| 3 | Discount rate | 0.09 | |||
| 4 | Year 1 saving | 6000 | |||
| 5 | Year 2 saving | 6000 | |||
| 6 | Year 3 saving | 6000 | |||
| 7 | Year 4 saving | 6000 | |||
| 8 | Year 5 saving plus resale | 8000 | |||
| 9 |
Common NPV errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | One of the value arguments is text that cannot be read as a number, such as a label inside the range. | Remove text from the cash flow range or reference only numeric cells. Blank cells are ignored, but labels are not. |
| #NUM! | The rate is -1 or lower, which makes the discounting formula invalid. | Use a rate greater than -1, normally a positive percentage such as 0.05 or 5%. |
| Wrong sign on the result | The initial cost was included as value1 inside NPV, so it was discounted by one period instead of being subtracted today. | Add the initial cost outside the function: =initial_cost+NPV(rate, later_cash_flows). |
| #DIV/0! | The rate cell is empty or refers to a blank cell, which Excel treats as zero in some contexts and can distort results. | Enter an explicit rate in the rate cell and confirm the reference points to it. |
Tips for using NPV
- Keep the discount rate in its own cell so you can test scenarios quickly by changing one value.
- Enter cash outflows as negative numbers and inflows as positive numbers so the result has a clear meaning.
- Remember that NPV assumes the first value occurs at the end of period 1; add any cost incurred today outside the function.
- Use a range such as
B4:B8rather than typing each value, which reduces typos and makes the formula easier to audit. - Compare NPV across options rather than in isolation: the highest positive NPV is usually the strongest choice.
- Label the rate clearly as a per-period rate, because NPV does not know whether periods are months or years.
Frequently asked questions
What does a negative NPV mean?
A negative NPV means the future cash flows, once discounted at your rate, are worth less than the upfront cost. At that discount rate the investment is expected to reduce value, so you would normally reject it or look for a cheaper alternative.
Should the initial investment be inside or outside the NPV formula?
Put it outside. NPV discounts the first value by one period, so an upfront cost entered inside would be discounted as if it occurred at the end of year 1. Add it separately: =initial_cost+NPV(rate, future_cash_flows).
Does NPV work with monthly cash flows?
Yes, but the rate must also be monthly. If your cash flows are monthly, divide an annual rate by 12 or use the equivalent monthly rate, otherwise the result will be misleading.
What is the difference between NPV and IRR?
NPV gives a value in currency at a rate you choose, while IRR gives the rate at which NPV equals zero. NPV is generally preferred for choosing between projects because it reflects the size of the gain.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. NPV reference