How to Calculate Interest Rate on a Loan in Excel (2 Criteria)

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.

2 Criteria to Calculate Interest Rate on a Loan in Excel

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.

2 Criteria to Calculate Interest Rate on a Loan in Excel

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

2 Criteria to Calculate Interest Rate on a Loan in Excel


Similar Readings


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.

https://www.exceldemy.com/excel-rate-function/


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!


Further Readings

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo