IRR - #DIV/0 Error

Corey

New Member
Joined
Mar 14, 2002
Messages
25
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?


That was very helpful...
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top