#DIV/0 error in IRR formula? How is that possible?

[bran987]

ok I have a list of these cash flows:

-500,000
-8,482,710
-578,710
-340,000
-340,000
-340,000
-340,000
-340,000
-1,192,854
-621,427
-1,192,854
-1,764,281
-4,621,417
-4,345,746
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
12,253,733
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
26,731,978

When I do IRR(A1:A68) I get a #DIV/0 error? I've never gotten that before on an IRR? Putting in a guess didn't seem to help, I traced the error and it referred to the first cell but I have no idea what I'm doing wrong. I've used IRR a million times I don't get it.

Thank you,
Brandon

 

[Jay Petrulis]

Hi,

A guess of -0.5 should allow IRR to converge in almost all situations.

=IRR(A1:A68,-0.5)

returned 0.9044%.

Also, you can attach a series of dates to the stream and use XIRR. If you make them annual dates (1/1/2001 through 1/1/2068 for instance) you will not have to unannualize the results.

=XIRR(A1:A68,B1:B68,SIGN(SUM(A1:A68))/10)

returned

0.9038%