Excel NPV initial cost in value1 and first year return in value2?

[Johanna]

The help screen for “NPV(rate, value1, value2, … value29)†says the values
after rate are evenly spaced in time. If the period is one year, then value1
occurs at the end of the 1st year, value2 at the end of the 2nd year. In
example 2 on the help screen, this is true. Value1 = "Return from first year"
and Value2 = "Return from second year".

However, in example 1, Value1 = "Initial cost of investment one year from
today" and Value2 = "Return from first year". These two values refer to the
same year.

But NPV( .1, -10000, 3000) would be treated as negative cash flow of 10,000
in the first year and a 3,000 return the second year, if I have read this
correctly, not as was explained in ex. 1.

 

[joeu2004]

But NPV( .1, -10000, 3000) would be treated as negative cash flow of 10,000
in the first year and a 3,000 return the second year, if I have read this
correctly, not as was explained in ex. 1.

Your understanding is correct. The key is to recognize that Excel
discounts the first value as well as the others, as you explained. So
if your "initial" cash flow should not be discounted, do not include in
the NPV() function. Your example becomes:

=-10000 + NPV(10%,3000)

Or equivalently:

=NPV(10%,3000) - 10000

 

[Johanna]

Thanks, joeu2004. Not only is the first value discounted, but each value is
successively discounted for an additional year. The second value is
discounted for two years (or periods). If the user intends that the first
two values should both be discounted for one year, then the NPV function will
not do this. The help screen gives the impression that the first two values
would both be discounted for one year, with the third value then discounted
for two years. This is not what it does, though.

 

[joeu2004]

The help screen gives the impression that the first two values
would both be discounted for one year, with the third value then discounted
for two years. This is not what it does, though.

And that is not what the Help page says, as I read it.

What in the Help page "gives the impression" (to you) that the first
two values are discounted over the same amount time, namely one year?

According to the Help page, the NPV() cash flows are labeled value1,
value2, etc. And the Help page shows how NPV is computed, which I will
write stylistically as: SUM(value/(1+rate)^i, i=1,n). That expands
to:

value[1]/(1+rate)^1 + value[2]/(1+rate)^2 + value[3]/(1+rate)^3 +....

Clearly, value1 is discounted for one period and value2 is discounted
for two periods, not for the same period of one year.

As an aside, I also want to note (primarily to others) that the Help
page also clearly states: "If your first cash flow occurs at the
beginning of the first period, the first value must be added to the NPV
result, not included in the values arguments". I explained that
previously, but I did not realize that the Help page also explains it.