< Go Back

What Is the Formula to Calculate the Interest Rate for a Loan in Excel?

Excel is often used to make financial calculations and decisions. If you have certain information, you create a formula to calculate the interest rate for a loan. 

 Calculating the Interest Rate for a Loan in Excel

There is a formula that works perfectly for loans which can be used by lending companies as well as leasing companies. Using this formula, they can easily fetch the interest rate against the loan amount, monthly installments, and other smaller details that are needed to calculate the interest rate. Based on the  RATE function, this formula instantly calculates the interest rate for a loan. Let us explain it.

Basic Formula for an Interest Rate Calculation

=RATE (periods,installment,-amount)*12

Explanation of the Formula

You can calculate the periodic interest rate for a loan using the RATE function. Make sure you have the loan amount, payment amount, and the total months of payment period as they will be used in the calculation of the interest.

Example of Interest Rate Calculation

In the given screenshot, we have used the following formula in D9:

=RATE(D6, D5, -D4)*12

There are four basic components of a loan; the loan amount, the interest rate, the loan term (total months), and the payment to be made every month (period).

The RATE function has another productive use as it can calculate the periodic or monthly interest rate if you already know the total amount, number of payment periods, and the monthly payment amount.

Figure1. Example to calculate Interest rate for the loan after applying the formula

In the given example, we have calculated the interest rate for a $10,000 loan that is payable in 60 months and the payment for every month is $186.44. We have configured the NPER function as follows:

NPER – Indicates the number of periods and defined in D6.                                                         

pmt – the Total monthly payment that is $186.44 and is given in D5.

pv – This is the total present value and is defined in D4. The minus sign (-D4) indicates that it is a negative value.

Using these values, we get 0.38% by the RATE function that is a periodic interest rate. Later, we have multiplied it with 12 to get an annual interest rate that is 5%.

=RATE(D6,D5,-D4)*12

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar