A Guide to Canadian Mortgage Calculations (with code)

Published over 1 year ago.

If you’re in a rush, here are a few helpful quick links:

I’ve recently taken an interest in real estate investing and as a result, needed to calculate mortgage payments within a spreadsheet. I went online to search for a quick and dirty formula I could use to recreate a mortgage payment calculator like this one by RateHub. To my surprise, it’s difficult to find the actual formulas for how to calcu…

Published over 1 year ago.

If you’re in a rush, here are a few helpful quick links:

So, in this post, we’ll be walking through calculating payments and and amortization schedule for a mortgage with the following terms:

$700,000 loan
30-year amortization
2.34% fixed interest
Monthly payments

Let’s explore the details!

Compounding in Canadian Mortgages

The first thing to understand is that fixed rate mortgages are compounded semi-annually by law. Variable rate mortgages can compound semi-annually, but it’s not required and you’d have to read the fine print of the mortgage terms to understand what the compounding frequency is.

Since in our example, we’re working with a fixed rate mortgage, we’ll assume semi-annual compounding.

Let’s look at how to calculate payments.

Calculating Payments

Step 1: Calculate the Effective Interest Rate

The rate you’re quoted from a bank is the nominal rate which does not incorporate the effects of compounding. To get an interest rate which incorporates compounding, we’ll calculate the effective rate.

The formula to calculate the effective rate is:

re=(1+rn)n−1r_e = (1 + \frac{r}{n})^n - 1

Where:

rr is the nominal rate
nn is the compounding frequency

In our case we’re computing the annual effective rate of interest and since we’re compounding semi-annually, n=2 because we compound twice per year.

re=(1+0.02342)2−1=0.02353689\begin{aligned} r_e &= (1 + \frac{0.0234}{2})^2 - 1 \ &= 0.02353689 \end{aligned}

We’re being tricked! 2.34%, compounded semi-annually is actually 2.36% per year, not 2.34%. Sneaky banks.

Cool, we’ve got our effective annual rate, now let’s move onto different payment frequencies.

Step 2: Calculate the Monthly Rate

To get our monthly payments, we first need to determine what interest rate, when compounded monthly is equal to our effective annual rate? We’ll call this our monthly periodic rate or just monthly rate for short.

Mathematically, we can solve this like so:

rm=(1+re)112−1=(1+0.02353689)112−1=0.001940561161\begin{aligned} r_m &= (1 + r_e)^{{\frac{1}{12}} - 1 \ &= (1 + 0.02353689)}{\frac{1}{12}} - 1 \ &= 0.001940561161 \end{aligned}

Plugging in our effective annual rate, we get a monthly rate of roughly 0.19%.

Step 3: Calculate the Monthly Payment

With the monthly rate in hand, we can finally calculate what the monthly principal and interest payments would be using another standard formula in finance:

pm=rmA1−(1+rm)−mp_m = \frac{r_m A}{1 - (1 + r_m)^{-m}}

Where:

rmr_m is the monthly rate we just calculated
AA is the amount we’re seeking to borrow
and mm is the number of months in we’re amortizing the loan over

If we plug-in our numbers we get:

pm=rmA1−(1+rm)−m=0.194056%×$700,0001−(1+0.194056%)−360=$2,703.90\begin{aligned} p_m &= \frac{r_m A}{1 - (1 + r_m)^{{-m}} \ &= \frac{\text{0.194056%} \times \text{$700,000}}{1 - (1 + \text{0.194056%})}{-360}} \ &= \text{$2,703.90} \end{aligned}

If you try this on a calculator, ensure you use at least 8 decimal points to ensure you’re accurate to the penny.

Let’s check out math against RBC’s mortgage payment calculator!

🎉 Boo ya!

Bonus: Different Payment Frequencies

Ok, we’ve got monthly payments, but banks offer lots of different options for payment frequencies: semi-monthly, bi-weekly, weekly, accelerated bi-weekly, and accelerated weekly. How do these work?

