Need Financial functions in SQL

[Ross1]

I need some financial functions that will work in SQL. In Excel (and Access too I believe) these functions are
RATE; which tells you the interest rate when you know loan amount, number of periods, and payment amount.
PMT; which tells you the payment amount when you know loan amount, number of periods, and interest rate.
NPER; which tells you the number of periods when you know interest rate payment amount and loan amount.

Has anyone created user-defined functions for these types of financial functions, or has anyone a solution for solving these equations using the functions available in SQL Server 2000?

Thanks

 

[RiverGuy]

Ross1,

In a regular .mdb file, this is extremely easy to accomplish. What you can do is create a module in VBA. Declare an Excel object. Populate cells with your input parameters (you're not really showing Excel on the screen). Use Excel's functions to return a value. This works well with .mdb files as you can reference VBA functions from Queries.

As far as ADP...it's not so easy. These functions would only be available from within VBA....You cannot reference them from your SQL statements. You can still call them from forms, however.

 

[MichaelRed]

It is not necessary to actually instantiate Excel to use the functions. Add a REFERENCE to the Excel Library and call them directly. A FEW of the function calls can be a bit obscure in hte translation, but -so far so good- all that I have tried work if you get the arguments sorted out correctly.




MichaelRed
mlred@verizon.net

 

[Ross1]

Thanks RiverGuy and MichaelRed, but I really need to do the calculations from within a view. I'm going to try to solve the basic equations algebraically and then maybe create some user-defined functions (which I've never done before.)

The trouble I'm running into is with the LOG functions. It's been quite awhile since I've had to manipulate logarithms and I'm not sure I can solve all the equations with the tools in SQL. Wish me luck

If I manage to get this solved, I'll post the SQL for my functions.

Thanks
Ross

 

[MichaelRed]

I have created several of the typical financiala functions in VB(A), but not as SQL Server functions. The syntax is sufficiently different that I am sure that VB(A) functions would be somewhere between useless and detrimental for use in developing the equivalent for DIRECT use in SQL Server.



MichaelRed
mlred@verizon.net

 

[cjowsey]

Since you know what functions you want and how you want them to work, it may be worthwhile asking the question in the Microsoft SQL Serverrogramming forum. There are some very good SQL people in there and somebody may have already faced the same issue.

 

[Ross1]

Good idea. Thanks cjowsey

 

[RiverGuy]

T-SQL has built in LOG and LOG10 functions. You will have more trouble with the special functions, such as computing interest rate, etc.