Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating interest query


Calculating interest query

Author
Message
FarNorthDBA
FarNorthDBA
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 184
Nope DECIMAL(19,12)
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
The only question I would have asked is whether you'd confirmed with your business side that the interest is compounded monthly and not daily or continuously. To support daily compounding, you would add calculations for the number of days in each month and, of course, divide the annual rate by 365 (and maybe 366 in leap years if that's the way the rate was defined). "Continuous" compounding would involve using a formula based on powers of e, the root for natural logarithms.

It seems clear that your requirements were for monthly compounding, regardless of the number of days elapsed, but I thought it worth while for others reading this thread to be made aware of possible wrinkles.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
nathan 7372 (3/1/2010)
Nope DECIMAL(19,12)

Thanks for the feedback!

Decimal places and rounding considerations scare me. I freely admit to being a total amateur with that sort of thing.

Last thing about that dollar-difference...do we know which was right/less wrong? It might be obvious that it is the method with a higher decimal scale, but I don't want to just assume that...

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
FarNorthDBA
FarNorthDBA
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 184
Thats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Reminds me of a Superman film!





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
nathan 7372 (3/2/2010)
Thats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.

After chewing on this "food for thought", I think the accountants may be right. Although you certainly want to use as high a precision as possible in calculations, the fact that the result is paid monthly means it should probably be expressed and saved rounded to two decimal places. The next month's calculation should use that actual monetary value as a base, not an imaginary 12 decimal place figure.

It's not about shaving fractions for the company's benefit, but about accurately representing the real value of the account. You wouldn't cut a check for $354.395840049589, so why would you say that's the balance at the end of July?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
nathan 7372 (3/2/2010)
Thats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.


I agree... you have to "close" the books each month on two decimal places.

As a side bar... If you keep track of the pennies, you can probably afford to buy a new red stapler. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
Jeff Moden (3/2/2010)
nathan 7372 (3/2/2010)
Thats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.


I agree... you have to "close" the books each month on two decimal places.



I'm not sure about that being compliant with the law of [WhereverYouAre].
AFAIK counting interest rates have to be at least precise to the third or even forth digit (guess what the scale of the MONEY data type is... ;-) ).
Over here (Germany) there's some rumor about a guy who wrote a program for a bank and transferred the cumulated friction of Cents (aka rounding difference) per account and month to his own account.
The bank took him to court for fraud. His statement was something like: "If I took money from somebody at all, it has been the owner of the accounts, but not the bank itself. The bank has no legal right of ownership of the rounding difference."
Whether it's true or not, the banks over here now have to add up rounding differences. (I'm not sure if they do, though. ...;-) )



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
The way I believe they do it in the states is with the interest rate out to "X" places as you say but the principle is rounded to the nearest penny each month or at the receipt of each payment if such a thing is allowed by the loan contract.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Jeff Moden (3/2/2010)
If you keep track of the pennies, you can probably afford to buy a new red stapler. ;-)

That would be awesome! I can never find a stapler when I need one.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search