How to calculate the effective annual interest rate (EAR) in Excel

Let’s play around with the effective annual interest rate in Excel, to understand the EAR concept and see the impact of the effective rate versus the nominal rate.

⏱️TIMESTAMPS⏱️
00:00 Effective vs nominal interest rate in Excel
00:38 Effective annual interest rate calculation Excel formula
01:34 Delta effective rate vs nominal rate
02:53 Effect of number of compounding periods on EAR
04:32 Effect of higher nominal interest rate on EAR
05:46 Calculating nominal annual interest rate from EAR

Over here at the top of the Excel file, I have inserted the EAR formula as a text field. Then there’s a section called “input variables” which we can populate with various scenarios, then a section with the “output calculations”, and at the bottom the various components of the EAR formula so you can follow through step-by-step what happens to each element of the calculation.

We are going to input the effective annual interest rate calculation as an Excel formula in cell B9. In the home tab of Excel, go to insert functions over here, and select the POWER function. The dialog box now guides you through the steps. The number that we want to raise to a power is 1+i/n. In Excel, you need to refer to the cells in which the data resides. 1 + cell B4 divided by cell B5. To the power B5. Click OK, and then add “-1” to the calculation in cell B9, for the Excel formula to match the EAR formula in text at the top.

Now let’s see what is going on with the inputs and the outputs. At a nominal annual interest rate of 6%, and 12 compounding periods, the effective annual interest rate is 6.17%. If the nominal annual interest rate is 6%, and the #effectiveannualinterestrate is 6.17%, then the delta between the two is 0.17%-points, or 17 basis points. If you apply that delta in interest rates to an amount of $10,000, then the additional amount of interest (earned or paid) is $16.78 per year.

We can now look at various scenarios of nominal interest rates, and number of compounding periods. Let’s start with changing the number of compounding periods.

If the number of compounding periods is one, then you are compounding annually, and the nominal annual interest rate equals the effective annual interest rate.

If the number of compounding periods is two, then you are compounding twice per year (every six months), which makes the effective annual interest rate slightly higher than the nominal annual interest rate. i divided by n is 3%, and 103% is raised to the power 2. The EAR is higher than the nominal rate once the number of compounding periods is higher than 1, in other words more frequent compounding than annually. The EAR can also be lower than the nominal rate in case the number of compounding periods is lower than 1, in other words less frequent compounding than annually.

If you keep increasing the number of compounding periods, for example to 52 (weekly compounding), then i divided by n keeps getting smaller, and the power to which this is raised keeps getting bigger. The incremental impact of more compounding periods, in terms of EAR, delta in interest rates, or the $ impact, keeps getting smaller.

What is very important to understand is that the delta between the effective and the nominal rate becomes bigger when the interest rate is higher and when compounding is more frequent. And the effect is not linear!
6% nominal annual interest rate compounded monthly, makes the EAR 0.17%-points higher than the nominal rate.
24% nominal annual interest rate (a factor 4 increase versus the previous 6%), compounded monthly, makes the #EAR 26.82%, which is 2.82%-points higher than the nominal rate, which is a factor of nearly 17X!

The great thing about Excel is that you can also calculate in the opposite direction: going from effective annual interest rate back to nominal annual interest rate. If the effective annual interest is maximized at 15%, then what is the nominal annual interest rate, based on daily compounding? Click cell B9, then go to the DATA tab, what-if analysis, goal seek. Set cell B9 to value 15%, by changing cell B4. Click OK, and then let’s display the nominal annual interest rate with two decimals. A nominal annual interest rate of 13.98%, compounded daily, equates to an effective annual interest rate of 15%.

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through    / @thefinancestoryteller