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?
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?
This at least confirms I’m understanding how cmhc is included. I’ll have to double check everything.