How to calculate NPV step by step
Discount each future cash flow back to today, then add them up with what you spend now. For the example at 8%, that comes to $7,961.
NPV = CF0 + CF1 ÷ (1 + r) + CF2 ÷ (1 + r)^2 + … + CFn ÷ (1 + r)^n| Year | Cash flow | Divide by | Present value |
|---|---|---|---|
| 0 | -$50,000 | 1.0000 | -$50,000.00 |
| 1 | $12,000 | 1.0800 | $11,111.11 |
| 2 | $15,000 | 1.1664 | $12,860.08 |
| 3 | $16,000 | 1.2597 | $12,701.32 |
| 4 | $16,000 | 1.3605 | $11,760.48 |
| 5 | $14,000 | 1.4693 | $9,528.16 |
| Total | $23,000 | $7,961.15 |
The project brings in $23,000 more than it costs in plain cash, but only $7,961 of that is value above your required return. The rest pays for waiting. Year 0 isn't discounted because it happens today.
What discount rate should you use for NPV?
Use the return you'd need to make the project worth the risk. That's often what the money costs you, or what you could earn elsewhere at similar risk. There's no universal right number, so test a range. The federal Office of Management and Budget's Circular A-94, written for government projects, makes the same point about direction: the higher the rate, the lower the present value of future benefits and costs.
| Discount rate | NPV | Verdict |
|---|---|---|
| 0% | $23,000 | Clears the rate |
| 4% | $14,815 | Clears the rate |
| 8% | $7,961 | Clears the rate |
| 10% | $4,948 | Clears the rate |
| 12% | $2,173 | Clears the rate |
| 15% | -$1,594 | Falls short |
| 20% | -$6,982 | Falls short |
| 25% | -$11,467 | Falls short |
That pattern is the NPV profile the calculator draws. Where the line crosses zero, at 13.69%, is the IRR. Keep your forecast and your rate consistent: if your cash flows include price rises, use a rate that includes inflation.
IRR formula with NPV: how the two connect
The IRR is the discount rate that makes NPV exactly zero. There's no direct formula for it once you have more than a couple of periods, so calculators find it by trial, closing in on the rate. If the IRR is above your required return and the flows are conventional, NPV is positive, so the two agree on whether a single project clears the bar.
0 = CF0 + CF1 ÷ (1 + IRR) + CF2 ÷ (1 + IRR)^2 + … + CFn ÷ (1 + IRR)^nThey can disagree when you rank projects, because IRR ignores size. At a 10% discount rate, the small project below has the higher IRR, while the large one adds far more value. If you can only do one, NPV is usually the better guide.
| Project | Cost | Back in a year | IRR | NPV |
|---|---|---|---|---|
| Small project | $10,000 | $16,000 | 60% | $4,545 |
| Large project | $100,000 | $130,000 | 30% | $18,182 |
NPV in Excel: why your spreadsheet gets a different answer
Excel's NPV function assumes the first value in its range comes one period from now. If you select every cash flow including today's, it discounts everything one period too many. On the example, =NPV(8%, all six cells) returns $7,371 instead of $7,961. Microsoft's documentation says to add a first-period flow to the result instead.
=NPV(rate, year 1 to year n cells) + year 0 cellExcel's =IRR() doesn't have this problem, since it takes the whole range, including today's flow. Both functions expect evenly spaced periods. For monthly flows, this calculator converts your annual rate so it compounds correctly: 12% a year is 0.949% a month, not 1%. A $10,000 outlay followed by 12 payments of $1,000 has an IRR of 2.92% a month, or 41.3% a year.
When IRR gives two answers, or none
If cash flows change sign more than once, IRR can have several correct answers. This happens with a cleanup, a big repair, or a reinvestment partway through. A stream of −$1,600, $10,000, and −$10,000 changes sign 2 times and has IRRs of both 25% and 400%. Excel returns only one, depending on your guess, and gives no hint that the other exists.
This calculator scans for all the roots and warns you. In that case, trust NPV at your discount rate. It also shows modified IRR, which always has one answer because it assumes you finance outflows and reinvest inflows at a set rate, here your discount rate. For the example project, that's 11.24%, against an IRR of 13.69%.
Payback, profitability index, and where forecasts go wrong
- Payback is when cash in catches up with cash out: 3.4 years here, assuming cash arrives evenly through each year. Counting the time value of money, discounted payback is 4.2 years. Neither one looks at cash after that point.
- Profitability index is the present value of later flows per dollar invested today: 1.16 here. Above 1 matches a positive NPV, and it helps when your budget is limited.
- Terminal value is often the biggest assumption. Adding a $20,000 resale value in year 5 moves NPV from $7,961 to $21,573. Try a cautious figure as well.
NPV can't check your forecast. Optimistic sales or a too-low rate will produce a confident number that means little. Taxes, financing, and working capital are left out unless you build them into your flows. For a decision of any size, ask an accountant or a financial professional to check the assumptions. To compare with a simple return, see the ROI calculator.
Common questions
What does a negative NPV mean?
The project earns less than your required return. It may still make money in plain cash, but you'd do better at the rate you entered. At 25%, the example project has an NPV of -$11,467, though it still returns more cash than it costs.
Is year 0 discounted?
No. A cash flow today is already in today's dollars, so it's divided by 1. Year 1 is divided by (1 + r), year 2 by (1 + r) squared, and so on.
How many years should an NPV include?
Include the years you can reasonably forecast, usually the useful life of the asset or project, then add a resale or terminal value if there will be one. Later years count for less because of discounting, but they also carry the most uncertainty.
Can I use this for a rental property or a pension?
For a rental, yes: enter rent after expenses and debt payments each year, with the sale proceeds in the last one. A lifetime pension is harder since its length depends on how long you live. You can test an assumed number of years, but the answer depends on that guess.
Can this handle uneven dates?
No. Rows must be evenly spaced, by year, quarter, or month. Excel's XNPV and XIRR handle exact dates if your cash flows are irregular.
Sources
- NPV function · Microsoft Support
- IRR function · Microsoft Support
- MIRR function · Microsoft Support
- Circular A-94, Guidelines and Discount Rates for Benefit-Cost Analysis · Office of Management and Budget