Solved

VB pmt function in SQL

Posted on 2014-03-12
659 Views
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
0
Question by:edjones1

LVL 69

Expert Comment

ID: 39925179
0

LVL 28

Accepted Solution

Ryan McCauley earned 500 total points
ID: 39946877
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

Author Closing Comment

ID: 39949913
perfect thanks
0

Featured Post

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.