This is an overview.

### Method 1 – Calculating the Periodic Interest Rate When the Annual Interest Rate Is Given

- To find the weekly periodic interest, use the formula in
**F9**:

**=APR/52**(you need to use the

**Name Manager**)

### Method 2 – Calculating the Monthly Interest Rate

Consider a loan of **$10,000** for **3** years. The monthly payment is **$333**.

**Loan,** **pv** =** $10,000
Total no. of periods for payments,**

**nper**=

**3 years x 12**=

**36**

Periodic payment,

Periodic payment

**pmt**=

**-$333**

- Find the monthly interest rate in
**C7**using the above information:

- Enter the following formula:

**=RATE(C5, C6, C4)**

**C5, C6**, and **C4 **refer to the **Total Periods of Payments (nper)**, **Periodic Payment (pmt)**, **Loan (pv)**.

- Press
**ENTER**.

The monthly interest rate is **1.015%**.

**Read More:** How to Calculate Effective Interest Rate in Excel with Formula

### Method 3 – Finding the Annual Interest Rate

Consider:

Loan, **pv** =** $10,000
**Total no. of periods for payments,

**nper**=

**3 years x 12**=

**36**

Periodic payment,

**pmt**=

**-$333**

- Enter the formula in
**C9**.

**=RATE(C5,C6,C4)*12**

- Press
**ENTER**.

The annual interest rate is **12.179%**.

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

### Method 4 – Using the RATE Function Without the PMT Value

Consider:

**Loan, pv** = **10,000
**

**Total Periods of Payments, nper**=

**36**

**Future Value, fv**=

**14,500**

- Enter the formula in
**C7**.

**=RATE(C5,,C4,C6)
**

**C5, C4** and **C6 **refer to the **Total Periods of Payments (nper), Loan (pv)** and **Future Value (fv)**.

Use a **double comma** after inserting the **Total Periods of Payments (nper).**

- Press
**ENTER**.

The **Monthly Interest Rate** is **037%**.

**Note:** In the **pmt**, use a negative number as the value is outgoing cash.

**Read More:** How to Use Nominal Interest Rate Formula in Excel

### Method 5 – Calculating the Periodic Interest Rate When the Interest Is Compounded Semi-Annually

Consider:

**r** = Interest rate for per payment period

**i** = Annual Interest Rate (%)

**n** = Number of Compounding Periods Per Year

**p** = Number of Payments Per Year

If the **APR (annual interest rate)** is **12%**, interest rate **(i)** is compounded semi-annually **(n = 2)**, and you have to pay monthly, you need to calculate the **Periodic Interest Rate** using an arithmetic formula.

The general formula to calculate the periodic interest rate is:

**r=(1+(i/n))^(n/p)-1**

- Enter the formula in
**C7**:

**=(1+D4/D5)^(D5/D6)-1
**i.e.

**r = (1 + 12%/2)^(2/12)-1 = (1+6%)^(1/6) – 1 = 0.97588%**

**D4,D5 **and **D6 **refer to the **Annual Interest Rate (i)**, **Number of Compounding Periods Per Year (n)** and **Number of Payments Per Year (p)**.

- Press
**ENTER**.

The output is **976%**.

## How to Find the Interest Rate on a Saving Account

You can save **$1,20,000** in** 7** years if you pay **$1,800** monthly with no prior investment.

The variables are:

**Total Number of Payments, Nper** in **D4**: **7*12
**

**Monthly Payment**,

**pmt**in

**D5**:

**-1,800**

**Future Value of Investment, Fv**in

**D6**:

**$1,20,000**

Calculate the **Monthly Interest Rate** in **D7 **and **Annual Interest Rate** in **D8**.

- Enter the formula in
**D7**.

**=-RATE(D4*12,D5, ,D6)**

- Press
**ENTER**.

The** Monthly Interest Rate** is **579%**.

- Enter the following formula in
**D8**.

**=-RATE(D4*12,D5, ,D6)*12**

- Press
**ENTER**.

The output is **7%**.

**Download Practice Workbook**

## Related Articles

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