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`