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.
Table of Contents
- 1) Calculate periodic interest rate when the interest rate is given
- 2) Periodic Interest Rate using Excel’s RATE Function
- 3) Periodic Interest Rate using Excel’s RATE Function without PMT value
- 4) Your payment is monthly but interest is compounded Semi-annually. What will be your periodic interest rate?
- Download Working File
- Related Articles
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 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)
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!
☕
