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.
Net Present Value (NPV) definition
NPV = PV(all future cashflows) - Initial Investment
The PV(one cashflow) = Cashflow / (1 + req. rate of return)number of years in the future
To calculate NPV manually:
1. Discount each future cashflow separately
2. Add all discounted cashflows
3. Subtract initial investment to calculate NPV
To calculate NPV using inbuilt function:
=Initial investment + NPV(range of ordered future cashflows)
To calculate NPV for non-periodic cashflows:
=XNPV(rate, range of cashflows, range of dates)
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
F2: Jump inside formula
F4: Anchor cells (when inside a formula)
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.