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_rate is the nominal interest rate and npery is the number of compounding periods per year.
For further details, see the Wikipedia Effective Interest Rate PageFunction 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 syntax of the function is:
EFFECT( nominal_rate, npery )
where the arguments are as follows:
| nominal_rate | - | The nominal interest rate (must be a numeric value, between 0 and 1). |
| npery | - | The number of compounding periods per year (must be a positive integer). |
Excel Effect Function Example
The spreadsheet below shows three examples of the Excel Effect Function:
|
Formulas:
|
Results:
|
Formatting the Result as a Percentage
If the result from your Effect function is displayed as a decimal, or shows 0%, both of these problems are likely to be due to the formatting of the cell containing the Effect function.
Therefore the problem can be fixed by formatting the cell as a percentage, with decimal places.
To do this:
- Select the cell(s) to be formatted as a percentage.
-
Open up the 'Format Cells' dialog box using any one of the following methods:
- Right-click on the selected cell or range and select the Format Cells ... option from the right-click menu;
-
Click on the dialog box launcher in the Number grouping within the Home tab of the Excel ribbon (see rightabove);
- Use the keyboard shortcut CTRL-1 (i.e. Select the CTRL key and while holding this down, select the "1" (one) key).
-
Within the 'Format Cells' dialog box:
- Make sure that the Number tab at the top of the dialog box is selected.
-
Select Percentage from the Category list on the left side of the dialog box.
This will cause further options to appear on the right hand side of the control box, which allow you to select the number of decimal places that you want to be displayed (see rightabove). - Once you have selected the number of decimal places that you want to display, click OK.
Further details of the Excel Effect function are provided 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 either:
|
| #VALUE! | - | Occurs if one or both of the supplied arguments are non-numeric. |