Semi-monthly: rm2\frac{r_m}{2} Take the monthly payment we have and divide it by 2 since we’ll be making two payments per month. In our example, this would be $1,351.95.
Bi-weekly: rm1226\frac{r_m 12}{26} Similar to the above, we annualize the monthly payments and divide by 26 (the number of bi-weekly periods in a year). In our example, this would be $1,247.96.
Weekly: rm1252\frac{r_m 12}{52} Annualize the monthly payments and divide by 52 (number of weeks in a year). In our example, this would be $623.98.
Accelerated Bi-Weekly: rm2\frac{r_m}{2} this one is interesting. "Accelerated" payments are calculated by assuming there are only 4 weeks in a month. Since you’ll be making this payment 26 times a year, you are making an additional monthly payment per year. This lets you shave a bit off of the total interest (hence "accelerated"). In our example, this would be the same as our semi-monthly payment $1,351.95.
Accelerated Weekly: rm4\frac{r_m}{4} similar to the accelerated bi-weekly scenario except here since you pay 52 weekly payments, by the end of the year, you have paid 4 additional weekly payments, i.e. a complete monthly payment. In our example, this would be $675.98.

Show me the Code!

I promised you some code, so here’s a function that encapsulates everything discussed above. It calculates the mortgage payment given a nominal rate, amount, amortization period and payment frequency.

Here’s that exact function working as a calculator you can use (note that the rounding is a little off as JavaScript’s default doesn’t follow round half-up).

Generating an Amortization Schedule

Calculating the Periodic Rate

We’ve done payments but what if we wanted to create an amortization schedule showing all the future payments? The good news is, we have almost everything we need already.

The only thing we’re missing is the periodic interest rate which tells us exactly how much interest is calculated on each payment. To get this rate, we’ll do the same calculation we used when calculating the monthly rate. We’re trying to answer the same question we did for the monthly rate: what interest rate, when compounded at the same rate as our monthly frequency, is equal to our effective annual rate?

rp=(1+re)1n−1r_p = (1 + r_e)^{\frac{1}{n}} - 1

The difference here is that nn will reflect the number of periods in a year corresponding to the selected payment frequency (24 for semi-monthly, 52 for weekly, 26 for bi-weekly and so on).

If, for example, we were using a weekly payment frequency, our periodic weekly rate would be:

rw=(1+re)152−1=(1+0.02353689)152−1=0.0004474879479\begin{aligned} r_w &= (1 + r_e)^{{\frac{1}{52}} - 1 \ &= (1 + 0.02353689)}{\frac{1}{52}} - 1 \ &= 0.0004474879479 \end{aligned}

Generating the amortization schedule

First, we’ll tweak our code previous code a little so we have a function that returns all the details we’d need to create an amortization schedule.

Now we’re ready to generate the amortization schedule:

And that’s it! Here’s the same calculator above but with a newly minted amortization schedule.

Mortgage Payment Calculator

Amount

Interest Rate (Nominal)

Rate Type

Fixed

Amortization (in years)

Payment Frequency

Your monthly payment would be

$2,703.90

Here’s what your first 5 payments look like:

Period	Starting Balance	Payment	Interest	Principal	Ending Balance
0	$700,000.00	$2,703.90	$1,358.39	$1,345.50	$698,654.50
1	$698,654.50	$2,703.90	$1,355.78	$1,348.12	$697,306.38
2	$697,306.38	$2,703.90	$1,353.17	$1,350.73	$695,955.65
3	$695,955.65	$2,703.90	$1,350.54	$1,353.35	$694,602.29
4	$694,602.29	$2,703.90	$1,347.92	$1,355.98	$693,246.32

Thanks

Thank you to Alan Marshall at York University who has maybe the only other post that tries to explain Canadian mortgages.

Finally, thanks for sticking all the way to the end!