XIRR function providing incorrect results

[Carolyn Seton]

When trying to calculate Rate of Return where the return
is negative and the values hit a certain level, the ROR
calculated by XIRR is not correct. The Example 1 and 2
below have $1.00 difference in the final values and yet
the ROR calculated by XIRR on Example 2 is basically 0.

Example 1 Example 2
Date Amount ($) Date Amount ($)
1/01/01 (1,000.00) 1/01/01 (1,000.00)
1/02/01 (2,000.00) 1/02/01 (2,000.00)
5/05/01 (1,000.00) 5/05/01 (1,000.00)
1/01/02 (2.00) 1/01/02 (2.00)
2/01/02 (1.00) 2/01/02 (1.00)
3/01/02 (1.00) 3/01/02 (1.00)
4/01/02 (1.00) 4/01/02 (1.00)
5/01/02 (1.00) 5/01/02 (1.00)
7/09/04 (1.00) 7/09/04 (1.00)
1/01/05 2,008.00 1/01/05 2,007.00

ROR = -16.3452115096% ROR = 0.0000002980%
The above formula (Example 1) is =XIRR(B2:B11,A2:A11)

If you try this and continue to reduce the final value or
adjust some of the dates, this will adjust but there
always seems to be a level where the % ROR drops to 0 at a
particular set of values.

Any assistance in this area would be appreciated.

Yours sincerely
Carolyn Seton

 

[Kevin Stecyk]

Carolyn,

I believe I have solved your mystery.

For example 2, enter the following formula into your spreadsheet:

=XIRR(D211,C2:C11,-15%)

The answer, not too surprisingly, is -16.201556404%


This answer looks close to your solution for Example 1, doesn't it?

So how do we know which answer is correct?

Well, if we total the amounts for Example 1, we get approx -1999. So if you
invest about 4K and on an undiscounted basis have a total of about -2K over
a 4 year time frame, we KNOW that the solution is NOT 0%. It is negative
something. And it is a substantial negative. -16% is about right.

Okay, then why did we get 0% originally for the second example?

Are you familiar with Descartes' Rule?

http://www.cut-the-knot.org/fta/ROS2.shtml

On connoift auffy de cecy combien il peut y auoir de vrayes racines, &
combien de fauffes en chafque Equation. A fçauoir il y en peut auoir autant
de vrayes, que les fignes + & -- s'y trouuent de fois eftre changés; &
autant de fauffes qu'il s'y trouue de fois deux fignes +, ou deux fignes --
quie s'entrefuiuent.

"We can determine also the number of true and false roots that any equation
can have, as follows: An equation can have as many true roots as it contains
changes of sign, from + to - or from - to +; and as many false roots as the
number of times two + signs or two - signs are found in succession."

My guess is that your string for XIRR goes something like 0 value, negatives
values, and a final positive value. So you have two roots. One proper root
at about neg 16%. And another root at about 0%. The 0% is incorrect.

If you want ROR to be truly 0, then simply ensure that the arithmetic sum of
your cash flows is 0. Adjusting the dates will not solve your problem. If
you invest 4K (that is neg 4K cash flow) and have a total sum of neg 2K,
then you have lost money. Granted if the positive value occurs in the year
4000 (2000 years from now), the value will be close to 0. You have simply
diluted the negative amount per year to an infinitesimal amount. So you
have achieved 0 ROR because of the infinitesimal amount.

But adjusting the final date by a couple years should NOT bring you to a 0%
ROR.

The only adjustment that should bring to you 0% ROR, is an increase in the
final positive cash flow (or conversely less negative cash flows) such that
the total equals 0.

There, that is probably more than you wanted to know.

Regards,
Kevin

 

[Kevin Stecyk]

Carolyn,

<<Granted if the positive value occurs in the year
4000 (2000 years from now), the value will be close to 0. You have simply
diluted the negative amount per year to an infinitesimal amount. So you
have achieved 0 ROR because of the infinitesimal amount.>>

Disregard the above snippet from my prior post.

Everything else remains.

Hope the information was beneficial.

Regards,
Kevin

 

[Carolyn]

Dear Kevin

Thank you very much - you have indeed solved my mystery.
Sorry for my delayed response - (especially as your
response was so fast).

I have adjusted my formulas so that when there is a loss,
I am putting in the guess at -10%. This seems to work,
keeping it on the right track (and not fiving #NUM or 0%)

.... and it made sense against my high school calculus
after talking it through with a mathematician co-worker
who appreciated your Descartes' Rule!

Thanks again.
Regards
Carolyn

 

[Kevin Stecyk]

Carolyn,

Thank you very much for your response, and you are most welcome.

IRRs and XIRRs can be tricky. You have to watch for the change in sign to
make sure you have the correct answer.

Best regards,
Kevin

Dear Kevin

Thank you very much - you have indeed solved my mystery.
Sorry for my delayed response - (especially as your
response was so fast).

I have adjusted my formulas so that when there is a loss,
I am putting in the guess at -10%. This seems to work,
keeping it on the right track (and not fiving #NUM or 0%)

.... and it made sense against my high school calculus
after talking it through with a mathematician co-worker
who appreciated your Descartes' Rule!

Thanks again.
Regards
Carolyn