Mortgage Payment Calculations

[gwasser]

Hi Garth,

On one of the other discussions, we were talking about the advantages of variable rate vs 5 year term mortgages. I suggested to do some comparison calculations to see what the amounts of money (i.e. the risk) was for various interest scenarios. In fact I was tickled by this problem and tried to do the calculations myself on an excel spreadsheet. But, as happened in the past, when using excel, I never seem to get the exact numbers when compared with official mortgage calculators. I think it has to do with the term `interest calculated semi-annually`, but I am not sure.


An example of the discrepany is given below (using REMA`s calculation and comparing it with Excel).

Principal: $100,000
Interest Rate 5.5%
Amortization 40 yrs.
Monthly Payments
Semi-annual interest calculations not in advance

REMA Calculates: 511.56

Excel, using the pmnt function:

=PMT($B$7/12,$B$4*12,$B$3,0)

$B$7/12 = 0.055/12 (interest rate divided by 12 = monthly interest rate)
$B$4*12 = 40*12 (number of loan periods = 40yrs x 12 months)
$B$3 = 100,000 (Pincipal)
0 = calculates interest at end of month.

Excel answers that my monthly payment is: 515.77


Godfried

 

[GarthChapman]

Hi Godfried, you are absolutely correct - the excel formula you are using is not allowing for the semi-annual interest calculation used for Canadian mortgages.

Let me know if you want the correct excel formula and I may be able to dig it up for you.

 

[FayWong]

QUOTE (gwasser @ Jul 13 2008, 11:14 AM) On one of the other discussions, we were talking about the advantages of variable rate vs 5 year term mortgages. I suggested to do some comparison calculations to see what the amounts of money (i.e. the risk) was for various interest scenarios. In fact I was tickled by this problem and tried to do the calculations myself on an excel spreadsheet. But, as happened in the past, when using excel, I never seem to get the exact numbers when compared with official mortgage calculators. I think it has to do with the term `interest calculated semi-annually`, but I am not sure. An example of the discrepany is given below (using REMA`s calculation and comparing it with Excel).Principal: $100,000
Interest Rate 5.5%
Amortization 40 yrs.
Monthly Payments
Semi-annual interest calculations not in advance

REMA Calculates: 511.56

Excel, using the pmnt function:

=PMT($B$7/12,$B$4*12,$B$3,0)

$B$7/12 = 0.055/12 (interest rate divided by 12 = monthly interest rate)
$B$4*12 = 40*12 (number of loan periods = 40yrs x 12 months)
$B$3 = 100,000 (Pincipal)
0 = calculates interest at end of month.

Excel answers that my monthly payment is: 515.77


Godfried

Try this one:
In Excel, fill in the Columns

in the first cell-A1 fill in the value of the property in this example $133,333.33

In the next cell, B1 fill in the Loan to value Ratio (75%, 80%) In this example 75%

in cell C1 copy the calculation formula to determine the loan principle - or You can just start here and plug in $100,000. The formula is "-sum(a1*b1)" [don`t copy quotation marks please]
=sum(a1*b1)

in the next cell cell # D1 fill in the number of years of amortizaion 25, 30 or as in example 40

Cell # E1 is a calculation to get the number of months
=sum(D1*12)

Cell F1 is where you put in your rate (4.75%, 5.00%, in this example put in 5.5% or .055 no percent)

cell # G1 Is your monthly payment calculation you must do a separate calculation (I) as well
=$C$1*$I$1/(1-(EXP((LN(1+$I$1)*-$E$1))))

cell # H1 is a calculation to find the total paid annually. You may not want to do H, if so the formula for cell G1 above has to be changed to "=$C$1*$H$1/(1-(EXP((LN(1+$H$1)*-$E$1))))"
=SUM(D1*12)

I is a separate calculation you need for G this is what accounts for the semi-annual calculation =EXP((LN(1+$F$4/2)*2/12))-1

Copy the calcultions, fill in the blanks for the others and G comes up as $511.56
Hope that helps
This works if you want to do it in excel. This is `Excel speak`

 

[brad]

What language are you speaking??

 

[gwasser]

QUOTE (brad @ Jul 13 2008, 09:50 PM) What language are you speaking??

I am speaking REINish.

Thanks for the help. I`ll try the above given Excel solution. If I still have problems, I may have to take Garth up on his offer to provide me the correct formula.

Again, thanks for the responses.

Godfried

 

[KimFranz]

Hey all you mortgage calculator geeks,

I use the Credit Union Central Alberta site for my mortgage calculations - it is to the penny!!! (I have compared to many mortgage payment schedules from the banks).

Here is the link:

http://www.albertacreditunions.com/public/...sp?cid=44-46-77

Cheers!
Kim

 

[gwasser]

Hi Fay,Your Excel procedure worked. There was one typo though. you wrote:
" I is a separate calculation you need for G this is what accounts for the semi-annual calculation =EXP((LN(1+$F$4/2)*2/12))-1"

The equation should be: =EXP((LN(1+$F$1/2)*2/12))-1

This should be a great starting point for my mortgage comparison sheet. Thanks again.


Godfried

 

[gottatango]

Hi REIN Members,

Here is a website that you might want to check out for a mortgage calculator.

http://www.vertex42.com/Calculators/Canadian-mortgage.html

It is in Excel and it works wonderfully. It does both Canadian and US mortgage calculations.