Calculating different types of interest rates is one of the common tasks that you can perform in Excel, Nominal Interest Rate is one of them. Several built-in functions (as well as formulas) can be used to calculate the rate in Excel. In this article, you’re going to see the Nominal Interest Rate formula in Excel. Before jumping into the article let’s have a brief look at what you are going to gain from article.
Besides learning how to use the nominal interest rate formula in Excel, we shall also cover several topics in this article.
What will you learn from this article?
- What is the Nominal, Real, and Effective Annual Interest Rate?
- Difference Between Nominal, Real, and Effective Interest Rate.
- How to calculate Nominal Interest Rate using Excel function NOMINAL. To use the 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
- Calculate Nominal Interest Rate from Real Interest Rate
- 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 Nominal and Effective Annual Interest Rates?
I want to start with an example.
Say, you are using a credit card or 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 the 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 a total $6200 (principal amount $5000 and interest = $5000 x 24% = $1200).
But your banker is saying that you have to pay a 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:
- 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 to 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.
Difference Between Nominal, Real, and Effective Interest Rate
When the compounding period is not included in the account then it is called the nominal interest rate. And when it is taken into account then it is called the effective interest rate. When we calculate inflation into account then it is stated as the real interest rate.
How to Calculate Nominal Interest Rate from Effective Interest Rate
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)
1. Using Excel NOMINAL Function
We can use Excel’s NOMINAL function in a formula to calculate the Nominal Interest Rate.
Syntax of Nominal Function: NOMINAL (effect_rate, npery)
Here, effect_rate = Effective Annual Interest Rate
And npery = Number of compounding per year
- Insert the following formula in Cell D11–
- Then just hit the ENTER button for the output.
2. Using Direct Formula to Calculate Nominal Interest Rate
You can also calculate Nominal Interest Rate using a direct formula in Excel.
- Direct formula to calculate Nominal Interest Rate from Effective Interest Rate:
Annual Nominal Interest Rate = npery * ((1 + effect_rate) ^ (1/npery) – 1)
- Type the following formula in Cell D16–
- Later, just press the ENTER button to finish.
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 the effective interest rate is changing for a certain nominal interest rate based on compounding.
For example, for a nominal interest rate of 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.
Calculate Nominal Interest Rate from Real Interest Rate
Now let’s calculate the nominal interest rate from the real interest rate. The calculation is quite simple, just add the inflation rate with the real interest rate.
- Insert the following formula in Cell C8–
- Then just hit the ENTER button to get the output.
Nominal Annual Interest Rate Calculator
Now we’ll introduce a calculator to use the nominal interest rate formula in Excel.
From Effective Interest Rate
Using the following calculator, you can use a formula to calculate the nominal interest rate from the effective 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).
From Real Interest Rate
By using this calculator, you will be able to find the nominal interest rate just by giving input the real rate and inflation rate.
Importance of Understanding Nominal, Real, and Effective Interest Rate
- From our above analysis, you are now aware of the crucial difference between nominal and effective interest rates based on compounding.
- It is important to know the true cost of your fund or the 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 is 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 interest from your deposit, they will state the effective interest rate to show their return lucrative.
- You can also able to know what is the total interest rate including the inflation rate means to the real interest rate. That will help you to take decisions while taking a loan.
Download the Working File and Calculator
That’s all for the article. We hope the procedures described above will be reasonable enough to use the nominal interest rate formula in Excel. Feel free to ask any questions in the comment section and give me feedback.