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,

=RATE(nper, pmt, pv, [fv], [type], [guess])

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.

Dataset of how to calculate monthly interest rates in Excel.

method

1. Implement RATE Function to Calculate Monthly Interest Rate on a Loan in Excel

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.
=RATE(D6,-D7,D8)
  • 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%.

Use of the RATE function to calculate monthly interest rates in Excel.

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

Use of RATE functions to calculate annual interest rates.

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

method

2. Find Monthly Interest Rate from Annual Interest Rate

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.

calculate the monthly interest rate from the annual interest rate in Excel.

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

method

3. Calculate Monthly Simple Interest Rate Using Formula 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.

Simple annual interest rate formula.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select blank a cell i.e. D12.
  • Then, write the following code in D12 and get the Annual interest rate.
=((D6/D7)-1)/D10
  • Therefore, we achieve the Interest Rate of an Annuity based on the selected cell references.

Calculation of annual interest rate using the simple interest rate formula.

  • 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

method

4.  Calculate Compound Interest Rate Monthly Using Excel Template

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.

Compound monthly interest rate formula to calculate in Excel.

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.
=((D6/D7)^(1/(D8*D10))-1)*D8
  • Therefore, we achieve an interest rate of 10.26% in a month based on the selected cell references.

Calculate the monthly interest rate using the compound interest rate formula in Excel.

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

method

5. Apply VBA Macro

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.

Enabling VBA Macro.

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

Getting the Developer menu at the Top Ribbon.

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

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

🔨 Code breakdown

👉  Firstly, A, P, t and i denote the Final amount, Principal Amount, Number of Years, and Annual Interest Rate.

👉  Secondly, A, P, and t are the integer. On the other hand, i is the single which allows decimals.

👉  Thirdly, the use of InputBox creates the question box and asks for input.

👉  Fourthly, the calculation of the Annual Interest Rate takes place and types the result is in cell D11 once we insert all the data according to the questions.

👉  Finally, the Monthly Interest Rate calculation takes place in the D12 cell, dividing the Annual interest rate by 12.

VBA Macro code to calculate the annual and monthly interest rate in Excel.

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

Enter the final amount.

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

Enter the principal amount.

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

Enter the number of years.

  • 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.
=D7/D8
  • Therefore, we obtain the Monthly Interest Rate in the D15 cell.

Calculate the monthly interest rate in Excel from the yearly rate.

  • Similarly, use the PPMT function in D16 that calculates the Principal amount.
=PPMT(D15,D9,D11,-D6,D12,D13)
  • 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.

PPMT function to measure the Principal amount of a loan.

  • Again, use the IPMT function in D17 that calculates the Interest amount.
=IPMT(D15,D9,D11,-D6,D12,D13)
  • 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.

IPMT function to measure the Interest amount of a loan.


📄 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

(Visited 33 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo