RATE formula #NUM! error

Question

Hello guys, 

So I have this RATE formula: 

=RATE(9,,-20,1665)

However, when I write exactly the same formula into Google's Sheets the formula works correctly.

Can somebody explain me how to fix it Excel?

Oh btw it used it it multiple times before and had no issue, so I'm really confused.

Thank you in advance

Answer 1

When there is no "pmt" (second parameter), you can compute the periodic rate directly as follows:

=(1665/20)^(1/9) - 1

formatted as Percentage.

I suspect that Google Sheets is smart enough to realize that.

Sadly, Excel is not.  But usually, it helps to provide a "guess".  In your case, the following works:

=RATE(9,,-20,1665,,60%)

However, that's a cheat.  There is no good way to know what a good "guess" is.  I used 60% only because you told us the answer, in the first place.

(I use a table of NPV formulas -- or PV formulas, in this case -- to see where a "guess" might be, using a list of rates from -99% to 100% or more.  But that's complicated.)

Answer 2

Hi Arturg438,

I'm Gustavo, an independent advisor. I'll be glad to help you.

Excel has a limit for this function. When it is necessary to perform a large number of calculations this error occurs. To avoid this, you must put an estimate at the end of the function. The estimate generally ranges from 0 to 1.

Example: =RATE(9;;-20;1665;;0,4)

If you want to know more about RATE function, check the link below:

https://support.microsoft.com/en-us/office/rate...

Let me know if I helped.

Besst regards.