How to use Excel EFFECT Function


EFFECT function in excel is a financial function. It is used to find effective annual interest rate. It is defined as the actual interest rate earned annually due to compounding. It is a worksheet function and can be entered in the cell in which value is required.


=EFFECT(nominal_rate, npery)


  • Nominal_Rate: It is the nominal interest rate.
  • NPERY: It is the number of compounding period per year.


  • Npery is converted to an integer.
  • If any of the argument is nonnumeric, #VALUE! error is displayed.
  • If nominal_rate less than or equal to 0 or if npery is less than 1, #NUM! error is displayed.
  • EFFECT is calculated as follows:
  • EFFECT (nominal_rate,npery) can be compared to NOMINAL(effect_rate,npery) by the following formula: effective_rate=(1+(nominal_rate/npery))*npery -1.


  • In this example, various arguments are entered in column A and their values are entered in column B and EFFECT function is used to retrieve the result in column D. Formula used for calculation is:

Leave a Comment