## Saturday, August 26, 2017

### The Effect Function

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.