A set-based solution to some of your financial problems
A couple of students in one of my SQL Server programming classes brought me a puzzle recently. In their production system, they work with various financial applications that must apply interest rates or indexes to monetary values such as deposits. The students had a specific problem relating to this system and wondered whether it had a set-based T-SQL solution. The answer is: of course! Let's explore a generalized version of the problem my students presented, so that you can adjust the solution to your specific needs.
Indexing Monetary Values
Your money's worth changes with time. Financial applications rarely display in its original form a monetary value that was entered into a database. Financial institutions need to apply interest rates to savings account deposits, apply index rates (like the consumer price index—CPI) to monetary values in a profit/loss report, apply currency exchange rates to debts owed to foreign suppliers, and so on.
Usually, a monetary value is entered into the financial application's database along with an effective date. The application would apply a series of adjustments to the principal value and calculate the resulting value as of a later date, such as "today" or "December 31, 2002." For example, say you enter the amount $1,300 into the system on September 7, 2002. You need to link that amount to the CPI, which is updated monthly on the 15th, and display the value as of December 31, 2002. The CPI contains monthly rates that reflect the changes in the value of money according to average consumer purchases. An index such as the CPI can be expressed either as a percentage of growth or as a rate factor. Consumers usually care about the percentage of growth, while accountants usually use index rates in their calculations.
Suppose in this example that the index rates grew 0.3 percent, 0.6 percent, 0.4 percent, and 0.9 percent during September through December. Your principal amount needs to undergo the following adjustments:
$1,300.0000 * (1 + 0.3%) = $1,303.9000
$1,303.9000 * (1 + 0.6%) = $1,311.7234
$1,311.7234 * (1 + 0.4%) = $1,316.9703
$1,316.9703 * (1 + 0.9%) = $1,328.8230
You'd use similar calculations when applying interest rates to your savings account's deposits (assuming the terms of your account specify changing interest rates), but the periods between interest rate changes might vary.