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
Bx2 - Px - P.
Okay, one more time (then we'll see the pattern):
Another month goes by and the balance of
Bx2 - Px - P
increases by a factor x again,
and we make another $P payment leaving a balance of
Bx3 - Px2 - Px - P.
After N months, the balance owing is:
xN-2 + ... + x + 1).
As you might imagine, there's a magic formula:
xN-2 + ... + x + 1 = (xN - 1) / (x - 1)
so the balance after N months (and N payments) is:
BxN - P(xN - 1) / (x - 1)
and, if it's an N-months mortgage (meaning it's all paid off after N months),
then the balance is zero, so:
BxN - P(xN - 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:
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 six-month 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:
P = BR/ (1 - (1 + R)-N)
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 RIGHT-click 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.