Nominal Interest Rate vs. Effective Interest Rate Excel Formulas

There are different types of interest rates and it is prudent for every investor or potential investor to know the differences between interest rates. The nominal interest rate is the simplest of the interest rates. In the case of the nominal interest rate, it is the given interest rate or stated interest rate of a bond or loan. The nominal interest rate is also referred to as the coupon rate for fixed income investments.

In Excel, one can convert between the nominal annual interest rate and the effective annual interest rate using either the NOMINAL or the EFFECT Function.

The syntax of NOMINAL Function is:

Nominal vs Effective Excel Formulas Image 1

The effective interest rate takes the effect of compounding into account. The difference between the nominal and effective rates widens as the compounding periods increase.

The effective annual interest rate is calculated using the EFFECT Function in Excel. The syntax for the EFFECT Function is:

Nominal vs Effective Excel Formulas Image 2

Calculating the nominal interest rate using the NOMINAL Function

The effective interest rate of a loan for a second-hand car is 2.5%, the number of compounding periods since its monthly payments required is 12. We want to figure out what the nominal annual interest rate is by utilizing the information given.

Nominal vs Effective Excel Formulas Image 3

1) In order to calculate the nominal annual interest rate, in Cell B6 we input the following formula:

=NOMINAL(B4, B5)

Nominal vs Effective Excel Formulas Image 4

2) Upon pressing CTRL-ENTER, a value of 0.0247 is returned, which is less than the effective interest rate, which is to be expected since the nominal interest rate does not include compounding.

Nominal vs Effective Excel Formulas Image 5

Calculating the effective annual interest rate using the EFFECT Function

The nominal annual interest rate of a loan is 7.8%, the number of compounding periods is 2. We want to figure out what the effective annual interest rate is, by utilizing the information given.

Nominal vs Effective Excel Formulas Image 5

1) In order to calculate the effective annual interest rate, in Cell B6 we input the following formula:

=EFFECT(B4, 2)

Nominal vs Effective Excel Formulas Image 6

2) Upon pressing CTRL-ENTER, a value of 0.0795 is returned, which is greater than the nominal rate since the effective annual interest rate includes compounding.

Nominal vs Effective Excel Formulas Image 7

Conclusion

Some financial institutions often quote the nominal interest rate on loans since its fairly straightforward, however it can be slightly misleading since it doesn’t take into account the effect of compounding which the effective interest rate does. For small amounts and small time periods, the difference between the two is not heavily felt but for larger amounts and increasing time periods, the slightly larger effective interest rate is felt.

Please feel free to comment and tell us if you use nominal and effective interest rates in your financial spreadsheets.

Download working file

Nominal-Versus-Effective-Interest-Rate

Useful Links

Interest Rates Explained: Nominal, Real, Effective

Investing 101: The Concept of Compounding

The NOMINAL Function

The EFFECT Function

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.