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

scrupul0us

Well-known Member

Joined

Sep 7, 2004

Messages

641

• Jan 10, 2005

• #2

Im bumping this... i cannot find a solution either and have also used this function many times

 

Upvote 0

B

bran987

New Member

Joined

Jan 10, 2005

Messages

45

• Jan 10, 2005

• #3

P.S. I tried turning off circular references changing the iterations and turning them back on too. There shouldn't be a circular reference though I pasted special "Values Only" so the formula would be eliminated as a possible error.

The cash flows are intended to be monthly

 

Upvote 0

B

bran987

New Member

Joined

Jan 10, 2005

Messages

45

• Jan 10, 2005

• #4

scrupul0us said:

Im bumping this... i cannot find a solution either and have also used this function many times

Click to expand...

Thanks, did you get the same error?

 

Upvote 0

S

Seti

Well-known Member

Joined

May 19, 2002

Messages

2,916

• Jan 10, 2005

• #5

Try an initial guess of 1%. If my initial huess is 10%, I get #div/0! too. You need a good initial guess sometimes. Try using NPV to get close, then use that as an initial guess if need be.

 

Upvote 0

O

Old Army

New Member

Joined

Jul 9, 2003

Messages

6

• Jan 10, 2005

• #6

I've played with this a little. Seems it has to do with the number of periods with zero values. If I take the negative and positie numbers without the zeros I can gett IRR to calculate. As I start populating with zeros it suddenly blows up, Just an observation.

 

Upvote 0

B

bran987

New Member

Joined

Jan 10, 2005

Messages

45

• Jan 10, 2005

• #7

Seti said:

Try an initial guess of 1%. If my initial guess is 10%, I get #div/0! too. You need a good initial guess sometimes. Try using NPV to get close, then use that as an initial guess if need be.

Click to expand...

Ah ha!!! I did this and got an IRR of 0.9044%, which would be 10.8% annually, did you get this too?

I guess the mistake was putting in an annual IRR guess right? duh, but odd that without a guess it still returned DIV/0

I hope it's correct haha, kind of important.

 

Upvote 0

B

bran987

New Member

Joined

Jan 10, 2005

Messages

45

• Jan 10, 2005

• #8

Thank You!

 

Upvote 0

S

Seti

Well-known Member

Joined

May 19, 2002

Messages

2,916

• Jan 10, 2005

• #9

I think the error is because of the data. If you calc the NPV using rates from -.05 to .05, you get a very steeply descending curve and then it flattens out at about 2%. The underlying algorithm must get a div by 0 for this particular stream.

 

Upvote 0

scrupul0us

Well-known Member

Joined

Sep 7, 2004

Messages

641

• Jan 10, 2005

• #10

This link might be useful

http://www.mrexcel.com/archive/Formulas/9928.html

 

Upvote 0