IRR - limited to 45 cells?

[chadm]

I am having trouble using the IRR formula when I have over 45 cash flows.

Is there a simple solution??

 

[Legacy 7259]

I have IRR formulas with more than 45 values without any problems.

What problems are you experiencing?

 

[gwkenny]

Yah, IRR barfs under a number of conditions.

Just for fun. Put 2000 in the first row. Make the next 100 or so rows -60 or something. Irr will barf on you.

Rate got the same type of problems.

Works in 99% of the times you want it too, but....

g-

 

[Legacy 7259]

Try using XIRR

 

[chadm]

ok, here is a simplified version of what I have:

Cell 1 a value of -10,000,000
the next 42 cells have a value of 0 (you have to enter 0 in each cell).
Cell 44 has a value of 50,000,000.

the formula: IRR(A1:A44) returns the value #NUM!

with one less cell it works.

Any ideas on how to fix this?

Thanks for any suggestions!

 

[Jay Petrulis]

Hi,

Use a guess of -0.9 with IRR and it should converge.

=IRR(A1:A44,-0.9)

with your data set returned 3.814% for me.

I have read that IRR will converge almost always when you give it a guess of -0.9.

If you use XIRR, give the guess as 0.1 times the sign of the sum of the cashflows.

Also be careful if you have non-normal cashflows (more than one sign change), as you will have multiple IRRs.

 

[chadm]

Thanks Jay, that works great!