pt@geovision.gvc.com (Paul Tomblin) (05/24/91)
Does anybody out there know how to calculate mortgage payments (and
interest, etc) for Canadian mortgages. I managed to come up with
a formula that is in fairly close agreement to my mortgage table book
for monthly payments, but I have no idea why. I also don't know if
it's correct for bi-weekly payments.
I'll take anything: Math notation, Lotus spreadsheets, C code,
even COBOL (god help me). I just need the formulas.
My bank doesn't know. They just plug the numbers in and the computer
tells them everything.....
--
Paul Tomblin, Department of Redundancy Department. ! My employer does
Two roads diverged in a wood, and I/ I took the one less ! not stand by my
travelled by/ And that is why I'm lost, dammit... ! opinions....
pt@geovision.gvc.com or {cognos,uunet}!geovision!pt ! Me neither.maceache@fox.nstn.ns.ca (Tim Maceachern) (05/27/91)
pt@geovision.gvc.com (Paul Tomblin) writes: >Does anybody out there know how to calculate mortgage payments (and >interest, etc) for Canadian mortgages. I managed to come up with >a formula that is in fairly close agreement to my mortgage table book >for monthly payments, but I have no idea why. I also don't know if >it's correct for bi-weekly payments. >I'll take anything: Math notation, Lotus spreadsheets, C code, >even COBOL (god help me). I just need the formulas. >My bank doesn't know. They just plug the numbers in and the computer >tells them everything..... >-- >Paul Tomblin, Department of Redundancy Department. ! My employer does >Two roads diverged in a wood, and I/ I took the one less ! not stand by my >travelled by/ And that is why I'm lost, dammit... ! opinions.... >pt@geovision.gvc.com or {cognos,uunet}!geovision!pt ! Me neither. I believe that the formula is arrived at by using 6 month compounding terms. That is, the actual rate quoted is the compounded rate arrived at in two 6 month terms -- annual rate = (1 + semi-annual rate)^2 - 1. Monthly payments can be calculated using the 6 month term calculations. Bi-weekly payments are usually made in the same amount (actually half) as the monthly payments, but of course you get a few more each year. I don't think you can calculate bi-weekly payments any other way. So, take your annual rate, figure out a semi-annual rate, figure out the payment amount in 6 month chunks (i.e. use a 50 term rate for a 25 year mortgage) using a spreadsheet payment function, then divide the 6 month chunk into 6 monthly payments. This is my understanding of the situation. Your mileage may vary. Don't forget to allow for life insurance payments. Tim MacEachern maceache@corp.nstn.ns.ca or maceache@fox.nstn.ns.ca
jameson@jade.uucp (Kevin Jameson) (05/27/91)
I once wrote a program to match my Alberta credit union's mortgage
calculation program. No one at the credit union could tell me how
it was calculated, since the calculations came from "Credit Union
Central".
It took me two years to get it right (four compounding periods).
Formulas don't work well because of all the special effects of leap
years, variable interest rate mortgages, varying days in the months,
larger or smaller monthly payments, missed payments, and so on.
Every institution that I have checked has calculated interest on a daily
basis.
What follows:
0. Definitions and terminology
1. How to do the calculation
2. Discussion and comments
----------------------------------------------------------------------
DEFINITIONS (These may or may not correspond to those used by institutions)
The QUOTE PERIOD of an interest rate is the denominator of the interest
rate ratio. The quote period is 1 year in the rate "11% per year", and
is 3 months in the rate "2 percent per quarter".
The COMPOUNDING PERIOD is the length of time that passes before the
accumulated interest is combined with the remaining principal. After
interest is added to the principal through compounding, future interest
is calculated on the combined total of principal plus interest, and so
forth.
The PAYMENT PERIOD is the interval after which you normally make a
payment. This is probably one month for most people.
The QUOTED RATE is the numerator of the interest rate ratio. In the
previous examples, the quoted rates are 11 and 2 percent, respectively.
The YEARLY QUOTED RATE is the quoted rate, adjusted to a quote period of
one year. In the previous examples, the yearly rate is 11 percent in
the first case, and 8 percent in the second case.
The EFFECTIVE YEARLY RATE is the quoted rate adjusted for compounding
over a period of one year. This is usually higher than the quoted
yearly rate. You can calculate it as something like
[(1 + yearly_rate/comp_period) ** (1year/comp_period)] - 1.0
[(1 + .11/6) ** (12/6)] - 1.0
[(1 + .055) ** 2] - 1.0
[1.1130] - 1.0
.113 so the effective rate is 11.3 percent.
I don't use it in the method below, so I doubt that it is important, but
maybe mortgage tables use it (I don't know).
----------------------------------------------------------------------
HOW TO DO IT
The following method simply calculates interest on a daily basis, and makes
the appropriate allowances for special effects.
My example will use an 11 percent per year loan/mortgage, compounded
every six months. I am happy to use either term (mortgage or loan),
since I have found that their mathematical treatment is the same for the
mortgage and loans that I have had.
quot_period = 1 year (they mean 365 here, even though it is 366 sometimes)
comp_period = 6 months (not a constant)
pay_period = 1 month (not a constant)
quote_rate = 11 percent per year, .11 as a decimal
year_rate = 11 percent per year, .11 as a decimal
Now convert the yearly rate to a daily rate and accumulate daily
interest charges up to the first payment date. Calculate the interest,
subtract the payment, and repeat the process until you get to the
compound date. On that date, calculate the interest, add the
accumulated interest to the principal, and then subtract your payment.
day_rate = year_rate/365 = .11/365
acc_int = 0 /* accumulated interest */
acc_int = acc_int + P * day_rate /* do this n_days_in_month times */
Note that n_days in month changes with leap years, so you have
to make allowances at the appropriate times.
if (date == compound_date) /* compound if appropriate */
P = P + acc_int;
P = P - payment; /* can be an arbitrary amount */
That is the basic calculation. It matched my mortgage TO THE PENNY over
several years (The auditor's included me in their client sample every
year.) It has also matched more than one loan at different institutions
(banks and credit unions). This daily calculation method may be the
standard way of doing things in the industry.
----------------------------------------------------------------------
DISCUSSION
FORMULAS DON'T WORK WELL because of the many special cases that affect
mortgage calculations. For example, I had a variable rate mortgage, and
the interest rate once changed twice in one week. I could also make
payments of arbitrary size. As you can see, formulas based on periods
longer than one day cannot deal well (or at all) with this type of
change.
If you do use a formula, take some time to MATCH THE PERIODS WITH THE
APPROPRIATE RATES. For example, you must convert the yearly quoted rate
into a monthly rate if your payments are made monthly. You may choose
to use 30 days as the "average" monthly period too. Using such average
numbers, you might be able to match a mortgage table. You may have to
use the effective rate (calculation shown in the definitions section) to
to match a mortgage table.
Hope this helped. If have an old PL/1 version of the program on my PC
somewhere if you get stuck.