September 18, 2017 at 10:00 am
I don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2017 at 10:51 am
ScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..
Frankly, I'm amazed this has garnered so much attention. While not all financial institutions that handle mortgages do exactly the same thing (at least, down at the query detail level anyway), regulations as well as common sense have dictated the need to compute all values in actual "smallest monetary unit" since at least two decades ago, if not more, and the computational methods banks use have been fairly well known for considerably longer. The only calculation you usually have to worry about precision on is when the annual interest rate doesn't evenly divide by 3 (due to their being 12 months in a year, and 3 is the LCD for 12). FLOAT is sufficient for this, and so is DECIMAL, because the nature of the process eliminates ALL of the arguments about precision, because while the process is iterative, the calculation is a one at a time to the nearest smallest monetary unit.
When I say "evenly divide", I'm not talking about getting an integer result, but simply a finite decimal value with relatively limited overall precision. What might be more useful to look at with respect to precision is whether or not there are specific interest rates, in combination with specific high values (let's think mortgages on a high-rise or a football stadium), where the FLOAT representational issue or the DECIMAL computational limitation causes inaccuracy. On interest rates, probably ought to test everything from 1/8th of a percent up to 30 percent, every 8th of a percent, and start looking at mortgage principal amounts of a million dollars or more, up to a couple hundred million. I don't have time at the moment, but it might be an interesting exercise.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 18, 2017 at 10:52 am
ScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..
I have two problem with that:
(i) Compounding interest daily for long term loans like mortgages tends (on this side of the pond) to be regarded as an indicator that the supplier is a loan shark.
(ii) In some countries (I suspect all 1st world countries plus a large number more) there are legal requirements that every time an amount of interest is added to the unpaid capital the resulting new balance outstanding must be a whole number of the lowest currency unit in the currency in which the balance is expressed and that adding the interest to the capital can only bedone at points in time speified in the mortgage contract. That means the result has to be a whole numbe of Euro cents if the loan is in Euros and Sterling pence if it's in British pounds (so a whole number of US cents if its in US dollars if the USA has similr regulations to the rest of the civilised world).
Tom
September 18, 2017 at 11:05 am
TomThomson - Monday, September 18, 2017 10:52 AMScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..Compounding interest daily for long term loans like mortgages tends (on this side of the pond) to be regarded as an indicator that the supplier is a loan shark.
Find me a bank that DOESN'T do that !!! Most of the time, they keep that hidden from you, but I'm not aware that there ARE any that don't. Of course, you already stated the reason why most of them hide that fact (or at least try to, anyway)...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 18, 2017 at 11:16 am
TomThomson - Monday, September 18, 2017 10:52 AMScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..I have two problem with that:
(i) Compounding interest daily for long term loans like mortgages tends (on this side of the pond) to be regarded as an indicator that the supplier is a loan shark.
(ii) In some countries (I suspect all 1st world countries plus a large number more) there are legal requirements that every time an amount of interest is added to the unpaid capital the resulting new balance outstanding must be a whole number of the lowest currency unit in the currency in which the balance is expressed and that adding the interest to the capital can only bedone at points in time speified in the mortgage contract. That means the result has to be a whole numbe of Euro cents if the loan is in Euros and Sterling pence if it's in British pounds (so a whole number of US cents if its in US dollars if the USA has similr regulations to the rest of the civilised world).
(i) Not at all! Every loan you get from any bank, including the most reputable, is virtually certain to have interest compounded daily. (I have an interest-bearing checking account and even it is computed daily, although it is not applied until the end of the month, i.e., computed daily but compounded monthly).
Admittedly it's more complicated in a mortgage, in that it appears to be non-computed, but that's because you're paying interest up front, but how that up-front interest is calculated is still critical.
(ii) Quite agree, and that was my point. All this caterwauling about umpteen decimal places is pointless. The interest will be computed daily anyway, and thus extra decimal places don't really matter. I worked in the finance area for a while (not happily, but I did it!), and I never saw or heard about anything beyond 4 decimal places at any American bank (some go to 5 places in order to round back to 4 in order to round back to 2 -- have to get that extra penny of interest!).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2017 at 11:19 am
ScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..
... The only calculation you usually have to worry about precision on is when the annual interest rate doesn't evenly divide by 3 (due to their being 12 months in a year, and 3 is the LCD for 12).
That doesn't seem relevant either, since interest is a daily rate calculated as yearly interest rate / 365 (yes, even during leap years).
Although, again, mortgages can have different calculations and different terms. Still, if you pay extra principle during the month, it lessens the interest due next month, which to me demonstrates that it still must be a daily calc, even if it does not compound daily.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2017 at 3:34 pm
Along similar lines, I've noticed that many brokerage firms use decimal places for fractional stock holdings, although they display only 3 or 4. Thus, it's often impossible to get an exact value of a stock account without looking at the brokerage account itself.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2017 at 5:16 pm
ScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..
But it's been proven to be a problem over the long haul. Ask Granny and her spreadsheet. 6 decimal places is frequently not enough.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2017 at 6:07 pm
sgmunson - Monday, September 18, 2017 11:05 AMTomThomson - Monday, September 18, 2017 10:52 AMScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..Compounding interest daily for long term loans like mortgages tends (on this side of the pond) to be regarded as an indicator that the supplier is a loan shark.
Find me a bank that DOESN'T do that !!! Most of the time, they keep that hidden from you, but I'm not aware that there ARE any that don't. Of course, you already stated the reason why most of them hide that fact (or at least try to, anyway)...
sgmunson - Monday, September 18, 2017 11:05 AMTomThomson - Monday, September 18, 2017 10:52 AMCompounding interest daily for long term loans like mortgages tends (on this side of the pond) to be regarded as an indicator that the supplier is a loan shark.
Find me a bank that DOESN'T do that !!! Most of the time, they keep that hidden from you, but I'm not aware that there ARE any that don't. Of course, you already stated the reason why most of them hide that fact (or at least try to, anyway)...
Here in the UK a mortgage provider must (by law) state the equivalent annually compounded rate for his nominal interest rate and whatever compounding interval he uses. So must credit card issuers and anyone else who lends money and charges interest on it. If I've understood the documents I've got from my Spanish bank the same rule applies in Spain. (It may be an EU regulation, but it applied in Scotland before thr UK joined the CEC and I haven't bothered to find out for certain whether it is or isn't now EU law). During my life (including time before EU regulations applied in Britain) I've held 1 mortgage under Scottish law and 4 under English law and for each of them the provider used interest compounded annually so that they only needed to state one rate. Of course I would never use a bank for a mortgage, I'd use a mortgage specialist instead; years ago I borrowed money from banks (not for mortgages), but I wouldn't dream of doing so now because banks charge too much. Credit card issuers in the UK all quote monthly rates and compound monthly, so they also have to state what equivalent annual rates compounded annually would be.
Tom
September 18, 2017 at 6:52 pm
ScottPletcher - Monday, September 18, 2017 11:19 AMThat doesn't seem relevant either, since interest is a daily rate calculated as yearly interest rate / 365 (yes, even during leap years).Although, again, mortgages can have different calculations and different terms. Still, if you pay extra principle during the month, it lessens the interest due next month, which to me demonstrates that it still must be a daily calc, even if it does not compound daily.
No, that doesn't imply any daily calculation.
Suppose that at the beginning of April you owe 1000; then on 14th of April you pay 100, and on the 30th of April you pay 2 (because 2 is your regular monthly payment). And suppose that the interest rate is X per unit per year ( = 100X per cent per year).
Then the total interest accrued during April is 1000*14*X/365 + 900*16*X/365 and the balance at the beginning of May should be 898 + 1000*14*X/365 + 900*16*X/365. The only actual interest amount calculated is the total interest incurred in the month (and that figure has to be correctly rounded, because it is used for detemining the amount outstanding at the beginning of the next month). The two partial calculations (interest on first 14 days and interest on remaining 16 days) should not be be individually rounded down to the required accuracy for their sum, and should be calculated to sufficient accuracy that their rounded sum is adequately accurate (calculating each to 2 more places after the decimal point than the required accuracy for the sum is considered to be good enough; I imagine that if the interested rate could change every day a third extra decimal place might be wanted in these non-final calculations).
Using the latest floating point standard (which you can't do in T-SQL as MS dosen't support it, and probably can't on most hardware even without the T-SQL issue as there's no point in including it in hardware when very little software supports it so most hardware doesn't support it) will ensure that there are no representation errors introduced by using powers of 2 or 16 instead of 10 in the expression of floating point and that the resolution is more than adequate for most purposes (it's roughly equivalent to 123 bit binary accuracy). If you want more than 37 decimal places of accuracy the latest floating point standard can't provide it - but the numeric/decimal type can't provide that either.
Tom
September 19, 2017 at 6:20 am
TomThomson - Monday, September 18, 2017 6:07 PMsgmunson - Monday, September 18, 2017 11:05 AMTomThomson - Monday, September 18, 2017 10:52 AMScottPletcher - Monday, September 18, 2017 10:00 AMI don't think this is really an issue, as a practical matter. Interest is computed daily, down to the cent (or, worst case, to hundredths of cents), how much the interest is. Then that daily interest amount can be used to calculate the next day's interest, etc..Compounding interest daily for long term loans like mortgages tends (on this side of the pond) to be regarded as an indicator that the supplier is a loan shark.
Find me a bank that DOESN'T do that !!! Most of the time, they keep that hidden from you, but I'm not aware that there ARE any that don't. Of course, you already stated the reason why most of them hide that fact (or at least try to, anyway)...
sgmunson - Monday, September 18, 2017 11:05 AMTomThomson - Monday, September 18, 2017 10:52 AMCompounding interest daily for long term loans like mortgages tends (on this side of the pond) to be regarded as an indicator that the supplier is a loan shark.
Find me a bank that DOESN'T do that !!! Most of the time, they keep that hidden from you, but I'm not aware that there ARE any that don't. Of course, you already stated the reason why most of them hide that fact (or at least try to, anyway)...
Here in the UK a mortgage provider must (by law) state the equivalent annually compounded rate for his nominal interest rate and whatever compounding interval he uses. So must credit card issuers and anyone else who lends money and charges interest on it. If I've understood the documents I've got from my Spanish bank the same rule applies in Spain. (It may be an EU regulation, but it applied in Scotland before thr UK joined the CEC and I haven't bothered to find out for certain whether it is or isn't now EU law). During my life (including time before EU regulations applied in Britain) I've held 1 mortgage under Scottish law and 4 under English law and for each of them the provider used interest compounded annually so that they only needed to state one rate. Of course I would never use a bank for a mortgage, I'd use a mortgage specialist instead; years ago I borrowed money from banks (not for mortgages), but I wouldn't dream of doing so now because banks charge too much. Credit card issuers in the UK all quote monthly rates and compound monthly, so they also have to state what equivalent annual rates compounded annually would be.
Just because you calculate the interest for the month by including the number of days still doesn't mean you don't calculate it every day. On the first of the month, you have a calculation that just includes one day's interest, and on the 12th, you have one that includes 12 days. It's still calculated daily to the nearest smallest monetary unit. It doesn't have to be summed from the daily calculations in order to calculate it daily. After all, how do you think a financial institution determines a payoff figure, for example, when a home under mortgage is sold. They have a daily calculation they can just go get and be done with it, based on the date they'll physically receive payoff funds.
That said, I just re-read what you wrote, and realized I missed the word "compounding", and that is a rather critical miss. My bad. I've always known that they calculate it every day, and while just doing the math daily doesn't necessarily compound it; having missed that word, the wheels fell off my verbal bus. One of the difficulties of the written word is that sometimes, it doesn't get read properly, especially when you're tired. Need to learn to think more...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 19, 2017 at 7:50 am
sgmunson - Tuesday, September 19, 2017 6:20 AMThat said, I just re-read what you wrote, and realized I missed the word "compounding", and that is a rather critical miss. My bad. I've always known that they calculate it every day, and while just doing the math daily doesn't necessarily compound it; having missed that word, the wheels fell off my verbal bus. One of the difficulties of the written word is that sometimes, it doesn't get read properly, especially when you're tired. Need to learn to think more...
Missing words is something most people (including me) do now and again. So is misinterpreting words - when you said a daily interest calculation I read it as a calculation done every day, and and interest calculations are normally only done when recalculating the balance outstanding is required so that tends to imply daily compounding, but now I can see that when you said daily you meant "calculated according to the relevant number of days" rather than "done every day" so my misinterpretation is as much a a failure to read properly as is your missed word. I need to think harder too...
Tom
September 19, 2017 at 11:24 am
There is no compounding of interest on mortgages (at least not in the U.S.)... That is to say the accrued interest is NOT added to the balance of the loan, meaning that you will not be paying interest on interest.
Interest is calculated, by day, on the outstanding balance. When you make your payment, ALL accrued interest is paid, then escrow and what's left over is applied to the principal balance.
The only loans (that I can think of at the moment) that actually use compound interest are revolving lines of credit (credit cards)... and... of course, student loans if you're ever foolish enough to accept a forbearance (not to be confused with a deferment).
September 19, 2017 at 3:01 pm
There is such a thing as "repayment period", or "statement period", which a part of the game here, and which no one still mentioned.
_____________
Code for TallyGenerator
September 20, 2017 at 7:13 am
Sergiy - Tuesday, September 19, 2017 3:01 PMThere is such a thing as "repayment period", or "statement period", which a part of the game here, and which no one still mentioned.
Because from a calculation perspective with regard to accuracy, it figures into the calculation of the periodic payment amount, but nothing else. The piece of that pie that figures in is whether payments occur at the beginning of the period, or at the end, but again, only in the calculation of the periodic payment amount.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 76 through 90 (of 97 total)
You must be logged in to reply to this topic. Login to reply