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?

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" wrote:

> 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" <post_a_reply@no_e-mail.com> wrote in message
news:4113CBB3-C8BB-4A33-8D07-(E-Mail Removed)...
> 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.

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

"Howard Kaikow" wrote:

> "Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
> news:4113CBB3-C8BB-4A33-8D07-(E-Mail Removed)...
> > 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.
>
>
>

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

"Jerry W. Lewis" wrote:

> 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
>
> "Howard Kaikow" wrote:
>
> > "Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
> > news:4113CBB3-C8BB-4A33-8D07-(E-Mail Removed)...
> > > 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.
> >
> >
> >

On Wed, 6 Aug 2008 17:03:21 -0400, "Howard Kaikow" <(E-Mail Removed)>
wrote:

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

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

On Wed, 6 Aug 2008 19:27:01 -0700, Jerry W. Lewis <post_a_reply@no_e-mail.com>
wrote:

>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

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:52208467-CE7B-4F1F-8174-(E-Mail Removed)...
> 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.

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:B788D236-8BBE-41C5-9515-(E-Mail Removed)...
> 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.,

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)