Aung Shine
We often pay interest on a loan or earn interest on a saving account. This article will show you some simple processes to Calculate the Monthly Interest Rate in Excel.
To illustrate, I’m going to use a sample dataset as an example. The below dataset represents the monthly payment amount to pay for a loan amount in a certain number of periods.
Download Practice Workbook
In order to practice by yourself, download the following workbook.
What is the Monthly Interest Rate?
In brief, the amount that is charged monthly on top of the principal by a lender to a borrower is called Monthly Interest Rate. Moreover, the interest rate is also applicable to the amount earned at a bank from a savings account.
3 Simple Ways to Calculate Monthly Interest Rate in Excel
1. Use Excel RATE Function to Calculate Monthly Interest Rate
Excel provides various Functions, Features, etc. to perform many operations. Here, in our first method, we’ll use the Excel RATE function to compute the Monthly Interest Rate. The RATE function is used to return the interest rate per period of a loan or an investment.
1.1 Interest Rate on a Loan
We usually pay our installment loans monthly. So, it’s useful for us to know the monthly interest rate. First, we’ll show how to calculate the monthly interest rate on a loan.
STEPS:
- In the beginning, select cell D8 and type the formula:
=RATE(D5,-D6,D7)
NOTE: Here, the Minus sign before D6 indicates the outgoing cash.
- Next, press Enter. Subsequently, it’ll return the result.
1.2 Interest Rate on a Saving Account
Additionally, we can also calculate the monthly interest rate on a Savings Account. A lot of times, we monthly save a certain portion of our earnings in a bank account and we fix a definite amount to reach. The interest rate is also present there and in this case, we don’t have present value but we do have a future value. In order to compute the interest rate on a savings account, follow the steps below.
STEPS:
- First, select cell D8 and type the formula:
=RATE(D5,-D6,0,D7)
NOTE: Here, the Minus sign before D6 indicates the outgoing cash and, 0 is placed in the formula as there is no present value.
- After that, press Enter. And so, the precise result will appear.
Read more: How to Calculate Interest Rate in Excel
2. Compute Monthly Interest Rate from Annual Percentage Rate in Excel
Again, we can get the monthly interest rate from the Annual Percentage Rate by doing a simple calculation. Most banking institutions use an annual percentage rate rather than the monthly rate. But, we need to know the monthly interest rate for our own benefit.
STEPS:
- Firstly, select cell C6. Here, type the formula:
=C5/12
- Now, press Enter. And consequently, the rate will appear in cell C6.
Read more: How to Calculate Interest Rate on a Loan in Excel
Similar Readings
- How to Calculate Gold Loan Interest in Excel (2 Ways)
- Create Late Payment Interest Calculator in Excel and Download for Free
- Calculate Interest in Excel with Payments (3 Examples)
- How to Calculate Principal and Interest on a Loan in Excel
3. Apply Excel EFFECT Function for Calculating Monthly Interest Rate
Furthermore, we can apply the Excel EFFECT function in the case of calculating the Effective Interest Rate where the monthly compounding is present. Effective interest rate is also known as Annual Percentage Yield (APY), which is higher than the nominal interest rate, also known as Annual Percentage Rate (APR). Here, we’ll use the Nominal Interest Rate which is based on a simple interest to get our desired result. The EFFECT function returns the effective annual interest rate.
STEPS:
- At first, select cell C7 and type the formula:
=EFFECT(C5,C6)
- Next, press Enter. Hence, it’ll return the effective interest rate which is per annum.
- Now, select cell C8. Here, type the formula:
=C7/12
- Finally, press Enter. Therefore, you’ll get your required monthly interest rate.
Read more: How to Find Interest Rate in Future Value Annuity
Conclusion
Now you will be able to Calculate the Monthly Interest Rate in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
