Excel EFFECT Function
EFFECT is an Excel function that calculates the annual effective interest rate given the annual nominal interest rate and the number of compounding periods per year. Effective interest rate is the interest rate that incorporates the compounding effect of multiple compounding periods in each year.
Effective annual interest rate is always higher than the nominal interest rate (also called annual percentage rate). Let's say you invest $10,000 at 10% compounded semiannually. After the first half-year, interest of $500 is credited (=$10,000 × 10% × 1/2) and for the second half-year, the interest is calculated by applying the semiannual rate of 5% (=10%/2) to the original principal plus the interest already credited. The second half-year interest equals $525 (=($10,000 + $500) × 10% × ½). Total interest credit over the year is hence $1,025, which represents the annual effective rate of 10.25% (=$1,025/$10,000).
The formula to work out effective interest rate is:
$$ \text{EFFECT}\ =\left(\text{1}+\frac{\text{NOMINAL}}{\text{NPERY}}\right)^{\text{NPERY}}-\text{1} $$
Where NOMINAL refers to the annual nominal interest rate and NPERY is the number of compounding periods per year.
Syntax
EFFECT syntax is:
EFFECT(nominal, npery)
Nominal is the nominal annual interest rate and npery stands for number of compounding periods per year.
Example
Mark is a university student whose parents set up a trust to finance his university education. 5% of the money which equals $15,000 is invested in a bank time deposit paying 6% compounded monthly. Calculate the effective annual return.
$$ \text{Effective Annual Return} \\ =\ \left(\text{1}+\frac{\text{6%}}{\text{12}}\right)^{\text{12}}-\ \text{1}\ =\ \text{6.17%} $$
EAR can also be calculated using Microsoft Excel EFFECT function. The formula you need to enter to work out effective annual return = EFFECT(6%, 12) as illustrated below:
by Obaidullah Jan, ACA, CFA and last modified on