How to calculate periodic interest rate in Excel (4 ways)

Last updated on October 11th, 2018

How to calculate periodic interest rate in Excel

Calculating Periodic Interest Rate in Excel when payment periods and compounding periods are different.

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

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 yearly interest rate by dividing the yearly interest rate by total No. of Periods / Year.

Interest CompoundedCalculated AfterNo. of Periods / YearPeriodic Interest Rate
Weekly7 days52=APR/52
Bi-weekly14 days26=APR/26
Semi-monthly15 days24=APR/24
Monthly30 days12=APR/12
Bi-monthly2 months6=APR/6
Quarterly3 months4=APR/4
Semi-annually6 months2=APR/2
Yearly12 months1=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

  1. How to Use Compound Interest Formula in Excel
  2. Nominal Interest Rate vs. Effective Interest Rate Excel Formulas
  3. How to calculate IRR (internal rate of return) in Excel (9 easy ways)
  4. How to calculate monthly payment in Excel (with Excel calculator)
  5. Nominal Interest Rate Formula Excel (calculate from Effective Rate)

Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply