Author |
Topic |
cardgunner
326 Posts |
Posted - 2008-08-27 : 15:00:43
|
Is there a SQL Function to calculate the interest amount of a number?Something likeselect fincsql(22,500, .07, 60) t_amntt_amnt------3490 so finsql('amount financed', 'interest rate', 'term')Hope this makes sense.CardGunner |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
cardgunner
326 Posts |
Posted - 2008-08-27 : 15:25:37
|
quote: Originally posted by tkizer No, you will need to build one.
Thanks for the reply.I searched calculate interest total and found only a few things in this forum.I have never built anything other then queries.Where should I start? Would it be a stored procedure or a function?CardGunner |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
cardgunner
326 Posts |
Posted - 2008-08-27 : 15:51:33
|
Tara, Right now I don't have any math per say.I was hoping there was a SQL function to do the math for me. But the math I would like figured is the amount of interest accrued on a loan, at a rate, given a certain time.So If you take out a loan for 100,000 @ 2.5% for 60 months how nuch interest will you have to pay, total.CardGunner |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-27 : 16:06:16
|
I don't know what the calculation is for that, so I can't help you write it. Do some searching to figure out what the calculation is and then post it here.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
cardgunner
326 Posts |
Posted - 2008-08-27 : 16:09:46
|
Will do, thanks.CardGunner |
 |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-27 : 17:21:25
|
you mean something like:r = rs*((tm*td)/ty)FV = PV*(1+r)where:r = total raters = start rate = 2.5% = 0.025tm = loan time in months = 60td = number of days in one month = 30 in most financial apps i think.ty = number of days in one year = 360 or 365 depending on your requirementPV = present value of money = 100,000FV = future value of moneyso for your example: FV = 112,328 plus some decimalsfor td = 30, ty = 365this is of course one of many ways to calculate this kind of thing. which one is correct for you is a different matter _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-28 : 08:30:12
|
Often Excel will have both the function as well as the underlying math (in their HELP). I have duplicated some statistical worksheet functions to t-sql and was able to test the results by comparing to what excel comes up with. Try looking there.Be One with the OptimizerTG |
 |
|
cardgunner
326 Posts |
Posted - 2008-08-29 : 09:48:17
|
quote: Originally posted by spirit1 you mean something like:r = rs*((tm*td)/ty)FV = PV*(1+r)where:r = total raters = start rate = 2.5% = 0.025tm = loan time in months = 60td = number of days in one month = 30 in most financial apps i think.ty = number of days in one year = 360 or 365 depending on your requirementPV = present value of money = 100,000FV = future value of moneyso for your example: FV = 112,328 plus some decimalsfor td = 30, ty = 365this is of course one of many ways to calculate this kind of thing. which one is correct for you is a different matter 
Thanks for the suggestion Spirit1, I believe this is the calculation for interest earned rather then interest paid.I need an amortization schedule of sorts. If I take a $100,000 note for 5 years @2.5% what will I have paid back in interest. CardGunner |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-29 : 09:53:08
|
You want the complete list, month by month, the current debt and current interest amount?Or only the total interest paid? E 12°55'05.63"N 56°04'39.26" |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-29 : 10:09:14
|
quote: Originally posted by cardgunner...I need an amortization schedule of sorts. If I take a $100,000 note for 5 years @2.5% what will I have paid back in interest...
Are you after an amortization schedule, or the total interest you will pay?If you want the total interest, just calculate the payment, multiply the payment by the number of payments, and subtract the loan amount to get the total interest.An amortization schedule is very different, since the loan amortizes by a greater rate with each payment. Basically, you start with the balance for that period, calculate the interest on that balance for that period, and subtract the interest from the payment amount to get the loan amortization for that period. Repeat for each period.CODO ERGO SUM |
 |
