Hi. A Math loop, as well as Goal Seek both came up with...
-0.24832597

The Mac version of Excel also came to the same conclusion:

=XIRR(B1:B68,A1:A68,-0.1)

-0.24832597

As a side note, It is a known issue that Excel's IRR has a major
algorithm bug. It just does not work well when the cash flows are
negative. Very simple math loops can arrive at the correct solution,
but Excel can not. It will even return an error if given the solution
as a guess! Go figure!! :~

= = =
Dana DeLouis



joeu2004 wrote:
On Feb 20, 1:13 pm, Dkline wrote:
Thanks for your help. It had not occured to me that putting
the IRR function as a guess was an option.

Whatever way you want to determine a guess that works is fine. Excel
does not care. Using IRR will not always work, since IRR itself
sometimes requires a guess. Catch-22.

In your case, considering the fact that most of the cash flows occur 2
and 3 per month, but IRR will see them as having the same frequency
(monthly, sort of), I would say that using IRR for the guess in this
case was dumb luck.

Moreover, IRR will return a monthly rate (sort of) in this case,
whereas the XIRR guess should be an annual rate, since that is what
XIRR always returns. Again, dumb luck.

PS: I could not get IRR(B1:B68) to work. It returns a #DIV/0 error,
which is another way that IRR says "gimme a guess". On the other
hand, XIRR worked just fine for me without a guess. It returns about
0.0000002980%. I have not tried to vet the result. FYI, I am using
Excel 2003.