Function implementation - 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

SQL Server Development (2000)

Function implementation

Author

Topic

blas53
Starting Member

15 Posts

Posted - 2005-12-15 : 11:57:31

Hello,

I'm trying to implement in SQL Server a function that calculates the payment amount for a loan based on an interest rate and a constant payment schedule. It's the same function in Excel called Pmt. But because this function is not implemented in SQL Server and I must use it there to calculate a value, I need to implement the function. I need a mathematical expression that uses the parameters the Pmt function receives -Pmt( interest_rate, number_payments, PV, FV, Type)-

Thanks for your collaboration.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 13:17:12

I seem to recall doing this for someone once...yep, here it is. You better validate the results:


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)
select dbo.PMT(0.00374, 24, 10945.60)

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-15 : 13:28:49

Damn, that was quick.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources