Sign in or start a free trial to avail of this feature.
2. Net Present Value
Net present value (NPV) is an alternative to IRR for evaluating investment decisions. Here, I show you how to calculate it manually and using the inbuilt function.
To explore more Kubicle data literacy subjects, please refer to our full library.
NPV Overview (00:03)
Net Present Value, or NPV, is another method of evaluating investments. To calculate NPV, we take all the cash flows we expect to occur from a project, and discount the future cash flows by the desired rate of return. If the sum of all these cash flows is positive, the rate of return is above the desired rate, and we should undertake the project.
Calculating NPV Manually (00:37)
To calculate the NPV for an investment proposal, we need to know the annual cash flows we expect from the proposal. We also need to know the rate of return required for us to invest in the proposal.
We discount each cash flow to account for the time value of money. This reflects the fact that money in the future is worth less than money today. For example, $10,000 one year from now is less valuable than $10,000 today. $10,000 two years from now is worth less again. How much less valuable money is in the future is determined by the required rate of return.
To calculate the discounted cash flows, we divide the cash flow by (1 + Required Rate of Return) ^ (Number of years in the future). To calculate the NPV, we sum the discounted future cash flows and subtract the initial investment. The result tells us how much the investment will return above our required rate of return. If it is positive, we should take on the investment.
Using the NPV formula (03:56)
Excel has an inbuilt function, NPV, for calculating the Net Present Value. It takes two arguments. First is the required rate of return, second is the array of expected cash flows.
There is a significant issue with the NPV function. It actually just calculates the present value of future cash flows, without accounting for the initial investment. When calculating NPV with the built-in formula, you should use the function on future cash flows only, then subtract the initial investment. This issue can cause a lot of confusion, and you may find it safer to calculate the NPV using the manual method instead of using the inbuilt function.
Irregular Cash Flows (05:11)
The NPV function assumes a regular schedule of cash flows. If your investment has irregular cash flows, you should use the XNPV function instead. This takes three arguments: the required rate of return, an array of cash flows, and the corresponding array of dates. When using this function, you don’t need to separate the initial investment from future cash flows, as XNPV can handle this correctly.
Net present value or NPV is another method of evaluating investment proposals for your business.
Thankfully, it doesn't have the same weaknesses of IRR, and as so is often preferred in academic literature.
However, it's less intuitive than IRR, so most business people have been slow to accept it.
The way NPV works is very straightforward.
We take all of the cash flows we assume will occur from a project, and then discount each cash flow by the required rate of return.
If the resulting sum of all cash flows is positive, then we undertake the project.
To explain this better, let's go back to our example of buying a house.
I've assumed we pay $100,000 for the house today, and for the next four years we receive $10,000 per year in rent.
We then sell the house after five years for $200,000.
The question is, given our required rate of return of 12%, should we buy the house? Let's first calculate NPV without using the inbuilt formula, which will give us a better understanding of the concept.
The first step is to discount each cash flow based on when it occurs and our rate of return.
The first cash flow is the $100,000 investment, and this is not discounted because we pay it immediately.
So I can simply write = P10, then press Tab to move to the next cell.
Now we have the first rental cash flow of $10,000.
Our required rate of return is 12% and I've named this cell G7 Required rate of return.
So one year from now, any cash we have today should be worth 12% more.
The reverse is also true however, so any cash flow we get one year from now, is worth less today.
To get the present value, or today's value of the 2014 cash flow, we write equals, select the cash flow and then divide by 1 plus the required rate of return.
Close the bracket and press Tab.
Based on our required rate of return, the $10,000 that we would receive next year, is worth $8,929 today.
As you can imagine, for the next cash flow, we divide the cash flow by the rate of return, twice, because it's two years from today.
So I'll write "=", select the cash flow, divide by 1 plus the required rate of return, and put that to the power of 2.
As you can see, cash flows have a lower present value the further into the future we receive them.
This principle that money in the future is worth less than it is today, is called the time value of money.
And I'll provide more description in the show notes that explains the concept in more detail.
Instead of writing out this formula for the remaining three years, it would be nice to have a common formula that we could simply copy and paste.
Given that the only changing variable is the number of years, let's go back into our previous cell and replace the two with the following.
We open a bracket and we'll subtract the current year, which is D9, from the first year, which is B9.
We'll also anchor B9, which we won't want to change.
We'll close the bracket and press Tab.
Now we can copy this formula for the remaining three cells.
To calculate the NPV, we simply sum all of these cash flows.
So I'll just select the range, close the bracket, and press Enter.
And this answer tells us that the investment delivers $43,859 above our required rate of return, so we should buy the house.
However, if I had a higher rate of return, let's say 30%, this would deliver a negative NPV, a lower required rate of return, and we should not make the investment.
Now let's take a look at Excel’s inbuilt NPV function.
So I'll write “=NPV”, I'll then select the rate, which is the required rate of return.
I'll then select all the cash flows as my final array, close the bracket, and press Enter.
And this gives me a different and incorrect answer.
Unfortunately, Excel’s NPV calculation isn't technically an NPV.
It's just the present values of the future cash flows.
So to get the right answer, we must first add the initial investment, and then separate that from a different NPV calculation for the remaining cash flows.
So let's write a new formula, starting off with the initial investment, and then we'll add the NPV.
We'll select the required rate of return, then we'll select all of the cash flows outside of the investment.
We'll close the bracket and press Enter.
And now you can see we get the same answer as our manual calculation from earlier.
As you can imagine, this quirk of Excel leads to all sorts of errors when calculating NPV using the built in formula. As a result, I think it's much safer to calculate the discount cash flows manually, to get the NPV.
As with IRR, Excel also provides an XNPV function for non-periodic cash flows.
We simply type “=XNPV”, open the bracket, select the required rate of return, then select the cash flows, then the dates, close the bracket and press Enter.
For investments such as this, with non-periodic cash flows, the XNPV function can save you quite a lot of time.
Thankfully, with XNPV, there's also no need to leave the initial investment outside the formula.
XNPV wraps up our look at net present value in this course.
But we'll be using it regularly in the financial modeling courses.
As an exercise, I've created another investment in the next Tab for you to calculate the discount cash flows and associated NPV's.