# IRR - limited to 45 cells?

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

Is there a simple solution??

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I have IRR formulas with more than 45 values without any problems.

What problems are you experiencing?

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-

Try using XIRR

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!

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.

Thanks Jay, that works great!

Replies
1
Views
261
Replies
6
Views
858
Replies
3
Views
2K
Replies
1
Views
514
Replies
1
Views
2K

1,219,519
Messages
6,148,750
Members
450,833
Latest member
Andyboi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back