Rate function

[Guest]

Getting #Num1 error for the rate function for 200000-PV -41067-PMT, and
20-years the rate is 20% . Excel is not calculating the rate. I can use my
financial calculator and get an answer.

Note: This does work for 15 years. I tried experimenting with the
iterations under excel options under calculation.

 

[joeu2004]

Getting #Num1 error for the rate function for 200000-PV -41067-PMT, and
20-years the rate is 20% . Excel is not calculating the rate.

From the Help page for the Rate function:

"If the successive results of RATE do not converge to within 0.0000001
after 20 iterations, RATE returns the #NUM! error value."

"[The parameter] Guess is your guess for what the rate will be. If you
omit guess, it is assumed to be 10 percent. If RATE does not converge,
try different values for guess."

Sure enough, the following works for me (Office Excel 2003):

=rate(20, -41067, 200000, 0, 0, 41067/200000)

I tried experimenting with the iterations under excel options under calculation.

One Help page says that changing the iteration option should benefit
RATE. But it has never worked for me either, even when I also change
"maximum change".