NPV calculation in Excel can be tricky. I have written few posts on this subject – NVP & IRR – Key Metrics of a Feasibility Analysis and Net Present Value and Returns to the Equity Holders. I recommend that you read them too.
The basics of net present value (NPV) have been discussed on this site on many occasions. In this post we will be discussing the NPV formula in Excel and why the result may differ from the manual calculation.
For this, we will consider a simple investment of 500$ in a project which generates 10$ each year in income and we will exit this investment by selling it in the fifth year for 1,000$.
The cash flow for this project will look like this:
Assume that the discount rate is 10%.
We can calculate the present value as follows:
The net present value (NPV) of this investment will be the sum of the present values which is 159$.
Now we will be doing the NPV calculation for the same cash flow by using Excel NPV formula.
Net Present Value = NPV(rate,value1,[value2],…); where rate is the discount rate and the value1, value2…are the cash flows.
By using this formula we get 144$ as the NPV.
Why are we getting a different result when using Excel NPV formula?
The reason is simple. Excel NPV formula assumes that the first time period is 1 and not 0. So, if your first cash flow occurs at the beginning of the first period (i.e. 0 period), the first value must be added to the NPV result, not included in the values arguments (as we did in the above calculation).
Going back to the same example, if change the time period on the top to start from 1, you will notice that the manual calculation now matches with the NPV calculation in Excel.
I have seen many people making this mistake of time period in calculating the NPV in Excel. When you get confusing results it is always better to refer Excel help, they explain such stuffs in very detail.
However, the best way to get rid of this time period issue is using exact dates for the current and future cash flows. We can use XNPV formula and also adjust the manual calculation slightly to calculate the correct time duration, and the NPV.
The NPV in this case will be 159$.
We can use the Excel XNPV formula also to calculate the net present value.
Net Present Value = XNPV(rate, values, dates); where rate is the discount rate, values are the cash flows and dates are the dates corresponding to these cash flows.
The output of this formula will be 159$.
Hope you enjoyed this post on NPV calculation in Excel. If you have any questions, let me know through the comment section below.
You can also download the NPV calculation in Excel workbook FREE!