Problems with XIRR function

  • Thread starter Thread starter Arvind Garg
  • Start date Start date
A

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.
 
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
 
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.
Click to expand...

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
 
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.
 
Back
Top