Home » Excel-Built-In-Functions » Excel-Financial-Functions » Excel-Effect-Function

# 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.

Further information is given on the

Wikipedia Effective Interest Rate Page

## 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 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 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.

Formulas: | A |
---|
1 | =EFFECT( 10%, 4 ) |
---|
2 | =EFFECT( 10%, 2 ) |
---|
3 | =EFFECT( 2.5%, 2 ) |
---|
| Results: |

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 Effect function.

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

To do this:

- Use the mouse to right click on the cell containing the function;
- Select the
__F__ormat Cells ... option and, when the dialog box pops up, ensure the **Number** tab is selected; - Under the
**Category** heading, make sure the option **Percentage** is selected; - In the box on the right side of the dialog box (entitled
__D__ecimal Places), change the value to 2 (or more); - 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 the supplied nominal_rate argument is ≤ 0, or the supplied npery argument is < 1. |

#VALUE! | - | Occurs if one or both of the supplied arguments are non-numeric. |