Why does =RATE return #NUM?

Question

I am following an article (http://www.ehow.com/how_4602938_use-excel-calculate-apr.html) and trying to calculate the Annual Percentage Rate of a loan:

=RATE(C10,C14,C13,0,0)

C10 = 50

C14=-84.91

C13=4667.55, which is the NPV calculated as Amount / (1 + interest rate) ^ number of interest compounding periods

The result of the formula is 0, why?

Answer 1

slyth wrote:

=RATE(C10,C14,C13,0,0)

C10 = 50

C14=-84.91

C13=4667.55, which is the NPV calculated as Amount / (1 + interest rate) ^ number of interest compounding periods

The result of the formula is 0, why?

First, you should see 0%, not 0.  Format as Percentage with more decimal places.  For example, with 2 dp, you should see about -0.37%.

Second, see the RATE help for an explanation of when it returns the #NUM error.  In a nutshell:  usually in that case, RATE needs help in the form the 6th "guess" parameter.

The problem is:  how to estimate a "guess"?  The fact is:  I have seen RATE fail even when I provide the correct periodic rate as a "guess".  It is just flaky in that way.

Oddly, a "guess" of -1% or -10% often works even when RATE should return a positive rate.

The important thing to note is:  RATE computes a periodic interest rate -- whatever the units are for the first parameter ("nper").  Ergo, the "guess" must be a periodic interest rate.

[EDIT] As for why RATE returns a negative rate....  It should be a rate between 0.0000000000001% and 0.00000000000012214%.  [ERRATA] Obviously that is wrong.  I misled myself by focusing on the results of PMT.  Since the loan can be retired with no interest with payments of about 93.35 (4667.55 / 50), anything less imputes a negative interest rate, which is impractical for loan.

If you are computing loan terms and you see a negative rate returned by RATE, the take-away should be:  84.91 is an impractically small periodic payment to retire a loan of 4667.55 over 50 payments.  A more reasonable minimum payment would be 93.36, which is a periodic rate of about 0.000378% (!).  Even that is impractically small, IMHO.

Answer 2

But in the body of your message you wrote:  "The result of the formula is 0"  So under what circumstances were you seeing #NUM ?

HELP for the RATE function explains why you might see a #NUM error, but since you provided no examples, it is difficult to advise you further.

Answer 3

I was seeing #NUM...

Answer 4

The body of your message has nothing to do with the title.  Under what circumstances are you seeing a #NUM error?

So far as the result in the body of your message, do you really have a result of 0, or are you just seeing 0%?

If the latter, you probably are not displaying the result with enough decimal places, as the answer is approximately -0.00365521802368394

It will be a small number, and negative,