• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • 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

1 Solution
Éric MoreauSenior .Net ConsultantCommented:
Ryan McCauleyData 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)
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 =
-- Do all multiplication before division
( @WK_principal * (@rate*power(@WK_One+@rate,@WK_periods)))
/ (power(@WK_One+@rate,@WK_periods)-@WK_One)

return @pmt


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

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

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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