Come and discuss anything related to personal finance, directly or indirectly, with other Canadians!
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.
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.