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.
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.
How to Calculate Monthly Interest Rate in Excel: 3 Simple Methods
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.
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.
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 Calculate Interest Rate from EMI in Excel
Download Practice Workbook
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.
