SWAPCURVE
Updated: 11 Mar 2013
Use the tablevalued function SWAPCURVE to calculate discount factors, zerocoupon rates, and continuously compounded zerocoupon 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 TSQL 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 (semiannually),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 nonbusiness 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 (nonbusiness) 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 convexityadjusted 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,'20130307', '20130311','') 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
,'20130307' @StartDate
,2 @Frequency
,'20130311' @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
    
20130311 0.999974445097514 C 0.002331944444439 0.002331914647881
20130411 0.999776433820482 C 0.002331997227822 0.002331736530539
20130611 0.999259419468472 C 0.002817835730195 0.002816792055380
20130911 0.997730659702805 C 0.004415921273181 0.004410906864564
20140311 0.996882833702945 S 0.003093017325855 0.003088191584351
20140911 0.994738254820303 I 0.003491312513587 0.003482111162617
20150311 0.992210547586236 S 0.003903910868845 0.003888666571725
20150911 0.988409313511400 I 0.004662539383365 0.004635413360614
20160311 0.983899336463167 S 0.005429918186613 0.005385969035826
20160912 0.977748016665409 I 0.006464449083408 0.006391986181362
20170313 0.970696482511936 S 0.007511022264681 0.007399881598589
20170911 0.961417467684632 I 0.008882821375967 0.008709213133916
20180312 0.950875411471596 S 0.010298638849671 0.010041433654505
20180911 0.939464625035334 I 0.011677831470972 0.011317014170550
20190311 0.927008124079818 I 0.013093306217858 0.012603383421096
20190911 0.913366689366424 I 0.014552518653478 0.013903116612769
20200311 0.898776137247601 S 0.016051464362951 0.015210179748591
20200911 0.885263536866259 I 0.017233734477848 0.016204922489765
20210311 0.871142848233315 I 0.018451755644985 0.017208304310179
20210913 0.856043392396973 I 0.019723732348518 0.018230555079216
20220311 0.840737223990592 I 0.021009658203270 0.019239983416046
20220912 0.824404497953078 I 0.022365887245351 0.020275978277086
20230313 0.807594871522675 S 0.023772357607831 0.021322739511739
20230911 0.797094925159550 I 0.024196051779693 0.021556054435498
20240311 0.786423651997823 I 0.024646053547664 0.021803770890632
20240911 0.775649881397395 I 0.025100603828752 0.022047010508417
20250311 0.764846851110325 I 0.025580059227344 0.022304325469763
20250911 0.753934741602770 I 0.026061426737071 0.022553938848428
20260311 0.743005676454337 I 0.026567325184766 0.022816462952316
20260911 0.731973829835692 I 0.027076926129074 0.023072090517917
20270311 0.720937156464926 I 0.027610994752764 0.023339692613036
20270913 0.709681477174981 I 0.028156749207472 0.023604138630319
20280313 0.698537864872117 I 0.028718321353362 0.023874120495007
20280911 0.687444256357346 I 0.029283949424948 0.024138469216980
20290312 0.676184715394252 I 0.029884310336269 0.024417931665849
20290911 0.664964823619493 I 0.030487602051719 0.024689607897286
20300311 0.653713425482731 I 0.031120012367053 0.024972874130968
20300911 0.642384168861800 I 0.031764236462648 0.025252086531706
20310311 0.631096460866758 I 0.032435162070314 0.025540930947872
20310911 0.619736959862560 I 0.033120311799466 0.025826375994832
20320311 0.608409473761774 I 0.033831352533024 0.026119111275627
20320913 0.596911871263372 I 0.034569502258474 0.026414420122145
20330311 0.585726776588969 I 0.035320487388731 0.026712159684831
20330912 0.574294803864427 I 0.036103827043696 0.027012747919353
20340313 0.562936528893093 I 0.036918409102998 0.027322142261104
20340911 0.551710553821896 I 0.037742273970760 0.027624978515574
20350312 0.540381814616948 I 0.038612977692682 0.027941536528259
20350911 0.529128442680286 I 0.039500621152143 0.028253835210513
20360311 0.517883036524153 I 0.040427405768877 0.028574912440061
20360911 0.506617814851251 I 0.041385982372857 0.028897357949475
20370311 0.495455358394768 I 0.042382672626902 0.029228218758796
20370911 0.484254989344199 I 0.043414690136202 0.029559687383324
20380311 0.473166430097940 I 0.044488096922467 0.029899557030417
20380913 0.461923703698719 I 0.045614610247248 0.030244584695020
20390311 0.451044182765596 I 0.046761406425977 0.030590457051664
20390912 0.439955265915978 I 0.047974166407578 0.030944244950757
20400312 0.429029768995448 I 0.049230446482325 0.031303696601327
20400911 0.418166530457326 I 0.050533133985254 0.031665131100932
20410311 0.407380249456956 I 0.051898037002262 0.032037241069873
20410911 0.396575302977135 I 0.053325017484750 0.032413308487447
20420311 0.385914704914699 I 0.054813594851867 0.032798291861593
20420911 0.375239743644282 I 0.056373976155606 0.033188256162288
20430311 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 1year 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, '20130307')
ELSE wct.TENOR2DATE(mDate,'20130307', '20130311','')
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
,'20130307' @StartDate
,2 @Frequency
,'20130311' @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
    
20130311 0.999974445097514 C 0.002331944444439 0.002331914647881
20130411 0.999776433820482 C 0.002331997227822 0.002331736530539
20130611 0.999259419468472 C 0.002817835730195 0.002816792055380
20130918 0.998413753235007 F 0.002973845813417 0.002971485938654
20131218 0.997619398788721 F 0.003045429821463 0.003041801964532
20140319 0.996724976556286 F 0.003181197160946 0.003175982217172
20140618 0.995743339580683 F 0.003334023114164 0.003326917122285
20140917 0.994637082114909 F 0.003520607073795 0.003511149789111
20141217 0.993369018968611 F 0.003748406499251 0.003735951132099
20150311 0.992209307176538 S 0.003904537417528 0.003889288239548
20160311 0.983897627574036 S 0.005430503938217 0.005386545355940
20170313 0.970694156874036 S 0.007511636364509 0.007400477702418
20180312 0.950872297832894 S 0.010299325328147 0.010042086408939
20200311 0.898771587255192 S 0.016052267136566 0.015210901260534
20230313 0.807588927565738 S 0.023773266978431 0.021323473912069
20430311 0.364711076002967 S 0.058004944774945 0.033587919093189