In this article, you’ll learn how to calculate NPV (Net Present Value). 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.
Advanced learner looking for the NPV formula? Here it is:
Here represents the cash flow at time . The discount rate (aka interest rate, or cost of capital) is denoted by . And represents the initial investment.
Beginner learner? Don’t let the equation above freak you out. We’ve got your back!
This article 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 capital budgeting techniques. In its simplest form, the NPV is calculated by…
Where is the Present Value of Future Cash Flows (or “Expectations”) (more on this later), and 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 cashflow”.
While this is mathematically sound, it is not as intuitive as the 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 (Present Value)?
The Present Value (or 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, the PV 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
therefore focuses on the money we earn (or the cash inflow). And 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: Investment Appraisal 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.
The 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?
How to Calculate NPV (Net Present Value)
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)…
RELATED: How to Calculate Present Value
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.
If it’s still freaking you out, then you should really check out our Financial Mathematics course wherein we’ll help you build your financial math foundation.
Assuming you’re more or less alright now though, let’s consider what happens when we expand the equation.
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 , and going all the way up to and including (the nth observation).
If the Cash flow remains 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.
Want to go further?
Get the Investment Appraisal Mastery Study Pack (for FREE!).
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…
Opening the equation, or expanding , yields…
Plugging in our numbers, we have…
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.
RELATED: Present Value of 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.
NPV Rule – 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, the cash flow ($100m) does 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”.
Excel actually has a dedicated “NPV” function, and it relies on users inputting two types of data:
- The rate (this is , or the discount rate), and
- 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, or 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 cash flow occurs exactly one year from now, and all ensuing cash flows occur annually thereafter.
Okay, hopefully, all of this makes sense and you now know how to calculate NPV both manually as well as on Excel.
If you want to learn about other capital budgeting techniques such as the Internal Rate of Return then feel free to look at our other articles, or check out the course below to gain a true command over these concepts.
Related: Investment Appraisal Course
Do you want to become a PRO at Investment Appraisal / Capital Budgeting?