Home »
ExcelBuiltInFunctions »
ExcelFinancialFunctions »
ExcelEffectFunction
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:
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.
The formulas are shown in the spreadsheet on the left and the results are shown in the spreadsheet
on the right.
Formulas:

A 
1 
=EFFECT( 10%, 4 ) 
2 
=EFFECT( 10%, 2 ) 
3 
=EFFECT( 2.5%, 2 ) 

Results:

A 
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 nonnumeric

#NAME?



Occurs when Analysis ToolPak addin 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:
 From the Tools dropdown menu, select the option AddIns ...
 An 'AddIns' window will pop up. From this, select the option Analysis ToolPak and click OK
+ Show How to Do This in Excel 2007:
 Click the Microsoft button on the top left of your spreadsheet and select
the Excel Options button
 From the menu on the left hand side, select AddIns
 In the 'Manage:' box, select Excel Addins and click Go...
 An 'AddIns' window will pop up. From this, select the option Analysis ToolPak and click OK
+ Show How to Do This in Excel 2010:
 Click the File tab (top left of your spreadsheet) and select Options
 From the menu on the left hand side, select AddIns
 If the window that pops up doesn't show the 'Addins' list, use the 'Manage:'
dropdown menu (at the bottom of the window) to select Excel Addins. Click Go...
 From the 'AddIns' window, select the option Analysis ToolPak and click OK
