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

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 rates 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 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

  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)
  6. Compound interest excel formula with regular deposits

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo