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.

**Table of Contents**hide

**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!