If you are looking for **how to calculate Periodic Interest Rate in Excel**, then you are in the right place. In the case of loans or payments, we need to calculate interest rates monthly, annually, or according to our requirements. In this article we’ll try to discuss how to calculate periodic interest rates in Excel.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Calculate Periodic Interest Rate in Excel

Excel offers different ways to calculate interest rates based on different times. We’ll discuss 5 methods to calculate the periodic interest rate. Here, we are giving a summary of our periodic interest rates.

### 1. Calculating Periodic Interest Rate When Annual Interest Rate Is Given

You can pay the repayments of a loan weekly, bi-weekly, semi-monthly, monthly, bi-monthly, quarterly, semi-annually, or yearly.

From the following table, you can find the **Periodic Interest Rate** from the** Yearly Interest Rate** by dividing the **Yearly Interest Rate** by the **Total No. of Periods / Year**.

In case of finding weekly periodic interest the formula for **F9 **cell will be like the following-**=APR/52**Additionally, one thing to mention is that you need to use the

**Name Manager**to give input of

**APR**.

### 2. Calculating Monthly Interest Rate

Besides you can calculate the monthly interest rate by using **the RATE function**.

Say, you took a loan of **$10,000** for **3** years. Your monthly payment is **$333**. What is your **Monthly Periodic Interest Rate** or **Yearly Interest Rate**?

Gradually, we’ll first calculate the** Monthly Interest Rate** here and in the next method we’ll calculate the **Annual Interest Rate**.

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

**nper**=

**3 years x 12**=

**36**

Periodic payment,

Periodic payment

**pmt**=

**-$333**

You need to find the monthly interest rate in the

**C7**cell using the above information.

Eventually, you need to write the following formula in the** C7** cell like this.**=RATE(C5, C6, C4)**Here,

**C5, C6**, and

**C4**refer to the

**Total Periods of Payments (nper)**,

**Periodic Payment (pmt)**,

**Loan (pv)**.

- Lastly, if you press
**ENTER**, you’ll get the output as**1.015%**.

So, the monthly interest rate is**1.015%**.

### 3. Finding Annual Interest Rate

You can use the same **RATE function** to calculate the annual interest rate. The procedure is the same, but there is a little addition to the formula.

Similarly, as before,

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

**nper**=

**3 years x 12**=

**36**

Periodic payment,

**pmt**=

**-$333**

Eventually, write the formula in the **C9 **cell like this.**=RATE(C5,C6,C4)*12**

- Finally, press
**ENTER**to get the output as**12.179%**.

So, the annual interest rate is**12.179%**.

### 4. Using RATE Function Without PMT Value

In the previous methods, while using **the RATE function**, we have used the **PMT **value. You can also calculate the periodic interest rate without using the** PMT** value. In this case, we’ll use a **Future Value**.

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

**Total Periods of Payments, nper**=

**36**

**Future Value, fv**=

**14,500**

Eventually, you need to calculate the

**Monthly Interest Rate**in the

**C7**cell.

- Firstly, write the formula in the
**C7**cell like this.

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

**C5, C4**and

**C6**refers to the

**Total Periods of Payments (nper), Loan (pv)**and

**Future Value (fv)**respectively.

You need to use a

**double comma**after inserting the

**Total Periods of Payments (nper).**

- Secondly, press
**ENTER**and you’ll get the**Monthly Interest Rate**as**037%**.

**Note:** While using the **pmt**, we have used a negative number as the value because it’s outgoing cash.

### 5. Calculating Periodic Interest Rate When Interest Is Compounded Semi-Annually

In Excel, you can calculate the periodic interest rate when the payment is monthly but interest is compounded semi-annually. You just need to use an arithmetic formula to do this.

Importantly,**r** = Interest rate for per payment period**i** = Annual Interest Rate (%)**n** = Number of Compounding Periods Per Year**p** = Number of Payments Per Year

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

Eventually, you need to find a **Periodic Interest Rate** in the **D7 **cell.

The general formula for finding the periodic interest rate is.**r=(1+(i/n))^(n/p)-1**

- So, firstly, you can write the following general formula for finding periodic interest in the
**C7**cell like this.

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

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

Here,

**D4,D5**and

**D6**cells refer to the

**Annual Interest Rate (i)**,

**Number of Compounding Periods Per Year (n)**and

**Number of Payments Per Year (p)**respectively.

- Finally, press
**ENTER**to get the output as**976%**.

## How to Find Interest Rate on Saving Account

The three main factors used to determine the interest rate in the cases above were the **Loan Term, Payment Amount each Period,** and **Loan Amount**.

Finding an interest rate for a sequence of periodic cash flows where we know the** Future Value** rather than the** Present Value** is another typical example.

Let’s use **$1,20,000** as an example, and assume you can save **$1,20,000** in** 7** years if you pay **$1,800** at the end of each month with no prior investment.

We define the following variables to make it happen:**Total Number of Payments, Nper** in **D4**: **7*12**

**Monthly Payment**,

**pmt**in

**D5**:

**-1,800**

Desired

**Future Value of Investment, Fv**in

**D6**:

**$1,20,000**

You need to calculate the

**Monthly Interest Rate**in the

**D7**cell and

**Annual Interest Rate**in the

**D8**cell.

The formula in **D7 **to determine the monthly interest rate is.

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

- Eventually, press
**ENTER**to get the**Monthly Interest Rate**as**579%**.

Similarly, you need to write the following formula in the **D8 **cell to calculate the **Annual Interest Rate**.**=-RATE(D4*12,D5, ,D6)*12**

- Again, press
**ENTER**and get the output as**7%**.

## Conclusion

That’s all about today’s session. And these are the ways to calculate the periodic interest rate in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website **Exceldemy**, a one-stop Excel solution provider.