Excel EFFECT Function

The Excel EFFECT function is useful for calculating the actual interest rate when interest is compounded multiple times per year.

The syntax of the EFFECT function is as follows.

EFFECT(Nominal_rate, Npery)

Use of the Excel EFFECT Function

The Excel EFFECT function calculates the effective annual rate.

The effective annual rate or EAR adjusts the nominal rate for the number of compounding periods in a year. The effective annual rate allows for the effect of compounding, whereas the nominal annual rate does not.

Excel EFFECT Function and Semi Annual Compounding

Suppose for example, the nominal rate is 8% per year with compounding every 6 months. The EFFECT function calculates the effective annual rate due to this compounding as follows:

Effective annual rate = EFFECT(nominal_rate, npery)
nominal_rate = 8%
npery = 2 ( 6 month period compounding)
Effective annual rate = EFFECT(8%,2)
Effective annual rate = 8.160%

The effective rate is higher than the nominal rate due to the effect of compounding two times within a year.

Excel EFFECT Function and Quarterly Compounding

Using the same nominal rate compounded quarterly. The Excel EFFECT function calculates the effective annual rate due to this compounding as follows:

Nominal_rate = 8%
Npery = 4 (quarterly compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,4)
Effective annual rate = 8.243%

The effective rate is higher than the nominal rate due to the effect of compounding four times within a year.

Excel EFFECT Function and Monthly Compounding

Again using the same nominal annual rate as above only compounding monthly. The Excel function calculates the effective annual rate due to this compounding as follows:

Nominal_rate = 8%
Npery = 12 (monthly compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,12)
Effective annual rate = 8.300%

The effective annual rate is higher than the nominal rate due to the effect of compounding twelve times within a year.

EFFECT and Daily Compounding

If the compounding is now daily, assuming a 365 day year, the EFFECT function calculates the effective annual rate due to this compounding as follows:

Nominal_rate = 8%
Npery = 365 (daily compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,365)
Effective annual rate = 8.328%

The effective annual rate is higher than the nominal rate due to the effect of compounding 365 times within a year.

EFFECT Function and Biennial Compounding

In this case the compounding is every 2 years and the number of compounding periods in a year is 0.5. The EFFECT function cannot deal with situations where the number of compounding periods is less than 1 and returns the #NUM! error.

To calculate effective annual rates in these circumstances we use the effective annual rate formula as follows.

r =  nominal annual rate = 8%
m = number of compounding periods in a year = 0.5 (2 year compounding)
Effective annual rate = (1 + r / m )m - 1
Effective annual rate = (1 + 8% / 0.5 )0.5 - 1
Effective annual rate = 7.703%

In this instance, the effective annual rate is less than the nominal rate (8%) as the compounding takes place less than once per year.

EFFECT and Annual Compounding

Finally in the special circumstance where compounding takes place annually, and the number of compounding periods is one, the effective annual rate due to this compounding is given by the EFFECT function as follows:

Nominal_rate = 8%
Npery = 1 (annual compounding)
Effective annual rate = EFFECT(nominal_rate, npery)
Effective annual rate = EFFECT(8%,1)
Effective annual rate = 8.000%

As expected, as the compounding is taking place once per year, the effective rate is the same as the nominal rate.

The EFFECT function, is used to convert a nominal rate, which does not allow for compounding, into an effective annual rate which does allow for the compounding effect.

As can be seen in the examples above, the more frequent the compounding the higher the effective rate will be. Additionally it is important to realize that in the special situation of annual compounding, the effective annual rate and the nominal annual rate are the same.

To illustrate, the effect of the number of compounding periods on the interest rate is summarized in the table below.

In conclusion, the Effect function in Excel is a useful tool for financial analysis. It can help users understand the true cost of borrowing or return on investment by taking into account the compounding of interest. It is important to use the correct inputs and understand the calculations involved to ensure that the results accurately reflect the financial situation being analyzed.

Additionally the EFFECT function is one of many Excel financial functions for time value of money calculations. Discover another function at the links below.

• Excel IRR Function
• Excel PV Function