How to Use EFFECT Function in Excel (2 Examples)

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.

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:

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:

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.

Read More: How to Use RATE Function in Excel

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:

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.