November 30, 2023

How to use EFFECT Function in Excel

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.

Syntax:= EFFECT(nominal_rate, npery)

The EFFECT function syntax has the following arguments:

  • Nominal_rate    Required. The nominal interest rate.
  • Npery    Required. The number of compounding periods per year.

Example: Let’s look at some Excel EFFECT function examples and explore how to use the EFFECT function as a worksheet function in Microsoft Excel:

EFFECT Function - How to use EFFECT Function in Excel

Syntax:  =EFFECT(A2,B2)


EFFECT Function in Excel 1 - How to use EFFECT Function in Excel

Based on the Excel spreadsheet above, the following EFFECT examples would return:

Syntax: =EFFECT(A3,B3)
Result: 0.075

Syntax: =EFFECT(A4,B4)
Result: 0.07640625

Syntax: =EFFECT(A5,B5)
Result: 0.077135866

Syntax: =EFFECT(A6,B6)
Result: 0.077632599

Syntax: =EFFECT(A7,B7)
Result: 0.077875846

Syntax: =EFFECT(A8,B8)
Result: 0.103812891

Syntax: =EFFECT(A9,B9)
Result: 0.02515625

Syntax: =EFFECT(A10,B10)
Result: 0.0816


  1. #VALUE! error – Occurs when:
    • The nominal rate argument is not a numeric value.
    • The npery argument is not a proper numeric value.
    • Any of the arguments provided is non-numeric.
  2. #NUM! error – Occurs when:
    • The given nominal rate argument is less than or equal to 0.
    • The given npery is less than or equal to 0.
  3. If npery is in decimal format, it is truncated to integers.
  4. The EFFECT function is related to the NOMINAL function through Effective rate = (1+(nominal_rate/npery))*npery – 1
  5. EFFECT is calculated as follows: EFFECT Function in Excel - How to use EFFECT Function in Excel
READ:  How to use COUPDAYBS Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *