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.
EFFECT syntax & arguments
Syntax
=EFFECT(nominal_rate, npery)
-
1
nominal_rate
RequiredThe positive nominal annual interest rate. This is the annual rate before accounting for compounding within the year.
-
2
npery
RequiredThe 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
nperyand returns an effective annual rate. -
Compounding periods are counted per year
nperycounts compounding periods within one year, regardless of the total term of the loan or investment. For example, a three-year loan with monthly compounding uses12, not36. -
Fractional period counts are truncated
Excel removes the fractional part of
npery. For example,=EFFECT(6%,12.9)uses12compounding periods, not13. A value below1returns#NUM!. -
The input rate must be positive
A zero or negative
nominal_ratereturns#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)returns6%. 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.
Advanced EFFECT practice problems
Use EFFECT alongside other Excel functions in realistic, less-prescriptive challenges.
No advanced EFFECT problems are currently available.