How to Calculate Effective Interest Rate in Excel with Formula

Get FREE Advanced Excel Exercises with Solutions!

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 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 of how to calculate effective interest rate in Excel with formula.


Download Practice Workbook

You can download practice workbook here.


3 Effective Ways to Calculate Effective Interest Rate in Excel with 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 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.

effective interest rate formula excel


1. Using Effective Interest Rate Formula

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 C7 cell and write down the following formula,
=(1+C4/C5)^C5-1
  • Then, hit Enter.

  • Consequently, we will get the EAR.

using direct formula to calculate effective interest rate in excel with formula

Read More: How to Calculate Effective Interest Rate On Bonds Using Excel


2. Applying EFFECT Function

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 C7 cell and write the following formula down:
=EFFECT(C4,C5)
  • Then, press Enter.

writing effective function formula to calculate effective interest rate in excel with formula

  • Consequently, we will obtain the EAR.

applying effective function to calculate effective interest rate in excel with formula

Read More: Nominal vs Effective Interest Rate in Excel (2 Practical Examples)


Similar Readings


3. Using Effective Interest Rate Calculator

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 C4 cell and write down the required nominal rate.
  • In this case, it is 10%.

inserting nominal rate in the calculator to calculate effective interest rate in excel with formula

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

  • Consequently, you will get an effective interest rate.

using effective rate calculator to calculate effective interest rate in excel with formula

We have used the VLOOKUP function to pass the npery argument of the Effective function. This argument denotes the number of payments per year. The VLOOKUP function searches through the Values array in another sheet to find out the value “Quarterly” and returns the value of the 3rd column of the row which is 4 in this case.

Read More: How to Calculate Interest Rate in Excel (3 Ways)


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

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 12%. 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.

Steps:

  • Firstly, select the D8 cell and type the following formula,
=C8*($C$5/12)
  • Then, hit Enter.
  • Consequently, you will get the amount of interest for the first month on a $10,000 deposit, which is $100.

  • Then, add the initial deposit and the interest in the E8 cell using the following formula:
=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 C9 cell 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 ending balance of December, which is also the ending balance of the year.

  • Then, write down the following formula in the E21 cell:
=(E19-C8)/C8
  • Finally, hit Enter.

inserting formula to calculate effective interest rate in excel with formula manually

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


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

Khan Muhammad Kawser

Khan Muhammad Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo