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 calculation 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!
Check out more useful articles on Our Blog Page
Are you someone involved with
real estate feasibility?
We are excited to launch the next generation of real estate feasibility software by April 2023.
claim your 15% early-bird discount
Know more about the product features
Hi,
That’s a good explanation about how the NPV works. If I may suggest something, pls look at how the IRR is constructed next time and compare that to the NPV. The IRR actually should include the beginning period (year 0) to match the NPV result.
Thanks Dayan;
You raised a very important issue, and I explored it https://www.feasibility.pro/how-does-excel-calculate-irr-1/
Hope you find this helpful.
Hi Naiyer,
I think you don’t know how NPV works. First initial investment is in Time 0, therefore, is not included in the NPV formula. So your cash flow starts 1, 2, 3, 4, 5. You need to write the formula like this: =NPV(C5,D12:H12)+C9
If you use the same excel file here, it should give the exact same result. Good luck!
Hi Gerek,
You’re right in approaching NPV(C5,D12:H12)+C9 to solve for the scenario, but I think you’re not quite right in stating the definition of NPV or how it works conceptually. NPV or net present value is the difference between the present value of cash inflows (future cash flows) at a required rate of return and the cash outflows (initial outlay). That is, when you talk about NPV, there must be two streams of cash flows included. The value returned by NPV(C5,D12:H12) is only giving the present value of the cash inflows regardless the formula name is NPV. Clearly, NPV(C5,D12:H12)+C9 solved for the scenario. Thank you!
Naiyer,
Very good explanation for a more accurate approach to calculating NPV through XNPV. However, Gerek has the accurate approach from the standpoint of using the just NPV. Normally, I don’t fall into the trap because I typically use dates and XNPV/XIRR. Funny, several years ago I used Gerek’s method, but had forgotten. Glad I found this site and thank you to both of you for your explanations because I was stumped over the same problem, just years apart!
Thanks Clayt; I’m glad you found this useful.
One very grateful finance student!
Thanks a lot, thought I was going mad with my manual calculations of the PV of Free Cash Flows!
Aidan, I’m glad you found this helpful.
Thanks. It was really helpful.
XNPV also introduces an error by assuming all years are 365 days with no leap years. Therefore the NPV in sheet 1 differs from the NPV in sheet 3 (158.83 vs. 158.66). The manual calculation in sheet 3 reads “=C11/(1+$C$5)^((C7-$C$7)/365)” but should read “=C11/(1+$C$5)^((Year(C7)-Year($C$7))”.
Thank you for your clarification, it helped the communication between me and my programmers.