Sign in or start a free trial to avail of this feature.
6. Calculating Net Present Value
Net Present Value or NPV combines the present value of future cashflows with the initial investment. It can be calculated manually or using an in-built Excel function
Net Present Value (NPV) definition
NPV = PV(all future cashflows) - Initial Investment
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 in-built function:
=Initial investment + NPV(range of ordered future cashflows)
To calculate NPV for non-periodic cashflows:
=XNPV(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
Net present value is very closely related to the concept of present value that we've discussed in the last few lessons.
In fact, the formula for net present value is simply NPV is equal to the PV of future cash flows minus the required initial investment.
The initial investment is assumed to take place immediately, which in our model, corresponds to year zero.
To see this in action, let's return to our example of the wind farm and calculate the NPV.
To calculate NPV, we need a new column of data for year zero that includes the initial buying price.
I'll create this off camera.
I can also extend the discount cash flows formula for this figure, but as you can imagine, when I run this formula, no discounting occurs because I assume that I pay the sale price immediately.
To calculate the NPV, we simply sum up these values.
And this gives me $32,561.
If the net present value is positive for a project, that means that we should invest.
However, if the net present value is negative, it means we should turn down the project.
You may have noticed that in Excel, there’s an inbuilt function to calculate NPV.
I'll try and use it here to see if it works.
So I'll write “=NPV”, open the bracket, and my first variable will be the rate which is simply the discount rate.
And then the remaining values will simply be the annual cash flows.
I'll then close the bracket and press Enter.
And unfortunately, this gives me a slightly different answer to what I had previously.
So which of these is correct? Well, it turns out the manual calculation is correct.
The net present function within Excel is slightly different to the traditional formula for net present value.
In Excel, net present value is actually the present value of future cash flows.
And all of the cash flows are discounted starting from year one, instead of starting from year zero.
To fix this problem, we simply calculate the NPV for the future cash flows, and then separately add on the initial investment.
So I'll write this formula again, and my first value will be the initial investment, and then I'll add the NPV function with the discount rate and the remaining cash flows.
And now our net present values are equal.
As you can imagine, this quirk of Excel has the potential to cause a lot of confusion and many people calculate NPV incorrectly as a result.
I tend to always calculate NPV manually, so as to avoid the potential for errors, but also so that I can see what value the cash flows have in later years of the project.
This is particularly relevant if we plan on selling the project in later years.
As you can see in this example, the discounted cash flows in year ten are under half the future value of that cash flow.
So far, we’ve been dealing with just annual cash flows.
But what if we receive cash flows on a monthly, quarterly or a regular basis? In this case, our best approach is to use an internal function.
And that function is called XNPV.
On sheet two, I have a simple example of a project with a set of cash flows that occur at random dates in the future.
XNPV will accept the discount rate, the range of dates, and the range of cash flows to calculate the necessary NPV.
So I'll write “=XNPV” and open the bracket.
The rate will simply be cell E7, the values will simply be this array, and the dates will be the cells above.
I'll then close the bracket and press Enter, and this tells me that my net present value is minus $912,489.
Thankfully, XNPV doesn't suffer from the same quirks that the NPV function suffers from.
And as a result, it can save us a lot of time when calculating the net present value for projects with irregular cash flows.