How to Calculate Periodic Interest Rate in Excel (5 Easy Ways)

If you are looking for how to calculate Periodic Interest Rate in Excel, then you are in the right place. In the case of loans or payments, we need to calculate interest rates monthly, annually, or according to our requirements. In this article we’ll try to discuss how to calculate periodic interest rates in Excel.


Download Practice Workbook


5 Ways to Calculate Periodic Interest Rate in Excel

Excel offers different ways to calculate interest rates based on different times. We’ll discuss 5 methods to calculate the periodic interest rate. Here, we are giving a summary of our periodic interest rates.

how to calculate periodic interest rate in excel

how to calculate periodic interest rate in excel

how to calculate periodic interest rate in excel


1. Calculating Periodic Interest Rate When Annual 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.
In case of finding weekly periodic interest the formula for F9 cell will be like the following-
=APR/52
Additionally, one thing to mention is that you need to use the Name Manager to give input of APR.

Calculating Periodic Interest Rate When Annual Interest Rate Is Given


2. Calculating Monthly Interest Rate

Besides you can calculate the monthly interest rate by using the RATE function.
Say, you took a loan of $10,000 for 3 years. Your monthly payment is $333. What is your Monthly Periodic Interest Rate or Yearly Interest Rate?
Gradually, we’ll first calculate the Monthly Interest Rate here and in the next method we’ll calculate the Annual Interest Rate.
Importantly,
Loan, pv = $10,000
Total no. of periods for payments,
nper = 3 years x 12 = 36
Periodic payment
, pmt = -$333
You need to find the monthly interest rate in the C7 cell using the above information.

Calculating Monthly Interest Rate

Eventually, you need to write the following formula in the C7 cell like this.
=RATE(C5, C6, C4)
Here, C5, C6, and C4 refer to the Total Periods of Payments (nper), Periodic Payment (pmt), Loan (pv).

how to calculate periodic interest rate in excel

  • Lastly, if you press ENTER, you’ll get the output as 1.015%.
    So, the monthly interest rate is 1.015%.


3. Finding Annual Interest Rate

You can use the same RATE function to calculate the annual interest rate. The procedure is the same, but there is a little addition to the formula.
Similarly, as before,
Loan, pv = $10,000
Total no. of periods for payments, nper = 3 years x 12 = 36
Periodic payment, pmt = -$333

Finding Annual Interest Rate

Eventually, write the formula in the C9 cell like this.
=RATE(C5,C6,C4)*12

how to calculate periodic interest rate in excel

  • Finally, press ENTER to get the output as 12.179%.
    So, the annual interest rate is 12.179%.


4. Using RATE Function Without PMT Value

In the previous methods, while using the RATE function, we have used the PMT value. You can also calculate the  periodic interest rate without using the PMT value. In this case, we’ll use a Future Value.
Suppose,
Loan, pv = 10,000
Total Periods of Payments, nper = 36
Future Value, fv = 14,500
Eventually, you need to calculate the Monthly Interest Rate in the C7 cell.

Using RATE Function without PMT Value

  • Firstly, write the formula in the C7 cell like this.

=RATE(C5,,C4,C6)
Here, C5, C4 and C6 refers to the Total Periods of Payments (nper), Loan (pv) and Future Value (fv) respectively.
You need to use a double comma after inserting the Total Periods of Payments (nper).

how to calculate periodic interest rate in excel

  • Secondly, press ENTER and you’ll get the Monthly Interest Rate as 037%.

Note: While using the pmt, we have used a negative number as the value because it’s outgoing cash.


5. Calculating Periodic Interest Rate When Interest Is Compounded Semi-Annually

In Excel, you can calculate the periodic interest rate when the payment is monthly but interest is compounded semi-annually. You just need to use an arithmetic formula to do this.
Importantly,
r = Interest rate for per payment period
i = Annual Interest Rate (%)
n = Number of Compounding Periods Per Year
p = Number of Payments Per Year
Additionally, if APR (annual interest rate) is 12%, interest rate (i) is compounded semi-annually (n = 2), but you have to pay monthly, then you need to calculate the Periodic Interest Rate using an arithmetic formula.
Eventually, you need to find a Periodic Interest Rate in the D7 cell.

Calculating Periodic Interest Rate When Interest Is Compounded Semi-Annually

The general formula for finding the periodic interest rate is.
r=(1+(i/n))^(n/p)-1

  • So, firstly, you can write the following general formula for finding periodic interest in the C7 cell like this.

=(1+D4/D5)^(D5/D6)-1
i.e.
r = (1 + 12%/2)^(2/12)-1 = (1+6%)^(1/6) – 1 = 0.97588%
Here, D4,D5 and D6 cells refer to the Annual Interest Rate (i), Number of Compounding Periods Per Year (n) and Number of Payments Per Year (p) respectively.

how to calculate periodic interest rate in excel

  • Finally, press ENTER to get the output as 976%.


How to Find Interest Rate on Saving Account

The three main factors used to determine the interest rate in the cases above were the Loan Term, Payment Amount each Period, and Loan Amount.
Finding an interest rate for a sequence of periodic cash flows where we know the Future Value rather than the Present Value is another typical example.
Let’s use $1,20,000 as an example, and assume you can save $1,20,000 in 7 years if you pay $1,800 at the end of each month with no prior investment.
We define the following variables to make it happen:
Total Number of Payments, Nper in D4: 7*12
Monthly Payment, pmt in D5: -1,800
Desired Future Value of Investment, Fv in D6: $1,20,000
You need to calculate the Monthly Interest Rate in the D7 cell and Annual Interest Rate in the D8 cell.

How to Find Interest Rate on Saving Account

The formula in D7 to determine the monthly interest rate is.

=-RATE(D4*12,D5, ,D6)

how to calculate periodic interest rate in excel

  • Eventually, press ENTER to get the Monthly Interest Rate as 579%.

Similarly, you need to write the following formula in the D8 cell to calculate the Annual Interest Rate.
=-RATE(D4*12,D5, ,D6)*12

how to calculate periodic interest rate in excel

  • Again, press ENTER and get the output as 7%.


Conclusion

That’s all about today’s session. And these are the ways to calculate the periodic interest rate in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website Exceldemy, a one-stop Excel solution provider.


Related Articles

Kawser

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