6 ms·

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 princip

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.

MattGaiser 3y ago
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.
- bluefirebrand 3y ago
  If you're on a fixed rate mortgage it seems like this should be trivial...? Variable rate throws it out of whack of course.
  - ponector 3y ago
    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.
- mikepurvis 3y ago
  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.
ipaddr 3y ago
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.
- prosaic-hacker 3y ago
  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 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 https://laws-lois.justice.gc.ca/eng/acts/i-15/page-1.html which should be.
- j45 3y ago
  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.
  - guhidalg 3y ago
    That sounds terrifying. Do people just pay them off within quickly if interest rates rise?
    - mikem170 3y ago
      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.
cperciva 3y ago
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.
- fadesibert 3y ago
  BTW - the fictitious calendar is very often used in bond repayments - e.g. https://www.lawinsider.com/dictionary/bond-payment-dates 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
  - throw0101d 3y ago
    > BTW - the fictitious calendar is very often used in bond repayments - e.g. https://www.lawinsider.com/dictionary/bond-payment-dates https://www.lawinsider.com/dictionary/bond-payment-dates Some sample calculations at: * https://www.bondsavvy.com/bonds/accrued-interest-calculation https://www.bondsavvy.com/bonds/accrued-interest-calculation See also perhaps: * https://www.investopedia.com/terms/d/daycount.asp https://www.investopedia.com/terms/d/daycount.asp
- jbs769 3y ago
  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.
- burnished 3y ago
  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.
humanlion87 3y ago
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.
cachvico 3y ago
Also don't get me started that I can't use an api to get my account details and statements in Canada. Anyone know if there's any ongoing effort to this effect - i.e. to force banks to implement a common API for querying data held in their accounts? A brief search reveals that Canada is of course missing from https://en.wikipedia.org/wiki/Open_banking https://en.wikipedia.org/wiki/Open_banking
- itsoktocry 3y ago
  >Anyone know if there's any ongoing effort to this effect - i.e. to force banks to implement a common API for querying data held in their accounts? Literally in the news as we speak! https://globalnews.ca/news/10195005/canada-banking-changes-2024/ https://globalnews.ca/news/10195005/canada-banking-changes-2... What it ends up looking like, who knows.
  - cachvico 3y ago
    hah! Nice. Although, $100 says we're gonna fsck it up.
- j45 3y ago
  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.
- ensemblehq 3y ago
  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.
- franga2000 3y ago
j45 3y ago
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
rootusrootus 3y ago
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.
franga2000 3y ago
> 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.
ape4 3y ago
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)
burnished 3y ago
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.
abe_m 3y ago
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.

MattGaiser 3y ago

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.

bluefirebrand 3y ago
If you're on a fixed rate mortgage it seems like this should be trivial...? Variable rate throws it out of whack of course.
- ponector 3y ago
  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.
mikepurvis 3y ago
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.

ipaddr 3y ago

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.

prosaic-hacker 3y ago
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 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 https://laws-lois.justice.gc.ca/eng/acts/i-15/page-1.html which should be.
j45 3y ago
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.
- guhidalg 3y ago
  That sounds terrifying. Do people just pay them off within quickly if interest rates rise?
  - mikem170 3y ago
    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.

cperciva 3y ago

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.

fadesibert 3y ago
BTW - the fictitious calendar is very often used in bond repayments - e.g. https://www.lawinsider.com/dictionary/bond-payment-dates 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
- throw0101d 3y ago
  > BTW - the fictitious calendar is very often used in bond repayments - e.g. https://www.lawinsider.com/dictionary/bond-payment-dates https://www.lawinsider.com/dictionary/bond-payment-dates Some sample calculations at: * https://www.bondsavvy.com/bonds/accrued-interest-calculation https://www.bondsavvy.com/bonds/accrued-interest-calculation See also perhaps: * https://www.investopedia.com/terms/d/daycount.asp https://www.investopedia.com/terms/d/daycount.asp
jbs769 3y ago
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.
burnished 3y ago
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.

humanlion87 3y ago

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.

cachvico 3y ago

Also don't get me started that I can't use an api to get my account details and statements in Canada. Anyone know if there's any ongoing effort to this effect - i.e. to force banks to implement a common API for querying data held in their accounts? A brief search reveals that Canada is of course missing from https://en.wikipedia.org/wiki/Open_banking https://en.wikipedia.org/wiki/Open_banking

itsoktocry 3y ago
>Anyone know if there's any ongoing effort to this effect - i.e. to force banks to implement a common API for querying data held in their accounts? Literally in the news as we speak! https://globalnews.ca/news/10195005/canada-banking-changes-2024/ https://globalnews.ca/news/10195005/canada-banking-changes-2... What it ends up looking like, who knows.
- cachvico 3y ago
  hah! Nice. Although, $100 says we're gonna fsck it up.
j45 3y ago
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.
ensemblehq 3y ago
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.
franga2000 3y ago

j45 3y ago

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

rootusrootus 3y ago

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.

franga2000 3y ago

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

ape4 3y ago

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)

burnished 3y ago

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.

abe_m 3y ago

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.