This **EFFECT **function is a financial function of Microsoft Excel. It is calculated based on the **nominal rate of interest** and compound periods in a year. This effective annual rate is important for banks and financial organizations. It helps the organization in making decisions that which loan will be better for the organization. This article will show the use of this **EFFECT **function in Excel with a proper illustration.

The above image shows how different periods affect the interest rate. Through our discussion, you get enough knowledge about the **EFFECT **function.

**Table of Contents**Expand

## Nominal and Effective Rate of Interest

**The Nominal Rate of Interest** is specified as the interest of borrowing money from any bank or any financial organization. This nominal rate is specified yearly basis. For, example if somebody lends $100 and the nominal rate of interest is 8%. He must pay $108 after one year.

**The Effective Rate of Interest** is a little bit different from the nominal rate of interest. The concept of compound periods is introduced here.

For, example if somebody takes a loan of $100 at 8% interest semi-annually. Then the interest after 6 months will be $4 and again inserts will be counted on $100+$4 total $104 after 6 months. After 12 months the inters will be $4.016. And the total payment will be $108.016 after 1 year.

The relation between Nominal and Effective Rate of Interest is as follows:

## EFFECT Function in Excel: Syntax

**Function Objective:**

The **EFFECT **function returns an effective annual interest rate based on the nominal interest rate and the number of compounding periods per year.

**Syntax:**

**=EFFECT(nominal_rate, npery)**

**Argument:**

ARGUMENT |
REQUIRED/OPTIONAL |
EXPLANATION |
---|---|---|

nominal_rate |
REQUIRED |
The nominal or annual interest rate. |

npery |
REQUIRED |
The number of compound periods per year. |

**Return Parameter:**

It returns a numeric value that refers to the effective rate of interest.

**Available in:**

Excel for Microsoft 365, Microsoft 365 for Mac, Excel for the web Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2019 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel for Mac 2011, Excel Starter 2010.

We can apply the **EFFECT **Function with both cell references and raw values. In the following examples, we will see how to use the function with raw values in Example 1 and with cell references in Example 2.

## Example 1: Finding the Effective Annual Rate of Interest with Raw Values Using the Excel EFFECT Function

In this section, we will show an example regarding the use of raw values to get the effective annual rate of interest using the **EFFECT **function in Excel.

**Step 1:**

- First, we take data of nominal rate of interest. We will use these raw values in the formula.

**Step 2:**

- Now, write the
**EFFECT**function on**Cell C5**. - Here, we put
**10%**in the first argument and**2**in the second argument. - So, the formula will be:

`=EFFECT(10%,2)`

**Step 3:**

- Then press
**Enter**.

In the result, we get a decimal value. But it is mandatory to show the percentage value.

**Step 4:**

- Now, press
**Ctrl+1**. - Get the
**Format Cells**dialog. - Now, select the
**Percentage**category. - We can also set the decimal places as per our requirements.

**Step 5:**

- Then press
**OK**.

Now, get the percentile value.

**Step 6:**

- Now, type the
**EFFECT**function and get the value for each cell manually.

**Step 7:**

- To see the formula of each cell, we add a column named
**Formula**.

Here, we get the effective rate of interest using raw values manually.

## Example 2: Calculating the Effective Annual Rate of Interest Using the Excel EFFECT Function with Cell References

Here, we will show how to use the **EFFECT **function with **Cell References** in Excel. To show this, first look at the data that contains a set of the nominal rates of interest with the number of periods per year.

Here, different periods to show effective interest rates change according to this. In all cases, the nominal rate is fixed. Now, watch the following steps carefully.

**Step 1:**

- Write the
**EFFECT**function on**Cell D5**. The formula is:

`=EFFECT(B5,C5)`

**Step 2:**

- Now, press
**Enter**.

In the result, we get a decimal value. But it is mandatory to show the percentage value.

**Step 3:**

- Get the percentile value as shown in the previous example.

**Step 4:**

- Now, pull the
**Fill Handle**icon.

Here, we get the effective interest rate by using the **EFFECT **Function. We can apply for as many periods as per our requirement.

## Things to Remember

**npery**must be an integer.- The
**nominal_rate**must be between**0**to**1**. - If any of the arguments is not numeric, this function will return
**#VALUE!**error value. - If
**nominal_rate**is less than or equal to 0 or if**npery**is less than 1,**EFFECT**will return the**#NUM!**error value.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

In this article, we showed the use of the **EFFECT **function in Excel. I hope this will satisfy your needs. Please give your suggestions in the comment box.

**<< Go Back to Excel Functions | Learn Excel**