Consider the following dataset that we’ll use to calculate interest rates. We’ll calculate the **Monthly **and **Yearly Interest Rates**, as well as the **Effective **and **Nominal Interest Rates**, using formulas and functions.

### Method 1 – Use a Formula to Calculate the Periodic Interest Rate in Excel

We will compute the interest rate for months and then for years in the two subsections below.

We will apply **the** **RATE function**.

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

Here,

**Nper**(required) – total payment periods number (years, months)**Pmt**(required) – the pre-set payment amounts each period that cannot be varied over the annuity’s lifetime. It generally involves principal and interest but excludes taxes.**Pv**(required) – the present worth of the loan.**Fv**(optional) – the future worth, or the cash balance you want after the last installment It defaults to**0**if not specified.**Type**(optional) – specifies the date on which the payments are made:- Payment is due at the end of the period if
**0**or absent (default). **1**– the first payment is required at the start of the period.**Guess**(optional) – your best guess as to what the rate may be If you leave it blank, it defaults to**10%.**

#### Case 1.1 – Monthly Interest Rate

**Steps:**

- Select cell
**F4**. - Insert the following formula.

`=RATE(C4,-C5, C6)`

**Press Enter.**

#### Case 1.2 – Annual Interest Rate

We’ll compute the monthly interest rate first, then multiply by **12 **to get the annual interest rate.

**Steps:**

- Insert the following formula in cell
**F4:**

`=RATE(C4,-C5, C6)`

- Multiply the previous calculation by the value of
**C7**or use the following formula to get the annual interest rate:

`=RATE(C4, -C5,C6)*C7`

- The Annual Interest Rate will be shown in cell
**F6**.

Pay attention to the **C5 **cell. It is in negative form since the payment will be reduced after each transaction. It will show a #**NUM** error if you don’t put the value in negative.

### Method 2 – Apply a Formula to Calculate the Effective Interest Rate in Excel

Here:

**i**= the rate of interest.**n**= number of the compounding periods.

#### Case 2.1 Use the Formula of Compound Interest

**Steps:**

- Use this formula.

- The formula becomes:

`=(1+C4/C5)^C5 - 1`

**Press Enter.**

#### Case 2.2 – Use the EFFECT Function

**Steps:**

- In cell
**C8**, use the formula of**the EFFECT function**:

`=EFFECT(C4, C5)`

- Press
**Enter**to get the**Effective Interest Rate**.

### Method 3 – Convert Effective Interest to Nominal Interest

You can get back to the **Nominal/Simple Interest Rate** from the **Effective Interest Rate**.

**Steps:**

- Enter the following formula of the
**NOMINAL function**.

`=NOMINAL(C7, C5)`

- Press
**Enter**to get the**Nominal Interest Rate**.

**Read More:** Nominal vs Effective Interest Rate in Excel

**Download the Practice Workbook**

## How to Calculate Interest Rate in Excel: Knowledge Hub

- How to Use Nominal Interest Rate Formula in Excel
- How to Calculate Weighted Average Interest Rate in Excel
- How to Calculate Periodic Interest Rate in Excel
- How to Convert Monthly Interest Rate to Annual in Excel
- How to Calculate APR in Excel
- How to Calculate Interest Rate from EMI in Excel
- How to Create Effective Interest Method of Amortization in Excel

**<< Go Back to ****Excel for Finance**** | ****Learn Excel**