I'm looking for financial function in T/SQL.
I need to implement the Rate-Function like in VBA.
Thanks
EricEric,
Here is a shot at it. It's probably much less reliable than the VBA
function,
but maybe you can tweak it some. Note I define a pv function also, since
rate() is iterated to be a good pv() predictor.
create function pv (
@.rate decimal(10,9),
@.nper int,
@.pmt decimal(18,4),
@.fv decimal(18,4),
@.type int
) returns decimal(18,4) as begin
return
(-@.fv -
@.pmt*(1+@.rate*@.type)*(power(1+@.rate,@.npe
r)-1)/@.rate)/power(1+@.rate,@.nper)
end
go
create function rate (
@.nper int,
@.pmt decimal(18,4),
@.pv decimal(18,4),
@.fv decimal(18,4) = 0.0,
@.type int = 0,
@.guess float = 0.1
) returns decimal(10,9) as begin
declare @.guesspv decimal(18,4)
declare @.nextguesspv decimal(18,4)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
declare @.error decimal(18,17)
declare @.nextguess float
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
declare @.tries int set @.tries = 20
while @.error > 0.000001 begin
if @.tries = 0 return null
set @.guess = @.guess +
(@.pv-@.guesspv)/(@.nextguesspv-@.guesspv)*(@.nextguess-@.guess)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
set @.tries = @.tries - 1
end
return @.guess
end
go
select dbo.rate(10*1, -1000, 6500, default, default, default)
go
drop function rate, pv
-- Steve Kass
-- Drew University
-- A32884BB-911C-472A-90F2-C72346B90995
Eric wrote:
>I'm looking for financial function in T/SQL.
>I need to implement the Rate-Function like in VBA.
>Thanks
>Eric
>
>|||One option is to create a VB6 class that has methods that implement the
requisite financial functions (perhaps calling VBA financial functions). You
then call your financial functions from TSQL by using the extended stored
procedures sp_OACreate, sp_OAMethod, and sp_OADestroy.
Here's what you'd need to do:
1. Compile your VB6 class into a DLL. You better have good error handling in
it otherwise you risk crashing your SQL Server.
2. Place that DLL into the WIN32 directory on your SQL Server, and REGISTER
it on your SQL Server by using RegSvr32.exe
3. Call methods of your VB6 class/dll from any stored procedure, using
something like the following (some of the names have been changed to protect
the innocent):
BEGIN
blah blah blah
DECLARE @.Object int -- holds a reference to your object instantiated
from the vb6 class.
-- Set a string equal to your entire method call - including any
parameter values.
SET @.MethodToCall = 'CalculatePayment(' + @.RatePercent + ', ' +
@.RateIncrease + ', ' + @.Months + ', ' + @.Fees + ')'
--Instantiate an instance of VB6 class and put it's reference in
@.object
EXEC sp_OACreate 'Your_VB_DLL.ClassName, @.object OUT
--Run the financial function (e.g., CalculatePayment method) of your
class - and place it's output into @.Return
EXEC sp_OAMethod @.object, @.MethodToCall, @.return OUT
-- Destroy the instance of our DataConversion class now that we're
done with it.
EXEC sp_OADestroy @.object
blah blah blah
END
4. Any time you upgrade your VB6 DLL, be sure to Unregister the old one
(using RegSvr32.exe... -U), then replace the old DLL with the new one, and
then register the new one like in step 2 above. No need to restart your SQL
Server.
-HTH
"Eric" <jug@.nospam.nospam> wrote in message
news:u0QX3AabFHA.724@.TK2MSFTNGP12.phx.gbl...
> I'm looking for financial function in T/SQL.
> I need to implement the Rate-Function like in VBA.
> Thanks
> Eric
>|||Steve, Do you also have a similar function to calculate the Pmt amount?
========================================
======================
quote:
Originally posted by Steve Kass
Eric,
Here is a shot at it. It's probably much less reliable than the VBA
function,
but maybe you can tweak it some. Note I define a pv function also, since
rate() is iterated to be a good pv() predictor.
create function pv (
@.rate decimal(10,9),
@.nper int,
@.pmt decimal(18,4),
@.fv decimal(18,4),
@.type int
) returns decimal(18,4) as begin
return
(-@.fv -
@.pmt*(1+@.rate*@.type)*(power(1+@.rate,@.npe
r)-1)/@.rate)/power(1+@.rate,@.nper)
end
go
create function rate (
@.nper int,
@.pmt decimal(18,4),
@.pv decimal(18,4),
@.fv decimal(18,4) = 0.0,
@.type int = 0,
@.guess float = 0.1
) returns decimal(10,9) as begin
declare @.guesspv decimal(18,4)
declare @.nextguesspv decimal(18,4)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
declare @.error decimal(18,17)
declare @.nextguess float
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
declare @.tries int set @.tries = 20
while @.error > 0.000001 begin
if @.tries = 0 return null
set @.guess = @.guess +
(@.pv-@.guesspv)/(@.nextguesspv-@.guesspv)*(@.nextguess-@.guess)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
set @.tries = @.tries - 1
end
return @.guess
end
go
select dbo.rate(10*1, -1000, 6500, default, default, default)
go
drop function rate, pv
-- Steve Kass
-- Drew University
-- A32884BB-911C-472A-90F2-C72346B90995
Eric wrote:
>I'm looking for financial function in T/SQL.
>I need to implement the Rate-Function like in VBA.
>Thanks
>Eric
>
>
