The inability to calculate monthly interest rates can be a calamity in todayโs modern world. Some of us seek loans while most of us deposit at the bank to burgeon assets. At the same time, Bank officials often belie the loan scheme as well as the deposit scheme; Hence there are many hidden charges as well as calculations. However, we need a blatant concept of how the monthly interest rate is calculated. We are going to expound you to calculate the monthly interest rate in Excel. Excel is one of the dedicated software that can be helpful to calculate multiple data of monthly interest rates altogether.

## ๐ Download Excel File

To practice please download the Excel file from here:

## Overview of RATE Function in Excel

The **RATE **function is a **Financial function**. The **RATE** function always returns the interest rate per period of an annuity. Before that, we need to construe the **Syntax** of a **RATE** function as,

**=RATE(nper, pmt, pv, [fv], [type], [guess])**

Description of the following arguments:

**Nper**:*Essential*. The number of periods in an annuity.**Pmt**:*Essential*. Payment in each period.**Pv**:*Essential*. Present value of Asset.**Fv**:*Optional*. Future value. It is considered**0**when**fv**is omitted.**Type**:*Optional*. It remarks due or undue by**0**or**1**.

## Learn to Calculate Monthly Interest Rate in Excel with These 5 Examples

In this article, we will construe **5 catchy examples** to calculate the monthly interest rate in Excel. Following that, we consider a dataset named **Interest Rate of Michigan State Bank** consisting of **Description**, **Argument**, and **Value**. There are also **4 columns** and **10 rows**. We will learn the calculation for** Simple Interest Rate**, **Compound** **Interest** **Rate**, the use of **VBA macro**, and the application of **RATE**, **PPMT**, and **IPMT** functions. So, Letโs get started.

**method**

### 1. Implement RATE Function to Calculate Monthly Interest Rate on a Loan in Excel

Using the **RATE** function we calculate the monthly interest rate within a moment. Please check out the following steps.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- First, select the cell
**D12**and insert the following formula including the**RATE**function.

**=RATE(D6,-D7,D8)**

- Then
**RATE**function automatically calculates taking the based on the data of**D6**,**D7,**and**D8**which is the**No. of Period**,**Monthly****Payment**, and**Loan****Amount**. - Therefore, the
**Monthly Interest Rate**is showing up in cell**E12**which is**0.62%**.

- Further, by multiplying the
**D12**cell by**12**, we obtain the**Annual Interest Rate**which is**7.42%**.

**๐ Read More: ****2 Approaches to Calculate Interest Between Two Dates in Excel**

**method**

### 2. Find Monthly Interest Rate from Annual Interest Rate

The simplest method is to get the interest data of the annuity and measure the monthly interest rate by dividing the **Annual Interest Rate** by **12**. Please check out the steps.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Primarily, select a cell i.e.
**D6**to get the monthly interest rate. - Secondly, divide the
**Annual Interest Rate**by**12**in the**D6**cell. - Therefore, we obtain the
**Monthly Interest Rate**from the annual interest rate directly.

**๐ Read More: ****3 Ways to Calculate Accrued Interest on Fixed Deposit in Excel**

**method**

### 3. Calculate Monthly Simple Interest Rate Using Formula in Excel

Are you tired of calculating the simple interest rate in the calculator? Calculating the data one by one is quite boring. Excel made it easier. By creating a formula one may calculate bulk data within a second.

But first, check out the Simple interest rate formula and the steps to calculate below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Initially, select blank a cell i.e.
**D12**. - Then, write the following code in
**D12**and get the Annual interest rate.

**=((D6/D7)-1)/D10**

- Therefore, we achieve the
**Interest Rate of an Annuity**based on the selected cell references.

- Finally, by dividing the
**Annual Interest Rate**by**12**months we further get the**Monthly Interest Rate**of**1.11%**.

**๐ Read More: ****Create and Download Late Payment Interest Calculator in Excel**

**method**

### 4.ย Calculate Compound Interest Rate Monthly Using Excel Template

Measuring monthly compound interest rates for bulk quantity data is hectic as well as tiresome. Creating a formula in Excel and auto-filling of cells can be a lifesaver move.

However, we need to learn the Compound interest rate formula first.

Please check out the steps to calculate the compound interest rate.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Initially, select blank a cell i.e.
**D12**. - Then, write the following code in
**D12**and get the Monthly interest rate.

**=((D6/D7)^(1/(D8*D10))-1)*D8**

- Therefore, we achieve an interest rate of
**10.26%**in a month based on the selected cell references.

**๐ Read More: ****4 Approaches to Calculate Home Loan Interest in Excel**

**method**

### 5. Apply VBA Macro

