I believe the rate function is broken in Excel 2003.... I can't verify this with other sources thoug

[williameis]

I apologize if this post is a duplication of 100 other people finding
the same thing, but I have been unable to see reference to it
elsewhere. I have Excel 2003 11.8117.8107 SP2.

for the Rate(Nper, Pmt, PV) equation I have found a strange case.

=RATE(998,860042,-37200000) --> 2.312%
Now if I lower the present value, I would except the rate to increaes
which it does:
=RATE(998,860042,-17200000) --> 5.00%

But the odd case is when I change the PV to something in between the
two it should give a result near 3.5%.... but instead it gives this
error
=RATE(998,860042,-27200000) --> #NUM

Any help would be appreciated or at least verification that this same
error is happening with other peoples version of Excel.

 

[Ed Ferrero]

Hi Willame,

Interesting, you appear to have found a case where Excel fails to calculate
the RATE.

Using Excel 2003, and nper = 998, pmt = 860,042, pv = -27,200,000 I confirm
that I get an error.
If pv is changed to -27,200,001 or to -27,199,999 then I get rate = 3.16%.

This error appears to have been fixed in Excel 2007.

Ed Ferrero

 

[John Taylor]

G'day,

I can verify that it happens on my system, using the same Excel version as
you are, but can't offer an explanation.

On my system I also have Excel 97 SR2, and the same thing happens when using
your formulae in that version.

Regards,

John

 

[williameis]

I apologize if this post is a duplication of 100 other people finding
the same thing, but I have been unable to see reference to it
elsewhere. I have Excel 2003 11.8117.8107 SP2.

for the Rate(Nper, Pmt, PV) equation I have found a strange case.

=RATE(998,860042,-37200000) --> 2.312%
Now if I lower the present value, I would except the rate to increaes
which it does:
=RATE(998,860042,-17200000) --> 5.00%

But the odd case is when I change the PV to something in between the
two it should give a result near 3.5%.... but instead it gives this
error
=RATE(998,860042,-27200000) --> #NUM

Any help would be appreciated or at least verification that this same
error is happening with other peoples version of Excel.

I just found a new one, but don't see any obvious relationship to the
old one:

=RATE(360,116474,-3332000,0) gives the #NUM error

Adding OR subtracting 1 from 116474 fixes the problem.