Solved

VB pmt function in SQL

Posted on 2014-03-12
3
728 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:Ed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 70

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:Ed
ID: 39949913
perfect thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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