XIRR Requiring Sale Price > 0

[Will]

I am noticing some strange behaviors in the XIRR function in Excel 2003.

First, if an investment is wiped out, and I have a sale price of $0 on the
final date, XIRR gives a #NUM error. I have to fudge past this by making
the sale price $0.01. Why does XIRR forbid $0 for the sale price?

Second, if the Sale date is not about eight months in the future, XIRR also
gives a #NUM error. When modeling a stock investment, the purchase and
sale might be within a three week period. So having to fudge the sale
date to eight months in the future makes the model artificial.

Any way around these two issues?

 

[joeu2004]

Why does XIRR forbid $0 for the sale price?

Generally, it doesn't. If the last cash flows are zero, they simply
have no effect on the XIRR computation.

First, if an investment is wiped out, and I have a sale price of $0 on the
final date, XIRR gives a #NUM error.

I presume you are saying that you have just two cash flows: an
investment (negative number), and a sales price (zero, in this case).

Look at the XIRR help page; it shows the formula for XIRR. In your
case, XIRR tries to find an IRR rate that satisfies this equation
(simplified):

0 = CF0/(1+r)^t1 + CF1/(1+r)^t2

Since, in your case, CF1 is zero, this simplifies to:

0 = CF0/(1+r)^t1

Normally there is no "r" that satisfies that equation. Exceptions:
(a) using binary computer arithmetic, there might be some
infinitessimal "r" that will cause the rounded result to be zero; and
(b) if CF0 is zero, there is an infinite number of solutions. So XIRR
correctly returns #NUM to indicate that it cannot find a solution
within its internal limitations.

Second, if the Sale date is not about eight months in the future,
XIRR also gives a #NUM error.

Generally, there is no such limitation. However, XIRR will return
#NUM if it cannot find a solution within its internal limitations. If
you are sure there is a reasonable solution, you might need to help
XIRR by specifying the "guess" parameter. That is unfortunate because
I do not know of any way, in general, to detemine a good "guess".


----- original posting ------

 

[joeu2004]

Errata (nitpick) ....

0 = CF0/(1+r)^t1

Normally there is no "r" that satisfies that equation.  Exceptions:
(a) using binary computer arithmetic, there might be some
infinitessimal "r" that will cause the rounded result to be zero

Actually, an infinitely large "r"; and even that is not quite right.
What I mean is: the divisor (1+r)^t1 would be infinitely large. The
size of "r" would be limited, depending on "t1", so that the divisor
expression does not overflow.

For example (probably not the only one), if CF0 is the smallest
possible number in Excel (=2^-1022), and the divisor is the largest
possible number (=(2^1023 - 2^(1023-53))*2), the result is exactly 0.
This is due to the behavior of binary computer arithmetic, not Excel
per se.