I'm looking for financial function in T/SQL.

I need to implement the Rate-Function like in VBA.

Thanks
Eric

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,@nper)-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:

Show quoteHide quote

Hi Steve,

Thanks for your very helpful function.
Do you have an idea how I can avoid a divide by zero error?
It happens when @nextguesspv and @guesspv are equal.

Thanks
Eric



Show quoteHide quote

Eric,

  You could just return NULL prematurely if this happens.  It would
also be good to do the same if the log() is about to give a domain error.
In the cases I looked at, these problems occurred because 0.1 is not
a good first guess at the rate, but I don't know off-hand whether there
is some easy way to make a better first guess from the function
parameters.

  The revision below eliminates the errors, and instead, you'll get NULL.
But in these cases, there is a non-NULL answer for dbo.rate(), and it
just can't be calculated with the default first guess of 0.1.  The only
other
error I saw was an overflow error.  The changes here may avoid that
for the most part, but you could probably eliminate these by changing
the types from decimal to float.

My choice of log(pv calculated from guessed rate / actual pv) as the
fraction by which to change the guess was pulled out of thin air, and
there are probably better choices that will handle a wider range
of first guess values.

If you have any particular parameter values for which this function
isn't giving good answers or is still crashing, let me know (or ask
your local actuary to dig around in old textbooks for an idea).


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
if @guesspv/@pv <= 0 return null -- ADDED
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
   if (@nextguesspv-@guesspv = 0) return null -- ADDED
   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

SK

Eric wrote:

Show quoteHide quote

Steve, Do you also have a similar function to calculate the Pmt amount?

==============================================================

Steve Kass wrote:
Show quoteHide quote

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


Show quoteHide quote

Here is a version of the aggregate product function in SQL that you
need for NPV, compound interest, etc.  The idea is that there are three
special cases - all positive numbers, one or more zeroes, and some
negative numbers in the set.
You can find out what your situation is with a quick test on the sign()
of the minimum value in the set.

Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives.  You then need to apply some High School algebra to
determine the sign of the final result.

SELECT CASE MIN (SIGN(nbr))
       WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
       WHEN 0 THEN 0.00                 -- some zeroes
       WHEN -1                          -- some negative numbers
       THEN (EXP(SUM(LN(ABS(nbr))))
             * (CASE WHEN
                     MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
                     THEN -1.00 ELSE 1.00 END)
       ELSE NULL END AS big_pi
FROM NumberTable;

SELECT CASE MIN(ABS(SIGN(nbr)))
       WHEN 0 THEN 0.00  -- some zeroes
       ELSE              -- no zeroes
          EXP(SUM(LOG(ABS(NULLIF(nbr, 0)))))
          * CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), 2)
= 1
        THEN -1.00 ELSE 1.00 END
END AS big_pi

Hi, Joe

Your code about aggregate products in interesting, however it's
somewhat buggy and totally irrelevant for the question of the original
poster.

It's buggy, because:
1. It's written in some SQL-1999 language that has the MOD() and LN()
functions (I don't know if you noticed, but this newsgroup it's called
microsoft.public.sqlserver.programming, so the solutions posted here
should work in Microsoft SQL Server)
2. The first query (after changing the functions with the right ones
and putting the correct number of parantheses) fails with the error "A
domain error occurred." if there is any negative number (because the
first part of the CASE expression is still evaluated by SQL Server,
even if MIN(...) is not 0, and it cannot compute the logarithm of a
negative number).
3. Even after fixing this (by adding an ABS in the first part of the
CASE expression), the error still occurs when there is a negative
number and a zero. The second query tries to deal with this case, and
almost succeeds... if it would have a FROM clause (!!!) and if the MOD
function call would be replaced by the % operator.

However, your response is irrelevant for the original poster, because
to compute the rate there is no need for aggregate products of
different numbers. All we need is the product of the same number, i.e.
the power of a number (see Steve's pv function). Joe, are you even
aware of the purpose and results of the Rate function in VBA (Visual
Basic for Applications, that is) ? Or did you searched in your bag of
tricks for anything vaguely related to "financial functions" and
copy/paste another untested and irrelevant query ?

Hierarchical Result Set!
trigger doesn't seem to work w/cursor
Duplicate Column Names - A Good Trick to Avoid Temp Tables?
Primary Key, bigint or char?
UPDATE SET.... ORDER BY? Help!