XIRR problem

[Dkline]

Below is the schedule that I am dealing with. The problem is that I cannot
find a rate that does not return a #NUM!. I've tried both negative and
positive return values and have not found one yet that returns something
other than #NUM!

3/27/2006 ($3,993.08)
4/1/2006 $871.42
5/1/2006 $871.52
5/3/2006 ($4,864.40)
5/3/2006 ($4,864.40)
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

 

[Bob Umlas]

Giving it a guess parameter works:
=IRR(B1:B68,0)
produces
-0.01212

HTH

 

[Dkline]

Thanks for your help. It had not occured to me that putting the IRR function
as a guess was an option. So I leanred something new today. Thank you.

 

[joeu2004]

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.

 

[Dana DeLouis]

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

 

[Dana DeLouis]

Oops! My mistake. I read the solution wrong.
I read that -0.01212 was the answer.
I see now that the negative value was used as the guess to arrive at the
solution of -0.248.
Please disregard. :>~
= = =
Dana DeLouis

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

<snip>