How to Calculate the Periodic Interest Rate in Excel

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

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

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.

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