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

• TypeScript code to generate payments
• TypeScript code to generate an amortization schedule
• Google Spreadsheet with mortgage calculator and amortization schedule

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 calculate mortgage payments due to some nuances with Canadian mortgages.

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:

Where:

• $r$ is the nominal rate
• $n$ 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.

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:

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:

Where:

• $r_m$ is the monthly rate we just calculated
• $A$ is the amount we're seeking to borrow
• and $m$ is the number of months in we're amortizing the loan over

If we plug-in our numbers we get:

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: $\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: $\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: $\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: $\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: $\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.

// TypeScript
function calculateMortgagePayment(
  nominalRate: number,
  amount: number,
  amortizationMonths: number,
  paymentFrequency:
    | "monthly"
    | "semiMonthly"
    | "biWeekly"
    | "weekly"
    | "acceleratedBiWeekly"
    | "acceleratedWeekly"
) {
  const effectiveRate = Math.pow(1 + nominalRate / 2, 2) - 1;
  const monthlyPeriodicRate = Math.pow(1 + effectiveRate, 1 / 12) - 1;
  const monthlyPayment =
    (monthlyPeriodicRate * amount) /
    (1 - Math.pow(1 + monthlyPeriodicRate, -amortizationMonths));

  switch (paymentFrequency) {
    case "monthly":
      return monthlyPayment;
    case "semiMonthly":
    case "acceleratedBiWeekly":
      return monthlyPayment / 2;
    case "biWeekly":
      return (monthlyPayment * 12) / 26;
    case "weekly":
      return (monthlyPayment * 12) / 52;
    case "acceleratedWeekly":
      return monthlyPayment / 4;
  }
}

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?

The difference here is that $n$ 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:

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.

// TypeScript
export type PaymentFrequency =
  | "monthly"
  | "semiMonthly"
  | "biWeekly"
  | "weekly"
  | "acceleratedBiWeekly"
  | "acceleratedWeekly";

export function calculateMortgageDetails(
  nominalRate: number,
  amount: number,
  amortizationMonths: number,
  paymentFrequency: PaymentFrequency
) {
  const effectiveRate = Math.pow(1 + nominalRate / 2, 2) - 1;
  const monthlyPeriodicRate = Math.pow(1 + effectiveRate, 1 / 12) - 1;
  const monthlyPayment =
    (monthlyPeriodicRate * amount) /
    (1 - Math.pow(1 + monthlyPeriodicRate, -amortizationMonths));
  let periodicRate = 0;
  let periodicPayment = 0;
  switch (paymentFrequency) {
    case "monthly":
      periodicPayment = monthlyPayment;
      periodicRate = monthlyPeriodicRate;
      break;
    case "semiMonthly":
      periodicPayment = monthlyPayment / 2;
      periodicRate = Math.pow(1 + effectiveRate, 1 / 24) - 1;
      break;
    case "biWeekly":
      periodicPayment = (monthlyPayment * 12) / 26;
      periodicRate = Math.pow(1 + effectiveRate, 1 / 26) - 1;
      break;
    case "weekly":
      periodicPayment = (monthlyPayment * 12) / 52;
      periodicRate = Math.pow(1 + effectiveRate, 1 / 52) - 1;
      break;
    case "acceleratedBiWeekly":
      periodicPayment = monthlyPayment / 2;
      periodicRate = Math.pow(1 + effectiveRate, 1 / 26) - 1;
      break;
    case "acceleratedWeekly":
      periodicPayment = monthlyPayment / 4;
      periodicRate = Math.pow(1 + effectiveRate, 1 / 52) - 1;
      break;
  }

  return {
    effectiveRate,
    periodicPayment,
    periodicRate,
  } as const;
}

Now we're ready to generate the amortization schedule:

// TypeScript
export interface AmortizationPeriod {
  period: number;
  payment: number;
  principal: number;
  interest: number;
  startingBalance: number;
  endingBalance: number;
}

export function generateAmortizationSchedule(
  nominalRate: number,
  amount: number,
  amortizationMonths: number,
  paymentFrequency: PaymentFrequency
) {
  const schedule: AmortizationPeriod[] = [];
  const { periodicPayment, periodicRate } = calculateMortgageDetails(
    nominalRate,
    amount,
    amortizationMonths,
    paymentFrequency
  );
  let balance = amount;
  let period = 0;
  while (true) {
    const interest = balance * periodicRate;
    let payment = periodicPayment;
    let principal = payment - interest;
    let startingBalance = balance;
    balance = balance - principal;

    if (balance < 0) {
      // Final payment exceeded balance. We leave interest untouched but adjust
      // the payment to cover the remaining principal.
      payment = payment + balance;
      principal = payment - interest;
      balance = 0;
    }

    schedule.push({
      period,
      payment,
      principal,
      interest,
      startingBalance,
      endingBalance: balance,
    });

    if (balance === 0) {
      break;
    }
    period++;
  }
  return schedule;
}

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

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!