5 Examples to Calculate Monthly Interest Rate in Excel

The inability to calculate monthly interest rates can be a calamity in today’s modern world. Some of us seek loans while most of us deposit at the bank to burgeon assets. At the same time, Bank officials often belie the loan scheme as well as the deposit scheme; Hence there are many hidden charges as well as calculations. However, we need a blatant concept of how the monthly interest rate is calculated. We are going to expound you to calculate the monthly interest rate in Excel. Excel is one of the dedicated software that can be helpful to calculate multiple data of monthly interest rates altogether.

📁 Download Excel File

To practice please download the Excel file from here:

Overview of RATE Function in Excel

The RATE function is a Financial function. The RATE function always returns the interest rate per period of an annuity. Before that, we need to construe the Syntax of a RATE function as,

Description of the following arguments:

• Nper: Essential. The number of periods in an annuity.
• Pmt: Essential. Payment in each period.
• Pv: Essential. Present value of Asset.
• Fv: Optional. Future value. It is considered 0 when fv is omitted.
• Type: Optional. It remarks due or undue by 0 or 1.

Learn to Calculate Monthly Interest Rate in Excel with These 5 Examples

In this article, we will construe 5 catchy examples to calculate the monthly interest rate in Excel. Following that, we consider a dataset named Interest Rate of Michigan State Bank consisting of Description, Argument, and Value. There are also 4 columns and 10 rows. We will learn the calculation for Simple Interest Rate, Compound Interest Rate, the use of VBA macro, and the application of RATE, PPMT, and IPMT functions. So, Let’s get started.

Using the RATE function we calculate the monthly interest rate within a moment. Please check out the following steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell D12 and insert the following formula including the RATE function.

• Then RATE function automatically calculates taking the based on the data of D6, D7, and D8 which is the No. of Period, Monthly Payment, and Loan Amount.
• Therefore, the Monthly Interest Rate is showing up in cell E12 which is 0.62%.

• Further, by multiplying the D12 cell by 12, we obtain the Annual Interest Rate which is 7.42%.

📕 Read More: 2 Approaches to Calculate Interest Between Two Dates in Excel

The simplest method is to get the interest data of the annuity and measure the monthly interest rate by dividing the Annual Interest Rate by 12. Please check out the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select a cell i.e. D6 to get the monthly interest rate.
• Secondly, divide the Annual Interest Rate by 12 in the D6 cell.
• Therefore, we obtain the Monthly Interest Rate from the annual interest rate directly.

📕 Read More: 3 Ways to Calculate Accrued Interest on Fixed Deposit in Excel

Are you tired of calculating the simple interest rate in the calculator? Calculating the data one by one is quite boring. Excel made it easier. By creating a formula one may calculate bulk data within a second.

But first, check out the Simple interest rate formula and the steps to calculate below.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select blank a cell i.e. D12.
• Then, write the following code in D12 and get the Annual interest rate.

• Therefore, we achieve the Interest Rate of an Annuity based on the selected cell references.

• Finally, by dividing the Annual Interest Rate by 12 months we further get the Monthly Interest Rate of 1.11%.

📕 Read More: Create and Download Late Payment Interest Calculator in Excel

Measuring monthly compound interest rates for bulk quantity data is hectic as well as tiresome. Creating a formula in Excel and auto-filling of cells can be a lifesaver move.

However, we need to learn the Compound interest rate formula first.

Please check out the steps to calculate the compound interest rate.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select blank a cell i.e. D12.
• Then, write the following code in D12 and get the Monthly interest rate.

• Therefore, we achieve an interest rate of 10.26% in a month based on the selected cell references.

📕 Read More: 4 Approaches to Calculate Home Loan Interest in Excel

One can use Excel as a calculator just by putting values in a question box with the help of a VBA code. By getting the Developer option in the Top Ribbon, you can generate a program on your own.  Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Check first if your Developer option is available or not.
• Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

• Thirdly, click on Developer and click on the OK button.

• Now Developer mode is on.
• Then, click on the Visual Basic option.
• Next, click on Module from the Insert menu to insert the VBA code.

• Now, write the following code in the module and hit on the Run icon to get the monthly interest rate.

Code

Sub Monthly_Interest_Rate()
Dim A As Integer
Dim P As Integer
Dim t As Integer
Dim i As Single
A = InputBox(Prompt:="Final Amount")
P = InputBox(Prompt:="Principal AMount")
t = InputBox(Prompt:="Number of years")
i = ((A / P) - 1) / t
Range("D11").Value = i
Range("D12").Value = i / 12
End Sub

• Thus a box appears asking Final Amount. Please write down the amount of 25000 and hit the OK button.

• Again a box appears asking Principal Amount. Please write down the amount of 15000 and hit the OK button.

• Further, you will be asked the Number of years. Typing the 5 years till maturity, hit the OK button.

• Therefore results of Annual and Monthly Interest Rates take place in the D11 and D12 cells respectively 13.33% and 1.11%.

How to Calculate Principal and Monthly Interest on a Loan in Excel

Using PPMT and IPMT functions one can calculate the principal and monthly interest amounts on a loan within a minute. However, you can also use the Excel file as a template to calculate on your own. Please check the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select blank a cell i.e. D15.
• Then divide the Yearly Interest Rate by the Number of Periods per month(12) to get the monthly interest rate.

• Therefore, we obtain the Monthly Interest Rate in the D15 cell.

• Similarly, use the PPMT function in D16 that calculates the Principal amount.

• Here, the syntax of the PPMT function, PMT(rate, per, nper, pv, [fv], [type]).
• According to the syntax, selecting the cells, we finally achieve the Principal Amount of $1257.58 in the D16 cell.

• Again, use the IPMT function in D17 that calculates the Interest amount.

• Here, the syntax of PPMT function, IPMT(rate, per, nper, pv, [fv], [type]).
• Therefore, electing the cells according to the syntax, we finally achieve the Interest Amount of $916.67 in the D17 cell.

📄 Important Notes

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  Watch out for the formulas carefully both the Simple interest rate and Compound interest rate formulas.

🖊️  Refer cell carefully while using RATE, PPMT, and IPMT functions.

📝 Takeaways from This Article

📌  RATE function to calculate the interest rate automatically by only selecting cells.

📌  Excel formula to calculate the Simple and Compound interest rate methods.

📌  Interest rate calculation from a loan.

📌  VBA macro to use as a calculator.

📌  PPMT and IPMT function to calculate the Principal amount and Interest amount from a loan respectively.

Conclusion

In this article, we learn to calculate the monthly interest rate in Excel. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.

Related Articles

• 3 Quick Steps to Calculate Credit Card Interest in Excel
• 2 Easy Ways to Calculate Gold Loan Interest in Excel
• 6 Steps to Create Car Loan Calculator in Excel Sheet