SWAPCURVE
Updated: 11 Mar 2013
Use the table-valued function SWAPCURVE to calculate discount factors, zero-coupon rates, and continuously compounded zero-coupon rates from a series of cash rates, futures prices, or swaps rates.
Syntax
SELECT * FROM [wctFinancial].[wct].[SWAPCURVE](
<@InputData_RangeQuery, nvarchar(max),>
,<@StartDate, datetime,>
,<@Frequency, float,>
,<@SpotDate, datetime,>
,<@CashBasis, nvarchar(4000),>
,<@FuturesBasis, nvarchar(4000),>
,<@SwapsBasis, nvarchar(4000),>
,<@Interpolation, nvarchar(4000),>
,<@DateRoll, nvarchar(4000),>
,<@Holidays, nvarchar(max),>)
Arguments
@InputData_RangeQuery
a T-SQL statement, as a string, the specifies the cash rates, futures prices, and swap rates to be used in the SWAPCURVE calculations.
@StartDate
the starting date associated with the cash rates. @StartDate must be of the type datetime or of a type that implicitly converts to datetime.
@Frequency
the compounding frequency for the swaps rates. Permissible values are 1 (annually),2 (semi-annually),4 (quarterly) ,and 12 (monthly).
@SpotDate
The normal settlement date associated with the @StartDate. @SpotDate must be of the type datetime or of a type that implicitly converts to datetime.
@CashBasis
The interest basis code associated with the cash rates. Valid values are 0 (30/360), 1 (Actual/Actual), 2 (Actual/360), 3 Actual/365), and 4 (E30/360).
@FuturesBasis
The interest basis code associated with the futures prices. Valid values are 0 (30/360), 1 (Actual/Actual), 2 (Actual/360), 3 Actual/365), and 4 (E30/360).
@SwapsBasis
The interest basis code associated with the swaps rates. Valid values are 0 (30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), and 4 (E30/360).
@Interpolation
The interpolation method to be used with the swaps rates. Valid values are L (linear) and S (Spline).
@DateRoll
The rule to be used when a calculated date lands on a non-business day. The @DateRollRule values are:
|
A
|
actual day is returned with no adjustment.
|
|
F
|
next business day is returned.
|
|
M
|
next business day is returned unless it is in a different month in which case the previous business day is returned.
|
|
P
|
preceding business day is returned.
|
|
MP
|
preceding business day is returned unless it is in a different month in which in case the next business day is returned.
|
@Holidays
a comma separated string containing the holiday (non-business) dates to be used in the calculation of the number of business days. You can use the aggregate function NBD to create an appropriately formatted string.
Return Type
RETURNS TABLE (
[mat_date] [datetime] NULL,
[df] [float] NULL,
[rsource] [nchar](3) NULL,
[zero_cpn] [float] NULL,
[cczero] [float] NULL
)
|
Column
|
Column Description
|
|
mat_date
|
Maturity date. The date of the discount factor.
|
|
df
|
Discount factor.
|
|
rsource
|
Rate source. 'C' for cash, 'F' for futures, 'S' for swaps, 'I' for interpolated.
|
|
zero_cpn
|
Zero coupon rate calculated from the discount factor
|
|
cczero
|
Continuously compounded zero coupon rate calculated from the discount factor
|
Remarks
· To interpolate the zero coupon or continuously compounded zero coupon rate from the discount factors use the DFINTERP aggregate function.
· Use the TENOR2DATE scalar function to convert abbreviations like 1M and 1Y to dates based on the spot date.
· Use the ED_FUT2DATE scalar function to convert futures contract codes to the correct settlement date based on the start date.
· Use the ED_FUT_CONV_ADJ_HL scalar function to convert the futures price to a convexity-adjusted interest rate.
Examples
In this example we will take a series of cash and swaps rates and convert them into zero coupon rates for the same date using linear interpolation. Note that we use the XLeratorDB TENOR2DATE function to calculate the actual maturity dates from the input.
SET NOCOUNT ON
SELECT wct.TENOR2DATE(mDate,'2013-03-07', '2013-03-11','') as mDate
,cRate
,iType
into #curves
FROM (
SELECT '1M', .0023, 'C' UNION ALL
SELECT '3M', .0028, 'C' UNION ALL
SELECT '6M', .0044, 'C' UNION ALL
SELECT '1Y', .0031, 'S' UNION ALL
SELECT '2Y', .0039, 'S' UNION ALL
SELECT '3Y', .0054, 'S' UNION ALL
SELECT '4Y', .0074, 'S' UNION ALL
SELECT '5Y', .0100, 'S' UNION ALL
SELECT '7Y', .0150, 'S' UNION ALL
SELECT '10Y', .0207, 'S' UNION ALL
SELECT '30Y', .0304, 'S'
)n(mDate, cRate, iType)
SELECT *
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --@InputData_RangeQuery
,'2013-03-07' --@StartDate
,2 --@Frequency
,'2013-03-11' --@SpotDate
,2 --@CashBasis
,2 --@FuturesBasis
,0 --@SwapsBasis
,'L' --@InterpMethod
,'M' --@DateRoll
,NULL --@Holidays
)
DROP TABLE #curves
This produces the following result.
mat_date df rsource zero_cpn cczero
---------- ----------------- ------- ----------------- -----------------
2013-03-11 0.999974445097514 C 0.002331944444439 0.002331914647881
2013-04-11 0.999776433820482 C 0.002331997227822 0.002331736530539
2013-06-11 0.999259419468472 C 0.002817835730195 0.002816792055380
2013-09-11 0.997730659702805 C 0.004415921273181 0.004410906864564
2014-03-11 0.996882833702945 S 0.003093017325855 0.003088191584351
2014-09-11 0.994738254820303 I 0.003491312513587 0.003482111162617
2015-03-11 0.992210547586236 S 0.003903910868845 0.003888666571725
2015-09-11 0.988409313511400 I 0.004662539383365 0.004635413360614
2016-03-11 0.983899336463167 S 0.005429918186613 0.005385969035826
2016-09-12 0.977748016665409 I 0.006464449083408 0.006391986181362
2017-03-13 0.970696482511936 S 0.007511022264681 0.007399881598589
2017-09-11 0.961417467684632 I 0.008882821375967 0.008709213133916
2018-03-12 0.950875411471596 S 0.010298638849671 0.010041433654505
2018-09-11 0.939464625035334 I 0.011677831470972 0.011317014170550
2019-03-11 0.927008124079818 I 0.013093306217858 0.012603383421096
2019-09-11 0.913366689366424 I 0.014552518653478 0.013903116612769
2020-03-11 0.898776137247601 S 0.016051464362951 0.015210179748591
2020-09-11 0.885263536866259 I 0.017233734477848 0.016204922489765
2021-03-11 0.871142848233315 I 0.018451755644985 0.017208304310179
2021-09-13 0.856043392396973 I 0.019723732348518 0.018230555079216
2022-03-11 0.840737223990592 I 0.021009658203270 0.019239983416046
2022-09-12 0.824404497953078 I 0.022365887245351 0.020275978277086
2023-03-13 0.807594871522675 S 0.023772357607831 0.021322739511739
2023-09-11 0.797094925159550 I 0.024196051779693 0.021556054435498
2024-03-11 0.786423651997823 I 0.024646053547664 0.021803770890632
2024-09-11 0.775649881397395 I 0.025100603828752 0.022047010508417
2025-03-11 0.764846851110325 I 0.025580059227344 0.022304325469763
2025-09-11 0.753934741602770 I 0.026061426737071 0.022553938848428
2026-03-11 0.743005676454337 I 0.026567325184766 0.022816462952316
2026-09-11 0.731973829835692 I 0.027076926129074 0.023072090517917
2027-03-11 0.720937156464926 I 0.027610994752764 0.023339692613036
2027-09-13 0.709681477174981 I 0.028156749207472 0.023604138630319
2028-03-13 0.698537864872117 I 0.028718321353362 0.023874120495007
2028-09-11 0.687444256357346 I 0.029283949424948 0.024138469216980
2029-03-12 0.676184715394252 I 0.029884310336269 0.024417931665849
2029-09-11 0.664964823619493 I 0.030487602051719 0.024689607897286
2030-03-11 0.653713425482731 I 0.031120012367053 0.024972874130968
2030-09-11 0.642384168861800 I 0.031764236462648 0.025252086531706
2031-03-11 0.631096460866758 I 0.032435162070314 0.025540930947872
2031-09-11 0.619736959862560 I 0.033120311799466 0.025826375994832
2032-03-11 0.608409473761774 I 0.033831352533024 0.026119111275627
2032-09-13 0.596911871263372 I 0.034569502258474 0.026414420122145
2033-03-11 0.585726776588969 I 0.035320487388731 0.026712159684831
2033-09-12 0.574294803864427 I 0.036103827043696 0.027012747919353
2034-03-13 0.562936528893093 I 0.036918409102998 0.027322142261104
2034-09-11 0.551710553821896 I 0.037742273970760 0.027624978515574
2035-03-12 0.540381814616948 I 0.038612977692682 0.027941536528259
2035-09-11 0.529128442680286 I 0.039500621152143 0.028253835210513
2036-03-11 0.517883036524153 I 0.040427405768877 0.028574912440061
2036-09-11 0.506617814851251 I 0.041385982372857 0.028897357949475
2037-03-11 0.495455358394768 I 0.042382672626902 0.029228218758796
2037-09-11 0.484254989344199 I 0.043414690136202 0.029559687383324
2038-03-11 0.473166430097940 I 0.044488096922467 0.029899557030417
2038-09-13 0.461923703698719 I 0.045614610247248 0.030244584695020
2039-03-11 0.451044182765596 I 0.046761406425977 0.030590457051664
2039-09-12 0.439955265915978 I 0.047974166407578 0.030944244950757
2040-03-12 0.429029768995448 I 0.049230446482325 0.031303696601327
2040-09-11 0.418166530457326 I 0.050533133985254 0.031665131100932
2041-03-11 0.407380249456956 I 0.051898037002262 0.032037241069873
2041-09-11 0.396575302977135 I 0.053325017484750 0.032413308487447
2042-03-11 0.385914704914699 I 0.054813594851867 0.032798291861593
2042-09-11 0.375239743644282 I 0.056373976155606 0.033188256162288
2043-03-11 0.364716317935350 S 0.058003632484538 0.033587440482903
In this example we include the Eurodollar’s futures strip, starting with the June 2013 contract and we eliminate the 1-year swaps contract. Note that we use the XLeratorDB ED_FUT2DATE function to convert the futures contract code to the appropriate settlement date. We also exclude the interpolated values from the resultant table.
SELECT CASE iType
WHEN 'F' THEN wct.ED_FUT2DATE(mDate, '2013-03-07')
ELSE wct.TENOR2DATE(mDate,'2013-03-07', '2013-03-11','')
END as mDate
,Case iType
WHEN 'F' THEN (100 - cRate) / 100
ELSE cRate
END as cRate
,iType
into #curves
FROM (
SELECT '1M', .0023, 'C' UNION ALL
SELECT '3M', .0028, 'C' UNION ALL
SELECT '6M', .0044, 'C' UNION ALL
SELECT '2Y', .0039, 'S' UNION ALL
SELECT '3Y', .0054, 'S' UNION ALL
SELECT '4Y', .0074, 'S' UNION ALL
SELECT '5Y', .0100, 'S' UNION ALL
SELECT '7Y', .0150, 'S' UNION ALL
SELECT '10Y', .0207, 'S' UNION ALL
SELECT '30Y', .0304, 'S' UNION ALL
SELECT 'M3', 99.7050, 'F' UNION ALL
SELECT 'U3', 99.6850, 'F' UNION ALL
SELECT 'Z3', 99.6450, 'F' UNION ALL
SELECT 'H4', 99.6100, 'F' UNION ALL
SELECT 'M4', 99.5600, 'F' UNION ALL
SELECT 'U4', 99.4950, 'F' UNION ALL
SELECT 'Z4', 99.4050, 'F'
)n(mDate, cRate, iType)
SELECT *
FROM wct.SWAPCURVE(
'SELECT * FROM #curves' --@InputData_RangeQuery
,'2013-03-07' --@StartDate
,2 --@Frequency
,'2013-03-11' --@SpotDate
,2 --@CashBasis
,2 --@FuturesBasis
,0 --@SwapsBasis
,'L' --@InterpMethod
,'M' --@DateRoll
,NULL --@Holidays
)
WHERE rsource <> 'I' --Exclude interpolation
DROP TABLE #curves
This produces the following result.
mat_date df rsource zero_cpn cczero
---------- ----------------- ------- ----------------- -----------------
2013-03-11 0.999974445097514 C 0.002331944444439 0.002331914647881
2013-04-11 0.999776433820482 C 0.002331997227822 0.002331736530539
2013-06-11 0.999259419468472 C 0.002817835730195 0.002816792055380
2013-09-18 0.998413753235007 F 0.002973845813417 0.002971485938654
2013-12-18 0.997619398788721 F 0.003045429821463 0.003041801964532
2014-03-19 0.996724976556286 F 0.003181197160946 0.003175982217172
2014-06-18 0.995743339580683 F 0.003334023114164 0.003326917122285
2014-09-17 0.994637082114909 F 0.003520607073795 0.003511149789111
2014-12-17 0.993369018968611 F 0.003748406499251 0.003735951132099
2015-03-11 0.992209307176538 S 0.003904537417528 0.003889288239548
2016-03-11 0.983897627574036 S 0.005430503938217 0.005386545355940
2017-03-13 0.970694156874036 S 0.007511636364509 0.007400477702418
2018-03-12 0.950872297832894 S 0.010299325328147 0.010042086408939
2020-03-11 0.898771587255192 S 0.016052267136566 0.015210901260534
2023-03-13 0.807588927565738 S 0.023773266978431 0.021323473912069
2043-03-11 0.364711076002967 S 0.058004944774945 0.033587919093189