problem with the RATE function

[David F. Carrithers]

I am running EXCEL 2002 with Crystal Ball. In a project
anaylsis simulation, I want to calculate IRR. I use the
RATE function, rather than the IRR function, since the
number of periods is a random variable selected from a
triangular distribution, as are the values for pv and
pmt. The problem I have encountered is that the values
for Nper,pv and pmt, selected in the simulation
occasionally result in a #NUM! error message. However, I
find that I can calculate an IRR from the values on a
financial calculator with out difficulty.

Please try the following and advise me whether you get a
numeric value or an error message. In the RATE function,
for Nper - input 10.60710529, for the pv - input
(2,370,224), for pmt - input 825,194. Click OK. I get
the error message, but I don't know why. Can anyone
explain this?
David

 

[Ron Rosenfeld]

I am running EXCEL 2002 with Crystal Ball. In a project
anaylsis simulation, I want to calculate IRR. I use the
RATE function, rather than the IRR function, since the
number of periods is a random variable selected from a
triangular distribution, as are the values for pv and
pmt. The problem I have encountered is that the values
for Nper,pv and pmt, selected in the simulation
occasionally result in a #NUM! error message. However, I
find that I can calculate an IRR from the values on a
financial calculator with out difficulty.

Please try the following and advise me whether you get a
numeric value or an error message. In the RATE function,
for Nper - input 10.60710529, for the pv - input
(2,370,224), for pmt - input 825,194. Click OK. I get
the error message, but I don't know why. Can anyone
explain this?
David

From HELP:

RATE is calculated by iteration and can have zero or more solutions. If the
successive results of RATE do not converge to within 0.0000001 after 20
iterations, RATE returns the #NUM! error value.


I note that the numbers you give --> #NUM! with the simple RATE formula, but if
I input 0.2 for the guess, then the result is 33%.


--ron