Except learning the Nominal interest formula Excel, we shall also cover several topics in this article.
What will you learn from this article?
- What is the Nominal and Effective Annual Interest Rate?
- How to calculate Nominal Interest Rate using Excel function NOMINAL (). To use Nominal () function, you need to know the Effective Interest Rate and the number of times compounding occurs in a year.
- How to calculate Nominal Interest Rate directly from Effective Interest Rate?
- Compare Nominal and Effective Interest Rates in a table
- How to use my Nominal Annual Interest Rate Calculator?
- Importance of Understanding Nominal and Effective Interest Rate
Are you on? So, let’s start.
What are the Nominal and Effective Annual Interest Rates?
I want to start with an example.
Say, you are using a credit card of a bank and they mentioned that the annual percentage Rate (APR) is 24%.
This Annual Percentage Rate (APR) or Annual Interest Rate is known as Nominal Interest Rate.
So, your Credit Card’s Nominal Interest Rate is 24%.
Now, you have bought something using your credit card and say the amount is $5,000.
After one year, you went to the Bank to pay off the loan and interest both and your preparation was to pay total $6200 (principal amount $5000 and interest = $5000 x 24% = $1200).
But your banker is saying that you have to pay total: $6341.21.
Where from the additional $141.21 ($6341.21 – $6200) came in the calculation?
Maybe when you took your credit card from the bank, you did not know that the interest would be calculated monthly (in this case).
But credit card interest could be calculated:
- Daily
- Weekly (every 7 days)
- Bi-weekly (every 14 days)
- Semi-monthly (every 15 days)
- Bi-monthly (every 2 months)
- Quarterly (every 3 months)
- Semi-annually (every 6 months)
- And even yearly (once in a year). In this case, the nominal interest rate and effective interest rate (you will know about this term soon) will be the same.
It all depends on the bank.
Now, let me explain how that extra money is added to your yearly balance and I will introduce you with the term Effective Yearly Interest Rate.
Your Credit Card’s Annual Percentage Rate (APR) = 24%
Monthly Interest Rate = 24% / 12 = 2% = 0.02
After 1 month, your Principal + Interest will be = $5000 + $5000 x 2% = $5000 (1 + 0.02) = $5100.
I am assuming that you did not pay the interest of the first month. So, for the next month (2nd month), your principal will be $5100.
After 2 months, your Principal + Interest will be = $5100 (1 + 0.02) = $5202.
As we’re going to make a formula from this analysis, I want to write the above equation in this way:
= $5000 x (1 + 0.02) x (1 + 0.02); [as $5100 = $5000 x (1 + 0.02)]
= $5000 x (1 + 0.02) ^ 2
In this way, after 3 months, your Principal + Interest will be: $5000 x (1 + 0.02) ^ 3
… … …
… … …
… … …
In the same way, after 12 months, your Principal + Interest will be: $5000 x (1 + 0.02) ^ 12 = $6341.21
And your effective annual interest rate will be: ($6341.21 – $5000) / $5000 = 26.824%.
So, you see how Effective Interest Rate comes into the scene when you are dealing with the compound interest rate.
How to calculate Nominal Interest Rate using Excel function NOMINAL ()
Now, let’s start with another scenario.
You went to a bank. And took a loan of amount $5000 and they said that the Effective Annual Interest Rate was 26.824% and the number of compounding per year will be 12 times.
So, we want to calculate the Nominal Interest Rate using these two pieces of information:
- Effective Annual Interest Rate (in our case, it is 26.824%)
- And the number of compounding per year (in our case, it is 12)
We can use Excel’s Nominal Function to calculate the Nominal Interest Rate.
Excel NOMINAL () Function
Syntax of Nominal Function: NOMINAL (effect_rate, npery)
Here, effect_rate = Effective Annual Interest Rate
And npery = Number of compounding per year
In the following image, you see that I have calculated Nominal Interest Rate using Excel’s NOMINAL Function.
How to calculate Nominal Interest Rate directly from Effective Interest Rate?
You can also calculate Nominal Interest Rate using direct formula.
Direct formula to calculate Nominal Interest Rate from Effective Interest Rate:
Annual Nominal Interest Rate = npery * ((1 + effect_rate) ^ (1/npery) – 1)
Check out the above image (way 2).
You see, I have used the direct formula to find the Annual Nominal Interest Rate.
So, it’s easy to calculate the Nominal Interest Rate.
Compare Nominal and Effective Interest Rates in a table
In the image below, you are seeing how effective interest rate is changing for a certain nominal interest rate based on compounding.
For example, for nominal interest rate 20%, effective interest rates for:
- Yearly = 20% as no compounding occurs,
- Semi-annually = 21%,
- Quarterly = 21.5506%,
- Monthly = 21.9391%,
- Weekly = 22.0934%
- And for Daily = 22.1336%
You can download this Excel template from the download section.
Nominal Annual Interest Rate Calculator
Using the following calculator, you can calculate the annual effective interest rate from the nominal interest rate.
This calculator is very easy to use.
Step 1: Input the annual effective interest rate at first.
Step 2: Then from the drop-down menu, select the period that will be your compounding base.
On the right side, you will find the annual nominal interest rate.
In the same way, you can also calculate the effective interest rate from the nominal rate (using the 2nd calculator).
Importance of Understanding Nominal and Effective Interest Rate
From our above analysis, you are now aware of the crucial difference between nominal and effective interest rate based on compounding.
It is important to know the true cost of your fund or true return of your investment.
When you go to a bank and take a loan, they will state the nominal interest rate of the loan to show their interest rate cheap. It is your duty to find the true cost of your fund.
And when you will go to a bank and deposit some cash to get some interests from your deposit, they will state the effective interest rate to show their return lucrative.
Download the Working File and Calculator
Related Article(s)
- Nominal Interest Rate vs. Effective Interest Rate Excel Formulas
- How to calculate periodic interest rate in Excel (4 ways)
The post Nominal Interest Rate Formula Excel (calculate from Effective Rate) appeared first on ExcelDemy.
