The NPV function in Excel
NPV tells you what a series of future payments is worth today, once a discount rate is applied.
What NPV does
Money you receive later is worth less than money today. NPV reduces each future payment by the discount rate, a little more for each extra year, and adds them up. Excel treats the first value as arriving one period from now, so a cost paid today is not part of the range. Subtract it from the NPV result.
Syntax
=NPV(rate, value1, [value2], ...)
- rate
- The discount rate for each period, such as 0.1 for 10%.
- value1, value2, ...
- The payments, earliest first. A range such as B2:B4 works well.
Worked example
| A | B | |
|---|---|---|
| 1 | Discount rate | 0.10 |
| 2 | Year 1 | 1000 |
| 3 | Year 2 | 1500 |
| 4 | Year 3 | 2000 |
| 5 | Present value | 3651.39 |
At a 10% discount rate, the three payments are worth 3,651.39 in today's money, less than their 4,500 total.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Finance
Compare investments or projects by what they are worth today.
Engineering
Weigh up the cost of equipment against the savings it brings.
Common mistakes
- Including today's cost in the range. NPV assumes the first value is one period away, so take the starting cost off afterwards.
- Listing the cash flows out of order. NPV discounts them in the order given, earliest first.
- Using a percentage typed as a whole number. Use 0.1 or 10% for ten per cent.
The NPV course
- 1Present value of cash flowsFree practice belowPractise
- 2Take off the starting costMembers lesson
- 3Cash flows across a rowMembers lesson
- 4Is it worth doing?Members lesson
- 5Compare two optionsMembers lesson
Common questions
What does a negative NPV mean?
The payments are worth less today than the cost, so the project loses value at that discount rate.
What is a good discount rate?
It depends on your cost of money or the return you could get elsewhere. Use the rate your organisation sets.
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.