The Formula Gym
XP 0
Streak 0

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

AB
1Discount rate0.10
2Year 11000
3Year 21500
4Year 32000
5Present value3651.39
B5 =ROUND(NPV(B1,B2:B4),2)

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

  1. 1
    Present value of cash flowsFree practice below
    Practise
  2. 2
    Take off the starting costMembers lesson
  3. 3
    Cash flows across a rowMembers lesson
  4. 4
    Is it worth doing?Members lesson
  5. 5
    Compare 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.

See membership