Don acknowledged in email that the -400% that he observed was due to human
error.

Nonetheless, for the cash flow stream that he sent me (below), XIRR()
returns about 0.0000002980%. That is obviously incorrect, since XNPV()
returns about -43,338.86, which is not even close to zero.

The root cause is the need for a "guess" argument (Don did not have one),
and a correct "guess" argument at that.

Reducing the irregular daily cash flows to monthly net cash flows (see
below), we see a series of negative cash flows (investments) and one
positive cash flow (return). Since the sum of the investments (about -136K)
is much larger than the return (about 96K), we expect a negative IRR. So I
tried a "guess" of -1%, and XIRR() returned -24.83%. Plugging the exact IRR
into XNPV() using the daily cash flow, the result is indeed close to zero.
QED.

Don still wonders, reasonably, why XIRR() returns such an obviously
incorrect rate when the "guess" was missing.

I invite people like Fred Smith and Myrna Larson to comment on the behavior
of the Newton-Raphson method of approximation, or whatever method Excel
might use. We all know that these methods can take a wrong turn under
adverse conditions. But some details might be interesting.

In any case, I would call this a defect in the XIRR() implementation.

No, I am not disparaging your beloved approximation method. I presume that
it is working as well as might be expected.

Instead, I am disparaging the witless MS programmer who does not seem to
know how to write an "if" statement just before exiting the XIRR() function,
of the form: "if there has not been an error and the XNPV with the last
result is not close to zero, return an error, per the function
specification".

I am referring to the XIRR Help page, which states: "If XIRR can't find a
result that works after 100 tries, the #NUM! error value is returned". Now,
we might argue about what "close to zero" means exactly. But I am sure
that -43,338.86 does not meet anyone's reasonable definition. So clearly,
the XIRR() result does not "work", even it was found in fewer than 100
iterations.

If XIRR() had returned the #NUM! error, as it should in this case, Don might
still have been perplexed. But at least his question would have been much
less mysterious; probably something to the effect of: "how in the heck am I
supposed to know what ``guess`` should be?", and "why does Excel need this,
but my HP 12C does not?". We've dealt with such questions before.

The following is Don's original data and my monthly net cash flow
approximation.

Don's cash flow (my apologies for the poor alignment; there are too many to
adjust manually):

3/27/2006 -3,994.58
4/1/2006 871.42
5/1/2006 871.52
5/3/2006 -9,728.80
6/1/2006 870.51
6/2/2006 -4,864.40
7/1/2006 870.04
7/5/2006 -4,864.40
8/1/2006 869.06
8/3/2006 -4,864.40
9/1/2006 868.53
9/6/2006 -4,864.40
10/1/2006 868.53
10/4/2006 -4,924.67
11/1/2006 867.95
11/6/2006 -4,966.81
12/1/2006 866.79
12/4/2006 -4,966.81
1/1/2007 866.19
1/8/2007 -4,966.81
2/1/2007 866.19
2/5/2007 -4,966.81
3/1/2007 910.86
3/5/2007 -4,864.40
4/1/2007 910.20
4/5/2007 -4,864.40
5/1/2007 909.34
5/3/2007 -4,864.40
6/1/2007 908.50
6/4/2007 -4,864.40
7/1/2007 908.02
7/5/2007 -4,864.40
8/1/2007 907.87
8/3/2007 -4,864.40
9/1/2007 906.91
9/4/2007 -4,864.40
10/1/2007 905.77
10/3/2007 -4,924.67
11/1/2007 905.17
11/5/2007 -4,966.81
12/1/2007 904.60
12/6/2007 -4,966.81
1/1/2008 904.60
1/7/2008 -4,966.81
2/1/2008 904.79
2/6/2008 -4,966.81
3/1/2008 942.92
3/6/2008 -4,864.40
4/1/2008 941.97
4/3/2008 -4,864.40
5/1/2008 940.92
5/5/2008 -4,864.40
6/1/2008 940.48
6/2/2008 -4,864.40
7/1/2008 941.24
7/2/2008 -4,864.40
8/1/2008 941.24
8/4/2008 -4,864.40
9/1/2008 940.68
9/5/2008 -4,864.40
10/1/2008 942.05
10/6/2008 -4,924.67
11/1/2008 944.31
11/3/2008 -4,966.81
12/1/2008 945.74
12/3/2008 -4,966.81
12/28/2008 92,520.92

Monthly net cash flow approximation (my apologies for reversing the
columns):

-3,123.16 4/1/2006
-8,857.28 5/1/2006
-3,993.89 6/1/2006
-3,994.36 7/1/2006
-3,995.34 8/1/2006
-3,995.87 9/1/2006
-4,056.14 10/1/2006
-4,098.86 11/1/2006
-4,100.02 12/1/2006
-4,100.62 1/1/2007
-4,100.62 2/1/2007
-3,953.54 3/1/2007
-3,954.20 4/1/2007
-3,955.06 5/1/2007
-3,955.90 6/1/2007
-3,956.38 7/1/2007
-3,956.53 8/1/2007
-3,957.49 9/1/2007
-4,018.90 10/1/2007
-4,061.64 11/1/2007
-4,062.21 12/1/2007
-4,062.21 1/1/2008
-4,062.02 2/1/2008
-3,921.48 3/1/2008
-3,922.43 4/1/2008
-3,923.48 5/1/2008
-3,923.92 6/1/2008
-3,923.16 7/1/2008
-3,923.16 8/1/2008
-3,923.72 9/1/2008
-3,982.62 10/1/2008
-4,022.50 11/1/2008
-4,021.07 12/1/2008
92,520.92 1/1/2009


----- original message -----

"Don Kline" wrote in message
...
I have a XLS file I can send you. I don't know how to attach the XLS file
to
the discussion. Please advise how I can get this to you.


"JoeU2004" wrote:

"Don Kline" wrote:
I have a series of monthly transactions over several years.
I have used the XIRR function to find the ROR for each month,
each quarter, and each calendar year.

That's a neat trick, since XIRR only returns the annualized rate. I hope
that is what you mean. But I wonder if you are misinterpreting the
results from XIRR.


The client now wants a rate from inception. My expectation
was I would get an annual rate for the entire period. However
I feel that a -400% ROR is not accurate.

Is there anything different I need to do when the period is
greater than one year?

Yes. But I cannot tell you what that is because I'm not a mindreader.
If you post an example, you might get some help.