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.
How to Calculate Periodic Interest Rate in Excel: 5 Easy Ways
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.
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.
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.
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).
- Lastly, if you press ENTER, you’ll get the output as 1.015%.
So, the monthly interest rate is 1.015%.
Read More: How to Calculate Effective Interest Rate in Excel with Formula
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
Eventually, write the formula in the C9 cell like this.
=RATE(C5,C6,C4)*12
- Finally, press ENTER to get the output as 12.179%.
So, the annual interest rate is 12.179%.
Read More:Â Nominal vs Effective Interest Rate in Excel
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.
- 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).
- 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.
Read More: How to Use Nominal Interest Rate Formula in Excel
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.
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.
- 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.
The formula in D7 to determine the monthly interest rate is.
=-RATE(D4*12,D5, ,D6)
- 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
- Again, press ENTER and get the output as 7%.
Download Practice Workbook
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.
Related Articles
<< Go Back to How to Calculate Interest Rate in Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