|
cardgunner
326 Posts |
Posted - 2008-08-29 : 10:10:48
|
You guys are too helpful I put this on the MY backburner until I could come up with the math in order to do this, so I didn't look so needy. Peso, I'm thinking the total paid( interest plus principle). I can subtract from that figure the opening amount to get total interest. I'm trying to work this into a report where if a unit is not set up with a loan then use x rate and y term to figure z as the total amount paid. Then divide z by y to get t_pymnt. Now I can use t_pymnt as a subsitute for the real thing to ensure our unit is not being rented to cheap.CardGunner |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-29 : 10:31:57
|
Payment formula:select Payment = (a.rate+(a.rate/(power(a.rate+1.0E,a.NumPayments)-1.0E)))*LoanAmountfrom ( -- Test data, cast all numbers float select -- 8% interest rate = (.08E/12E), -- 30 years of monthly payments NumPayments = 360E, LoanAmount = 150000E ) aResults:Payment----------------------1100.6468608190669(1 row(s) affected) CODO ERGO SUM |
 |
|
cardgunner
326 Posts |
Posted - 2008-08-29 : 13:33:01
|
Thanks Michael, What is the purpose of the E after some of the numbers?CardGunner |
 |
|
cardgunner
326 Posts |
Posted - 2008-08-29 : 14:29:53
|
WOW! This is some pretty intense stuff!I'm not sure if I understand everything going on on the calculation and exactly what it does, but it works.I broke everything out to better understandDeclare @rate floatdeclare @term floatdeclare @note floatselect @rate=(.025/12E)select @term=60Eselect @note=100000select z = power(@rate+1.0E,@term) z ----------------------------------------------------- 1.1330011218785867 select y = power(@rate+1.0E,@term)-1.0Ey ----------------------------------------------------- 0.1330011218785867 select x = @rate/ (power(@rate+1.0E,@term)-1.0E)x ----------------------------------------------------- 1.5664028272146115E-2 select q = @rate+(@rate/ (power(@rate+1.0E,@term)-1.0E))q ----------------------------------------------------- 1.7747361605479448E-2 select v = ((@rate+(@rate/ (power(@rate+1.0E,@term)-1.0E)))*@note)v ----------------------------------------------------- 1774.7361605479448 select t = ((@rate+(@rate/ (power(@rate+1.0E,@term)-1.0E)))*@note)* @termt ----------------------------------------------------- 106484.16963287668 So this is perfect! Imprerssive! Thank you.CardGunner |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-29 : 14:31:29
|
As Michael wrote in the code, to "cast all numbers [as] float". E 12°55'05.63"N 56°04'39.26" |
 |
|
cardgunner
326 Posts |
Posted - 2008-08-29 : 14:44:41
|
quote: Originally posted by Peso As Michael wrote in the code, to "cast all numbers [as] float".
I didn't pick that up or know that.Now I know.I feel so fortunate enough to have learned so much today but no one to share it with It's hard describing your daily truimphs to your friends or wife. They just don't have the same passion.Thanks for the input.CardGunner |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-29 : 15:16:50
|
quote: Originally posted by cardgunner I feel so fortunate enough to have learned so much today but no one to share it with It's hard describing your daily truimphs to your friends or wife. They just don't have the same passion.
I hear ya. I tell family and friends I'm a software programmer as that's easier for them to comprehend than a DBA. They hear the word administrator and think I'm a secretary. Luckily my husband is a software developer who works on Oracle and SQL Server, so he knows what I'm talking about when discuss my day.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-29 : 15:33:11
|
I tell people I’m a truck driver. Just seems cooler.“I been warped by the rain, driven by the snowI'm beat down and dirty, and don't you know...I've driven every kind of rig that's ever been madeI've driven the back roads so I wouldn't get weighed”CODO ERGO SUM |
 |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-30 : 08:42:39
|
You don't see too many Little Feat references on this forum - Thanks!When I can't resist telling my wife something I did that I was proud of she closes her eyes, bobs her head and makes loud snoring noises :)Be One with the OptimizerTG |
 |
|
Next Page
|