If you’re here and reading this post, then you’ve probably been asked to calculate the Net Present Value of a set of cash flows in Excel. Over the next few posts, I’ll walk you through Net Present Value (or NPV) as a concept and then go into the details of how to calculate this in Excel.
Introducing NPV, PV and FV
Net Present Value, as the name suggests, is the sum of the present values of future cash flows. Both incoming (i.e. receipts) and outgoing (i.e. payments) are considered for calculating the NPV. So, what’s present value (PV)?
Let’s try to understand this via a simple example.
Imagine that you deposit $100 in the bank today for one year only at an annual interest rate of 5%. After one year, the bank would return the principal ($100) along with some interest ($5), which is a total of $105.
You’ve probably heard the saying “a dollar today is worth more than a dollar tomorrow”. In our example, our dollar today is worth 1.05 dollars in a year. The Future Value (FV) of $1 is $1.05.
Mathematically: At the end of Year 1, the Future value of $100 = $100 * ( 1+ 5% ) = $105
Reversing this, $105 next year is worth $100 today (assuming that the discount rate is 5%). Hence the present value of $105 is $100.
Still with me? Good. Now let’s complicate this example further by adding in yet another year.
If, instead of paying you back the principal at the end of the year, the bank just paid you the interest ($5) and rolled over the principal for another year.
And, then after another year has passed, the bank paid back the principal and interest i.e. a total of $105, what would the present value of this $105 be today?
If you’re thinking $95.24, then you’ve hit the nail on the head and you can skip the explanation below. If that’s you in the image below, then read on.
To find out what the present value of the $105 in Year 2 is today, we need to take two steps back in time.
Step 1: In Year 1, the present value of the $105 is $100 (just like we calculated in our previous example)
Step 2: In Year 0, i.e. today, the present value of this $100 is: $100/(1+5%) = $95.24
Note that in the example above, we’ve ignored the $5 that the bank paid us at the end of Year 1. Keeping with the calculation in Step 2 above, the present value of $5 would be $5/(1+5%) = $4.76.
And, $4.76 + $95.24 = $100!
Looking at this example:
$4.76 is the present value of $5 received in Year 1
$95.24 is the present value of $105 received in Year 2
$100 is the sum of the present values of the receipts of Year 1 and Year 2. In other words, $100 is the net present value of two cash flows of $5 and $105 received in the following two years.
By now, I hope I have cleared any doubts or questions you’ve had on Net Present Value and you should conceptually be able to calculate the NPV of a set of cash flows and decide if a project is feasible based on the cash flows.
In the next post, I’ll explain how you can calculate NPV in Microsoft Excel. Stay tuned.