problem calculating rate of return (IRR)

T

ties

I have a strange problem when trying to calculate function IRR. It gives me
a divide by zero error, which I don't understand. According to the help-file
it shouldn't be able to give me this error. It occurs when I insert the
following numbers into cells A1 to A10.

-10687
-44634
-45750
-5249219
1669875
1249913
11050
878220
815253
=IRR(A1:A9;0.25)

I know the second argument (0.25) is optional, but I need to keep it in.
So solving the problem by leaving it out is not an option for me.

Can someone explain to me why this happens?

Thanks in advance
Regards
Thijs van Bon
 
T

ties

Thanks,
This will certainly help.

I found this code in a .xls I received from my boss and am adjusting it for
his needs. I think I'll try to do it my own way and get rid of this
alltogether.

Regards,
Thijs van Bon
 
G

Grey Newt

The problem is that your guess is so far away from the actual answer (-5%)
and the wrong side of the positive/negative divide.
If you used a guess of -.25 for example - no problem.

Just out of interest - why the need for the guess ????
 
G

Gary Brown

Unfortunately, until you get to a positive rate of return,
you will get an error using the 'guess' UNLESS the 'guess'
is a negative #.
HTH,
Gary Brown
 
R

Ron Rosenfeld

Unfortunately, until you get to a positive rate of return,
you will get an error using the 'guess' UNLESS the 'guess'
is a negative #.
HTH,
Gary Brown
Click to expand...

That's not the case with XL 2002. I get NO error with a guess of 0.24.


--ron
 
D

Dana DeLouis

Don't have an answer, but thought you might want to view this...

XL: IRR Returns #DIV/0! Instead of #NUM! If No IRR Exists
http://support.microsoft.com/default.aspx?scid=kb;en-us;30567&Product=xlw

In Excel 2003, I get a #Div/0! error also with a guess of 20%

=IRR(A1:A9,20%)
#DIV/0!


The algorithm used is probably not very good. How Excel loops to find the
IRR is affected by its starting value. As Excel loops with a discout factor
from a high value (20% or 25%) it probably passes close to 0% on it's way
toward -5%. It looks like a guess of 0% is causing the problem. I don't
think the algorithm is sophistaced enough to handle this.

HTH
Dana DeLouis
 
D

Dana DeLouis

Just for gee wiz. Even Excel's Goal Seek function has a hard time with an
initial guess of about 14% and above for your problem with a neg. return.
In a cell for rate, put in 10%. In another cell put the NPV function in
as:

=NPV(rate,A1:A9)

If I use Tools | Goal Seek... and try to set the NPV to zero by adjusting
the "rate", I get the same answer of -5.36%.

However, with a higher initial guess, Goal Seek doesn't work either. Your
guess needs to be closer with a negative return. Leaving "guess" out of IRR
assumes a guess of 10%.

HTH
Dana DeLouis
 
Top