Corrupt XIRR formula

[Howard Kaikow]

I just noticed a value of 0.000% for an annualized return computed using
XIRR in Excel 2003.
The probability of that result being correct is rather slim.

The formula used is =XIRR(B3:B56, A3:A56).
The problem appears to be caused by a Link.

If I instruct Excel to Open the Source for the link, the formula is
displayed as
=ATPVBAEN.XLA!XIRR(B3:B56, A3:A56)
If I open the workbook in Open Office's Calc, the formula is displayed as
=#NAME!XIRR(B3:B56; A3:A56)
If I remove the #NAME!, Calc seems to produce a plausible value.

There are over 100 XIRR functions in the workbook, thus far, this is the
only corrupt critter.

Looking back, I find a version of the workbook saved on 5 Jul 2008 did not
have this problem.
A workbook saved on 27 July 2008 does have the problem. I did not do any
Office Update in that interval.

How do I fix the Excel workbook?

 

[Jerry W. Lewis]

What do you get for =COUNT(B3:B56, A3:A56)? Since you seem to agree with all
other XIRR results, my guess is that in this case your "numbers" are text
instead of numbers, and therefore are ignored by the formula.

If COUNT returns 108, then you need to post the data before anyone can
comment intelligently.

Prior to 2007, XIRR was not a native Excel function. To use it, you had to
link in the Analysis ToolPak Add-In (ATPVBAEN.XLA). XIRR is a native
function in OpenOffice Calc, which cannot use the ATP add-in, hence the
perfectly predictable behavior that you describe between the two packages.

Jerry

 

[Howard Kaikow]

What do you get for =COUNT(B3:B56, A3:A56)? Since you seem to agree with all
other XIRR results, my guess is that in this case your "numbers" are text
instead of numbers, and therefore are ignored by the formula.

If COUNT returns 108, then you need to post the data before anyone can
comment intelligently.

I've eliminated the empty rows and posted a watered down version of the file
at
http://www.standards.com/temp/excel2003.xls.

Prior to 2007, XIRR was not a native Excel function. To use it, you had to
link in the Analysis ToolPak Add-In (ATPVBAEN.XLA). XIRR is a native
function in OpenOffice Calc, which cannot use the ATP add-in, hence the
perfectly predictable behavior that you describe between the two packages.

Ayup, I knew that.
Calc returns the plausible value of -9.384% instead of 0.

A few months ago, I posted a related issue in one of these newsgroups.
In that case, all I had to do was break the links (affected only 2 cells)
and re-enter the XIRR formulae. That solution does not work here.

 

[Jerry W. Lewis]

It appears to relate to the fact that your return is less than your
investment. I am not an accountant, but I would think that descibing a loss
as a negative return would be more useful than simply reporting that you
didn't make any money. Excel 2007 converted ATP functions to worksheet
functions; it would be interesting to know if it also changed this behavior.

Jerry

 

[Jerry W. Lewis]

More likely it is a convergence problem due to the negative return. If you
provide a negative initial guess (optional 3rd argument) then XIRR returns
-9.834%.

Jerry

 

[Ron Rosenfeld]

I've eliminated the empty rows and posted a watered down version of the file
at
http://www.standards.com/temp/excel2003.xls.


Ayup, I knew that.
Calc returns the plausible value of -9.384% instead of 0.

A few months ago, I posted a related issue in one of these newsgroups.
In that case, all I had to do was break the links (affected only 2 cells)
and re-enter the XIRR formulae. That solution does not work here.

Curious.

If you enter a guess that is 0% or greater, the function returns a very small
number.

Your formula, on that sheet, actually is returning 0.000000298023224%

If you enter a guess that is negative, even quite small, the function returns
the expected result:

=XIRR(B3:B56, A3:A56,-0.0001%) --> -9.834%

--ron

 

[Ron Rosenfeld]

It appears to relate to the fact that your return is less than your
investment. I am not an accountant, but I would think that descibing a loss
as a negative return would be more useful than simply reporting that you
didn't make any money. Excel 2007 converted ATP functions to worksheet
functions; it would be interesting to know if it also changed this behavior.

Jerry

Jerry,

I'm not sure why.

The OP's function, on the sheet he posted, actually returned a very small
number (not 0). 0.000000298023224%


Including a "guess" that was negative, albeit quite small, resulted in the
probably correct result.

=XIRR(B3:B44, A3:A44,-0.000001%) --> -9.83%


So far as reporting zero when there is a loss, not necessarily the case. This
series, with no guess supplied, returns the expected amount:

1/1/2008 -500
2/1/2008 -500
12/31/2008 900


XIRR(values,dates) --> -10.42%


--ron

 

[Howard Kaikow]

It appears to relate to the fact that your return is less than your
investment. I am not an accountant, but I would think that descibing a loss
as a negative return would be more useful than simply reporting that you
didn't make any money. Excel 2007 converted ATP functions to worksheet
functions; it would be interesting to know if it also changed this

behavior.

XIRR does report negative returns as negative numbers.
If I artificially double the current value, then a positive number is
reported.

 

[Howard Kaikow]

More likely it is a convergence problem due to the negative return. If you
provide a negative initial guess (optional 3rd argument) then XIRR returns
-9.834%.

The XIRR does report negative returns elsewhere,
It's an algorithm issue, for example.

If I change the value for 26 Dec 2006 from -940 to -1881, XIRR produces a
negative return.,

If I change the value for 26 Dec 2006 from -940 to -1880, XIRR produces a 0
return.,

 

[Howard Kaikow]

As pointed out by Jerry and Ron, if I include a guess in the XIRR, then I
get the expected result.
But, in the general case, there is no way for a user to know what to guess.

Excel 2003's Help states:

"In most cases you do not need to provide guess for the XIRR calculation. If
omitted, guess is assumed to be 0.1 (10 percent)."

If I use a guess of -.1, I get the expected result.

What do folkes think about using the following as a guess:

=XIRR(B3:B44, A3:A44, SIGN(SUM(B3:B44)) * .1)

 

[Howard Kaikow]

I used XNPV to verify that the NPV of the stream is not 0 using the return
given by XIRR.
I then tried to use Goal Seek (never used it before).

Given =XIRR(D344, C3:C44), which returns -0.09833663106 in H2,
=XNPV(H2,D344, C3:C44) returns -2820.37.

I then tried Goal Seek to determine the right value for H2, so I copied the
raw value to H11 and
used Goal Seek with =XNPV(H11,D344, C3:C44) wich results in error
"Formula in cell must result in a number".

Goal Seek returns an error if the rate in H11 is negative.

Am I doing anything wrong?