xirr

[Janven]

The result of below XIRR calculation is very weired.

Apr 5, 06 16328
Oct 2, 06 16096
Jul 3 07 11251
Nov 1 07 71414
Dec 3 07 79637
Jan 2 08 13845
Feb 1 08 12610
Sep 17 08 -175691

Xirr answer: 2.98E-09

But if I change Sep 17 08 cash flow to -189000, XIRR come correct answer
-14.3%.
Any clue.
Thanks.
Janven

 

[Bob Bridges]

Well, taking its output literally it seems to be claiming that the rate of
return for this flow is very nearly nothing, that is, even. That doesn't
look right to me, but I get the same result.

Here's one thing I notice: When, with the below numbers, I enter a guess of
some negative percentage like -0.1, it comes up with -20.43%, which is
probably correct. Maybe for some ranges its iterative calculation gets it
going in the wrong direction and it craps out with misleading results. Tell
it to start guessing with a negative result rather than its default +0.1 and
it seems to do better.

 

[Ron Rosenfeld]

The result of below XIRR calculation is very weired.

Apr 5, 06 16328
Oct 2, 06 16096
Jul 3 07 11251
Nov 1 07 71414
Dec 3 07 79637
Jan 2 08 13845
Feb 1 08 12610
Sep 17 08 -175691

Xirr answer: 2.98E-09

But if I change Sep 17 08 cash flow to -189000, XIRR come correct answer
-14.3%.
Any clue.
Thanks.
Janven

If you check HELP for XIRR, you will discover that you need to supply an
appropriate guess. If you do not, then XIRR assumes a guess of 10%.

Since you have an absolute loss of about 45000 with an investment of about
220000, I'd say an appropriate guess might be about -20%.

Entering that value as a guess gives an appropriate result of -20.43%.

There is no documentation that I've seen from MS as to when a guess of other
than the assumed 10% will be required.
--ron

 

[Fred Smith]

One way to improve XIRR's results is to always use a Guess with the same
sign as the results. If you've made money, guess 10%; if you've lost money,
guess -10%. You can generalize this as follows:

=Xirr(Values, Dates, Sign(Sum(Values))*10%)

Regards,
Fred.