The** effective interest rate**, also referred to as the ** Annual Equivalent Rate(AER)**, is the amount of interest that a person actually pays or earns on a financial investment. It is determined by taking into account the effect of compounding over a certain period of time. In this article, we will discuss 3 effective ways to calculate effective interest rates in

*Excel*with a formula.

In this article, we will learn 3 ways to calculate the effective interest rate of investment in *Excel *with the proper formula. Firstly, we will use an Excel formula to calculate the effective interest formula. Then we will go for ** the EFFECT function** to calculate the effective interest. Finally, we will use an effective interest rate calculator to do the job. We will use the following sample dataset to illustrate the methods.

**Table of Contents**Expand

## 1. Using Manual Excel Formula to Calculate Effective Interest Rate

In this method, we will directly use the effective interest rate formula. The formula is

**EAR=(1+i/n)^n-1**

Here,

**I = stated annual interest or nominal interest**

**n = number of compounding periods per year**

**Steps:**

- Firstly, select the
cell and write down the following formula,*C7*

`=(1+C4/C5)^C5-1`

- Then, hit
.*Enter*

- Consequently, we will get the
.**EAR**

**Read More: **How to Use Nominal Interest Rate Formula in Excel

## 2. Applying Excel EFFECT Function To Calculate the Effective Interest Rate

** The EFFECT function** is Excel’s default function to calculate the effective annual interest rate. It takes the nominal interest and the number of compounding periods per year as its argument.

**Steps:**

- To begin with, select the
cell and write the following formula:*C7*

`=EFFECT(C4,C5)`

- Then, press
.*Enter*

- Consequently, we will obtain the
**EAR**.

**Read More: **Nominal vs Effective Interest Rate in Excel

## 3. Using Our Effective Interest Rate Calculator To Calculate Effective Interest Rate

In the final method, we will use an effective interest rate calculator to accomplish the task. We have built a calculator based on the data table with data providing the number of payments for a particular compounding period.

**Steps:**

- Firstly, select the
cell and write down the required nominal rate.*C4* - In this case, it is
*10%.*

- Then, go to the
box.*“Interest Compounded”* - From the drop-down list, select the period by which your interest will be compounded.
- In this instance, we will select the
rate of compound interest.*Quarterly*

- Consequently, you will get an effective interest rate.

We have used * the VLOOKUP function* to pass the

*argument of the Effective function. This argument denotes the number of payments per year.*

**npery***searches through the*

**The VLOOKUP function***array in another sheet to find out the value*

**Values***and returns the value of the*

**“Quarterly”***column of the row which is*

**3rd***in this case.*

**4**## What Is the Effective Interest Rate (EIR) or Annual Equivalent Rate (AER)?

Effective Interest Rate (EIR) or Annual Equivalent Rate (AER) is the true cost of a project or true return from an investment in a specific period of time (generally it is one year). Let’s explain this with an example.

For example, you went to a bank for a loan of ** $10,000**. The bank has told you that their interest rate (stated rate or annual percentage rate) was

**. And they also mentioned that your interest would compound monthly. After one year, how much would you pay to the bank? Assume that you have not paid anything to your bank by this time. Check out the steps below. It shows the effective annual interest rate concept clearly.**

*12%***Steps:**

- Firstly, select the
cell and type the following formula,*D8*

`=C8*($C$5/12)`

- Then, hit
.*Ente*r - Consequently, you will get the amount of interest for the first month on a
deposit, which is*$10,000*.*$100*

- Then, add the initial deposit and the interest in the
cell using the following formula:*E8*

`=C8+D8`

- Then, hit
.*Enter* - Consequently, you will get the ending balance of the first month, which is
.*$10100*

- Now, paste the same formula in the
cell to find the beginning balance for the next month, which is*C9*.*$10100*

- Follow the same process for the rest of the months of the year to finally get the ending balance of
**December**, which is also the ending balance of the year.

- Then, write down the following formula in the
cell:*E21*

`=(E19-C8)/C8`

- Finally, hit
.*Enter*

- Consequently, we will get the effective interest rate of the year.

**Read More: **How to Calculate Periodic Interest Rate in Excel

**Download Practice Workbook**

## Conclusion

In this article, we have talked about three handy ways to calculate the effective interest rate. These methods will help users to calculate their effective interests properly.

**Related Articles**

**<< Go Back to How to Calculate Interest Rate in Excel | ****Excel for Finance**** | ****Learn Excel**