This article will demonstrate some simple processes to calculate the **Monthly Interest Rate **in Excel. To illustrate our methods, we’ll use the following sample dataset, representing the monthly interest on a loan amount (or principal) over a certain number of periods.

## What is the Monthly Interest Rate?

The amount of interest charged monthly by a lender to a borrower on a principal is called the **Monthly Interest Rate**. The interest rate is also applicable when receiving interest as opposed to paying it, such as the interest earned on a bank savings account.

## How to Calculate Monthly Interest Rate in Excel: 3 Simple Methods

### Method 1 – Using RATE Function

The **RATE** function is used to return the nominal interest rate per period on a loan or an investment.

#### 1.1 – Interest Rate on a Loan

**STEPS:**

- Select cell
**D8**and enter the following formula:

`=RATE(D5,-D6,D7)`

**NOTE:** Here, the **Minus **sign before **D6 **indicates payment amounts.

- Press
**Enter**to return the result.

#### 1.2 – Interest Rate on a Saving Account

Additionally, we can also calculate the monthly interest rate on a **Savings Account**. In this case, we don’t have a current value but we do have a future value.

**STEPS:**

- Select cell
**D8**and enter the following formula:

`=RATE(D5,-D6,0,D7)`

**NOTE:** Here, the **Minus **sign before **D6 **indicates the payment amounts, and **0 **is placed in the formula as there is no current value.

- Press
**Enter**to return the result.

### Method 2 – Using Annual Percentage Rate

Most financial institutions use an annual percentage rate rather than a monthly rate. We can calculate the monthly interest rate from the Annual Percentage Rate (APR) with a simple calculation.

**STEPS:**

- In cell
**C6**, enter the following formula:

`=C5/12`

- Press
**Enter**to display the result in cell**C6**.

### Method 3 – Using EFFECT Function

We can apply the Excel **EFFECT** function to calculate the Effective Interest Rate where monthly compounding is present. Effective interest rate is also known as **Annual Percentage Yield** (**APY**), which is higher than the nominal interest rate or **Annual Percentage Rate **(**APR**). We’ll apply the **EFFECT** function to the **Nominal Interest Rate** to return the effective annual interest rate, then derive the monthly interest from that.

**STEPS:**

- Select cell
**C7**and enter the following formula:

`=EFFECT(C5,C6)`

- Press
**Enter**to return the effective annual interest rate.

- Select cell
**C8**and enter the following formula:

`=C7/12`

- Press
**Enter**to return the monthly interest rate.

**Read more:** How to Calculate Interest Rate from EMI in Excel

**Download Practice Workbook**

## Further Readings

- How to Convert Monthly Interest Rate to Annual in Excel
- How to Create Effective Interest Method of Amortization in Excel

**<< Go Back to How to Calculate Interest Rate in Excel | ****Excel for Finance**** | ****Learn Excel**