One can use Excel as a calculator just by putting values in a question box with the help of a **VBA** code. By getting the **Developer** option in the **Top Ribbon**, you can generate a program on your own.ย Please follow the steps below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Check first if your
**Developer**option is available or not. - Secondly, to enable the
**Developer**option,**Right-Click**on the mouse at the**Top Ribbon**and select**Customize the Ribbon**.

- Thirdly, click on
**Developer**and click on the**OKย**button.

- Now
**Developer**mode is on. - Then, click on the
**Visual Basic**option**.** - Next, click on
**Module**from the**Insert**menu to insert the**VBA**code.

- Now, write the following code in the module and hit on the Run icon to get the monthly interest rate.

__Code__

```
Sub Monthly_Interest_Rate()
Dim A As Integer
Dim P As Integer
Dim t As Integer
Dim i As Single
A = InputBox(Prompt:="Final Amount")
P = InputBox(Prompt:="Principal AMount")
t = InputBox(Prompt:="Number of years")
i = ((A / P) - 1) / t
Range("D11").Value = i
Range("D12").Value = i / 12
End Sub
```

**๐จ Code breakdown **

๐ย Firstly, **A**, **P**, **t** and** i** denote the Final amount, Principal Amount, Number of Years, and Annual Interest Rate.

๐ย Secondly, **A**, **P**, and **t** are the **integer**. On the other hand, **i** is the **single** which allows **decimals**.

`๐`

ย Thirdly, the use of **InputBox** creates the **question** box and asks for input.

๐ย Fourthly, the calculation of the** Annual Interest Rate** takes place and types the result is in cell **D11** once we **insert** all the data according to the questions.

๐ย Finally, the **Monthly Interest Rate** calculation takes place in the **D12** cell, dividing the **Annual interest rate** by **12**.

- Thus a box appears asking
**Final Amount**. Please write down the amount of**25000**and hit the**OK**button.

- Again a box appears asking
**Principal****Amount**. Please write down the amount of**15000**and hit the**OK**button.

- Further, you will be asked the
**Number of years**. Typing the**5**years till maturity, hit the**OK**button.

- Therefore results of
**Annual**and**Monthly****Interest****Rates**take place in the**D11**and**D12**cells respectively**13.33%**and**1.11%**.

## How to Calculate Principal and Monthly Interest on a Loan in Excel

Using **PPMT** and **IPMT** functions one can calculate the principal and monthly interest amounts on a loan within a minute. However, you can also use the Excel file as a template to calculate on your own. Please check the steps below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Primarily, select blank a cell i.e.
**D15**. - Then divide the
**Yearly Interest Rate**by the Number of Periods per month(**12**) to get the monthly interest rate.

**=D7/D8**

- Therefore, we obtain the
**Monthly Interest Rate**in the**D15**cell.

- Similarly, use the
**PPMT**function in**D16**that calculates the**Principal amount**.

**=PPMT(D15,D9,D11,-D6,D12,D13)**

- Here, the syntax of the
**PPMT**function,**PMT(rate, per, nper, pv, [fv], [type])**. - According to the
**syntax**, selecting the cells, we finally achieve the**Principal Amount**of**$1257.58**in the**D16**cell.

- Again, use the
**IPMT**function in**D17**that calculates the Interest amount.

**=IPMT(D15,D9,D11,-D6,D12,D13)**

- Here, the
**syntax**of**PPMT**function,**IPMT(rate, per, nper, pv, [fv], [type])**. - Therefore, electing the cells according to the
**syntax**, we finally achieve the**Interest Amount**of**$916.67**in the**D17**cell.

## ๐ Important Notes

๐๏ธย Enable the** Developer** menu first to execute** VBA** code.

`๐๏ธ`

ย Watch out for the formulas carefully both the **Simple interest rate** and** Compound interest rate** formulas.

๐๏ธย Refer cell carefully while using **RATE**, **PPMT**, and **IPMT **functions.

## ๐ Takeaways from This Article

๐ย **RATE **function to calculate the interest rate automatically by only selecting cells.

๐ย Excel **formula** to calculate the **Simple** and **Compound interest rate** methods.

`๐`

ย Interest rate calculation from a loan.

๐ย **VBA macro** to use as a calculator.

๐ย **PPMT **and **IPMT **function to calculate the Principal amount and Interest amount from a loan respectively.

## Conclusion

In this article, we learn to calculate the monthly interest rate in Excel. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Donโt hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, donโt forget to visit **www.ExcelDen.com**.

## Related Articles

**3 Quick Steps to Calculate Credit Card Interest in Excel****2 Easy Ways to Calculate Gold Loan Interest in Excel****6 Steps to Create Car Loan Calculator in Excel Sheet**