Calculating Discount Factors in SQL Server for Yield Curve Construction
Apr
1
Written by:
Charles Flock
4/1/2013 3:32 PM
A demonstration of how to use the latest XLeratorDB / financial functions to perform yield curve construction in SQL Server.
The SWAPCURVE function, introduced in XLeratorDB / financial version 1.11, calculates yield curve discount factors from the supplied cash rates, futures prices, and swap rates. The cash rates are processed first, followed by the futures prices, concluding with the swap rates.
To illustrate all of the complex calculations performed within the XLeratorDB SWAPCURVE table-valued function, this article shows examples of how the equivalent discount factors can be calculated using a long series of SQL statements and invoking other XLeratorDB functions.
Cash Rates
Discount factors for cash rates can be calculated directly from the supplied rates. In constructing the cash rates, it is assumed that the term of the cash rate is from the spot date until the maturity date of the deposit, with the exception of overnight rates and tom/next rates. In other words, if the maturity date of the cash rate is equal to the spot date, then the value date of the cash rate is assumed to be the previous business day. If the maturity date of the cash rate is less than the spot date, then the value date is assumed to be the start date.
If there is no cash rate for the spot date, then the spot date discount factor is calculated using the cash rate for the earliest maturity date after the spot date. The following example demonstrates the mechanics of the calculation, using T-SQL. For purposes of this example, the start date is 2013-01-15 and the spot date is 2 business days after the start date and we are using SQL Server 2008 with XLeratorDB installed on it. For purposes of this example we will ignore holidays, though weekend days are taken into account.
We can start by setting up the following variables, simply to make it easier to follow the example, using the XLeratorDB BUSINESSDATE function to calculate the spot date.
DECLARE @date_start as date = '2013-01-15' --Start Date
DECLARE @date_spot as date = wct.BUSINESSDATE(@date_start,'D',2,'F','') --Spot Date
Using the TENOR2DATE function to automatically calculate the maturity dates, we put a series of cash rates into a temporary table called #curves.
SELECT wct.TENOR2DATE(tenor, @date_start,@date_spot,'') as mDate
,rate
,'C' as iType
INTO #curves
FROM (VALUES
('ON',.00160),
('1W',.00180),
('2W',.00192),
('1M',.00210),
('2M',.00250),
('3M',.00300),
('4M',.00354),
('5M',.00425),
('6M',.00491),
('7M',.00561),
('8M',.00614),
('9M',.00667),
('10M',.00715),
('11M',.00764),
('1Y',.00819)
) n(tenor,rate)
With the SWAPCURVE function, we calculate the discount factors for the cash rates. The ROW_NUMBER() function is included simply to make it easier to reference rows in the resultant table.SELECT ROW_NUMBER() OVER (ORDER BY mat_date) as rn
,mat_date
,df
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --InputData_RangeQuery
,@date_start --Start Date
,2 --Frequency (Semi-Annual)
,@date_spot --Spot Date
,2 --Cash Basis (A/360)
,2 --Futures Basis (A/3600
,0 --Swaps Basis (30/360)
,'L' --Interpolation (Linear)
,'M' --Date Roll (Modified Following)
,NULL --Holidays
)
This produces the following result.
rn mat_date df
---- ---------- -----------------
1 2013-01-16 0.999995555575309
2 2013-01-17 0.999991111170370
3 2013-01-24 0.999956112706425
4 2013-01-31 0.999916450742048
5 2013-02-18 0.999804481000583
6 2013-03-18 0.999574621744643
7 2013-04-17 0.999241679910437
8 2013-05-17 0.998812512405731
9 2013-06-17 0.998211660245474
10 2013-07-17 0.997528573341441
11 2013-08-19 0.996667391530846
12 2013-09-17 0.995863753842569
13 2013-10-17 0.994958528022422
14 2013-11-18 0.993969999844920
15 2013-12-17 0.992952840778743
16 2014-01-17 0.991755818790092
The first thing to notice is that we passed 15 rows of data into the function, but 16 were returned. As there was no data point for the spot date (row 2) in the #curves table, we needed to derive a discount factor for the spot date so that we can calculate the discount factors for all the data points after the spot date.
A discount factor is a number which is used to calculate the difference in value between a unit today and some unit in the future. For purposes of this article, it is useful to think of the discount factor as the inverse of the interest rate. The calculation of interest is very straightforward: it is the principal multiplied by the rate multiple by time. By setting the principal to 1, the interest is simply rate * time (rt) and the amount received at maturity is 1 + rt. The discount factor then becomes 1/(1 + rt).
In calculating the discount factors for the cash rate, we need to account for all the time from the start date to the maturity date of the cash instruments. Generally, the time period of a cash instrument is calculated from the spot date to the maturity date, leaving a gap from the start date to the spot date. In order to obtain the discount factor with respect to the start date, we need to multiply the cash discount factor by the discount factor for the spot date.
The following SQL demonstrates the mechanics for calculating the discount factors for the first 2 rows in this example using the #curves table. We use the XLeratorDB YEARFRAC function to calculate the time component of the discount factor equation. ;WITH mycte as (
SELECT c.mdate
,c.rate
,1/(1+(c.rate * wct.YEARFRAC(@date_start,c.mDate,2))) as df
FROM #curves c
WHERE c.mDate = '2013-01-16'
UNION ALL
SELECT wct.BUSINESSDATE(mDate,'D',1,'F','')
,rate
,1/(1+(rate * wct.YEARFRAC(mdate,wct.BUSINESSDATE(mDate,'D',1,'F',''),2))) * df
FROM myCTE
WHERE mDate = '2013-01-16'
) SELECT mdate
,rate
,df
FROM mycte
This produces the following result.
mDate rate df
---------- ------- -----------------
2013-01-16 0.00160 0.999995555575309
2013-01-17 0.00160 0.999991111170370
As you can see, the same cash rate was used for both maturity dates. The discount factor (df) for 2013-01-16 incorporates one day’s worth of interest. The discount factor for 2013-01-17 also has one day’s worth of interest and is multiplied by the discount factor for 2013-01-16.
All the discount factors subsequent to the spot date will use the spot date as an anchor, calculating the number of days from spot date to maturity date and then multiplying the result by the discount factor for the spot date. The following T-SQL demonstrates the mechanics of the calculation.
;WITH mycte as (
SELECT c.mdate
,c.rate
,1/(1+(c.rate * wct.YEARFRAC(@date_start,c.mDate,2))) as df
FROM #curves c
WHERE c.mDate = '2013-01-16'
UNION ALL
SELECT wct.BUSINESSDATE(mDate,'D',1,'F','')
,rate
,1/(1+(rate * wct.YEARFRAC(mdate,@date_spot,2))) * df
FROM myCTE
WHERE mDate = '2013-01-16'
)
SELECT mdate
,rate
,df
FROM mycte
UNION ALL
SELECT c.mDate
,c.rate
,1/(1+(c.rate * wct.YEARFRAC(@date_spot,c.mdate,2))) * m.df
FROM #curves c
,mycte m
WHERE c.mDate > @date_spot
AND m.mdate = @date_spot
This produces the following result.
mdate rate df
----------- ------- -----------------
2013-01-16 0.00160 0.999995555575309
2013-01-17 0.00160 0.999991111170370
2013-01-24 0.00180 0.999956112706425
2013-01-31 0.00192 0.999916450742048
2013-02-18 0.00210 0.999804481000583
2013-03-18 0.00250 0.999574621744643
2013-04-17 0.00300 0.999241679910437
2013-05-17 0.00354 0.998812512405731
2013-06-17 0.00425 0.998211660245474
2013-07-17 0.00491 0.997528573341441
2013-08-19 0.00561 0.996667391530846
2013-09-17 0.00614 0.995863753842569
2013-10-17 0.00667 0.994958528022422
2013-11-18 0.00715 0.993969999844920
2013-12-17 0.00764 0.992952840778743
2014-01-17 0.00819 0.991755818790092
Futures Prices
Having calculated the discount factors for the cash rates, we are ready to look at incorporating futures prices into the calculation. If you are follwing along in SMS, it’s best to just open up a new query in SMS and execute the following SQL which will put the cash discount factors into a temporary table called #df.
DECLARE @date_start as date = '2013-01-15' --Start Date
DECLARE @date_spot as date = wct.BUSINESSDATE(@date_start,'D',2,'F','') --Spot Date
SELECT wct.TENOR2DATE(tenor, @date_start,@date_spot,'') as mDate
,rate
,'C' as iType
INTO #curves
FROM (VALUES
('ON',.00160),
('1W',.00180),
('2W',.00192),
('1M',.00210),
('2M',.00250),
('3M',.00300),
('4M',.00354),
('5M',.00425),
('6M',.00491),
('7M',.00561),
('8M',.00614),
('9M',.00667),
('10M',.00715),
('11M',.00764),
('1Y',.00819)
) n(tenor,rate)
SELECT mat_date as mDate
,df
INTO #df
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --InputData_RangeQuery
,@date_start --Start Date
,2 --Frequency (Semi-Annual)
,@date_spot --Spot Date
,2 --Cash Basis (A/360)
,2 --Futures Basis (A/3600
,0 --Swaps Basis (30/360)
,'L' --Interpolation (Linear)
,'M' --Date Roll (Modified Following)
,NULL --Holidays
)
Futures contracts are quoted as prices and the prices need to be converted to interest rates. To keep things simple, we will ignore the convexity adjustment for the futures contracts (which can be calculated using the ED_FUT_CONV_ADJ_HL function) and we will calculate the interest rate for the futures contract as (100-price)/100. Using the ED_FUT2DATE function we can use the following SQL to insert the futures contracts into our #curves table.INSERT INTO #curves
SELECT wct.ED_FUT2DATE(ccode, '2013-01-15')
,(100-pr)/100 as rate
,'F' as iType
FROM (VALUES
('H3',99.705),
('M3',99.695),
('U3',99.675),
('Z3',99.650),
('H4',99.620),
('M4',99.575),
('U4',99.525),
('Z4',99.455)
)n(ccode, pr)
The contents of the #curve table should be:
mdate rate iType
---------- ------- -----
2013-01-16 0.00160 C
2013-01-24 0.00180 C
2013-01-31 0.00192 C
2013-02-18 0.00210 C
2013-03-18 0.00250 C
2013-03-20 0.00295 F
2013-04-17 0.00300 C
2013-05-17 0.00354 C
2013-06-17 0.00425 C
2013-06-19 0.00305 F
2013-07-17 0.00491 C
2013-08-19 0.00561 C
2013-09-17 0.00614 C
2013-09-18 0.00325 F
2013-10-17 0.00667 C
2013-11-18 0.00715 C
2013-12-17 0.00764 C
2013-12-18 0.00350 F
2014-01-17 0.00819 C
2014-03-19 0.00380 F
2014-06-18 0.00425 F
2014-09-17 0.00475 F
2014-12-17 0.00545 F
We can run the following T-SQL to see how the introduction of futures prices (also referred to as the futures strip) affected the discount factor calculation. The ROW_NUMBER() function is included simply to make it easier to make reference to rows in the resultant table.
SELECT ROW_NUMBER() OVER (ORDER by mat_date) as rn
,mat_date
,df
,rsource
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --InputData_RangeQuery
,@date_start --Start Date
,2 --Frequency
,@date_spot --Spot Date
,2 --Cash Basis
,2 --Futures Basis
,0 --Swaps Basis
,'L' --Interpolation
,'M' --Date Roll Rule
,NULL --Holidays
)
This produces the following result.
rn mat_date df rsource
---- ----------- ----------------- -------
1 2013-01-16 0.999995555575309 C
2 2013-01-17 0.999991111170370 C
3 2013-01-24 0.999956112706425 C
4 2013-01-31 0.999916450742048 C
5 2013-02-18 0.999804481000583 C
6 2013-03-18 0.999574621744643 C
7 2013-03-20 0.999555003248605 I
8 2013-06-19 0.998810196034368 F
9 2013-09-18 0.998040734351536 F
10 2013-12-18 0.997221489197563 F
11 2014-03-19 0.996340005054203 F
12 2014-06-18 0.995383883534963 F
13 2014-09-17 0.994315684670945 F
14 2014-12-17 0.993123247105497 F
15 2015-03-18 0.991756964143366 F
With the addition of the futures strip, we actually have fewer rows returned than when we just had the cash rates. We have also added a column to the resultant table, rsource, which identifies the source of the rate: 'C' for cash, 'I' for interpolated, and 'F' for futures.
The discount factors from the cash rates make up for the first six rows. The seventh row is an interpolated rate (using the discount factors from the cash rates) which marks the commencement of the futures strip. The discount factors in all the subsequent rows are calculated from the futures strip.
Unlike the cash rates, the futures contracts are not dated from the spot date; they are forward-dated contracts. Row 7 marks the beginning of the futures strip and is the value date for the March 2013 futures contract. Even though the futures contracts themselves are quoted for a fixed number of days, for purpose of our calculations the maturity date of the futures contract is the settlement date of the next futures contract in the strip. Additionally, it is assumed that the futures contracts settle on the third Wednesday of the settlement month.
The calculation of the discount factor, then, will still use the same 1/(1 + rt) formula as the cash rates, but instead of then multiplying that value by the spot date discount factor, we use the discount factor for the start date of the futures strip.
Since we stored the discount factors from the cash rates in the #df table, we have them available to us now for incorporation into the futures strip. We can use the following recursive CTE to calculate the discount factors. This CTE uses several XLeratorDB function including DFINTERP, FIRSWEEKDAY, EDATE, and YEARFRAC.
;WITH mycte as (
SELECT @date_spot as vDate
,n.mdate as mDate
,wct.DFINTERP(d.mDate,d.df,n.mdate,@date_start,'DF') as df
FROM (
SELECT MIN(mDate)
FROM #curves c
WHERE c.iType = 'F'
)n(mDate)
,#df d
GROUP BY n.mDate
UNION ALL
SELECT cast(c.mDate as date) as vDate
,wct.FIRSTWEEKDAY(wct.EDATE(c.mdate,3),'wed') + 14 as mDate
,1/(1+(c.rate * wct.YEARFRAC(c.mdate,wct.FIRSTWEEKDAY(wct.EDATE(c.mdate,3),'wed')+14,2))) * m.df
FROM #curves c
,mycte m
WHERE c.iType = 'F'
and c.mDate = m.mDate
)
SELECT mDate
,df
FROM mycte
This produces the following result.
mDate DF
---------- -----------------
2013-03-20 0.999555003248605
2013-06-19 0.998810196034368
2013-09-18 0.998040734351536
2013-12-18 0.997221489197563
2014-03-19 0.996340005054203
2014-06-18 0.995383883534963
2014-09-17 0.994315684670945
2014-12-17 0.993123247105497
2015-03-18 0.991756964143366
In the first part of the CTE we simply calculate the discount factor for the start of the futures strip by interpolating the discount factors from the cash rates. This is the value that is returned for the date 2013-03-20.
In the recursive part of the CTE, we calculate the discount factor from one futures date to the next and multiply it by the cumulative discount factor as at the start. For example, the discount factor for 2013-06-19 is calculated as:
SELECT 1/(1+(0.002950 * wct.YEARFRAC('2013-03-20','2013-06-19',2))) * 0.999555003248605
And the discount factor for 2013-09-18 is calculated as:
SELECT 1/(1+(0.003050 * wct.YEARFRAC('2013-06-19','2013-09-18',2))) * 0.998810196034368
The recursive part of the CTE carries out this calculation for us until the end of the futures strip.
Once the futures strip is incorporated into the curve, the cash rates are thrown out. This is why the last discount factor that is calculated from the cash rates is dated 2013-03-18. The futures strip commences on 2013-03-20 and it is these discount factors that will be used when the swap rates are added to the curve. For information purposes we return the discount factor for 2013-03-20 and identify it as having been interpolated, but it will not be used in the calculation when swap rates are added; only discount factors where the rsource is equal to 'C' (cash) or 'F' (futures) will be used.
Swap rates
Having calculated the discount factors for the cash rates and the futures strip we are ready to look at incorporating swap rates into the calculation. If you are follwing along in SMS, it’s best to just open up a new query in SMS and execute the following SQL which will put the discount factors into a temporary table called #df.
DECLARE @date_start as date = '2013-01-15' --Start Date
DECLARE @date_spot as date = wct.BUSINESSDATE(@date_start,'D',2,'F','') --Spot Date
SELECT wct.TENOR2DATE(tenor, @date_start,@date_spot,'') as mDate
,rate
,'C' as iType
INTO #curves
FROM (VALUES
('ON',.00160),
('1W',.00180),
('2W',.00192),
('1M',.00210),
('2M',.00250),
('3M',.00300),
('4M',.00354),
('5M',.00425),
('6M',.00491),
('7M',.00561),
('8M',.00614),
('9M',.00667),
('10M',.00715),
('11M',.00764),
('1Y',.00819)
) n(tenor,rate)
INSERT INTO #curves
SELECT wct.ED_FUT2DATE(ccode, @date_start)
,(100-pr)/100 as rate
,'F' as iType
FROM (VALUES
('H3',99.705),
('M3',99.695),
('U3',99.675),
('Z3',99.650),
('H4',99.620),
('M4',99.575),
('U4',99.525),
('Z4',99.455)
)n(ccode, pr)
SELECT mat_date as mDate
,df
INTO #df
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --InputData_RangeQuery
,@date_start --Start Date
,2 --Frequency (Semi-Annual)
,@date_spot --Spot Date
,2 --Cash Basis (A/360)
,2 --Futures Basis (A/3600
,0 --Swaps Basis (30/360)
,'L' --Interpolation (Linear)
,'M' --Date Roll (Modified Following)
,NULL --Holidays
)
WHERE rsource <> 'I'
This is the contents of the #df table.
mat_date df
---------- -----------------
2013-01-16 0.999995555575309
2013-01-17 0.999991111170370
2013-01-24 0.999956112706425
2013-01-31 0.999916450742048
2013-02-18 0.999804481000583
2013-03-18 0.999574621744643
2013-06-19 0.998810196034368
2013-09-18 0.998040734351536
2013-12-18 0.997221489197563
2014-03-19 0.996340005054203
2014-06-18 0.995383883534963
2014-09-17 0.994315684670945
2014-12-17 0.993123247105497
2015-03-18 0.991756964143366
We will add the following swap rate information to the #curves table and store the discount factor for the spot date in a variable.
DECLARE @df_spot as float =(SELECT df FROM #df WHERE mDate = @date_spot)
INSERT INTO #curves
SELECT wct.TENOR2DATE(tenor, @date_start,@date_spot,'') as mDate
,rate
,'S' as iType
FROM (VALUES
('2Y', .0038),
('3Y', .0050),
('4Y', .0068),
('5Y', .0091),
('7Y', .0138),
('10Y', .0192),
('30Y', .0288)
)n(tenor,rate)
Once again we call the SWAPCURVE table-valued-function to see what the discount factors look like.
SELECT ROW_NUMBER() over (ORDER BY mat_date) as rn
,mat_date
,df
,rsource
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --InputData_RangeQuery
,@date_start --Start Date
,2 --Frequency (Semi-Annual)
,@date_spot --Spot Date
,2 --Cash Basis (A/360)
,2 --Futures Basis (A/3600
,0 --Swaps Basis (30/360)
,'L' --Interpolation (Linear)
,'M' --Date Roll (Modified Following)
,NULL --Holidays
)
WHERE rsource <> 'I'
This produces the following result.
rn mat_date df rsource
---- ---------- ----------------- -------
1 2013-01-16 0.999995555575309 C
2 2013-01-17 0.999991111170370 C
3 2013-01-24 0.999956112706425 C
4 2013-01-31 0.999916450742048 C
5 2013-02-18 0.999804481000583 C
6 2013-03-18 0.999574621744643 C
7 2013-06-19 0.998810196034368 F
8 2013-09-18 0.998040734351536 F
9 2013-12-18 0.997221489197563 F
10 2014-03-19 0.996340005054203 F
11 2014-06-18 0.995383883534963 F
12 2014-09-17 0.994315684670945 F
13 2014-12-17 0.993123247105497 F
14 2015-01-19 0.992402568921137 S
15 2016-01-18 0.985084619298185 S
16 2017-01-17 0.973097820380660 S
17 2018-01-17 0.955265536976040 S
18 2020-01-17 0.906604014739839 S
19 2023-01-17 0.820620037129217 S
20 2043-01-19 0.385645644679129 S
Similar to what we saw when we added the futures strip, once we reach the first swap maturity date (row 14), the swap rates take over. Thus, even though we included futures contracts settling on 2014-12-17 and 2015-03-18, those values are thrown out in the final calculation. Let’s look at how the discount factors for the swap dates are calculated.
Like cash rates, swap interest amounts are calculated from the spot date. Unlike the cash rates, however, swaps have periodic interest payments. In this particular case the swaps pay interest semi-annually and interest is calculated using the 30/360 interest basis. Thus, swap interest rates are not directly comparable to cash rates. In order to include the swap rates in the construction of the yield curve, we need to use a technique called bootstrapping.
Bootstrapping is a recursive function that allows us to calculate interim discount factors starting from the first swap coupon date until the final swap maturity date. It is assumed that all swaps are quoted using the same calendar and that the swaps have the same interest payment dates. In other words, the 2-year, 3-year, 5-year, and 10-year swap are all assumed to have same date for the first, second, and third interest payment, and the maturity date of the 2-year swap is the same as the fourth interest payment date for all the other swaps.
To keep things (relatively) simple, we will look at all the discount factors (including interpolated values) from the SWAPCURVE function up to the 5-year swap date.
SELECT ROW_NUMBER() over (ORDER BY mat_date) as rn
,mat_date
,df
,rsource
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --InputData_RangeQuery
,@date_start --Start Date
,2 --Frequency (Semi-Annual)
,@date_spot --Spot Date
,2 --Cash Basis (A/360)
,2 --Futures Basis (A/3600
,0 --Swaps Basis (30/360)
,'L' --Interpolation (Linear)
,'M' --Date Roll (Modified Following)
,NULL --Holidays
)
/*WHERE rsource <> 'I'*/
WHERE mat_date <= '2018-01-17'
We can see some of the interpolated rates that were using in the bootstrapping process.
rn mat_date df rsource
---- ---------- ----------------- -------
1 2013-01-16 0.999995555575309 C
2 2013-01-17 0.999991111170370 C
3 2013-01-24 0.999956112706425 C
4 2013-01-31 0.999916450742048 C
5 2013-02-18 0.999804481000583 C
6 2013-03-18 0.999574621744643 C
7 2013-03-20 0.999555003248605 I
8 2013-06-19 0.998810196034368 F
9 2013-07-17 0.998578341032047 I
10 2013-09-18 0.998040734351536 F
11 2013-12-18 0.997221489197563 F
12 2014-01-17 0.996936852289729 I
13 2014-03-19 0.996340005054203 F
14 2014-06-18 0.995383883534963 F
15 2014-07-17 0.995051730046122 I
16 2014-09-17 0.994315684670945 F
17 2014-12-17 0.993123247105497 F
18 2015-01-19 0.992402568921137 S
19 2015-07-17 0.989077192332711 I
20 2016-01-18 0.985084619298185 S
21 2016-07-18 0.979520450171401 I
22 2017-01-17 0.973097820380660 S
23 2017-07-17 0.964756757717377 I
24 2018-01-17 0.955265536976040 S
Row 7 identifies the interpolated rate that was used to start the calculation of the discount factors from the futures strip. Rows 9, 12, and 15 are also identified as interpolated rates, representing the interest payment dates for the first swap maturing on 2015-01-19 (row 18).
Our first step is to put all the interest payment dates into a temp table. We are going out 5 years and interest is paid semi-annually requiring us to calculate 10 interest payment dates. We can use the XLeratorDB SeriesInt table-valued function and BUSINESSDATE function.
SELECT k.SeriesValue as PayNo
,wct.BUSINESSDATE('2013-01-17','M',6 *(k.seriesvalue - 1) ,'M','') as iStartDate
,wct.BUSINESSDATE('2013-01-17','M',6 * k.seriesvalue ,'M','') as iPayDate
INTO #iPayDates
FROM wctMath.wct.Seriesint(1,10,NULL,NULL,NULL) k
The #iPayDates table should contain the following data.
Payno iStartDate iPayDate
----- ---------- ----------
1 2013-01-17 2013-07-17
2 2013-07-17 2014-01-17
3 2014-01-17 2014-07-17
4 2014-07-17 2015-01-19
5 2015-01-19 2015-07-17
6 2015-07-17 2016-01-18
7 2016-01-18 2016-07-18
8 2016-07-18 2017-01-17
9 2017-01-17 2017-07-17
10 2017-07-17 2018-01-17
Since we already have discount factors from the cash and futures strip out to the 2-year maturity date, we only need to calculate interpolated swap rates from the 2-year to the 5-year maturity dates. We have specified linear interpolation and use the XLeratorDB INTERP function. We will put the interpolated rates into a temporary table as well.SELECT p.iPayDate
,wctMath.wct.INTERP(wct.YEARFRAC(@date_spot, c.mdate,3),c.rate,wct.YEARFRAC(@date_spot,p.iPaydate,3)) as iRate
into #interp
FROM #iPayDates p
,#curves c
WHERE c.iType = 'S'
AND p.IpayDate >=(SELECT MIN(c2.mdate) FROM #curves c2 where c2.iType = 'S')
GROUP BY p.iPayDate
This is what should be in the #interp table.
iPayDate iRate
---------- ----------------------
2015-01-19 0.0038
2015-07-17 0.00439010989010989
2016-01-18 0.005
2016-07-18 0.00589753424657534
2017-01-17 0.0068
2017-07-17 0.00794054794520548
2018-01-17 0.0091
We are also going to need the discount factor as of the spot date.
The bootstrap calculation is a recursive process; the calculation in each row is dependent on the previous row. As in the futures strip examples, we can model this process using a recursive CTE. The anchor of the CTE calculates the cumulative discount factor for all the interest payment dates prior to the 2-year swap maturity date. It includes some extra information that is not necessarily needed other than to keep the number of columns and the data type consistent within the CTE
The recursive part of the CTE then calculates the new discount factors from 2-year date forward, using the interpolated swap rates previously stored in the #interp table and the discount factor as of the spot date, @df_spot. While the cumulative discount factor is an important element in the calculation and it is being returned as part of this SELECT statement, it is not part of the resultant table returned by SWAPCURVE.
;WITH mycte as (
--Anchor
SELECT 3 as PayNo
,MAX(iStartDate) as iStartDate
,MAX(iPayDate) as iPayDate
,MIN(df) as df
,SUM(wct.YEARFRAC(iStartDate, iPayDate, 0) * df) as df_cum
FROM (
SELECT iStartDate
,iPayDate
,wct.DFINTERP(d.mDate,d.df,iPayDate,@date_spot,'DF') as df
FROM #df d
,#iPayDates
WHERE PayNo < 4
GROUP BY iStartDate
,iPayDate
) n
UNION ALL
--Recursion
SELECT P.PayNo
,p.iStartDate
,p.iPayDate
,(1-i.iRate * df_cum) /(i.iRate * wct.YEARFRAC(p.iStartDate, p.iPayDate, 0) + 1) * @df_spot
,df_cum +(1-i.iRate * df_cum) /(i.iRate * wct.YEARFRAC(p.iStartDate, p.iPayDate, 0) + 1) * @df_spot * wct.YEARFRAC(p.iStartDate, p.iPayDate, 0)
FROM #iPayDates p
,mycte m
,#interp i
WHERE p.PayNo = m.PayNo + 1
AND i.iPayDate = p.iPayDate
)
SELECT *
FROM mycte
This produces the following result.
PayNo iStartDate iPayDate df df_cum
----------- ---------- ---------- ----------------- -----------------
3 2014-01-17 2014-07-17 0.995051730046122 1.495283461683949
4 2014-07-17 2015-01-19 0.992402568921137 1.996998093749635
5 2015-01-19 2015-07-17 0.989077192332711 2.486041816625253
6 2015-07-17 2016-01-18 0.985084619298185 2.981320472439063
7 2016-01-18 2016-07-18 0.979520450171401 3.471080697524763
8 2016-07-18 2017-01-17 0.973097820380660 3.954926558214036
9 2017-01-17 2017-07-17 0.964756757717377 4.437304937072724
10 2017-07-17 2018-01-17 0.955265536976040 4.914937705560744
As you can see, this gives all the discount factors returned by the SWAPCURVE function from 2014-7-17 to 2018-01-17.
Of course, you should always use the SWAPCURVE function to calculate the discount factors; it takes care of all of the calculations described above. They are included in this article simply as a way to explain the math behind how the numbers are calculated.
The SWAPCURVE function also returns the zero coupon rate and the continuously compounded zero coupon rate. If we wanted to combine all of the above data into a single call to SWAPCURVE it might look like this.
DECLARE @date_start as date = '2013-01-15' --Start Date
DECLARE @date_spot as date = wct.BUSINESSDATE(@date_start,'D',2,'F','') --Spot Date
SELECT CASE iType
WHEN 'F' THEN wct.ED_FUT2DATE(tenor, @date_start)
ELSE wct.TENOR2DATE(tenor, @date_start,@date_spot,'')
END as mDate
,CASE iType
WHEN 'F' THEN (100-rate)/100
ELSE rate
END as rate
,iType
INTO #curves
FROM (VALUES
('ON',.00160,'C'),('1W',.00180,'C'),
('2W',.00192,'C'),('1M',.00210,'C'),
('2M',.00250,'C'),('3M',.00300,'C'),
('4M',.00354,'C'),('5M',.00425,'C'),
('6M',.00491,'C'),('7M',.00561,'C'),
('8M',.00614,'C'),('9M',.00667,'C'),
('10M',.00715,'C'),('11M',.00764,'C'),
('1Y',.00819,'C'),('H3',99.705,'F' ),
('M3',99.695,'F' ),('U3',99.675,'F' ),
('Z3',99.650,'F' ),('H4',99.620,'F' ),
('M4',99.575,'F' ),('U4',99.525,'F' ),
('Z4',99.455,'F' ),('2Y', .0038,'S'),
('3Y', .0050,'S'),('4Y', .0068,'S'),
('5Y', .0091,'S'),('7Y', .0138,'S'),
('10Y', .0192,'S'), ('30Y', .0288,'S')
) n(tenor,rate,iType)
SELECT *
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --InputData_RangeQuery
,@date_start --Start Date
,2 --Frequency (Semi-Annual)
,@date_spot --Spot Date
,2 --Cash Basis (A/360)
,2 --Futures Basis (A/3600
,0 --Swaps Basis (30/360)
,'L' --Interpolation (Linear)
,'M' --Date Roll (Modified Following)
,NULL --Holidays
)
WHERE rsource <> 'I'
This produces the following result.
mat_date df rsource zero_cpn cczero
---------- ----------------- ------- ----------------- -----------------
2013-01-16 0.999995555575309 C 0.001622222222233 0.001622218617313
2013-01-17 0.999991111170370 C 0.001622225827171 0.001622218617321
2013-01-24 0.999956112706425 C 0.001779951690017 0.001779912630818
2013-01-31 0.999916450742048 C 0.001906126702507 0.001906047072555
2013-02-18 0.999804481000583 C 0.002099364429962 0.002099159183767
2013-03-18 0.999574621744643 C 0.002505308658751 0.002504775731267
2013-06-19 0.998810196034368 F 0.002805133994750 0.002803464552740
2013-09-18 0.998040734351536 F 0.002912747340479 0.002909892052210
2013-12-18 0.997221489197563 F 0.003017751153554 0.003013554838160
2014-03-19 0.996340005054203 F 0.003132723122796 0.003126983240472
2014-06-18 0.995383883534963 F 0.003261457018310 0.003253917775971
2014-09-17 0.994315684670945 F 0.003420715070827 0.003410974385362
2014-12-17 0.993123247105497 F 0.003605413805865 0.003592988521333
2015-01-19 0.992402568921137 S 0.003806937034088 0.003792438800117
2016-01-18 0.985084619298185 S 0.005033282760393 0.004995558067203
2017-01-17 0.973097820380660 S 0.006897306200549 0.006803686579246
2018-01-17 0.955265536976040 S 0.009350499075811 0.009138163952695
2020-01-17 0.906604014739839 S 0.014699511229320 0.013990645789781
2023-01-17 0.820620037129217 S 0.021835147714837 0.019747866663816
2043-01-19 0.385645644679129 S 0.053048510856013 0.031729337450423
You can take the results of the SWAPCURVE function and then use other XLeratorDB functions to interpolate the discount factors using log-linear interpolation (DFINTERP), to interpolate the zero coupon rate or the continuously compounded zero rates using spline interpolation (SPLINE), straight-line interpolation (INTERP) or even Nelson-Siegel interpolation (NELSONSIEGEL). You can also use the table-valued function INTERPDFACT to do log-linear discount factor interpolation over a range of dates, store the discount factors in a table and then calculate the discounted cash flow value for hundreds of thousands or even millions of cash flows in just a few seconds.
If you have any questions, please feel free to contact us at support@westclintech.com. If you would like to obtain a free 15-day trial of XLeratorDB just click on this link.