In Microsoft Excel, we can do the financial calculations quickly, because excel has some built-in financial functions. For example, suppose you want to get a loan to buy a car. Now, you need to know how much interest rate you will have to pay your bank. In this article, we will learn to calculate the interest rate on a loan in excel. Excel has a function called RATE, which is specifically built for computing interest rates for a specified time period.
Download Practice Workbook
You can download the workbook and practice with them.
Introduction to the Excel RATE Function
The RATE function is an Excel Financial function. It is used to determine the interest rate on a loan. We can also use it to get the periodical rate of interest, which can then be multiplied to get the annual interest rate.
➧ Syntax
The syntax for the RATE function is:
=RATE(nper, pmt, pv, [fv], [type], [guess])
➧ Arguments
nper: [required] The number of payment cycles in total.
pmt: [required] Each period, a payment is made.
pv: [required] The entire worth of all current loan installments, or the present value.
fv: [optional] The desired cash balance after the last payment, or the future worth. The default value is 0.
type: [optional] Due payments. 0 indicates the end of the period. 1 indicates the start of the period. The default value is 0.
guess: [optional] Estimation of the rate. The default percentage is 10%.
➧ Return Value
The interest payment rate.
2 Criteria to Calculate Interest Rate on a Loan in Excel
We have a lot of economic factors in deciding our daily lives. When we take a loan from any financial institution, we must refund the money plus a fee for using the service. Interest is the term used to describe the extra amount of money paid. The interest rate is calculated on a period-by-period basis. Let’s demonstrate the criteria to calculate the interest rate on a loan in excel.
1. RATE Function to Get the Monthly Interest Rate on a Loan in Excel
In most cases, the installment of the loans is paid monthly. For calculating the interest rate for monthly installments we just need to put some information on the RATE function.
Suppose, we want to get a loan to buy a car. And now we need to know about the interest for monthly installments. For this, we have the following dataset, the loan will be paid back in two years of monthly installments. To get the number of the payment period, multiply 2 years and the months per year 12, (2*12=24) in cell C5. And the payment amount per period is -$300 in cell C6 and the loan amount is $5,000. Now we want to calculate the monthly interest rate in cell C9. To get this, let’s go through the steps down.
STEPS:
- Firstly, select the cell where you want to calculate the interest rate. So, we select cell C9.
- Secondly, we can use the formula there. The formula has some parameters, nper (number of payment period) in cell C5 is 24, pmt (payment amount per period) in cell C6 is -$300, pv (loan payment) in cell C7 is $5,000. Now, write down the following formula.
=RATE(C5,C6,C7)- After that, press Enter.
- And, that’s it. By using the above formula we get the interest rate in cell C9. If the dataset has the number of payments per period in a year, we can multiply it by the months inside nper parameter. So, the formula is.
=RATE(C5*12,C6,C7)- Finally, press Enter on your keyboard.
Similar Readings
- Calculate Principal and Interest on a Loan in Excel
- How to Calculate Daily Interest in Excel (2 Easy Ways)
- Calculate Home Loan Interest in Excel (2 Easy Ways)
- How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)
- Calculation of Interest During Construction in Excel
2. Calculate Annual Interest Rate on a Loan Using RATE Function
We can also get the annual interest rate by using the RATE function. For this, let’s follow the procedure below.
STEPS:
- Firstly, choose the cell where you wish o be calculate the interest rate. As a result, we choose cell C9.
- Secondly, write down the formula below. We have to multiply the periods per year with the RATE function result.
=RATE(C5,C6,C7)*C8- After that, press Enter.
Get Monthly Payment with Interest Loan in Excel
If we already know the interest rate and want to know the monthly payment. We can get it by using the PMT function. To calculate the monthly payment with interest for the loan follow the simple steps.
STEPS:
- In the beginning, likewise, the previous methods, select cell C10.
- After that, write the formula there.
=PMT(C6/12, C5, -C7)- Finally, press Enter.
The monthly payment for the loan has four parameters. In cell C5, the number of monthly payment periods, in cell C6, the interest rate, in cell C7, the loan amount, and the periods per year in cell C8. If we have all four information, we can utilize the Excel PMT Function to compute the payment amount.
Things to Remember
- The RATE function returns the interest rate for such a period in which the loan installments are being made.
- The outcome of the formula is a percentage of zero and without any decimal points (0%). Apply the Percentage format using two or many decimal points towards the cell that contains your calculation to solve this issue.
Conclusion
The above methods will assist you to calculate the interest rate on a loan in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
