How to Calculate NPV – Step By Step Walkthrough

How to calculate NPV?

Starting from the basics and building you up to a pro, we’ll teach you how to calculate NPV (Net Present Value) and why it’s calculated the way it is.

You’ll learn the mechanical rule of the net present value method because it’s easy. But more importantly, you’ll learn about the logic behind the NPV, which will set you up for actually using it.

This post will show you how to calculate NPV using the equations in a discounted cash flow setting, as well as Excel. But just in case you’re not familiar with any of it at all, let’s start with the basics…

What is the NPV?

The Net Present Value (NPV) is one of the most powerful and widely used investment appraisal techniques out there. In its simplest form, the NPV is calculated by…

   

Where “PV” is the Present Value of Future Expectations (more on this later), and “I” is the Initial Investment (i.e. the amount of money we’re investing today). You can also think of it as the initial cost of a project, for instance. Or the initial cash investment / injection required to get the project going.

Often times textbooks and learning materials suggest that the Net Present Value is calculated by “adding up all the cashflows”. While this is mathematically sound, it is not as intuitive as the “PV – I” approach on how to calculate NPV.

The logic behind the NPV calculation

To understand the logic behind the NPV calculation, we need to gain a solid understanding of the logic behind each of the two variables in the NPV formula.

What is the PV?

The PV in this context refers to the value of all the money we expect to earn in the future, expressed in today’s terms. In other words, it tells us how much that future cash flow is worth to us right here, right now (i.e. the Present Value). Put differently, it’s telling us the future value in today’s value or terms.

What is “I”?

The I (or Investment) is what we’re paying today in order to earn cash flow in the future.

What we invest vs. what we earn

“PV” therefore focuses on the money we earn, or the cash inflow. And “I” focuses on the money we invest, or the cash outflow.

If we earn more than we invest, we’re making money and should therefore accept such a project. Mechanically, this would translate to the rule of “Accept Projects that have a positive NPV”.

If however we earn less than we invest, then we’re losing money! To avoid that, we should reject such a project. And again, mechanically, this would translate to the rule of “Reject Projects that have a negative NPV”.

---

Related Course

This Article features a concept that is covered extensively in our course on Investment Appraisal Mastery.

If you’re interested in mastering the NPV and other investment appraisal / capital budgeting techniques, then you should definitely check out the course.

---

Mechanical rule for NPV (and why you shouldn’t pay attention to it)

You’ve probably already heard of the mechanical rules:

• Accept projects that yield a positive NPV
• Reject projects that have a negative NPV

While this is a handy rule for an Excel spreadsheet (because you could easily use an IF function to filter projects after applying an Excel NPV function)… it’s not the best way to think about NPV in terms of learning and understanding.

You’re much better off deciding whether to accept or reject projects based on the financial logic of the NPV method. That is to say, we accept projects if we expect to make money, and reject projects if we expect to lose money.

That’s not hard now, is it?

Expanding the basic equation…

Now that you know the logic behind the net present value formula, let’s look at how to calculate NPV by expanding the equation.

   

Recall that “PV” is the Present Value of Future Cash flows. Mathematically, the NPV looks like this (when we expand the “PV” part)…

   

If the equation above is freaking you out, please don’t let it freak you out. Chances are, it’s the funky symbol that’s scaring you. That thing (which looks like a “funky E”) is called the “Sigma Operator”. Essentially, it’s a sum operator, adding everything that’s in front of it, starting from the point where t = 1, and going all the way up to and including the nth observation.

When we expand the “PV” part further, it looks like this…

   

This equation and the one that precedes it are equivalent, of course. Notice that we’re just adding all the elements starting from t = 1, and going all the way up to and including t = n (the nth observation).

If the Cash flow remain constant each year, and the discount rate (“r”) remains unchanged, and the cashflows occur for a finite period, then the stream of cash flow is an ‘Annuity’ – so the equation transforms into…

   

As complicated as this may look at first sight, it makes a lot more sense when we apply it with real numbers. Let’s look at an example.

Calculating the NPV with an example question

The Government of Elysium is looking to invest $3 billion today for its Metro project, which is expected to earn $100 million in net ticket cash flow every year for the next 100 years. If the appropriate cost of capital is 6%, should the Government proceed with this project?

Using exactly the same equations as above, here’s how we can calculate NPV and solve this question…

   

   

   

Notice that we’ve written $100,000,000 as $100m and $3,000,000,000 ($3 billion) as $3,000m. Because 1,000 million is 1 billion. How you write out the numbers doesn’t really matter as long as you’re consistent. For instance, we could work in billions in which case the cash flow would be 0.1bn and the investment would be 3bn. We’re just choosing to work in millions, so when expanded, the equation above would look like this…

   

Since the cashflows remain constant (at $100m each year), the discount rate remains unchanged (at 6%), and the project is for a finite duration (100 years), the stream of cash flow is an Annuity. The equation thus transforms into…

   

Solving this gives us a of $1,661.75 million and an initial investment of $3,000m (i.e. $3 billion). This project therefore yields a negative NPV of -$1,338,245,377.

Mechanical vs. Logic based decision making

While the mechanical rule of accepting vs. rejecting would immediately suggest we reject this project, it’s important to consider stakeholder needs as well as the context of the project. In this example, we’re dealing with a Government led project. Unlike companies – whose objective is to maximise value – Government objectives tend to be socially oriented. Their policies are usually made to benefit citizens above all else.

Even though this “Metro project” results in a negative NPV, the Government may still choose to go ahead with the project! They can do that for a whole host of justifiable, rational reasons too. For example, these cash flow ($100m) do not factor in the new employment and business opportunities a project with such scale and magnificence would result in. In real life, a Government would quantify those other benefits into the NPV computation. That allows them to get a more realistic result and expectation.

How to calculate NPV on Excel?

While calculating NPV by hand is a lot of fun (seriously!), using Microsoft’s Excel can be a lot quicker. Of course it’s unlikely you’ll be allowed to do this in an exam setting, but you’ll certainly do it this way in the “real world” once you finish your studies.

Excel actually has a dedicated “NPV” function, and it relies on users inputting two types of data:

1. The rate (this is ‘r’, or the discount rate), and
2. The cash flow (defined in Excel as ‘value’)

The problem with the NPV function in Excel however, is that it is only useful for short duration projects. Or if you’ve physically typed out all the cash flow in separate cells. And that’s just painful.

Excel PV vs Excel NPV function

To counter that issue, and to deal with annuities seamlessly, you should use Excel’s “PV” function instead of the “NPV” function. But be very careful with the positive / negative signs you use to reflect cash inflow vs cash outflow! Using the PV function on Excel, we can solve the question above in one single cell!

Excel wants a few variables / data for the PV function including:

• Rate (this is the discount rate, i.e. the cost of capital)
• nper (this is the ‘number of periods’, i.e. the duration of the project).
• pmt (this is the annual cash flow we expect to earn; you need to use a minus sign for positive cash flows, and a plus sign for negative cash flows).
• fv and type are not really necessary for these types of questions – it’s just easier to subtract the initial investment from the PV function result!

So here’s what we get from Excel’s PV function minus the initial investment (note again – we’re working in millions).

Crucially though, Excel will assume that the first cash flow (‘value’) occurs exactly one year from today unless you specify otherwise. And that’s okay because most NPV questions use the assumption that the first cashflow occurs exactly one year from now, and all ensuing cashflows occur annually thereafter.

---

Related Course

Do you want to become a PRO at Investment Appraisal / Capital Budgeting?

Explore the Course