I’m trying to put together a google sheets reference chart for mortgage payments but I’m having a hard time figuring out the cmhc portion.

The basics of the chart is having a row for the price (200k-900k, incrementing 25k per row), down payment amount by %(set as a global in a field above), the cmhc insurance amount (price amount - down payment * CNBC rate based on down payment %; is this right?), monthly payment amount (using the PMT function on sheets, this is close but not matching to other calculators). My input for the PMT is the interest %/12, the lifetime (300 monthly period, 25 years), the total amount of the mortgage (price - down payment + CNBC insurance). This is mostly working but is still off by 10s-100s on the monthly payment. Is there something like graduated % in calculating the cmhc amount that I’m missing?

  • Dalvoron@lemm.ee
    3·
    10 months ago

    I believe the interest per month should be ((12th root of (1 + %)) - 1) if it matches how mortgage rates are calculated in my country, and if it meant to compound. If you can’t find a suitable root function in your software, a power of 1/12 will also work. Everything else looks fine to me, but insurance also works differently for me (calculated separately but probably ends up the same).

    There are full amortisation formulas you can implement also.

    • ShaggyBlarneyOPEnglish
      1·
      10 months ago

      You are correct, the interest rate should be (1 + r / n)^n(t) - 1, where r = interest, n = number of periods, t = terms. This is handled by the PMT function in sheets and only requires the rate to be entered as r/n or 5.84/12. I did an earlier version of the chart without the cmhc interest included and it was producing spot on values that matched agaisnt other available mortgage calculators. My issue comes when adding in there cmhc amount. At this point the chart works as a quick reference, which is what I was looking for, but it would be good to get it right.

  • tleb
    1·
    10 months ago

    CHMC should just be based on the down payment %, for example 4% of the loan at 5% down. The CHMC premium is then added to the loan amount.

    So for a house that’s 200k, with 5% down your loan is 190k, + 4% for cmhc, 197.6k. Then do your payment calc as normal on 197.6k.

    Maybe you just have an order of operations issue? Or are calculating it off of the purchase price and not the loan?

    • ShaggyBlarneyOPEnglish
      1·
      10 months ago

      This at least confirms I’m understanding how cmhc is included. I’ll have to double check everything.