| Downloads |
|---|
| 25914.zip |
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.
Now, let's move on to the generalized problem. Run the script in Listing 1 to create the Amounts and IndexRates tables and populate them with sample data. The Amounts table contains monetary values and their effective dates, and the IndexRates table contains the monthly percentage of the CPI's growth. Your task is to write a query that adjusts the amounts to reflect a future given date (provided in the variable @givendate). For example, Table 1, page 18, shows the desired results if the given date was December 31, 2002.
Solution That Imitates Aggregate PRODUCT()
First, let's write out the formula that we need to apply to each amount:
indexed_value = amt_value * (1+1st idx_growth) * (1+2nd idx_growth) * ... * (1+last idx_growth)
The 1st, 2nd (and so on) rates are respective to the amount's effective date and the intervening periods, and the last rate is the one in effect on the given date.
Correlating the appropriate index growths to each amount is the simpler part of the problem. You can join the Amounts table to the IndexRates table by using the following JOIN condition:
ON IR.idx_date BETWEEN amt_date AND @givendate
You'll get multiple index rows for each amount, representing the multiple index changes in the formula above. Using a left outer join ensures that amounts with an effective date more recent than the last index rate change date (e.g., the row in Amounts with the effective date December 24, 2002) won't disappear from the output.
The tougher problem here is to calculate the product of all the returned index changes as the formula requires. If T-SQL supported an aggregate PRODUCT() function (which would calculate the product of a group's values, much like the SUM() function calculates the sum of a group's values), you could use the pseudo query that Listing 2 shows. (You can't run that query, though, because T-SQL doesn't support an aggregate PRODUCT() function.) I used ISNULL() in the pseudo query's SELECT list to return the principal amount when its effective date is more recent than the last index-rate change date.
In "Adding Performance," May 2001, InstantDoc ID 20131, I discussed a mathematical way to imitate an aggregate PRODUCT() function by using the LOG10 function in the following formula:
PRODUCT(<col>) = POWER(10., SUM(LOG10(<col>)))
If you apply the same formula to this problem's query, you get the following expression:
ISNULL(POWER(CAST(10 AS FLOAT), SUM(LOG10(1+idx_growth))), amt_value) * amt_value
The return value of the POWER() function is of the same data type as its first argument, so the code explicitly converts the number 10 to FLOAT to force a FLOAT result. Finally, convert the preceding expression to the money data type, and you're done. You can run the final query that Listing 3 shows to get the desired results that Table 1 shows.
Solution That Uses Respective Index Rate Factors
Another approach to solving the problem is to use a different representation of the CPI. Instead of storing the percentage that the index rate changed compared to the previous month, you can store a rate factor that represents the ratio of a month's index rate to a predetermined base month's index rate. For example, you can decide that December 2001 is your base month and set its rate factor value to 1. January's index growth was 0.1 percent, so January's index rate factor would be 1 * (1+0.1%) = 1.001. February's index growth was 0.2 percent, so its index rate factor would be 1.001 * (1+0.2%), and so on. Keep in mind that each index rate factor includes all the cumulative index changes since the base date. Therefore, to apply the index changes for the period from_month through to_month to amt_value, you can use the following formula:
amt_value * (to_index_rate / from_index_rate)
It's that simple. You can store the index rate factor instead of the index growth, or you can add another column so that you can use whichever factor best suits your needs in the various financial calculations that you perform. You can even specify only one of the values in your INSERT statement and let a trigger calculate the other for you. An important point that works to your advantage is that a table such as IndexRates will have no updates or deletes, and for each period (e.g., monthly), only one row needs to be inserted into the table.
Running the code in Listing 4, page 20, recreates the IndexRates table with the additional column idx_rate. The code also creates an insert trigger that calculates the index rate factor based on the index growth value stored in the idx_growth column and repopulates the table with index changes. The trigger runs an UPDATE statement that joins the IndexRates table and the inserted table to locate the new row that needs to be updated. The SET clause uses a subquery that retrieves the last month's index rate factor and multiplies it by (1 + current_month's_index_growth). The whole expression would yield NULL when the first row was entered into the table, so the code uses ISNULL() to return 1 in that case.
Next, you need to write a query that locates, for each amount, the index rate factors of the amount's effective date and the given date; you'll use these dates in the simple formula I presented earlier in this section. The problem with writing the query is that the amt_date and @givendate values aren't necessarily on the 15th of the month. You can use a CASE expression to check whether the day value of the date is smaller than 15—in which case, the effective date is the 15th of the previous month; otherwise, it's the 15th of the current month. Thus, you'd calculate the effective given date as follows:
DECLARE @givendate AS SMALLDATETIME
SET @givendate = '20021231'
DECLARE @effective_givendate AS SMALLDATETIME
SET @effective_givendate =
CASE
WHEN DAY(@givendate)
Now, you can use a similar technique to calculate the amount's effective date, then use a three-way join between the Amounts table and two instances of the IndexRates table. One instance retrieves the index rate factor of the amount's effective date; the other retrieves the index rate factor of the given date. Listing 5 shows the full query. The code might look a bit longer than that of the previous solution, but it should perform better because it doesn't need to locate all index changes in the desired period for each amount—just the first and last index rate factors.
Putting Knowledge into Action
Providing solutions in T-SQL to financial problems often looks complex. However, when you're equipped with a toolbox that contains many T-SQL tricks, such as imitating an aggregate PRODUCT() function, you're ready for the task. Try to come up with several solutions to the problem, and don't be satisfied when you find a solution that works. When you have a variety of solutions, you can choose the one that best suits your needs at the moment and use the techniques you've learned to devise solutions for your next task.
