[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference kaosws::canada

Title:True North Strong & Free
Notice:Introduction in Note 535, For Sale/Wanted in 524
Moderator:POLAR::RICHARDSON
Created:Fri Jun 19 1987
Last Modified:Fri Jun 06 1997
Last Successful Update:Fri Jun 06 1997
Number of topics:1040
Total number of notes:13668

600.0. "Canadain mortgage calculations?" by TRCOA::WALLACE (Ed Wallace Toronto, Canada.) Fri Aug 07 1992 17:02

    Does anyone have the formula one would use in a spreadsheet like Lotus
    or DECalc to calculate Canadian mortgage rates, mortgage payments,
    principal balance etc, just like the Canadian banks do it.
    
    The formulae built into Lotus and DECalc use the same method as used in
    the US, not the way it's done in Canada.
    
    Thanks,
    Ed
T.RTitleUserPersonal
Name
DateLines
600.1KAOFS::S_BROOKFri Aug 07 1992 18:4886
    
    OK, here you go ...  hope it makes sense
    
    
    
    Where I is the Quoted interest rate
          I1 is the Interest rate for the compounding period
    	  M is the Payment
    	  P is the Principal
          Z is the Amortization in whatever units (Months, weeks,
                   biweeks,semimonths etc)
    	  X is the number of compounding period per 6 months (6 for months
    		13 for bi weeks, 26 for weeks, 12 for semi-months)
    
    	IS = I/2
        I1 = (1+IS^(1/X))-1
    
        M = P*I1*(1+I)^Z / ((1+I1)^Z)-1)
    
    Examples 
    
    
    1)	$100,000  paid monthly  25 year amortization 12% rate
    
    	I = .12		( 12% )
    	P = 100000	( $100,000 )
    	X = 6		( Interest Rate Calculated Semi-annually )
    	Z = 300		( 25 years = 25*12 = 300 months amortization )
    
    	IS = .06
    	I1 = .00975883
    
    	M = 1031.90
    	
        Monthly payment = $1,031.90
    
    2)	$100,000 12% weekly pay 25 years
    
    	X = 26
    	Z = 52 * 25 = 1300
    
    	M = 237.24
    
    	Weekly payment 237.24
    
    $100,000 12% weekly pay 17.5 years
    
    	X = 26
    	Z = 52 * 17.5
    
    	M = 257.92
    
    	Weekly payment 257.92  (note this is approx 1 month divided by 4
    
    ---------------------------------------------------------------
    
    For Monthly and semimonthly payments, the result you get from this
    formula is exact.  For weekly and bi-weekly payments, this yields
    very close to the correct amount, because it does not take into
    account that there are more than 52 weeks a year.  The result though
    is usually less than $1 different.
    
    Also, it does not take into account the fact that the months are
    varying length.  Generally most financial institutions used to assume
    that the months are all an equal length ... but now some are taking
    the differences into account, either by adjusting the payment, or 
    usually by shortening the amortization a little bit extra each year.
    (Over the life of the mortgage though this is only a matter of a few
    weeks).
    
    Note that some institutions quote rates for weekly, biweekly and semi-
    monthly that are calculated as 1/4, 1/2 and 1/2 the monthly payments
    respectively.  These all have the effect of reducing the amortization
    period.  For example a 25 year mortgage paid weekly as 1/4 of the
    monthly payments is really approximately a 17.5 year amortization.  (If
    you use the above formula with an amortization of 17.5 years and weekly
    payments, you'll find the payments very similar to dividing the monthly
    payment by 4)
    
    
    
    I have a program that will calculate the amounts and also an
    amortization program for Canadian Mortgages if you are interested.
    (The latter has a few tweaks to do to it)
    
    Stuart
600.2check metoo""::sw_tools_catalogCGOOA::OWONGSKIWI in Canada (VAO)Wed Aug 12 1992 04:279
    There's a program in the metoo""::sw_tools_catalog for calculating
    stuff for mortgages - don't remember the exact note number but a
    dir/title=mortgage should get it.
    
    I have used it to give me and idea what payments could be like
    especially with the current downward trend in rates.
    
    	Owen
    
600.3KAOFS::S_BROOKWed Aug 12 1992 13:4314
    The mortgage program in the tools catalogue is for calculating
    mortgages the *American* way, where the interest rate is "Calculated
    monthly" as opposed to the Canadian way which is "Calculated Semi-
    annually).
    
    The difference, for example, on a monthly pay mortgage quoted as
    12% per year, on an American mortgage, you'll be charged 1% interest
    per month.  On a Canadian mortgage, you'll be charged 0.976% interest
    per month.  It results in Canadian payments being slightly lower.
    
    If you use the American program, you'll forever be having disagreements
    with the bank / trust company!
    
    Stuart
