The Excel EFFECT Function

Effective Annual Interest Rate
The Effective Annual Interest Rate is a measure of interest, that incorporates the compounding of interest and is frequently used to compare financial loans with different compounding terms.

The effective annual interest rate is calculated using the following equation:
Effective Annual Interest Rate Equation
where nominal_interest is the nominal interest rate and npery is the number of compounding periods per year

Further information is given on the Wikipedia Effective Interest Rate Page
Formatting the Result of the Excel Effect Function

If your function result is presented as a decimal, or shows 0%, both of these problems are likely to be due to the formatting of the cell containing the function.

Therefore the problem can be fixed by formatting the cell as a percentage, with decimal places :

  • Use the mouse to right click on the cell containing the function
  • Select the Format Cells ... option and ensure the Number tab is selected
  • Under the Category heading, make sure the option Percentage is selected, and in the box to the right (entitled Decimal Places), change the value to 2 (or more).
  • Click OK

Basic Description

The Excel EFFECT function returns the effective annual interest rate for a given nominal interest rate and number of compounding periods per year.

The format of the function is :

EFFECT( nominal_rate, npery )

where the nominal_rate argument is a numerical value, between 0 and 1, which represents the nominal interest rate, and the npery argument is a positive integer that gives the number of compounding periods per year.

Excel Effect Function Example

Column A of the spreadsheet below, shows examples of the Excel Effect Function, used to calculate the effective annual interest rate for 3 different nominal interest rates and numbers of compounding periods.

1 =EFFECT( 10%, 4 )
2 =EFFECT( 10%, 2 )
3 =EFFECT( 2.5%, 2 )
1 10.38%
2 10.25%
3 2.52%

Further information and examples of the Excel Effect function can be found on the Microsoft Office website.

Common Errors

If you get an error from the Excel Effect function this is likely to be one of the following :

Common Errors
#NUM! - Occurs if the nominal_rate argument is ≤ 0 or > 1, or if the npery argument is < 1
#VALUE! - Occurs if either of the supplied arguments is non-numeric
#NAME? -

Occurs when Analysis ToolPak add-in is not enabled in your Excel. You will need to enable the Analysis ToolPak if you wish to use the Excel Effect Function.

  +   Show How to Do This in Excel 2003:

  +   Show How to Do This in Excel 2007:

  +   Show How to Do This in Excel 2010 or Excel 2013: