Kawser
In this article, I will discuss how to use the effective interest formula in Excel (the EFFECT Function). Also discussed topics like:
- What is the effective interest rate (EAR)?
- How to calculate the effective interest rate (using EFFECT function & using formula)
- Importance of understanding the effective interest rate (EAR)
- Why don’t bank use the effective annual interest rate?
- And effective interest rate Excel calculator
What is the Effective Interest Rate (EIR) or Annual Equivalent Rate (AER)?
For example, you went to a bank for a loan of amount $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.
Now after 1 year, how much would you pay to the bank? Assume that you did not pay anything to your bank by this time.
Check out the table below. It shows the effective annual interest rate concept clearly.
Month 1
At the end of the first month, your interest will be Beginning Balance of the First Month x Monthly Interest Rate = $10,000 x 1% = $100.
Monthly interest rate = Stated Annual Rate / 12 = 12%/12 = 1%.
So, at the end of the first month, your Ending Balance will be: Beginning Balance of the First Month + Interest for the First Month = $10,000 + $100 = $10,100
Month 2
$10,100 will be the beginning balance for the second month.
At the end of the second month, your interest will be: Beginning Balance of the Second Month x Monthly Interest Rate = $10,100 x 1% = $101
So, at the end of the second month, your Ending Balance will be: Beginning Balance of the Second Month + Interest for the Second Month = $10,100 + $101 = $10,201
This is how the above calculations are done.
And at the end of the 12th month, your ending balance will be: $11,268.25
So, Effective Annual Interest Rate will be 12.6825% (check out the calculations below).
Excel has a function (EFFECT) to calculate the effective interest rate formula from the nominal interest rate.
How to Calculate the Effective Interest Rate in Excel?
Here is the step by step process to calculate the effective interest rate from the nominal interest rate.
1. Determine the nominal/stated interest rate
The nominal or stated interest rate is also called the annual percentage rate (APR). Normally, you will get this interest rate stated in the headlines or in the inner part of your loan agreement papers.
2. Determine the compounding periods per year
In most of the cases, your compounding periods will be either monthly (every month) or quarterly (every 3 months).
For monthly compounding, your compounding periods per year will be 12 as a year has 12 months.
For quarterly compounding, your compounding periods per year will be 4 as a year as 4 quarters.
For other compounding periods per year, use the following reference table:
| Interest Compounded | Calculated After | Number of Payments Per Year (npery) |
| Daily | 365 Days | 365 |
| Weekly | 7 Days | 52 |
| Bi-weekly | 14 Days | 26 |
| Semi-monthly | 15 Days | 24 |
| Monthly | 1 Month | 12 |
| Bi-monthly | 2 Months | 6 |
| Quarterly | 3 Months | 4 |
| Semi-annually | 6 Months | 2 |
| Yearly | 1 Year | 1 |
3. Calculate Effective Interest Rate Using Excel’s EFFECT Function
You can use Excel’s EFFECT function to calculate the effective annual interest rate from the nominal interest rate.
The syntax of EFFECT Function:
EFFECT (nominal_rate, npery)
The function takes two arguments: nominal_rate and npery.
Here, nominal_rate is the nominal annual interest rate
And npery is the number of compounding periods per year
It is a very easy and straightforward function to use. See the image below.
4. Calculating Effective Annual Interest Rate Directly
You can also calculate the effective annual interest rate using a formula (shown in the following image).
Importance of Understanding Effective Interest Rate
Understanding the effective interest rate is very important for a person. If you are taking a loan for short-term (for 1/2/3 years), the nominal and effective interest rate will not differ very much. But if you’re taking a loan for long-term (10/20/30 years), it will make a huge difference in interest.
Suppose, you are going to take a loan:
- Of amount $10,000
- At 12% nominal interest rate
- Payment frequency is monthly
After 30 years, your ending balance will be $359496 (image below).
What if the interest were calculated yearly (instead of monthly) for the above case?
The ending balance would be $299599. Here would be the outcome (image below).
So, you see the difference is huge: $359496 – $299599 = $59897. That is really a big amount of money.
Life is full of financial decisions such as:
- Finding out the best way to saving or investing money?
- Renting or buying a home?
- Cost of borrowing money. Which bank is offering the best?
- Making a retirement plan. Where to put the money?
- And etc.
Without the knowledge of effective interest rate, you cannot calculate the true return from your investments, or true cost of your loan.
Effective Interest Rate helps you to take the following decisions:
- Effective interest rate gives the true return of your investment or savings
- EAR provides the true cost of a loan
- EAR helps to select the best loan offers available
- EAR helps to choose the best investment opportunities
Why don’t Banks Use the Effective Annual Interest Rate?
When you take a loan from a bank, the bank will talk about the nominal interest rate, not the effective interest rate. They do so because they want to make you believe that their interest rate is lower.
For example, a loan with a 12% nominal interest rate and with monthly payments, you’re actually paying 12.6825% effective interest rate to the bank.
But when you will go to a bank to deposit some money, they will talk about the effective interest rate. This is to make you feel that you’re getting a higher interest rate from that particular bank.
For example, if a bank provides a 6.17% interest rate for savings, and they pay you monthly, the nominal interest rate is actually 6%.
Effective Interest Rate Calculator Excel
I have made an Excel calculator that will calculate the effective annual interest rate.
Here is the calculator.
It is very easy to use this calculator. Just input the nominal interest rate and then select the payment frequency from a drop-down list.
On the right side, you will get the Effective Annual Interest Rate (EIR). Download the calculator from the download section of this article (at the end of the article).
