Am I using RATE function wrongly?

Question

right now I am using Excel for practice, and about rate function, I cannot calculate the interest rate of the product.

Please look at the photo below↓ Column G is where I must fulfil the formula.

What I tried is, for example

Row10, =RATE(C10,-E10,0,B10) then get -1.31%

Or, =RATE(C10,-E10,0,F10) then get 0.00%

Or, =RATE(C10,-E10,B10,F10) then get #NUM!

Without function, I calculate Row10

(426.80$-399.90$)/399.90$/11x100=0.611516515%

Therefore, G10 must be 0.611516515%

Am I using RATE function wrongly? Or should I use a different function?

enter image description here

Answer 1

You are returning the average interest paid over the term of the load with your check formula not the interest rate.

To get the RATE:

=RATE(C10,-E10,B10)

The FV(Future Value) on a load is assumed to be 0 as the loan should be paid off. That formula returns 1.101%

If we do a quick amortization schedule you will see that to be the correct answer:

enter image description here

• K Lee: How about this calculation? (426.80$-399.90$)/399.90$/11x100=0.611516515% Because 100$ in bank, next year 101$ in the bank, then interest rate per year is 1% right? And, this can be found (101-100)/100x100=0.01.
• Scott Craner: see my first sentence. You are returning the average interest paid as part of the initial value. not the interest rate. Interest is compounding. as payments advance less of the payment goes to interest. Your formula does not return what you think it does.
• Scott Craner: for one month that formula works but not for multiple months. at 1% 100-->101-->102.1-->103.03-->104.06 by your formula (104.06-100)/100/4*100 = 1.015%
• K Lee: Ok, now I see my mistake, as you indicate, Interest is compounding, so ↑ means 1.015% all 4 months. Not too straight from the rate of 1 month to 4 months by times 4, because it including exponentially. Thank you very much