In this short article, I will show how to calculate periodic interest rates in Excel. I will show 4 methods of calculating the periodic interest rate.

**Table of Contents**hide

## 1) Calculate periodic interest rate when the 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.

Interest Compounded |
Calculated After |
No. of Periods / Year |
Periodic Interest Rate |

Weekly |
7 days |
52 |
=APR/52 |

Bi-weekly |
14 days |
26 |
=APR/26 |

Semi-monthly |
15 days |
24 |
=APR/24 |

Monthly |
30 days |
12 |
=APR/12 |

Bi-monthly |
2 months |
6 |
=APR/6 |

Quarterly |
3 months |
4 |
=APR/4 |

Semi-annually |
6 months |
2 |
=APR/2 |

Yearly |
12 months |
1 |
=APR/1 |

## 2) Periodic Interest Rate using Excelâ€™s RATE Function

**Syntax of Excelâ€™s RATE Function: =RATE(nper, pmt, pv, [fv], [type], [guess])**

Say, you took a loan of $10,000 for 3 years. Your monthly payment is $332.14. What is your monthly periodic interest rate or yearly interest rate?

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

Total no. of periods for payments, **nper** = 3 years x 12 = 36

Periodic payment, **pmt** = -$332.14

**Rate (Periodic Rate) = RATE(36, -332.14, 10000) **= 1%

So, the monthly interest rate is 1%.

Then, the yearly interest rate is 1% x 12 = 12%

## 3) Periodic Interest Rate using Excelâ€™s RATE Function without PMT value

This time, the monthly payment is not given. But the future value of your loans is given.

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

Total periods of payments, **nper** = 36

Future value, **fv** = $14,307.55

Rate (Periodic Rate) **= RATE(36, , 10000, 14307.55)** **= 1%**

As the **pmt** value is not given, we have kept a blank space for this argument in the **RATE** formula.

## 4) Your payment is monthly but interest is compounded Semi-annually. What will be your periodic interest rate?

You have to use this formula: **r = (1 + i/n)^(n/p) â€“ 1**

Here,

**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 **APR (annual interest rate)** is 12%, interest rate **(i)** is compounded semi-annually **(n = 2)**, but you have to pay monthly, then your periodic interest rate,

**r = (1 + 12%/2)^(2/12)-1 = (1+6%)^(1/6) â€“ 1 = 0.97588%**

This is a general formula for finding the periodic interest rate.

## Download Working File

## Related Articles

- How to Use Compound Interest Formula in Excel
- Nominal Interest Rate vs. Effective Interest Rate Excel Formulas
- How to calculate IRR (internal rate of return) in Excel (9 easy ways)
- How to calculate monthly payment in Excel (with Excel calculator)
- Nominal Interest Rate Formula Excel (calculate from Effective Rate)
- Compound interest excel formula with regular deposits