XIRR 0.00% Return with Small First Deposit

[Jan]

I need to be able to calculate a rate of return on deposits/withdrawals at
uneven intervals, and keep running into a problem with XIRR when first
deposit is small. Simplified example:
-$100 1Mar98
-$1000 1Apr98
-$500 20Dec99
-$500 03Mar04
(+$X) 01Dec05 (Final Withdrawal)

If final withdrawal (X) is larger than $1,901.00, then XIRR works well. If,
however, final withdrawal is $1,900.01 or less, XIRR returns 0.000% for any
value of X. (Formula =XIRR (A1:A5,B1:B5). If, however, first deposit is
$1,000 and second deposit is $100, it shows a return all the way down to the
value of x=100.01 (-60.7889%), then returns 0.000 if x=or<$100.00 .

Is there another formula for this or a workaround I'm missing? I have some
complex data to calculate and need to ensure that output is reliable. Data
often starts with small first deposits.

 

[Fred Smith]

The only thing you are missing is the third parameter for XIRR.

The reason for the third parameter (guess) is that in esoteric cases like yours,
XIRR can't converge properly. You need to help it out with a more reasonable
guess than its default of +10%.

When I entered =xirr(a1:a5,b1:b5,-10%), xirr returned the result properly.

A more general formula would be:

=XIRR(A1:A5,B1:B5,10%*SIGN(SUM(A1:A5)))

This will guess +10% when the return is positive, and =10% when the return is
negative. This will give you accurate answers in a much higher (but not 100)
percent of the cases.