What it does
NPV assumes each value arrives one period apart, at the end of the period: the first value is discounted by one period, the second by two. That is why an investment made today is not put inside NPV; it is added outside the formula: =NPV(rate,B3:B6)+B2.
The sign rule applies here too: money paid for the investment is negative, cash coming in from the project is positive. A positive result means the project creates value above the discount rate; a negative one means it falls short of the expected return.
If the cash flows fall on irregular dates, use XNPV; to find the project's rate of return use IRR, or XIRR for irregular dates. When the same amount arrives every period, PV is shorter.
Syntax
=NPV(rate, value1, [value2], ...)=NBD(oran; değer1; [değer2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
rate | Required | The discount rate per period: the expected return or cost of capital, e.g. 0.1 for annual cash flows. |
value1 | Required | The cash coming in (positive) or going out (negative) at the end of the first period; it can be a range. |
value2 | Optional | The cash flows of later periods; up to 254 values or ranges. Empty cells and text in a range are ignored. |
Examples
1The net present value of an investment project
The year-0 investment is paid today, so it is negative and outside the formula. NPV discounts the year 1-4 flows at 10%, and the investment is added on top.
=NPV(D2,B3:B6)+B2| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash Flow | Discount Rate | Net Present Value | |
| 2 | 0 | -250,000.00 | 10.00% | 106,929.17 | |
| 3 | 1 | 80,000.00 | |||
| 4 | 2 | 110,000.00 | |||
| 5 | 3 | 130,000.00 | |||
| 6 | 4 | 140,000.00 |
The result is 106,929.17: positive, so the project beats the 10% return you expect. Had year 0 been inside the range, the investment would also have been discounted and the project would look better than it is.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Net present value as the discount rate changes
The same project is evaluated at three discount rates. The formula in E2 is filled down; the cash flows are locked with $.
=NPV(D2,$B$3:$B$6)+$B$2| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | Cash Flow | Discount Rate | Net Present Value | |
| 2 | 0 | -250,000.00 | 10.00% | 106,929.17 | |
| 3 | 1 | 80,000.00 | 20.00% | 35,802.47 | |
| 4 | 2 | 110,000.00 | 30.00% | -15,183.29 | |
| 5 | 3 | 130,000.00 | |||
| 6 | 4 | 140,000.00 |
- E2 (filled down to E4)
=NPV(D2,$B$3:$B$6)+$B$2
As the rate rises, future cash counts for less and NPV falls: 106,929.17 at 10%, -15,183.29 at 30%. The change of sign between 20% and 30% shows the project's internal rate of return lies in that range (IRR).
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- NPV too low
Cause: Today's investment was included in the NPV range, so Excel discounted it by one period too.
Fix: Add the initial investment outside the formula:
=NPV(D2,B3:B6)+B2.- Rate unit
Cause: The cash flows are monthly but an annual rate was used.
Fix: Put the rate in the same unit as the periods; use a monthly rate for monthly flows.
- Missing period
Cause: A year with no cash was left blank; NPV skips the empty cell and pulls later flows one period earlier.
Fix: Enter 0 instead of leaving periods with no cash blank.
Good to know
- The highest discount rate at which the project still pays off is the rate that makes NPV zero; IRR gives it directly.
- Put the discount rate in a cell and try different values; where the result changes sign is the project's break-even return.
In Nexell
Get Nexell to do it from a sentence
Add a summary to my project table that works out the net present value of the yearly cash flows at a 10% discount rate
Convert to tableBring your project table into the tool and type this. Nexell builds the net present value with real Excel formulas and shows it in the preview; in the downloaded file, the result changes when you change the discount rate.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between NPV and PV?
PV is for cases where the same amount is paid every period; NPV takes a separate amount for each period. NPV also treats the first value as arriving one period from now, so an investment made today must be added outside the formula.