Exceljet

Quick, clean, and to the point

Effective annual interest rate

Excel formula: Effective annual interest rate
Generic formula 
=EFFECT(rate,periods)
Explanation 

To calculate the effective annual interest rate, when the nominal rate and compounding periods are given, you can use the EFFECT function. In the example shown, the formula in D5, copied down, is:

=EFFECT(rate,C5)

where "rate" is the named range H4.

How this formula works 

The Effective Annual Rate (EAR) is the interest rate after factoring in compounding. In other words, the EAR is the rate actually earned due to the effect of compounding more frequently than once a year (annually).

The EFFECT function calculates the effective annual interest rate based on the nominal annual interest rate, and the number of compounding periods per year.

To demonstrate how this works, the table shown in the example is set up with various compounding periods in column C. The nominal interest rate is provided in cell H4, which is the named range "rate".

The formula in D5 is:

=EFFECT(rate,C5)

Because named ranges behave like absolute references, this formula can simply be copied down the table. The EFFECT function returns the calculated EAR for each of the given periods.

Manual check

The generic formula for calculating EAR (in Excel formula syntax) is:

=(1+i/n)^n–1

where n stands for periods, and i is the stated interest rate. This formula is used to check the results from EFFECT. In E5, the formula is:

=(1+rate/C5)^C5-1

When this formula is copied down the table, the results in columns D and E match:

Effective annual interest rate - manual check

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.