Problems with XIRR function

[Arvind Garg]

I am having problems trying to understand why a certain
combination of entries is not working in the XIRR
function. I list the 3 examples below:

A)=XIRR({-1215.68,-1215.67,-1215.69,-1214.52,-1221.08,-
1221,-1221,-1221,8796.03},
{36931,37019,37110,37207,37295,37383,37842,37561,37561},0.1
) = -13.87% (works fine)

B)=XIRR({-1215.68,-1215.67,-1215.69,-1214.52,-1221.08,-
1221,-1221,-1221,-1197,-1197,10546.32},
{36931,37019,37110,37207,37295,37383,37842,37561,37665,3776
0,37760},0.1) = -13.10% (works fine)

c)=XIRR({-1215.68,-1215.67,-1215.69,-1214.52,-1221.08,-
1221,-1221,-1221,-1197,8472.72},
{36931,37019,37110,37207,37295,37383,37842,37561,37665,3766
5},0.1) = 0.0% (wrong, it should be -31.31%)

Any ideas or suggestions would be most helpful. Thank you.

 

[Paul Corrado]

Arvind,

I did some testing and it appears that the problem is your guess is too far
off. As a result, the iterative process cannot find the correct answer.
Oddly enough, an even larger error in the guess does not seem to effect the
other calculations.

Also, check your dates. The sequence goes from 5/7/02 to 8/9/03, back to
11/1/02.

PC

 

[Ron Rosenfeld]

I am having problems trying to understand why a certain
combination of entries is not working in the XIRR
function. I list the 3 examples below:

A)=XIRR({-1215.68,-1215.67,-1215.69,-1214.52,-1221.08,-
1221,-1221,-1221,8796.03},
{36931,37019,37110,37207,37295,37383,37842,37561,37561},0.1
) = -13.87% (works fine)

B)=XIRR({-1215.68,-1215.67,-1215.69,-1214.52,-1221.08,-
1221,-1221,-1221,-1197,-1197,10546.32},
{36931,37019,37110,37207,37295,37383,37842,37561,37665,3776
0,37760},0.1) = -13.10% (works fine)

c)=XIRR({-1215.68,-1215.67,-1215.69,-1214.52,-1221.08,-
1221,-1221,-1221,-1197,8472.72},
{36931,37019,37110,37207,37295,37383,37842,37561,37665,3766
5},0.1) = 0.0% (wrong, it should be -31.31%)

Any ideas or suggestions would be most helpful. Thank you.

I obtain a different result than you do for the cash flow in example c).

First of all, with XIRR if you get nonsense results, it helps to change the
guess. There's no need to enter the 0.1 as this is assumed if not specified.
But for the third example, specifying -0.1 for the guess gives a result of
-27.27%, not -31.31%


--ron

 

[Harlan Grove]

IRR and XIRR are overly sensitive to the initial guess, and given how the
precise iterative procedure actually works, the default 0.10 assumption is
really awful. If you ALWAYS use -0.50 (yes, NEGATIVE), you'll almost always
get a result that works in the sense of giving a zero NPV or XNPV. You could
get other results with other initial guesses if there were multiple sign
changes, but there's no practical way to control that.