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 and statements in Canada.
A brief search reveals that Canada is of course missing from https://en.wikipedia.org/wiki/Open_banking
Don't be fooled by the "open banking" propaganda, we've had it in the EU for years and we're still scraping web apps like in the old days.
<Rant> I tried to get API access to my bank account and was told by my bank that Open Banking is not for physical persons. I tried to the same for my nonprofit and was told that only licensed PSD2 TPPs ("third-party providers") can use it. The national bank issues those licenses, but provides no information on what the process for obtaining one is. I called some friends and got in touch with someone at a major payment services provider, who explained that to get their license, the national bank insisted on them having essentially unlimited-coverege insurance - something no insusrer in the country was even willing to sell at any price. They managed to get the insurance from elsewhere in the EU and whole process cost them "six figures". Even after that, you still don't get full access to their own accounts, because the whole API is built on the idea of connecting customers with fintech apps, so there are a bunch of limitations intended for privacy and security that don't make any sense in a "I want full access to my own account" scenario. All the advice I got was "just keep scraping". How the fuck do they dare call that "open" banking?!? </Rant>
Crypto-currency was a test-run for what happens when you put a financial transaction API on the public Internet. And those servers were written by smart people with no regulatory compliance to worry about.
Cryptocurrency is so wildly different from normal money in so many ways, that any attempt to look at just the "open API" aspect of it is futile.
Or do you see any conclusions about "money APIs" that can be reasonably made from crypto?
Result is a massive ecosystem, so yes it proves the point (regulation aside).
I wouldn't even trust the API with the numbers. The CSV and OBX published by a number of banks I used were always broken in some interesting but obvious ways. Minutes replacing months (someone mixed up %m/%M), values with cents appended twice for international transactions, unique IDs in OBX rolling over every month, and many others. Unless the same API is used for their legal reporting, I don't have a reason to expect it's better. Scraping the pages was way more reliable.
Believable.
Literally in the news as we speak!
https://globalnews.ca/news/10195005/canada-banking-changes-2...
What it ends up looking like, who knows.
hah! Nice. Although, $100 says we're gonna fsck it up.
Weird, is Open Banking only a thing in Turkiye? I have all my bank accounts added to my primary bank’s app, though it’s read only and I can view balances and transactions.
Going out on a limb to say ironically it's easier for the developing world to implement this stuff because they're so much less entrenched to begin with. Have also seen this in South Africa for example.
Canada is working on Open Banking from what information is out there - although it will be important to confirm that the Canadian interpretation of Open Banking is the same or compatible with other nations, which in no way is guaranteed just because it's called the same thing.
Consulted in the space about 3 years ago. Regulatory powers lack tooth. Most large banks are not ready (i.e. poor security, complex backend, no scalability, etc.) and are scrambling to find solutions while begging for my time. Also, lots of discussion and jostling and fear of what might happen when it goes live. No one can truly agree on the legal framework as well so feels like much work to be done.
Was also part of the implementation phase in the UK and the regulatory powers there definitely sped things up.
What is your mortgage amount and what is rate?
Say your mortgage is 300,000 and rate 5%. You would pay 15,000 in interest. Subtract from your total yearly payment say 25,000. You pay back 10,000 a year divide by 12 for months or payment period to get the amount.
Canadian mortgages are a bit different than the US.
First, they renew their interest rates every 5 years over a 20-30-40 mortgage. Think of it as mandatory refinancing.
Unlike the US where a homeowner can lock in a low interest rate for a very long time.
That sounds terrifying. Do people just pay them off within quickly if interest rates rise?
It would be terrifying for any bank to give people 30 thirty year loans at historically low interest rates (because of the interest rate risk - rates have nowhere to go but up, and the bank could easily be upside down for most of the loan), so the U.S. government subsidizes/backs most mortgages (fannie and freddie). This tends to increase the cost of housing while taxpayers are taking on the interest rate risk instead of the banks.
The U.S. is an anomaly with these loans. Most countries don't do this 30 year fixed low rate mortgages like the U.S. It simply doesn't make sense for lenders to underwrite long loans at low rates.
Agreed, but technically, the loan is written at time of interest, and for that period it can provide a certain kind of stability in housing prices as well.
As 2008 taught us, other ways can come up to try and make money.
A lot of mortgages here in South Africa do this every three months. We have ce tral bank interest rates which are relatively stable (except recently), and mortgages are given out as a function of that rate (e.g. rate + 2.4%). After the central bank rate changes, at the next three month period they use the new rate to adjust your mortgage payments.
Variable rate mortgages were a better bet 88% of the time between 1950 and 2000:
* https://wowa.ca/static/fixed-vs-variable-study.pdf
Post-2000 variable did fairly well as well.
It's the recency bias of the last 1-2 years that have people really freaking out about it.
you're usually fixed to a specific payment during that 5 year term. you can't pay it faster without paying a penalty. the bank expects their profit of X and by God they're going to get it.
during the re-up you can throw down extra money, but that's because it's an open contract, essentially.
Paying off mortgages quickly is often a topic, how much faster it happens compared to the US is another thing.
Typically, you decide on how you are going to approach the interest rate, and for how long. Owners can lock in for a fixed interest rate, or a variable rate that changes with prime. Term is most often 5 years, but latesly shorter or more flexible mortgages are more popular, sometimes between 2-4 years, and some folks who might take a risk on a 5+ year mortgage rate.
The variable rate mortgages out performed fixed rate mortgages for nearly 15-20 years. The tables turned in the past year or two.
There is a twist in Canadian law regarding mortgages. Quoting from the page linked below. --- Canada’s Interest Act states the following about amortizing mortgages: “... the principal money and the rate of interest chargeable on that money, calculated yearly or half-yearly, not in advance.” --- This page does go into the detail a little more. https://wowa.ca/how-is-mortgage-interest-calculated
It may not be authoritative but you can start with https://laws-lois.justice.gc.ca/eng/acts/i-15/page-1.html which should be.
FWIW, being off by 10s of dollars sounds to me like different ideas of how to count days. Banks sometimes use calendar days, sometimes business days, and sometimes a fictitious calendar with 12 months of 30 days each.
Yes, you'd call that the calendar convention if I remember correctly. The one you are referencing is called 30/360 for 30 days in a month, 360 days in a year.
Would different departments within a bank use different calendar conventions?
Absolutely. Even within one department, since accounts often originated from different institutions.
Good question! I don't know, though it wouldn't surprise me if different products offered by the same bank used different calendar conventions. Calendar conventions are an accounting practice though, so different departments wouldn't interpret the same loan differently due to differing conventions.
BTW - the fictitious calendar is very often used in bond repayments - e.g. https://www.lawinsider.com/dictionary/bond-payment-dates
It has historically been used to allow for bonds to have uniform repayments across maturities (when the principal of a bond becomes due) - useful in the context of a bank wanting to hedge risks such as interest rate risk, repayment risk (if the bond is callable it can be paid back early, which means the bondholders have cash yielding ~0% instead of whatever the bond yield was)
Mortgages, though not traditional bonds (in that it's not a sophisticated company borrowing) are, depending on the jurisdiction, treated as bonds because they get packaged up and sold as bonds - the underlying bit of financial engineering that, after serious perversion, gave us the 2008 credit crisis
Some sample calculations at:
* https://www.bondsavvy.com/bonds/accrued-interest-calculation
See also perhaps:
* https://www.investopedia.com/terms/d/daycount.asp
Having been through this exercise as a former employee of a bank calculating this exact thing, this makes sense to me so is hopefully helpful for the poster.
Anecdotally, the bank often cannot tell you how much. It has come up multiple times on /r/personalfinancecanada that the bank isn't really sure or generates a few different calculations. I also worked for a mid sized Canadian bank where the advisors couldn't consistently do it either.
If you're on a fixed rate mortgage it seems like this should be trivial...?
Variable rate throws it out of whack of course.
Having a mortgage with 3-months variable rate bank sends a new schedule everytime rate changes.
New schedule is calculated with new rate and consists of payments till the end of the term, with additional info about interest and principal amounts.
Same if you do additional payment into the principal - they will give you a new schedule.
That seems odd given that you can walk into your branch any time and get a payout statement that will tell you a total outstanding number. The statement is valid for the next 30 days or whatever, and any fees or penalties are itemized on it— anyone who has paid off their mortgage due to either windfall or sale will have gone through this process.
Is this something particular to how Canada does mortgages? In the US, a bog standard mortgage is 12 payments a year, with all interest for the month accruing on the 1st day of each month. It's pretty trivial to put that into Excel and use the IPMT function (and related ones like PMT) to spit out the interest being paid in a given month. It's always been dead-on accurate for me.
Maybe this is just one little upside of mortgage terms in the US being dictated mostly by the gov't through fannie mae and freddie mac.
While open banking is on it's way in Canada it still might be a few years until the API is available.
Mortgage Calculators are sadly something that appear to need to be build by hand in Excel
I was in the same boat. I ran into similar discrepancies until I realized that I was not accounting for the number of days in the month correctly.
One option is to call the mortgage department and ask them to provide the split. When I asked the agent why I couldn't see it online, she mentioned their system is not setup to show this information. No idea why. Pretty frustrating.
Can you do that anywhere else? Even under the EU's "open banking" system, this is basically impossible unless you use an intermediate that charges absurdly high fees, because they themselves had to pay hugr licensing and insurance fees to be granted access to the "open" APIs. The only other way is scraping, which I imagine is just as possible in Canada as everywhere else.
Are you using the correct date convention? That can cause small differences in both the duration of the interest accrual period as well as the daily interest rate for that period
If they aren't providing you complete information there is probably a Canadian authority you can reach out to, though you may just need to go higher up the food chain at your bank - accounting can be a little arcane and the people you are speaking to may simply not know.
That being said being off by tens of dollars is absurd, the error is likely in your calculations, and I feel it is their duty to make how they calculate your interest owed plain to you.
Apologies if you're already aware... In Canada you'll get an annual statement showing how much principal is paid off. So you can interpolate future payments from that. (Of course, it could be better)
First of all: I fully agree with you. The calculation should be listed explicitly in the mortgage documents.
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.
I completely disagree. Then you're just handing them a mechanism to make this as complicated as they possibly can. Taxes should be simple enough to be fully understood by the people paying them.
I'm not some algorithmic tool for the government to abuse when it's failed to balance it's budgets and I should not _need_ software to figure out something so basic as a deduction in wages.
It's a shame there isn't a law forcing the government to make this possible.
I don't understand what you mean. You're beholden to the law regardless of whether you understand it. The government ought to be required to create a reference implementation if for no purpose other than to prove that it is indeed possible to implement.
the government should be barred from implementing a law or regulation that requires "reference implementation " to prove it is "possible to implement"
If it needs such a thing it is too complex and should be repealed
Over in The Netherlands we're seeing the opposite happen. Seemingly trivial laws are getting rejected because the responsible Ministry considers it "too difficult". In reality it's most likely just politically inconvenient.
For example, it was deemed "impossible" to adjust student loan interest rates, because the software would take two years to modify. Either they are completely incompetent, or they are deliberately lying for political reasons.
So yeah, refusing a law because it is "too complex" won't work either.
Lots of our dutch bureaucracies have comprehensively failed and are barely treading water. There is no capacity to do the current job, let alone any new changes.
The only way out is by accepting that the current state is unacceptable, and working on a long and slow fix, during which we don't say "this is unacceptable therefore we require immediate changes". This will fuck people over. But not making those changes properly will fuck people over even more.
We might be on an administrative brink. That takes a radical approach to fix.
OK, well, in spirit I can see how that sentiment arises. For practical purposes, though, no matter how simple a calculation, if a government requires that I do it, I want to see precisely what it's demanding. It is unreasonable to be put in the position of guessing.
You are speaking about different objectives. Quite apart from the basic "(how) does it work logically" which published formulas address, simplicity can be an objective in itself.
For example, Germany has one of the most complex income tax codes in the world, with an average compliance burden of 136 hours[1]. That's a hit to national competitiveness and GDP, and worth addressing in its own right.
[1] https://taxfoundation.org/blog/germany-tax-g7/
Ah yes, because the current system is incentivising them to keep it simple... otherwise we'll vote for the simple taxes party... which party is that again?
You're right, and it's extremely hard to undo.
Once you have that complexity, you'll always be taking away something from somebody by simplifying.
So all simplifications end up as additional "if" clauses, defeating the purpose.
All laws should be expressed in code. It would quickly show how many either conflict with each other or are incoherent.
And then there's other comments here that mocks people who think law as code hence you can cheat/glitch through it. They say law in the end is run by humans and there's something called "intent" that will still catch you. Tbf I lean more to law as code side myself and this intent thing sounds like arbitrary enforcement.
Arbitrary enforcement is a requirement for fair laws. It's why the quality of judges matters.
This exists, but I haven't dug deep enough to know better: https://github.com/CatalaLang/catala.
It wouldn't even work if the universe was expressed as data.
Unfortunately there will be an input layer that requires predicates or measurements that cannot avoid ambiguity.
Ignoring that, there's Godel's Incomplete Theorem to deliver the final blow. If integer arithmetic cannot be complete and consistent, a set of laws governing society has no hope. If you disagree, you'd at least have to admit that such a set of laws could not include integer arithmetic, and could certainly be no more complex than integer arithmetic. I'd posit that such a restriction renders such a project impossible.
We've chosen completeness (every action can be rendered legal or illegal) over consistency. The alternative - consistency - would make some actions legally undecidable.
It could be a github repo.
There are likely some digital government projects and software providers who's software might be a good fit for this kind of thing.
Why stop there, one monorepo could hold all the laws and receive PRs from the ppl, cutting down the middle man (politicians) leaving just the bureaucracy
This is a joke - I doubt it will work, fewer people to fight over interpretations :)
I remember in my pursuit of learning about change management and digital transformation, someone tried to explain that too many bureaucracies ultimately have one goal - to ensure their survival at any cost. Maybe not so different than politicians in that way.
It's worth noting that the Government of Canada and various other gov orgs do have GitHub repos where they already host a variety of algorithms, configs, the design system, and a few others, so it would be good to add something like this.
https://github.com/canada-ca https://github.com/statcan
The Bank of Canada even has one where they host various economic models
https://github.com/bankofcanada
This is nice to see and learn about!
Germany has been publishing standardized flowcharts with named variables and formulas for payroll calculation for a long time (at least since the 1970s — hence the notation employed): https://www.bundesfinanzministerium.de/Content/DE/Downloads/...
That's very cool, but that's taxes only.
For social security, you have to get certified by a completely different set of bureaucrats with partially incompatible requirements:
https://gkv-ag.de/
The simplifications, they are additional! (Usually.)
The French invented their own DSL for their taxes: https://github.com/MLanguage/mlang
The issue is too pervasive, having a formula published won't solve it.
You will need the historical formulas, as well as all the underlying data, not even accounting for the publication delays and errors, missing informations, discretionary classifications, etc.
What we need is all that data and code in an introspectable flow of code and data:
<downvote>a blockchain, containing both your data and the smart contract used to perform the computations</downvote>.
Same here. I just take the form