## Method 1 â€“Â Using a Manual Excel Formula

**Steps:**

- Select cell
**C7**and enter the following formula:

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

- Hit
**Enter**.

- We will get the
**Effective Annual Rate**.

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

## Method 2 â€“ Applying the Excel EFFECT Function

**Steps:**

- Select cell
**C7**and enter the following formula:

`=EFFECT(C4,C5)`

- Press
**Enter**.

- We will obtain the
**Effective Annual Rate**.

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

## Method 3 â€“ Using the Effective Interest Rate Calculator

**Steps:**

- Select the cell
**C4**and enter the required nominal rate. - In this case, it is
**10%.**

- Go to the
**â€œInterest Compounded”Â**box. - From the drop-down list, select the period by which your interest will be compounded. We have selected the
**Quarterly**rate of compound interest.

- 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 over a specific period of time (generally one year). Let’s explain this with an example.

For example, you went to a bank for a loan of ** $10,000**. The bank told you that their interest rate (stated rate or annual percentage rate) was 12%. 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. They clearly show the effective annual interest rate concept.

**Steps:**

- Select cell
**D8**and enter the following formula:

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

- Press
**Enter**. - You will get the amount of interest for the first month on a
**$10,000**deposit, which is.*$*100

- Enter the initial deposit and the interest in cell
**E8**Â using the following formula:

`=C8+D8`

- Press
**Enter**. - You will get the ending balance of the first month, which is
**$10100**.

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

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

- Enter the following formula in cell
**E21**:

`=(E19-C8)/C8`

- Press
**Enter**.

- We will get the effective interest rate of the year.

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

**Download the Practice Workbook**

**Related Articles**

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