Summary

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.

Generic formula

=EFFECT(rate,periods)

Explanation 

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

Related formulas

Excel formula: Calculate compound interest

Calculate compound interest

Compound interest is a financial concept that describes how an initial investment grows over time, taking into account not only the interest earned on the initial amount but also the interest earned on the interest itself. Compound interest allows your money to grow exponentially, which makes it a...
Excel formula: Calculate simple interest

Calculate simple interest

The general formula for simple interest is: interest=principal*rate*term So, using cell references, we have: =C5*C7*C6 =1000*10*0.05 =500
Excel formula: Annual compound interest schedule

Annual compound interest schedule

If you have an annual interest rate, and a starting balance you can calculate interest with: =balance * rate and the ending balance with: =balance+(balance*rate) So, for each period in the example, we use this formula copied down the table: =C5+(C5*rate) With the FV function The FV function can...
Excel formula: Compare effect of compounding periods

Compare effect of compounding periods

The FV function calculates compound interest and returns the future value of an investment over a specified term. To configure the function, we need to provide a rate, the number of periods, the periodic payment, and the present value: Present value ( pv ) is the named range G4 Rate is provided as...

Related functions

Excel EFFECT function

EFFECT Function

The Excel EFFECT function returns the effective annual interest rate, given a nominal interest rate and the number of compounding periods per year. Effective annual interest rate is the interest rate actually earned due to compounding. 
Excel RRI function

RRI Function

The Excel RRI function returns an equivalent interest rate for the growth of an investment. You can use RRI to calculate Compound Annual Growth Rate (CAGR) in Excel.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions