We arrange for a $B mortgage, to be paid back
over N months, and the
monthly interest rate is R (R = .01 means 1%).
At the end of the first month the amount we owe is now
B(1+R) so we make our first payment of $P,
leaving a balance owing of B(1+R)  P.
To make things look simpler, we'll let (1+R) = x, so
the balance in our mortgage (after one month) is Bx  P.
After another month this balance has grown by a
factor (1+R) = x,
so it's now (Bx  P)x
but then we make another payment of P
leaving a balance of
Bx^{2}  Px  P.
Okay, one more time (then we'll see the pattern):
Another month goes by and the balance of
Bx^{2}  Px  P
increases by a factor x again,
and we make another $P payment leaving a balance of
Bx^{3}  Px^{2}  Px  P.
After N months, the balance owing is:
Bx^{N} 
P(x^{N1} +
x^{N2} + ... + x + 1).
As you might imagine, there's a magic formula:
x^{N1} +
x^{N2} + ... + x + 1 = (x^{N}  1) / (x  1)
so the balance after N months (and N payments) is:
Bx^{N}  P(x^{N}  1) / (x  1)
and, if it's an Nmonths mortgage (meaning it's all paid off after N months),
then the balance is zero, so:
Bx^{N}  P(x^{N}  1) / (x  1) = 0
and now we can determine the monthly payments
... but it's time to replace
x by (1 + R) again,
so we solve for P and get:
[!]
P = BR/ (1  (1 + R)^{N})

The last problem is to determine R, the monthly mortgage rate.
If the annual rate is Q, then, for a Canadian mortgage
(which involves the peculiar phrase half yearly rests),
the sixmonth rate is Q/2
and the monthly rate of R,
compounded for 6 months must give Q/2 so
(1 + R)^{6} = 1 + Q/2 and that gives us:
You supply the value of B and N and Q and
the spreadsheet calculates
R and the monthly payments and
the balance after each payment, etc.
Here's a BETTER spreadsheet to play with:
Just RIGHTclick on the picture and Save Target to download a .ZIPd spreadsheet.
It calculates a bunch of stuff (Canadian or U.S. mortgages) and compares two mortgages with different parameters.