600.4Thanks, I'll try it.TRCOA::WALLACEEd Wallace Toronto, Canada.Wed Aug 12 1992 20:127
    Well thanks for the info, as soon as I get some time I'll give these
    things a try.
    
    Then again, now that 'we' have Lotus in All-in-1, someone could just
    mail me a spreadsheet already done, and I could just try it...
    
    
600.5KAOFS::S_BROOKWed Aug 12 1992 20:354
    All-in-1 is slow to access from here, and setting up Macros in
    123 is a pain ...
    
    Stuart
600.6I have a "C" mortgage programKAOFS::J_WEIRTue Sep 01 1992 13:038
    I have a "C" program that I created to show mortgage payments.
    It's not too fancy, it doesnt give a table for the life of the 
    mortgage or anything. It does show the payment on monthly and weekly
    or bi-weekly payments. It also shows the amount remaining on your
    mortgage after so many payments.
    
    It works for my mortgage, you are free to try it for yours. Just 
    send me mail and I'll send you the source code.
600.7correction to 600.1TROOA::BOUNDYits a kludge, but it works...Mon Jun 07 1993 16:5216
From:	KAOFS::CSC32::S_BROOK      "I just passed myself going the other way"  7-JUN-1993 12:32:07.00
To:	KAOFS::TROOA::BOUNDY
CC:	S_BROOK
Subj:	RE: KAOSWS::CANADA note 600.1

Hi Graham,

>Try as I might, I can't get the mortgage calculation to work. Specifically,
>
>        M = P*I1*(1+I)^Z / ((1+I1)^Z)-1)

Oops ... there is one bug ... that should be (1+I1) on top

Stuart
    
600.8Interest Component Formula?KAOFS::LOCKYERNO! (Tact Is For Weenies!!)Thu Dec 09 1993 13:285
    Stuart, thanks for the Canadian mortgage payment formula.  Now, do you,
    or anyone else, have the formula for calculating the interest component
    of each payment?
    
    Lockyer
600.9CTHP12::M_MORINA dead man with the most toys is still a dead man.Thu Dec 09 1993 13:544
I'm willing to bet he does...

/Mario

600.10CSC32::S_BROOKThere and back to see how far it isThu Dec 09 1993 16:1627
Yup, sure do!

Note that this has to be recalculated each month on the declining 
Principle balance ...

Where

INT= monthly interest payment
I= interest rate per annum (expressed as decimal ... eg 12% =.12)
P= outstanding Principle
IM= monthly interest rate (as a decimal)


IM = ((I/2)+1)^(1/6))-1

INT = P * IM

So, for example

Where I = 10% p.a.  Outstanding balance = $100,000.00

IM = (1.05^.16666666667)-1
   = .0081648460519 etc .....

INT = $816.48


600.11KAOFS::LOCKYERNO! (Tact Is For Weenies!!)Thu Dec 09 1993 16:309
    Thanks Stuart, but I dod have a question -
    
    Is this formula valid for all payment frequencies (ie. for monthly,
    bi-weekly, weekly and semi-monthy)?
    
    Regards,
    
    Garry
    
600.12CSC32::S_BROOKThere and back to see how far it isThu Dec 09 1993 16:5937
Where
    INT= periodic interest payment
    I= interest rate per annum (expressed as decimal ... eg 12% =.12)
    P= outstanding Principle
    IM= periodic interest rate (as a decimal)
    
    For Semi Monthly ...
    
    IM = ((I/2)+1)^(1/12))-1
    
    
    For Bi-Weekly
    
    IM = ((I/2)+1)^(1/13))-1
    
    
    For Weekly
    
    IM = ((I/2)+1)^(1/26))-1
    
    
    Then INT = IM * P
    
    
    This works for Canadian Mortgage Payments where the Interest
    rate is quoted in the following manner for example 
    
    10% calculated semi-annually, not in advance
    
    and compounded at the rate of payment frequency.
    
    NOTE that there are SOME lenders who still compound monthly with
    higher payment frequencies and make some kind of interest rate
    adjustment to cope with your higher payment frequency.  This would
    be explained in the disclosure statements from the lender.
    
    
600.13Thanks!!KAOFS::LOCKYERNO! (Tact Is For Weenies!!)Thu Dec 09 1993 17:023
    Thanks again,
    
    Garry