Solved

VB pmt function in SQL

Posted on 2014-03-12
3
659 Views
Last Modified: 2014-03-24
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)

Open in new window


Thanks
0
Comment
Question by:edjones1
3 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39925179
0
 
LVL 28

Accepted Solution

by:
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

Open in new window


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

Open in new window

0
 

Author Closing Comment

by:edjones1
ID: 39949913
perfect thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now