# VB pmt function in SQL

I have some vb that works out loan repayments but i need to code into a stored procedure.

I've found that there is no pmt function in sql to do this. Can anyone help me write the following in SQL?

Math.Round(Pmt(0.119 / 12, 300, -17030), 2)

Thanks
###### Who is Participating?

Data and Analytics ManagerCommented:
Looks like somebody may have already written a function for you that does this exact calculation (from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55819):

create function dbo.PMT
(
-- Datetype for @rate changed to float
@rate float,
@periods smallint,
@principal numeric(20,2)
)
returns numeric (38,9)
as
begin
declare @pmt numeric (38,9)

declare @WK_periods float,
@WK_principal float,
@wk_One float,
@WK_power float

-- Convert data for calculation
select  @WK_periods = @periods,
@WK_principal = @principal,
@WK_One = 1

select  @pmt =
round(
-- Do all multiplication before division
( @WK_principal * (@rate*power(@WK_One+@rate,@WK_periods)))
/ (power(@WK_One+@rate,@WK_periods)-@WK_One)
,9)

return @pmt

end
go

And it would be a direct swap in your example above:

= CONVERT(NUMERIC(20,2), CONVERT(INT, dbo.PMT(0.119 / 12, 300, -17030) * 100))/100
0

Senior .Net ConsultantCommented:
0

Author Commented:
perfect thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.