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:
Based on the Excel spreadsheet above, the following EFFECT examples would return:
- #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: