IRR - #DIV/0 Error

[Corey]

I usually encounter this error if the IRR is so low that the IRR is not worth calculating (investment loss). However, I have $2.4 million of investment with $5.9 million of return. I keep getting a #DIV/0 error until I try numerous "Guesses." I can get it to work with a -.10% guess.

I understand that Excel will attempt 20 passes at calculating the IRR, then return an error if it cannot solve for the IRR. Is there a way to get Excel to try more than 20 attempts?

I don't think changing "Iterations" in the Calculation tab will increase the number of attempts Excel will try before giving up on the IRR calculation. Any ideas?

[Jay Petrulis]

On 2002-04-22 13:59, Corey wrote:
I usually encounter this error if the IRR is so low that the IRR is not worth calculating (investment loss). However, I have $2.4 million of investment with $5.9 million of return. I keep getting a #DIV/0 error until I try numerous "Guesses." I can get it to work with a -.10% guess.

I understand that Excel will attempt 20 passes at calculating the IRR, then return an error if it cannot solve for the IRR. Is there a way to get Excel to try more than 20 attempts?

I don't think changing "Iterations" in the Calculation tab will increase the number of attempts Excel will try before giving up on the IRR calculation. Any ideas?

Hi Corey,

Please tell us exactly what you did.

cell A1 holds -2.4
cell A2 holds 5.9
=IRR(A1:A2,.1) returns 145.83%

If I add 7 zeros between the two numbers
=IRR({-2.4,0,0,0,0,0,0,0,5.9},0.1)
returns 11.9%

I have read that if you use -0.9 as a guess, IRR will always converge to a value, whether the retrun is + or -.

Also, try using XIRR if you can, at least to verify the annualized IRR result.

Bye,
Jay

P.S. By the way, how did the explanation work for you (from a thread from awhile ago), where you had to explain to your boss annualizing the IRR result to get the XIRR value?

[ This Message was edited by: Jay Petrulis on 2002-04-22 15:07 ]

[Corey]

Thanks Jay. I'll give that a try. Thanks also for the posting last month. Been holding off on using XIRR because it is an add-in, worried future users might not understand why an error occured if they don't have that particular add-in installed. Once I define who the users are, maybe I'll start using XIRR and make sure all futute user have the add-in installed. Thanks.

[apsnyc]

Jay,

I want to thank you as well. I had a similar problem to Corey's and your suggestion to use '-0.9' as the guess solved the problem.

Great site. Will use it again in the future.

Regards,
Aaron

[Richard Schollar]

This post was useful to me too - didn't realise a small IRR could produce a #DIV/0! error.