irr, xirr, npv frustrations

[Zachary Chan]

Copied below, I have a series of monthly cash flows for a project. The IRR
function is giving me a "#DIV/0!" error, while the XIRR gives me a return of
10.19%. However, when I try to double check that return by discounting each
cash flow individually (NPV), I arrive at a rate of 9.75%. So I have two
questions for all you excel brains out there:
1) Why is my IRR not working when XIRR is? and
2) Why is my NPV check not matching the XIRR result?

I've been searching through microsoft excel's help section and poring over
various forums without any luck. Any advice would be much appreciated, and
allow me to stop bashing my head against the wall!

Thanks,
Zach

12/1/2005 ($128,931,571)
1/1/2006 ($1,431,571)
2/1/2006 ($1,431,571)
3/1/2006 ($1,431,571)
4/1/2006 ($130,363,142)
5/1/2006 ($2,863,142)
6/1/2006 ($2,863,142)
7/1/2006 ($2,863,142)
8/1/2006 ($2,863,142)
9/1/2006 ($2,863,142)
10/1/2006 ($2,863,142)
11/1/2006 ($2,863,142)
12/1/2006 ($621,565,535)
1/1/2007 ($45,536,678)
2/1/2007 ($2,822,847)
3/1/2007 ($2,458,944)
4/1/2007 ($2,170,616)
5/1/2007 ($1,883,945)
6/1/2007 ($1,598,931)
7/1/2007 ($1,315,573)
8/1/2007 ($1,033,872)
9/1/2007 ($753,828)
10/1/2007 ($475,440)
11/1/2007 ($198,708)
12/1/2007 $76,366
1/1/2008 $349,784
2/1/2008 $440,556
3/1/2008 $440,556
4/1/2008 $443,149
5/1/2008 $444,446
6/1/2008 $444,446
7/1/2008 $444,446
8/1/2008 $444,446
9/1/2008 $444,446
10/1/2008 $444,446
11/1/2008 $444,446
12/1/2008 $444,446
1/1/2009 $444,446
2/1/2009 $444,446
3/1/2009 $444,446
4/1/2009 $444,446
5/1/2009 $444,446
6/1/2009 $444,446
7/1/2009 $444,446
8/1/2009 $444,446
9/1/2009 $444,446
10/1/2009 $444,446
11/1/2009 $444,446
12/1/2009 $2,228,768
1/1/2010 $2,332,674
2/1/2010 $2,413,092
3/1/2010 $2,539,117
4/1/2010 $2,643,023
5/1/2010 $2,746,928
6/1/2010 $2,850,834
7/1/2010 $2,969,999
8/1/2010 $3,089,164
9/1/2010 $3,208,128
10/1/2010 $3,327,293
11/1/2010 $1,400,293,587

 

[joeu2004]

Copied below, I have a series of monthly cash flows for
a project. The IRR function is giving me a "#DIV/0!" error

Of course, it might have been helpful if you had posted
the IRR() formula that you are using.

I cannot answer your questions. But I __can__ tell you
how to make IRR() work with your data, which I think is
what you really want to know. Add a "guess" of 0.01.

I discovered this by debugging your problem in the
following manner. This might help you solve problems
on your own in the future.

I entered the formula =IRR(Bx:B60) into D1, starting
with B24 for "Bx" and decreasing it (actually using a
"binary search" methodology). I discovered that for
IRR(B13:B60), I got a #NUM! error. The IRR help text
explains that this means that IRR() could not determine
the rate of return after 20 tries starting with a guess
of 10%. When I got to IRR(B12:B60), the #DIV/0! error
appeared. Apparently, this has the same implications
as #NUM!. The implementation of IRR() must be reaching
a divisor of zero even before the 20 iteration limit.

(Wouldn't it be nice of that were documented in the IRR
help text?)

That convinced me to try a "guess" less than 10%.
Experimentally, I discovered that 1% worked.
IRR(B1:B60,0.01) results in a rate of return 0.81%.

I checked this number by putting =PV($D$1,ROW()-1,,-B1)
into C1 and copying the formula down through B60.
SUM(C1:C60) is approximately 0, as we would expect.

 

[Zachary Chan]

joeu2004 - Thank you very very much! I was simply using IRR(cash flows) and
not putting in a "guess" value. As you discovered, Excel starts from 10% and
tries 20 iterations before giving up. Honestly, that's pretty disappointing
that it only gives you 20 iterations. I wonder if that is adjustable, the
way general iterations on excel are...

As you showed, using the excel PV() function also confirms the IRR. I was
using the more old fashioned formula of Bx/(1+$D$1)^(ROW()-1), and didn't
realize that I was getting a simple rate, not compounded. Compounding
appropriately gave me the same answer as PV().

Thanks again - I had posted earlier on excelforum.com and had 10 views and
NO ONE could solve my problem!

 

[Fred Smith]

With respect to the limit of 20 iterations, this makes sense in my experience. I
have programmed the Newton-Raphson method to calculate the rate, and it is
*extremely* efficient. I have seen it converge from a guess of 10% to the
correct answer of -20% on a large set of data in less than 7 iterations.

If IRR doesn't get to the right answer in 20 tries, it's almost always spinning
its wheels and you're better off if it quit.

 

[Zachary Chan]

Does Excel use this "Newton-Raphson" method? Because as in my case, IRR
couldn't move from 10% to 0.81% in 20 iterations, returning an error message
instead.