The Excel EFFECT Function
Home »
Excel-Built-In-Functions »
Excel-Financial-Functions »
Excel-Effect-Function
|
Search this site:
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.
|
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
|
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 :
|
#NAME?
|
-
|
Occurs when Analysis ToolPak add-in is not enabled in your Excel.
To enable this add-in in Excel 2003 :
- From the Tools drop-down menu, select the option Add-Ins ...
- An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK
To enable this add-in 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 Add-Ins
- In the 'Manage:' box, select Excel Add-ins and click Go...
- An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak
and click OK
|
|
#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
|
Formatting the Result
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
