Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

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)

Open in new window


Thanks
0
Ed
Asked:
Ed
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
Ryan McCauleyDatabase and Reporting 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

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
 
EdAuthor Commented:
perfect thanks
0

Featured Post

Technology Partners: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now