What is the formula in Excel for figuring an interest rate on a loan?

Question

I need to be able to figure what the interest rate is when the loan amount, payment amount and terms are known. Does anyone know the way to build a formula in Excel for calculating this?

That helped somewhat. I used the RATE function to get the monthly rate on a 60 month loan, then multiplied by 12 to get yearly, but it is coming out about .005 percent off. When I put the numbers into my amortization schedule the payment is off by about 25 dollars. (74,500 total loan, 60 months with $1500 pmt is what I want. The rate it gives me makes the payment $1475 in my schedule.)

Never mind that last comment. It is working perfectly. THANKS!!!

Answer 1

Actually Excel has a number of built in formulas to figure stuff like compound interest. However, to find the interest rate you would use the RATE formula:

RATE(Number of payments, Payment, -Amount)

For example, if you were financing a car over 5 years, $500/month, 20,000 it would look like this:

RATE(60, 500, -20000)

Answer 2

You can get some really spiffy templates from the microsoft office website that can be used for calculating rates, payments and even excelerated payments. Some for auto loans, some for home loans. They have templates for just about anything you can think of.

Answer 3

Yes. type in equals and click the loan amount including the interest rate then type in "/" and click the loan amount without the interest rate.

For example. Loan is 20,000 in cell A1 and total amount is 21,120 in cell A2 you would type in:
=A2/A1 hit enter and update the format to a percentage.

Hope this helps. :)

Answer 4

interest rate on a loan

Answer 5

Click on insert, function. Use the dropdown to get the financial formulas. Find the one labelled RATE.

From there, you just plug in your numbers. You'll have to follow the instructions regarding time periods etc... to make sure it comes out right, but it is very easy.