Mortgage Calculator

Amortization tables by hand - Canada & U.S.A.

find us online

Mortgage Calculator, amortization tables by hand in Canada & U.S.A.

Canadian mortgage calculation by hand

Canadian mortgages are compounded semi-annually instead of monthly like US mortgages.
 
          Monthly Pmt =
(P*(((1+i/200)^(1/6)-1))/(1-(((1+i/200)^(1/6)))^-(n*12)))
 
          Where:
 
                        P = principal outstanding
                        i = annual interest rate percentage
                        n = number of years

Here is a easier to read representation:

                           i    1/6
                   ( 1 +  --- )       -   1
                          200
Pmt = Principal x  ------------------------
                                i   1/6    -12 x n
                   1 -  [ (1 + --- )     ] 
                               200

Or to convert canadian interest rates to US interest rates:

                           Can. Rate  1/6
US Rate =  1200 x [ ( 1 +  --------- )     - 1 ]
                              200

or as a formula, US Rate = 1200 * ((1 + Can.Rate/200)^(1/6) - 1)

How to calculate mortgage, amortization tables by hand in U.S.A.

First you must define some variables to make it easier to set up:

  • P = principal, the initial amount of the loan
  • I = the annual interest rate (from 1 to 100 percent)
  • L = length, the length (in years) of the loan, or at least the length over which the loan is amortized.

    The following assumes a typical conventional loan where the interest is compounded monthly. First I will define two more variables to make the calculations easier:

  • J = monthly interest in decimal form = I / (12 x 100)
  • N = number of months over which loan is amortized = L x 12

    Okay now for the big monthly payment (M) formula, it is:

                                  J
             M  =  P  x ------------------------
                     
                          1  - ( 1 + J ) ^ -N
    
       where 1 is the number one (it does not appear too clearly on some browsers)
    

    So to calculate it, you would first calculate 1 + J then take that to the -N (minus N) power, subtract that from the number 1. Now take the inverse of that (if you have a 1/X button on your calculator push that). Then multiply the result times J and then times P. Sorry, for the long way of explaining it, but I just wanted to be clear for everybody.

             M = P * ( J / (1 - (1 + J) ** -N))
    

    So now you should be able to calculate the monthly payment, M. To calculate the amortization table you need to do some iteration (i.e. a simple loop). I will tell you the simple steps :

    Step 1: Calculate H = P x J, this is your current monthly interest
    Step 2: Calculate C = M - H, this is your monthly payment minus your monthly interest, so it is the amount of principal you pay for that month
    Step 3: Calculate Q = P - C, this is the new balance of your principal of your loan.
    Step 4: Set P equal to Q and go back to Step 1: You thusly loop around until the value Q (and hence P)
    goes to zero.


    Finding the Number of Periods given a Payment, Interest and Loan Amount

    This formula previously was not explicit enough!! The 1/q factor in there was to convert the number of periods into years. For number of payments this must actually be left out.
    n = - (LN(1-(B/m)*(r/q)))/LN(1+(r/q))
    # years = - 1/q * (LN(1-(B/m)*(r/q)))/LN(1+(r/q))

    Where:

    • q = amount of annual payment periods
    • r = interest rate
    • B = principal
    • m = payment amount
    • n = amount payment periods
    • LN = natural logarithm

    For Finding Remaining Principal Balance

    
    P = P * (1 - ((1 + J) ** t - 1) / ((1 + J) ** N - 1))
    
    
    where:
    • P = principal, the initial amount of the loan
    • I = the annual interest rate (from 1 to 100 percent)
    • L = length, the length (in years) of the loan, or at least the length over which the loan is amortized.
    • J = monthly interest in decimal form = I / (12 x 100)
    • N = number of months over which loan is amortized = L x 12
    • t=number of paid monthly loan payments

    Finding the Interest Rate Given Loan Amount, Payment and Number of Periods

       min_rate = 0; max_rate = 100; # Set Maximum and minimum rate
       while (min_rate < max_rate - 0.0001)
       {
          mid_rate = (min_rate + max_rate) / 2; # Divide by 2 to find midpoint
          J = mid_rate / 1200; # Convert to monthly decimal percentage
          # calculate payment based on this interest, term of F and loan_amt
          guessed_pmt = loan_amt * (1 - ((1 + J) ** t - 1) / ((1 + J) ** N - 1));
          if (guessed_pmt > actual_payment)
            { 
               max_rate = mid_rate; # current rate is new maximum 
            }
          else
            {
               min_rate = mid_rate; # current rate is new minimum
            }
       }
       print " The Rate is ", mid_rate;
    


    Related links


    Converter, Calculator & conversion table»» Mortgage Calculator | Loan Calculator | Population Calculator | Converter | Weight Conversion Table | Weight Chart table | Excess Fat Calculator