SWAPCURVE
Updated 2023-10-13 13:59:13.957000
Syntax
SELECT * FROM [westclintech].[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),>)
Description
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.
Arguments
@DateRoll
The rule to be used when a calculated date lands on a non-business day. The @DateRollRule values are:
{"columns":[{"field":"A","width":67},{"field":"actual day is returned with no adjustment.","width":523}],"rows":[{"A":"F","actual day is returned with no adjustment.":"next business day is returned."},{"A":"M","actual day is returned with no adjustment.":"next business day is returned unless it is in a different month in which case the previous business day is returned."},{"A":"P","actual day is returned with no adjustment.":"preceding business day is returned."},{"A":"MP","actual day is returned with no adjustment.":"preceding business day is returned unless it is in a different month in which in case the next business day is returned."}]}
@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).
@Interpolation
The interpolation method to be used with the swaps rates. Valid values are L (linear) and S (Spline).
@Frequency
the compounding frequency for the swaps rates. Permissible values are 1 (annually), 2 (semi-annually), 4 (quarterly) and 12 (monthly).
@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).
@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.
@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.
@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.
@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).
@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
table
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "d36f1bcc-4131-4275-93c3-3e3bd76fd4d9", "colName": "mat_date", "colDatatype": "datetime", "colDesc": "Maturity date. The date of the discount factor."}, {"id": "baff5a02-f510-4243-bb5f-20abc86b66d2", "colName": "df", "colDatatype": "float", "colDesc": "Discount factor."}, {"id": "8e49791f-7fd8-427e-b91e-d05152c13213", "colName": "rsource", "colDatatype": "nvarchar(4000)", "colDesc": "Rate source. 'C' for cash, 'F' for futures, 'S' for swaps, 'I' for interpolated."}, {"id": "4356c81e-2812-4f41-b661-566c95171c12", "colName": "cczero", "colDatatype": "float", "colDesc": "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.
{"columns":[{"field":"mat_date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"df","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"rsource"},{"field":"zero_cpn","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cczero","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"mat_date":"2013-03-11","df":"0.999974445097514","rsource":"C","zero_cpn":"0.002331944444439","cczero":"0.002331914647881"},{"mat_date":"2013-04-11","df":"0.999776433820482","rsource":"C","zero_cpn":"0.002331997227822","cczero":"0.002331736530539"},{"mat_date":"2013-06-11","df":"0.999259419468472","rsource":"C","zero_cpn":"0.002817835730195","cczero":"0.002816792055380"},{"mat_date":"2013-09-11","df":"0.997730659702805","rsource":"C","zero_cpn":"0.004415921273181","cczero":"0.004410906864564"},{"mat_date":"2014-03-11","df":"0.996882833702945","rsource":"S","zero_cpn":"0.003093017325855","cczero":"0.003088191584351"},{"mat_date":"2014-09-11","df":"0.994738254820303","rsource":"I","zero_cpn":"0.003491312513587","cczero":"0.003482111162617"},{"mat_date":"2015-03-11","df":"0.992210547586236","rsource":"S","zero_cpn":"0.003903910868845","cczero":"0.003888666571725"},{"mat_date":"2015-09-11","df":"0.988409313511400","rsource":"I","zero_cpn":"0.004662539383365","cczero":"0.004635413360614"},{"mat_date":"2016-03-11","df":"0.983899336463167","rsource":"S","zero_cpn":"0.005429918186613","cczero":"0.005385969035826"},{"mat_date":"2016-09-12","df":"0.977748016665409","rsource":"I","zero_cpn":"0.006464449083408","cczero":"0.006391986181362"},{"mat_date":"2017-03-13","df":"0.970696482511936","rsource":"S","zero_cpn":"0.007511022264681","cczero":"0.007399881598589"},{"mat_date":"2017-09-11","df":"0.961417467684632","rsource":"I","zero_cpn":"0.008882821375967","cczero":"0.008709213133916"},{"mat_date":"2018-03-12","df":"0.950875411471596","rsource":"S","zero_cpn":"0.010298638849671","cczero":"0.010041433654505"},{"mat_date":"2018-09-11","df":"0.939464625035334","rsource":"I","zero_cpn":"0.011677831470972","cczero":"0.011317014170550"},{"mat_date":"2019-03-11","df":"0.927008124079818","rsource":"I","zero_cpn":"0.013093306217858","cczero":"0.012603383421096"},{"mat_date":"2019-09-11","df":"0.913366689366424","rsource":"I","zero_cpn":"0.014552518653478","cczero":"0.013903116612769"},{"mat_date":"2020-03-11","df":"0.898776137247601","rsource":"S","zero_cpn":"0.016051464362951","cczero":"0.015210179748591"},{"mat_date":"2020-09-11","df":"0.885263536866259","rsource":"I","zero_cpn":"0.017233734477848","cczero":"0.016204922489765"},{"mat_date":"2021-03-11","df":"0.871142848233315","rsource":"I","zero_cpn":"0.018451755644985","cczero":"0.017208304310179"},{"mat_date":"2021-09-13","df":"0.856043392396973","rsource":"I","zero_cpn":"0.019723732348518","cczero":"0.018230555079216"},{"mat_date":"2022-03-11","df":"0.840737223990592","rsource":"I","zero_cpn":"0.021009658203270","cczero":"0.019239983416046"},{"mat_date":"2022-09-12","df":"0.824404497953078","rsource":"I","zero_cpn":"0.022365887245351","cczero":"0.020275978277086"},{"mat_date":"2023-03-13","df":"0.807594871522675","rsource":"S","zero_cpn":"0.023772357607831","cczero":"0.021322739511739"},{"mat_date":"2023-09-11","df":"0.797094925159550","rsource":"I","zero_cpn":"0.024196051779693","cczero":"0.021556054435498"},{"mat_date":"2024-03-11","df":"0.786423651997823","rsource":"I","zero_cpn":"0.024646053547664","cczero":"0.021803770890632"},{"mat_date":"2024-09-11","df":"0.775649881397395","rsource":"I","zero_cpn":"0.025100603828752","cczero":"0.022047010508417"},{"mat_date":"2025-03-11","df":"0.764846851110325","rsource":"I","zero_cpn":"0.025580059227344","cczero":"0.022304325469763"},{"mat_date":"2025-09-11","df":"0.753934741602770","rsource":"I","zero_cpn":"0.026061426737071","cczero":"0.022553938848428"},{"mat_date":"2026-03-11","df":"0.743005676454337","rsource":"I","zero_cpn":"0.026567325184766","cczero":"0.022816462952316"},{"mat_date":"2026-09-11","df":"0.731973829835692","rsource":"I","zero_cpn":"0.027076926129074","cczero":"0.023072090517917"},{"mat_date":"2027-03-11","df":"0.720937156464926","rsource":"I","zero_cpn":"0.027610994752764","cczero":"0.023339692613036"},{"mat_date":"2027-09-13","df":"0.709681477174981","rsource":"I","zero_cpn":"0.028156749207472","cczero":"0.023604138630319"},{"mat_date":"2028-03-13","df":"0.698537864872117","rsource":"I","zero_cpn":"0.028718321353362","cczero":"0.023874120495007"},{"mat_date":"2028-09-11","df":"0.687444256357346","rsource":"I","zero_cpn":"0.029283949424948","cczero":"0.024138469216980"},{"mat_date":"2029-03-12","df":"0.676184715394252","rsource":"I","zero_cpn":"0.029884310336269","cczero":"0.024417931665849"},{"mat_date":"2029-09-11","df":"0.664964823619493","rsource":"I","zero_cpn":"0.030487602051719","cczero":"0.024689607897286"},{"mat_date":"2030-03-11","df":"0.653713425482731","rsource":"I","zero_cpn":"0.031120012367053","cczero":"0.024972874130968"},{"mat_date":"2030-09-11","df":"0.642384168861800","rsource":"I","zero_cpn":"0.031764236462648","cczero":"0.025252086531706"},{"mat_date":"2031-03-11","df":"0.631096460866758","rsource":"I","zero_cpn":"0.032435162070314","cczero":"0.025540930947872"},{"mat_date":"2031-09-11","df":"0.619736959862560","rsource":"I","zero_cpn":"0.033120311799466","cczero":"0.025826375994832"},{"mat_date":"2032-03-11","df":"0.608409473761774","rsource":"I","zero_cpn":"0.033831352533024","cczero":"0.026119111275627"},{"mat_date":"2032-09-13","df":"0.596911871263372","rsource":"I","zero_cpn":"0.034569502258474","cczero":"0.026414420122145"},{"mat_date":"2033-03-11","df":"0.585726776588969","rsource":"I","zero_cpn":"0.035320487388731","cczero":"0.026712159684831"},{"mat_date":"2033-09-12","df":"0.574294803864427","rsource":"I","zero_cpn":"0.036103827043696","cczero":"0.027012747919353"},{"mat_date":"2034-03-13","df":"0.562936528893093","rsource":"I","zero_cpn":"0.036918409102998","cczero":"0.027322142261104"},{"mat_date":"2034-09-11","df":"0.551710553821896","rsource":"I","zero_cpn":"0.037742273970760","cczero":"0.027624978515574"},{"mat_date":"2035-03-12","df":"0.540381814616948","rsource":"I","zero_cpn":"0.038612977692682","cczero":"0.027941536528259"},{"mat_date":"2035-09-11","df":"0.529128442680286","rsource":"I","zero_cpn":"0.039500621152143","cczero":"0.028253835210513"},{"mat_date":"2036-03-11","df":"0.517883036524153","rsource":"I","zero_cpn":"0.040427405768877","cczero":"0.028574912440061"},{"mat_date":"2036-09-11","df":"0.506617814851251","rsource":"I","zero_cpn":"0.041385982372857","cczero":"0.028897357949475"},{"mat_date":"2037-03-11","df":"0.495455358394768","rsource":"I","zero_cpn":"0.042382672626902","cczero":"0.029228218758796"},{"mat_date":"2037-09-11","df":"0.484254989344199","rsource":"I","zero_cpn":"0.043414690136202","cczero":"0.029559687383324"},{"mat_date":"2038-03-11","df":"0.473166430097940","rsource":"I","zero_cpn":"0.044488096922467","cczero":"0.029899557030417"},{"mat_date":"2038-09-13","df":"0.461923703698719","rsource":"I","zero_cpn":"0.045614610247248","cczero":"0.030244584695020"},{"mat_date":"2039-03-11","df":"0.451044182765596","rsource":"I","zero_cpn":"0.046761406425977","cczero":"0.030590457051664"},{"mat_date":"2039-09-12","df":"0.439955265915978","rsource":"I","zero_cpn":"0.047974166407578","cczero":"0.030944244950757"},{"mat_date":"2040-03-12","df":"0.429029768995448","rsource":"I","zero_cpn":"0.049230446482325","cczero":"0.031303696601327"},{"mat_date":"2040-09-11","df":"0.418166530457326","rsource":"I","zero_cpn":"0.050533133985254","cczero":"0.031665131100932"},{"mat_date":"2041-03-11","df":"0.407380249456956","rsource":"I","zero_cpn":"0.051898037002262","cczero":"0.032037241069873"},{"mat_date":"2041-09-11","df":"0.396575302977135","rsource":"I","zero_cpn":"0.053325017484750","cczero":"0.032413308487447"},{"mat_date":"2042-03-11","df":"0.385914704914699","rsource":"I","zero_cpn":"0.054813594851867","cczero":"0.032798291861593"},{"mat_date":"2042-09-11","df":"0.375239743644282","rsource":"I","zero_cpn":"0.056373976155606","cczero":"0.033188256162288"},{"mat_date":"2043-03-11","df":"0.364716317935350","rsource":"S","zero_cpn":"0.058003632484538","cczero":"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.
{"columns":[{"field":"mat_date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"df","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"rsource"},{"field":"zero_cpn","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cczero","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"mat_date":"2013-03-11","df":"0.999974445097514","rsource":"C","zero_cpn":"0.002331944444439","cczero":"0.002331914647881"},{"mat_date":"2013-04-11","df":"0.999776433820482","rsource":"C","zero_cpn":"0.002331997227822","cczero":"0.002331736530539"},{"mat_date":"2013-06-11","df":"0.999259419468472","rsource":"C","zero_cpn":"0.002817835730195","cczero":"0.002816792055380"},{"mat_date":"2013-09-18","df":"0.998413753235007","rsource":"F","zero_cpn":"0.002973845813417","cczero":"0.002971485938654"},{"mat_date":"2013-12-18","df":"0.997619398788721","rsource":"F","zero_cpn":"0.003045429821463","cczero":"0.003041801964532"},{"mat_date":"2014-03-19","df":"0.996724976556286","rsource":"F","zero_cpn":"0.003181197160946","cczero":"0.003175982217172"},{"mat_date":"2014-06-18","df":"0.995743339580683","rsource":"F","zero_cpn":"0.003334023114164","cczero":"0.003326917122285"},{"mat_date":"2014-09-17","df":"0.994637082114909","rsource":"F","zero_cpn":"0.003520607073795","cczero":"0.003511149789111"},{"mat_date":"2014-12-17","df":"0.993369018968611","rsource":"F","zero_cpn":"0.003748406499251","cczero":"0.003735951132099"},{"mat_date":"2015-03-11","df":"0.992209307176538","rsource":"S","zero_cpn":"0.003904537417528","cczero":"0.003889288239548"},{"mat_date":"2016-03-11","df":"0.983897627574036","rsource":"S","zero_cpn":"0.005430503938217","cczero":"0.005386545355940"},{"mat_date":"2017-03-13","df":"0.970694156874036","rsource":"S","zero_cpn":"0.007511636364509","cczero":"0.007400477702418"},{"mat_date":"2018-03-12","df":"0.950872297832894","rsource":"S","zero_cpn":"0.010299325328147","cczero":"0.010042086408939"},{"mat_date":"2020-03-11","df":"0.898771587255192","rsource":"S","zero_cpn":"0.016052267136566","cczero":"0.015210901260534"},{"mat_date":"2023-03-13","df":"0.807588927565738","rsource":"S","zero_cpn":"0.023773266978431","cczero":"0.021323473912069"},{"mat_date":"2043-03-11","df":"0.364711076002967","rsource":"S","zero_cpn":"0.058004944774945","cczero":"0.033587919093189"}]}