The Effect Function
Question: Consider three loans with annual percentage
rates of 4.0%, 8.0% and 12.0% respectively.
What is the effective interest rate on each loan if mortgage payments
are received monthly? What is the
effective interest rates on the loans if payments are weekly?
How does the relationship
between annual percentage rates and effective rates vary with the level of interest
rates? How is the relationship between
APR and effective rates impacted by the number of payments in a year?
Some background on the effective interest rate: The effective interest rate is higher than the
average annual percentage rate whenever loan payments are received more than
once per year because lenders can reinvest payments.
The Effect function in Excel
provides a value of the effective interest givens two inputs – the APR and the
number of payments in a year.
Calculations of effective interest rate for monthly mortgages:
Effective Rate Calculations for Monthly Mortgages


APR

0.040

0.080

0.120

# Payments

12

12

12

Effective Rate

0.041

0.083

0.127

% Diff.

1.9%

3.7%

5.7%

Observations:
The gap between the effective
interest rate and the APR rises with the level of APR. This gap is 1.9% when the APR is 4.0%, 3.7% when
the APR is 8.0%, and 5.7% when the APR is 12.0%.
Calculation of effective interest rates for weekly
mortgages:
Effective Rate Calculation for Weekly Mortgages


APR

0.040

0.080

0.120

# Payments

52

52

52

Effective Rate

0.041

0.083

0.127

% Diff.

2.0%

4.0%

6.1%

Observations:
The gap between effective
interest rates and APR rises with income.
The gap is larger when there
are more payment dates in a year.
Authors Note: I am writing
several notes on how to use different Excel finance functions. This is an ongoing project. A list of available questions is obtained at
the page below.
No comments:
Post a Comment