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?