This is an overview.
Method 1 – Calculating the Periodic Interest Rate When the Annual Interest Rate Is Given
- To find the weekly periodic interest, use the formula in F9:
=APR/52
(you need to use the Name Manager)
Method 2 – Calculating the Monthly Interest Rate
Consider a loan of $10,000 for 3 years. The monthly payment is $333.
Loan, pv = $10,000
Total no. of periods for payments, nper = 3 years x 12 = 36
Periodic payment, pmt = -$333
- Find the monthly interest rate in C7 using the above information:
- Enter the following formula:
=RATE(C5, C6, C4)
C5, C6, and C4 refer to the Total Periods of Payments (nper), Periodic Payment (pmt), Loan (pv).
- Press ENTER.
The monthly interest rate is 1.015%.
Read More: How to Calculate Effective Interest Rate in Excel with Formula
Method 3 – Finding the Annual Interest Rate
Consider:
Loan, pv = $10,000
Total no. of periods for payments, nper = 3 years x 12 = 36
Periodic payment, pmt = -$333
- Enter the formula in C9.
=RATE(C5,C6,C4)*12
- Press ENTER.
The annual interest rate is 12.179%.
Read More: Nominal vs Effective Interest Rate in Excel
Method 4 – Using the RATE Function Without the PMT Value
Consider:
Loan, pv = 10,000
Total Periods of Payments, nper = 36
Future Value, fv = 14,500
- Enter the formula in C7.
=RATE(C5,,C4,C6)
C5, C4 and C6 refer to the Total Periods of Payments (nper), Loan (pv) and Future Value (fv).
Use a double comma after inserting the Total Periods of Payments (nper).
- Press ENTER.
The Monthly Interest Rate is 037%.
Note: In the pmt, use a negative number as the value is outgoing cash.
Read More: How to Use Nominal Interest Rate Formula in Excel
Method 5 – Calculating the Periodic Interest Rate When the Interest Is Compounded Semi-Annually
Consider:
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 the APR (annual interest rate) is 12%, interest rate (i) is compounded semi-annually (n = 2), and you have to pay monthly, you need to calculate the Periodic Interest Rate using an arithmetic formula.
The general formula to calculate the periodic interest rate is:
r=(1+(i/n))^(n/p)-1
- Enter the formula in C7:
=(1+D4/D5)^(D5/D6)-1
i.e.
r = (1 + 12%/2)^(2/12)-1 = (1+6%)^(1/6) – 1 = 0.97588%
D4,D5 and D6 refer to the Annual Interest Rate (i), Number of Compounding Periods Per Year (n) and Number of Payments Per Year (p).
- Press ENTER.
The output is 976%.
How to Find the Interest Rate on a Saving Account
You can save $1,20,000 in 7 years if you pay $1,800 monthly with no prior investment.
The variables are:
Total Number of Payments, Nper in D4: 7*12
Monthly Payment, pmt in D5: -1,800
Future Value of Investment, Fv in D6: $1,20,000
Calculate the Monthly Interest Rate in D7 and Annual Interest Rate in D8.
- Enter the formula in D7.
=-RATE(D4*12,D5, ,D6)
- Press ENTER.
The Monthly Interest Rate is 579%.
- Enter the following formula in D8.
=-RATE(D4*12,D5, ,D6)*12
- Press ENTER.
The output is 7%.
Download Practice Workbook
Related Articles
<< Go Back to How to Calculate Interest Rate in Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!