How to calculate NPV in Excel using Formulae
I’ve covered the basics of Net Present Value (NPV) previously. If you don’t know what NPV is, then please read that post first before continuing. After reading this post, you will be able to learn how to use Excel to calculate NPV of cash flows. This tutorial will teach you how calculate the NPV using formulae.
Although this is a more detailed approach to calculate NPV, it gives you more flexibility vs. using the in-built Excel NPV functions.
Using this method, you will:
- Calculate the discount factor that is to be applied in each year based on a specific discount rate
- Apply this discount factor to the cash flow of each year to get the present value of the cash flow of that year
- Sum up these cash flows to arrive at the NPV
This does sound like a lot of work. Don’t worry, we’re going to take the calculations one step at a time!
1. Calculate the discount factor
From the previous post, you would have learnt that you can calculate the present value of next (read first) year’s cash flows using $100/(1+5%) where $100 is the cash flow and 5% is the discount rate.
For the second year’s cash flows you will use $100/(1+5%)^2 and so on.
This portion: 1/(1+5%) and 1/(1+5%)^2 are the discount factors for the first and second year.
Putting this in Excel is very easy and this is what the formulae would look like in Excel to calculate the discount rates for the next 3 years. You can extend these to as many years as needed.
2. Apply this discount rate
Next, multiply the discount rate you calculated in the previous step to the cash flow in the same year
3. Sum the PVs of all the cash flows
Now, add the various cash flows that you calculated in the previous step to get the net present value of the stream of cash flows.
You can see the three steps in the screenshot below.
The above method assumes that the cash flows are at the end of the year. If you’re assuming mid-year cash flows then the recommended method is to discount the first year by 0.5 (instead of 1), the second by 1.5, etc. The rest of the calculations remain the same.
In the next post, I will show you how to use in-built Excel functions to calculate the NPV.