Excel EFFECT Function

EFFECT converts a nominal annual interest rate into an effective annual rate for a specified compounding frequency.

Use it to find the total annual growth after interest compounds within the year, or to compare nominal rates with different compounding frequencies.

1
Set the nominal rate and compounding frequency Supply the nominal annual interest rate and the number of compounding periods per year.
2
Calculate the effective annual rate EFFECT accounts for interest earned on earlier interest to calculate the total annual growth rate.

EFFECT syntax & arguments

Syntax

=EFFECT(nominal_rate, npery)
Required Optional
  1. 1

    nominal_rate

    Required

    The positive nominal annual interest rate. This is the annual rate before accounting for compounding within the year.

  2. 2

    npery

    Required

    The number of compounding periods per year. The value must be at least 1. Fractional values are truncated to an integer.

Example

=EFFECT(6%,12)

Return the effective annual interest rate for a nominal annual rate of 6% compounded monthly.

EFFECT caveats

  • The input and result are both annual rates

    Supply the nominal annual rate without converting it to a rate per compounding period. EFFECT handles that conversion internally using npery and returns an effective annual rate.

  • Compounding periods are counted per year

    npery counts compounding periods within one year, regardless of the total term of the loan or investment. For example, a three-year loan with monthly compounding uses 12, not 36.

  • Fractional period counts are truncated

    Excel removes the fractional part of npery. For example, =EFFECT(6%,12.9) uses 12 compounding periods, not 13. A value below 1 returns #NUM!.

  • The input rate must be positive

    A zero or negative nominal_rate returns #NUM!. For example, =EFFECT(0,12) returns an error rather than a zero rate.

  • The two rates match with annual compounding

    With npery=1, there is no compounding within the year, so the effective and nominal rates are equal. For example, =EFFECT(6%,1) returns 6%. With more than one compounding period and a positive rate, the effective annual rate is higher than the nominal annual rate.

Need the reverse conversion? Use NOMINAL to convert an effective annual rate to a nominal annual rate for a specified compounding frequency.

Intro EFFECT practice problem

Solve the intro problem directly here, or open it on its own page.

Open full problem

Advanced EFFECT practice problems

Use EFFECT alongside other Excel functions in realistic, less-prescriptive challenges.

No advanced EFFECT problems are currently available.