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