Excel EFFECT Function

The Excel EFFECT function has the syntax shown below.

EFFECT(Nominal_rate, Npery)
Arguments used in the Excel EFFECT function
EFFECT = Effective Annual rate
Nominal_rate = Annual nominal rate of interest (i)
Npery = Number of compounding periods in a year (m)

Use of the Excel EFFECT Function

The Excel EFFECT function is used to calculate the effective annual rate.

The effective annual rate or EAR is the nominal rate adjusted 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 compounded every 6 months. The effective annual rate due to this compounding is given by the Excel EFFECT function 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 annual 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 effective annual rate due to this compounding is given by the Excel EFFECT function 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 annual 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 effective annual rate due to this compounding is given by the Excel EFFECT function 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.

Excel EFFECT Function and Daily Compounding

If the compounding is now daily, assuming a 365 day year, the effective annual rate due to this compounding is given by the Excel EFFECT function 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.

Excel 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 Excel 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 the effective annual rate formula is used

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.

Excel EFFECT Function 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 Excel 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 Excel 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 seen in the examples above, the more frequent the compounding the higher the effective rate will be. In the special situation of annual compounding, the effective annual rate and the nominal annual rate are the same.

The Excel EFFECT function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

Excel EFFECT Function November 6th, 2016Team

You May Also Like