How to Calculate Effective Interest Rate in Excel with Formula

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.

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

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.

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

  • We will obtain the Effective Annual Rate.

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

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

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

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

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.


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.

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

  • 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

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo