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:
Syntax: =EFFECT(A2,B2)
Result:
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
Note:
- #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.
- #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.
- If npery is in decimal format, it is truncated to integers.
- The EFFECT function is related to the NOMINAL function through Effective rate = (1+(nominal_rate/npery))*npery – 1
- EFFECT is calculated as follows:
