How to Use Nominal Interest Rate Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Use Nominal Interest Rate Formula in Excel

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:

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

Steps:

  • Insert the following formula in Cell D11–
=NOMINAL(D9,D10)
  • Then just hit the ENTER button for the output.

Using Excel NOMINAL () Function

Read More: Nominal Interest Rate vs. Effective Interest Rate in Excel


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)

Steps:

  • Type the following formula in Cell D16–
=D10*((1+D9)^(1/D10)-1)
  • Later, just press the ENTER button to finish.

Using Direct Formula to Calculate Nominal Interest Rate from Effective Interest Rate

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.

Read More: How to Calculate Periodic Interest Rate in Excel


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.

Steps:

  • Insert the following formula in Cell C8–
=C5+C6
  • Then just hit the ENTER button to get the output.

Calculate Nominal Interest Rate from Real Interest Rate


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


Conclusion

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.


Related Article


<< Go Back to How to Calculate Interest Rate in Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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