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
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%.
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.