Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating interest query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 01, 2010 4:32 PM
 Valued Member Group: General Forum Members Last Login: Wednesday, April 02, 2014 11:57 AM Points: 71, Visits: 172
 Nope DECIMAL(19,12)
Post #874788
 Posted Monday, March 01, 2010 7:15 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 21, 2014 8:55 AM Points: 1,520, Visits: 3,035
 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.
Post #874822
 Posted Monday, March 01, 2010 8:10 PM
 SSChampion Group: General Forum Members Last Login: Today @ 5:59 AM Points: 11,168, Visits: 10,931
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #874834
 Posted Tuesday, March 02, 2010 9:22 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, April 02, 2014 11:57 AM Points: 71, Visits: 172
 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.
Post #875273
 Posted Tuesday, March 02, 2010 9:25 AM
 SSChampion Group: General Forum Members Last Login: Today @ 5:59 AM Points: 11,168, Visits: 10,931
 Reminds me of a Superman film! Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #875278
 Posted Tuesday, March 02, 2010 1:33 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 21, 2014 8:55 AM Points: 1,520, Visits: 3,035
 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?
Post #875506
 Posted Tuesday, March 02, 2010 1:42 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 10:34 AM Points: 35,955, Visits: 30,246
Post #875513
 Posted Tuesday, March 02, 2010 2:12 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:01 PM Points: 6,922, Visits: 12,633
Post #875527
 Posted Tuesday, March 02, 2010 6:26 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 10:34 AM Points: 35,955, Visits: 30,246
 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #875613
 Posted Tuesday, March 02, 2010 8:56 PM
 SSChampion Group: General Forum Members Last Login: Today @ 5:59 AM Points: 11,168, Visits: 10,931
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #875660

 Permissions