Microsoft Office Tutorials and References
In Depth Information
Using EFFECT to Calculate the Effect of Compounding Period on Interest Rates
EFFECT tto Calculate
Effect oof Compounding
Period oon Interest
Does it really matter if your bank compounds interest daily, monthly, or
quarterly? If the numbers are big enough, it can matter. The EFFECT function
converts an interest rate to an effective rate, depending on how frequently
the bank compounds the interest.
The EFFECT function returns the effective annual interest rate, given the
nominal annual interest rate and the number of compounding periods per year.
This function takes the following arguments:
nominal_rate — This is the nominal interest rate.
npery — This is the number of compounding periods per year. npery is
truncated to an integer.
If either argument is nonnumeric, EFFECT returns a #VALUE! error. If nomin-
al_rateis less than or equal to 0 or if nperyis less than 1, EFFECT re-
turns a #NUM! error.
In Figure 13.9 , the nominal interest rate is 4%. If the bank compounds interest
once per year, the effective interest rate is still 4%, as shown in cell A5.
If interest is compounded monthly, the effective rate increases to 4.07%. Row
9 compares the monthly mortgage payment at the various effective rates.
Daily compounding adds about $10 per month to a typical mortgage payment.