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?
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.
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.