Problem With NPV function

I'm having some problems with the NPV function. Please refer to the attached spreadsheet.

So let me explain my understanding of Net Present Value "NPV= current cashflows + discounted future cashflows, hence (current cashflows - NPV) + discounted future cashflows = 0"

So let me explain what I have done in the spreadsheet and why there is a problem. The first line is just a regular cashflow and below it is its NPV. In the test cashflow line, I've subtracted the NPV of the above cashflow from the current cashflow. Using the same rate I've done an NPV calculation of the test cashflow, expecting to get 0, but instead i get $83.97.

Originally Posted by brendonagius

I'm having some problems with the NPV function. Please refer to the attached spreadsheet.
[....]
Using the same rate I've done an NPV calculation of the test cashflow, expecting to get 0, but instead i get $83.97.

The NPV formulas should be:

B4: =B3+NPV(B2,C3:Z3)
B6: =B5+NPV(B2,C5:Z5)

instead of NPV(B2,B3:Z3) and NPV(B2,B5:Z5).

I presume that B3 and B5 represent CF0, the present value time frame. That is, CF0 should not be discounted.

Look at the Excel NPV help page carefully: the Sigma is from i=1, not i=0. That is, Excel NPV discounts the first cash flow in the range.

There are times when that is valid to do so. But apparently, not in your case, I presume.

Just to add on to the previous post, the issue was your formula. You had the initial investment starting at the end of the year rather than the beginning. See the attached:

NPV Function Test.xlsx