Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Financial SQL function to get total interest

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 like

select fincsql(22,500, .07, 60) t_amnt

t_amnt
------
3490

so finsql('amount financed', 'interest rate', 'term')

Hope this makes sense.


CardGunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-27 : 15:12:16
No, you will need to build one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-27 : 15:27:50
It depends on what you want.

Could you show us the math behind what you want?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-08-27 : 16:09:46
Will do, thanks.

CardGunner
Go to Top of Page

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 rate
rs = start rate = 2.5% = 0.025
tm = loan time in months = 60
td = 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 requirement
PV = present value of money = 100,000
FV = future value of money

so for your example:
FV = 112,328 plus some decimals
for td = 30, ty = 365

this 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 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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 rate
rs = start rate = 2.5% = 0.025
tm = loan time in months = 60
td = 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 requirement
PV = present value of money = 100,000
FV = future value of money

so for your example:
FV = 112,328 plus some decimals
for td = 30, ty = 365

this 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
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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)))*LoanAmount
from
(
-- Test data, cast all numbers float
select
-- 8% interest
rate = (.08E/12E),
-- 30 years of monthly payments
NumPayments = 360E,
LoanAmount = 150000E
) a


Results:

Payment
----------------------
1100.6468608190669

(1 row(s) affected)


CODO ERGO SUM
Go to Top of Page

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
Go to Top of Page

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 understand


Declare @rate float
declare @term float
declare @note float
select @rate=(.025/12E)
select @term=60E
select @note=100000

select z = power(@rate+1.0E,@term)
z
-----------------------------------------------------
1.1330011218785867

select y = power(@rate+1.0E,@term)-1.0E
y
-----------------------------------------------------
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)* @term
t
-----------------------------------------------------
106484.16963287668



So this is perfect!

Imprerssive! Thank you.


CardGunner
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 snow
I'm beat down and dirty, and don't you know
...
I've driven every kind of rig that's ever been made
I've driven the back roads so I wouldn't get weighed”







CODO ERGO SUM
Go to Top of Page

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 Optimizer
TG
Go to Top of Page
    Next Page

- Advertisement -