Jim said:
I calculated a XNPV on a series of cash flows. The cash flow in the first 27
years is zero and then there are positive cash flows for another 25 years.
The calculation read the first positive cash flow as if it were at time zero
instead of discounting it back 27 years.
Not in my experiments. Am I misinterpreting your example? In any
case, I would be interested in knowing what version of Excel you are
using. And I would be interested in seeing the details of a simple
experiment, complete with formulas and your results.
I am using Office Excel 2003. For my example, here is A1:A5 and B1:B5.
1/1/1999 0
1/1/2003 1000
1/1/2004 1000
1/1/2005 1000
1/1/2006 1000
(4 years of zero cash flows followed by 4 years of positive cash
flows.)
XNPV(5%, B1:B5, A1:A5) results in $3062.52. If C1:C5 has the following
formula to compute the number of years (C5, for example):
=YEAR(C5) - YEAR($C$1)
and D1
5 has the following formula to compute (-)PV (D5 for example):
=PV(5%, D5, 0, -B5)
then the =SUM(D1
5) results in $3,063.13. Close enough: a 59-cent
difference.
Note: In practical terms, the formulation of my example probably has
an off-by-one error in terms of number of discount periods. I am using
this only to demonstrate that XNPV does indeed seem to take the periods
of zero cash flows into account. In fact, if you look at the formula
in the XNPV help page, it should be obvious that XNPV would.
But I wonder if it is the off-by-one error of the formulation that is
tripping you up. That would be a usage error, not an XNPV error.
PS: If your cash flows are indeed evenly spaced (e.g. every year), you
might be able to use NPV instead of XNPV. NPV has a debatable
off-one-error by discounting the first cash flow. Some people complain
that NPV is wrongly defined. But ironically, that might be exactly
what you want.
