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.
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.
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
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.
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.
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.
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>
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 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.
> Also don't get me started that I can't use an api to get my account details and statements in Canada.
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.
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.
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 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.
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.
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.
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)
Also don't get me started that I can't use an api to get my account details and statements in Canada.