How to Calculate Monthly Interest Rate in Excel (3 Simple Methods)

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.

Calculate Monthly Interest Rate in Excel


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)

Use Excel RATE Function to Calculate Monthly Interest Rate

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)

Use Excel RATE Function to Calculate Monthly Interest Rate

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.

Compute Monthly Interest Rate from Annual Percentage Rate in Excel

STEPS:

  • Firstly, select cell C6. Here, type the formula:
=C5/12

Compute Monthly Interest Rate from Annual Percentage Rate in Excel

  • 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


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.

Apply Excel EFFECT Function for Calculating Monthly Interest Rate

STEPS:

  • At first, select cell C7 and type the formula:
=EFFECT(C5,C6)

Apply Excel EFFECT Function for Calculating Monthly Interest Rate

  • Next, press Enter. Hence, it’ll return the effective interest rate which is per annum.

Apply Excel EFFECT Function for Calculating Monthly Interest Rate

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


Further Readings

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo