Calculating financial values in SQL Server

by Arthur Fuller | Aug 23, 2006 9:36:00 PM

Tags: Databases, Arthur Fuller, server, present value, Microsoft SQL Server, future value, financial, SQL Server Newsletter

2 comment(s)

Takeaway: Although SQL Server 2000 and 2005 do not include much in the way of financial functions, Arthur Fuller shows you how to use the present value and future value functions to calculate financial functions in SQL Server.

SQL Server 2000 and 2005 do not include much in the way of financial functions, but there are many sources for the formulas for financial calculations. In this tip, I present the present value (PV) and future value (FV) functions, both of which are scalar UDFs.

Here's the basic logic of PV: Given a value in the future and an interest rate, and the number of compounding periods, the PV calculation determines the value of said investment now. The formula is:

PV = FV [ 1 / (1 + i)ⁿ ]

Listing A demonstrates that this is not difficult to turn into a SQL Server UDF.

The converse of PV is FV; that is, given an amount of money now, an interest, and a number of compounding periods, what will your money be worth at the end?

The formula for this calculation is:

FV = PV (1 + i)ⁿ

When you translate this formula into a SQL Server function, you get the code in Listing B.

Once you have the formula in hand, it is not difficult to create the functions your application needs. For instance, if the values were stored in a table, you could pass them in as parameters to the functions.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

2 comment(s)