"I'm trying to make the calculation of Net Present Value (NPV) of an investment and get an incorrect result.
The example that we send is:
VAN function in Excel has several peculiarities:
a) The name of the function in Excel is VNA , not NPV.
b) The initial outlay or investment should not be introduced within the function, but outside of that sum.
c) The range of cash flows considered in the function should not contain any blank cell. If, as is the case, we expect a flow of zero at some point during the project must write zero value in this period. This is precisely the mistake that we are in this case.
We are located in cell C13 and type the formula:
= NPV (C3, C6: C11) + C5
The first argument refers to the discount rate. In our example this is the Annual Nominal Interest Rate (TIN) located in cell C3.
The second argument is the range where the cash flows (not including the initial outlay, as I indicated). In our case the range C6: C11.
Finally, out of the NPV function add (as it is in the negative) the initial outlay.
The formula is well posed. The problem here is that in years 3 and 4, we expect cash flow equal to zero, we left the blank cells. With this approach excel interprets the flow of year 3 (which is blank) is the next cell containing a value range, ie the year 5 in our example. In year 4 interprets its value is the year 6. Obviously this generates an error when updating values. The solution is as simple as inserting zero values \u200b\u200bin cells that have been left blank, as shown below:
