IRR - limited to 45 cells?

chadm

New Member
Joined
Apr 14, 2002
Messages
45
I am having trouble using the IRR formula when I have over 45 cash flows.

Is there a simple solution??
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
L

Legacy 7259

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

What problems are you experiencing?
 

gwkenny

Well-known Member
Joined
Aug 13, 2002
Messages
565
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-
 

chadm

New Member
Joined
Apr 14, 2002
Messages
45

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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.
 

Forum statistics

Threads
1,144,124
Messages
5,722,620
Members
422,448
Latest member
AugyIA

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top