NPV Calculation in Excel: Why the Numbers do not Match!
Calculating Net Present Value in Excel can be challenging.
In this post we will be talking about the formula of Net Present Value in .xls and why the result might differ when it is calculated manually.
Let us take an example, lets consider a basic financial investment of USD 500 in investment which creates 10$ each year in net income and we will sell this project by exiting for USD 500 in the fifth year.
The capital for this task will look like this:
NPV Calculation in Excel case solution
Lets take assumption of rate of discount as 10%.
Then PV will be:
Excel Sheet case solution
The NPV will be the summation of the PV which is USD 159.
Now lets do the Net Present Value computation for the CF by using NPV formula in excel.
Net Present Value = NPV(r,v1, v2,..);
NPV Calculations in excel for case study
By using this we will obtain USD 144 as the Net Present Value.
But we do we get to a totally different result when using Excel NPV builtin formula?
The answer is easy. Formula in Excel NPV assumes that the very first time duration is ONE and not ZERO. If your 1st CF happens at the start of the very first duration (i.e. 0 time period), the very first value should be added to the Net Present Value result, not in the value arguments (as in the above computation).
If we change the time period on the leading to start from 1, you will observe that the manual calculation now matches with the NPV estimation in Excel.
So many people have made this error of time duration in determining the NPV in Excel so many times.
The best method to eliminate this time duration issue is using precise dates for the present and future capital. We can utilize XNPV formula as well as change the manual computation somewhat to get the right time period, and the Net Present Value.
How We Work?
Just email us your case materials and instructions to order@thecasesolutions.com and confirm your order by making the payment here
