Math Function in SQL

[funkytunaa]

Nice and simple function in access and excel is the PMT function.

I don't believe there is such a function when using SQL but I have found a workaround which calculates the same thing but I can't get it to work.

Below is the supposed Mathimatical equation that will give me the right figure

-n
(amt - x * (1 + rate) ) * rate
pmt(rate, n, amt, x) = -------------------------------
-n
1 - (1 + rate)

Could anyone help writing this out in SQL? I think I am stuffing up the "n" figure which is the number of payments. The number of payments is 60 which is a 5 year term.

Below is what I have currently.

(([TotalPayable]*(POWER((1+[InterestRate]),-[Term])))*[InterestRate])/(1-(POWER((1+[InterestRate]),-5))) As Monthly Amount

I'm fairly certain that I have it completely wrong....Which I can deal with....Well not really. It's driving me up the wall!!!


Cheers!!!

BTW You guys Rock!

 

[earthandfire]

Try the following:

declare @pmt float
declare @pv float
declare @fv float
declare @rate float
declare @nper float


set @pv = -50000	--pv must be negative
set @fv = 10000
set @rate = convert(float, 10) / convert(float, 100)
set @nper = 24

set @rate = @rate / convert(float, 12) --monthly payments

set @pmt = [b]-@rate * (@pv + ((@pv + @fv) / (power(1 + @rate, @nper) - 1)))[/b]

print @pmt

Hope this helps.

 

[funkytunaa]

Don't know whether this is the same

((-[Amount]*(POWER((1+([InterestRate]/12)),[Term])))*([InterestRate]/12))/(1-(POWER((1+([InterestRate]/12)),[Term])))

But it seems to be working, I figure if it works don't "bleep" with it.

I think the problem was that I had the term as a negative figure.

I dunno,What I do know is "M + S = W" New scientific term.

Math + SQL = Wierd

Cheers!!!