Function Conversion! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Function Conversion!

Author

Topic

d
Starting Member

26 Posts

Posted - 2005-07-05 : 09:16:06

Going on from my last post can I be cheeky. I have a function that I am trying to make work in SQL. Could someone tell me if I am using the incorrect syntax before I go too far??

SELECT @IPTAmount = round(((@amountofloan - @totalCPI) + (@term * PMT((1+APR/100) ^(1/12) - 1, @term, -(@amountofloan - @totalCPI), 0, 0) - (@amountofloan-@totalCPI))) * @iptrate,2)

Many thanks again people

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-05 : 09:51:59

Are you intending to use "^" as a bitwise operator?
Is "PMT" a udf (it's not a sql function)

Other than those issues, the syntax seems to be correct. I have no idea if the results are good.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-05 : 10:01:19

Can you provide sample data with expected outcome?

Madhivanan

Failing to plan is Planning to fail

d
Starting Member

26 Posts

Posted - 2005-07-05 : 10:20:19

Hi, using the following figures I am expecting to see a @IPTAmount of about 108.00. PMT is a VB function returning a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate!

set @Term = 24
set @amountofloan = 10945.60
set @totalcpi = 945.60
set @IPTRate = 0.00374

The PMT is the part I am struggling to get working in SQL

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-05 : 10:38:55

Well unless you find a PMT function for sql (or code it yourself) there's no way to get this working. Since vb has one and I know Excel has one I bet someone as already coded a version for sql. Does anyone know the rules calculating PMT?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37530&SearchTerms=PMT,function

Be One with the Optimizer
TG

d
Starting Member

26 Posts

Posted - 2005-07-05 : 10:55:09

Thanks for you help anyway!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-05 : 11:05:10

I got this PMT formula from various posts on MS newsgroups. This seems to match results from Excel:


if object_id('dbo.PMT') > 0
	drop function dbo.PMT
go

create function dbo.PMT(@rate numeric(15,9), @periods smallint, @principal numeric(20,2) )
returns numeric (38,9)
as
begin
	declare @pmt numeric (38,9)
	select @pmt = @principal 
		 / (power(1+@rate,@periods)-1) 
	 	* (@rate*power(1+@rate,@periods))

	return @pmt
end

go

select dbo.pmt(0.0625/12, 30*12, 100000)

Be One with the Optimizer
TG

d
Starting Member

26 Posts

Posted - 2005-07-06 : 04:54:42

TG, many thanks, I will look into this

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-06 : 10:12:30

I edited my last post because I forgot the final "go" after the function definition.

good luck...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources