It's a shame that we don't have open formulas in code form published by the government. The only way that I know how to do this reliably is using the web form provided by the CRA here: https://www.canada.ca/en/revenue-agency/services/e-services/... As you can imagine, doing this by hand is quite painful and error-prone.
I want something similar for my mortgage calculation. For example I cannot get my bank (TD Canada) to inform me of how much each payment goes towards my principal. I'm pretty sure I've calculated it properly, however the number is often in the 10's of dollars off from what the bank reports my outstanding balance is after a payment is made. Also don't get me started that I can't use an api to get my account details an…
I've also struggled through figuring it out on my own, and the two things that got me to less than a dollar of difference were: 1) "Mortgage" in Canada is a specific legal term used for fixed term mortgages that compounds every 6 months. Therefore, to get your effective annual you take you have to convert it: ((1 + nom_rate/2)^2)-1 2) You then take your effective rate, divide by the days in the year to get your daily rate. You then take your daily rate, multiply it by your outstanding principle, and multiply it by the number of days since your last payment. That result is the amount of interest you pay, and the rest of your payment goes to pay down principle.
In practice, I have a monthly payment, and just divide my annual effective rate by 12, and call it close enough. The extra precision of daily vs monthly isn't worth the extra complexity in the spreadsheet.