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

This article will demonstrate some simple processes to calculate the Monthly Interest Rate in Excel. To illustrate our methods, we’ll use the following sample dataset, representing the monthly interest on a loan amount (or principal) over a certain number of periods.

What is the Monthly Interest Rate?

The amount of interest charged monthly by a lender to a borrower on a principal is called the Monthly Interest Rate. The interest rate is also applicable when receiving interest as opposed to paying it, such as the interest earned on a bank savings account.

How to Calculate Monthly Interest Rate in Excel: 3 Simple Methods

Method 1 – Using RATE Function

The RATE function is used to return the nominal interest rate per period on a loan or an investment.

1.1 – Interest Rate on a Loan

STEPS:

Select cell D8 and enter the following formula:

=RATE(D5,-D6,D7)

NOTE: Here, the Minus sign before D6 indicates payment amounts.

Press Enter to return the result.

1.2 – Interest Rate on a Saving Account

Additionally, we can also calculate the monthly interest rate on a Savings Account. In this case, we don’t have a current value but we do have a future value.

STEPS:

Select cell D8 and enter the following formula:

=RATE(D5,-D6,0,D7)

NOTE: Here, the Minus sign before D6 indicates the payment amounts, and 0 is placed in the formula as there is no current value.

Press Enter to return the result.

Method 2 – Using Annual Percentage Rate

Most financial institutions use an annual percentage rate rather than a monthly rate. We can calculate the monthly interest rate from the Annual Percentage Rate (APR) with a simple calculation.

STEPS:

In cell C6, enter the following formula:

=C5/12

Press Enter to display the result in cell C6.

Method 3 – Using EFFECT Function

We can apply the Excel EFFECT function to calculate the Effective Interest Rate where monthly compounding is present. Effective interest rate is also known as Annual Percentage Yield (APY), which is higher than the nominal interest rate or Annual Percentage Rate (APR). We’ll apply the EFFECT function to the Nominal Interest Rate to return the effective annual interest rate, then derive the monthly interest from that.