CMTCurve
Updated 2024-02-14 19:56:24.683000
Syntax
SELECT * FROM [westclintech].[wct].[CMTCurve](
<@Curve, nvarchar(max),>
,<@InterpMethod, nvarchar(4000),>
,<@Freq, int,>)
Description
Use the table-valued function CMTCurve to generate a yield curve using Constant Maturity Treasury (CMT) rates or other similar rate types.
CMTCurve expects the rates to be supplied using dynamic SQL in which the resultant table consists of the time (in years) and the rate (where 10% = 0.10).
CMTCurve supports annual (1) and semi-annual (2) compounding. It assumes that all supplied rates which have a time value less than or equal to 1 / compounding frequency are cash rates which can be directly converted into discount factors. For all other rates, a bootstrapping processing is used to calculate the discount factors. Bootstrapped rates are identified as such in the table returned by the function. CMTCurve supports linear and spline interpolation for all the coupons. If we were to generate a lower triangular matrix containing the (calculated) cash flows for each point on the yield curve, then the calculation of the discount factors is straightforward forward substitution for each of the interpolated points.
For example, given the following annual rates:
{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"R","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"1","R":".035"},{"T":"2","R":".042"},{"T":"3","R":".047"},{"T":"4","R":".052"}]}
We are able to directly calculate the discount factors for each point by setting up the following equation:
\begin{pmatrix}100\\100\\100\\100\end{pmatrix}=\begin{pmatrix}103.5&&&\\4.2&104.2&&\\4.7&4.7&104.7&\\5.2&5.2&5.2&105.2\end{pmatrix}\times\begin{pmatrix}P(0,1)\\P(0,2)\\P(0,3)\\P(0,4)\end{pmatrix}
Which can be solved directly in SQL Server using the existing XLeratorDB math functions MATRIX2STRING_q, MUPDATE, EYE, FWDSUB, MTRIL and Matrix.
--Put the par rates into matrix format
DECLARE @cf as varchar(max) = wct.Matrix2String_q('
SELECT r
FROM (VALUES (1,.035),(2,.042),(3,.047),(4,.052))n(T,r)
ORDER BY T'
);
--Generate the cash flows in matrix format
SET @cf = wct.MUPDATE(100,NULL,NULL,NULL,NULL,'*',wct.MUPDATE(@cf,NULL,NULL,NULL,NULL,'+',wct.EYE(4,4),NULL,NULL,NULL,NULL),NULL,NULL,NULL,NULL);
--Solve by forward substitution
SELECT
rownum + 1 as T
,ItemValue as df
FROM
wct.Matrix(wct.FWDSUB(wct.MTRIL(@cf),'100;100;100;100'));
This produces the following result.
T df
----------- ----------------------
1 0.966183574879227
2 0.920748838632507
3 0.870405135210075
4 0.814276090747591
CMTCURVE simplifies the SQL into a single, easy-to-use function call.
Arguments
@Curve
A SELECT statement which return a resultant consisting of the time (in years) and the associated rates (where 10% = 0.10). @Curve should return 2 columns.
@InterpMethod
Identifies the interpolation method used in the bootstrapping process. Use 'S' for spline interpolation, 'L0' for linear interpolation where bounds are used for any values that would otherwise be out-of-bonds, or 'L1' for linear interpolation with extrapolation.
@Freq
Use 1 for annual or 2 for semi-annual. @Freq is an expression of type int or of a type that can be implicitly converted to int.
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": "b6510816-b5a6-4ffa-b083-d2152758db40", "colName": "T", "colDatatype": "float", "colDesc": "Time (in years) associated with the returned rate"}, {"id": "3e1317d1-37af-46d1-ab0f-0ee913bf7124", "colName": "r", "colDatatype": "float", "colDesc": "Par rate"}, {"id": "e872c651-a774-4ae2-8348-619ef02a6977", "colName": "df", "colDatatype": "float", "colDesc": "Discount factor"}, {"id": "ccdf8b98-82c1-4ce9-9299-f12dc6477983", "colName": "spot", "colDatatype": "float", "colDesc": "Spot rate; @Freq*(POWER(1/df,1/(@Freq*T))-1"}, {"id": "8deeb663-bb25-434f-a4a1-81202e1b1432", "colName": "cczero", "colDatatype": "float", "colDesc": "Continuously compounded zero rate; -LOG(df)/T"}, {"id": "d5ddabf4-a4d2-41ea-b327-8ec49a194405", "colName": "bootstrap", "colDatatype": "float", "colDesc": "identifies T and r as having come from @Curve (1) or having been interpolated (0)"}]}
Remarks
If @Curve returns less than 2 columns an error is generated.
NULL values returned by @Curve are discarded.
Only 1 r value should be returned for each T in @Curve.
If @InterpMethod is NULL then @InterpMethod = 'S'.
If @Freq is NULL then @Freq = 2.
Examples
Example #1
Using the data from the introduction.
SELECT *
FROM wct.CMTCurve(
'
SELECT T, r
FROM (VALUES (1, .035), (2, .042), (3, .047), (4, .052))n(T, r)
ORDER BY T' ,
--@Curve
'L',--@InterpMethod
1 --@Freq
);
This produces the following result.
{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"r","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"df","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"spot","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cczero","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"bootstrap","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"1","r":"0.035","df":"0.966183574879227","spot":"0.035","cczero":"0.0344014267173323","bootstrap":"0"},{"T":"2","r":"0.042","df":"0.920748838632507","spot":"0.0421480257395637","cczero":"0.041283992492736","bootstrap":"0"},{"T":"3","r":"0.047","df":"0.870405135210075","spot":"0.0473524471924105","cczero":"0.0462655010233704","bootstrap":"0"},{"T":"4","r":"0.052","df":"0.814276090747591","spot":"0.0527059539733534","cczero":"0.0513639481661994","bootstrap":"0"}]}
Example #2
In this example we put the curve into a temp table, #cmt, and have the dynamic SQL select from #cmt.
SELECT T,
r / 100 as R
INTO #cmt
FROM ( VALUES (0.0833333333333333, 0.44),
(0.25, 0.51),
(0.5, 0.62),
(1, 0.85),
(2, 1.2),
(3, 1.47),
(5, 1.93),
(7, 2.25),
(10, 2.45),
(20, 2.79),
(30, 3.06)) n (T, r);
SELECT *
FROM wct.CMTCurve('
SELECT T,R FROM #cmt,', --@Curve,
NULL, --@InterpMethod
NULL --@Freq
);
This produces the following result.
{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"r","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"df","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"spot","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cczero","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"bootstrap","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"0.0833333333333333","r":"0.0044","df":"0.9996334677285","spot":"0.0044","cczero":"0.00439919353046381","bootstrap":"0"},{"T":"0.25","r":"0.0051","df":"0.998726623554968","spot":"0.0051","cczero":"0.00509675151092199","bootstrap":"0"},{"T":"0.5","r":"0.0062","df":"0.996909580301067","spot":"0.0062","cczero":"0.00619040981460507","bootstrap":"0"},{"T":"1","r":"0.0085","df":"0.991549050817745","spot":"0.008504893121136","cczero":"0.00848686092195307","bootstrap":"0"},{"T":"1.5","r":"0.0103238435248282","df":"0.984653030213283","spot":"0.0103372586698782","cczero":"0.0103106356376624","bootstrap":"1"},{"T":"2","r":"0.012","df":"0.976303508978139","spot":"0.012026901569032","cczero":"0.0119908842986231","bootstrap":"0"},{"T":"2.5","r":"0.0134284694255153","df":"0.966990101064948","spot":"0.0134719789524844","cczero":"0.0134268081315861","bootstrap":"1"},{"T":"3","r":"0.0147","df":"0.956831708200122","spot":"0.0147634755387185","cczero":"0.0147092521645393","bootstrap":"0"},{"T":"3.5","r":"0.0159266919912166","df":"0.945698457790541","spot":"0.0160156465758301","cczero":"0.0159518616347747","bootstrap":"1"},{"T":"4","r":"0.0171153057448472","df":"0.933656013501756","spot":"0.0172356435332821","cczero":"0.0171618006213709","bootstrap":"1"},{"T":"4.5","r":"0.0182462666260543","df":"0.920870847169493","spot":"0.0184031495101311","cczero":"0.0183189963658789","bootstrap":"1"},{"T":"5","r":"0.0193","df":"0.907543296017374","spot":"0.0194972232413644","cczero":"0.019402800974224","bootstrap":"0"},{"T":"5.5","r":"0.0202592567673714","df":"0.893893168181817","spot":"0.0204987022674885","cczero":"0.0203943653867665","bootstrap":"1"},{"T":"6","r":"0.0211160899709546","df":"0.880113272130519","spot":"0.02139776640165","cczero":"0.0212841102438738","bootstrap":"1"},{"T":"6.5","r":"0.0218648781890605","df":"0.866390263726345","spot":"0.0221867481748759","cczero":"0.0220645878427904","bootstrap":"1"},{"T":"7","r":"0.0225","df":"0.852913942295626","spot":"0.0228577213593164","cczero":"0.0227280892659744","bootstrap":"0"},{"T":"7.5","r":"0.023019959275137","df":"0.83984825004137","spot":"0.0234071216698939","cczero":"0.0232712077569947","bootstrap":"1"},{"T":"8","r":"0.0234397610580474","df":"0.827232587416736","spot":"0.0238497548619034","cczero":"0.0237086726447223","bootstrap":"1"},{"T":"8.5","r":"0.02377853568536","df":"0.815045290009342","spot":"0.0242054931034548","cczero":"0.024060187849606","bootstrap":"1"},{"T":"9","r":"0.0240554134937037","df":"0.803230650461292","spot":"0.0244947142168481","cczero":"0.0243459300375431","bootstrap":"1"},{"T":"9.5","r":"0.0242895248197074","df":"0.791700641894107","spot":"0.0247382649956602","cczero":"0.0245865195816429","bootstrap":"1"},{"T":"10","r":"0.0245","df":"0.780336549925816","spot":"0.0249574327322333","cczero":"0.0248029978092886","bootstrap":"0"},{"T":"10.5","r":"0.0247028060422834","df":"0.769019747503637","spot":"0.0251702720875628","cczero":"0.0250132028902514","bootstrap":"1"},{"T":"11","r":"0.0249012566385522","df":"0.75772994804872","spot":"0.025380439890565","cczero":"0.0252207478076072","bootstrap":"1"},{"T":"11.5","r":"0.025095502151874","df":"0.746473524808233","spot":"0.0255879664759338","cczero":"0.0254256633386433","bootstrap":"1"},{"T":"12","r":"0.0252856929453164","df":"0.735256394928809","spot":"0.0257929025297967","cczero":"0.0256280003300683","bootstrap":"1"},{"T":"12.5","r":"0.0254719793819472","df":"0.724084031495805","spot":"0.0259953155745971","cczero":"0.0258278262183657","bootstrap":"1"},{"T":"13","r":"0.0256545118248338","df":"0.712961475775108","spot":"0.0261952873123867","cczero":"0.0260252223998872","bootstrap":"1"},{"T":"13.5","r":"0.0258334406370441","df":"0.701893349615381","spot":"0.0263929116049582","cczero":"0.0262202822313113","bootstrap":"1"},{"T":"14","r":"0.0260089161816455","df":"0.690883867971492","spot":"0.0265882929327779","cczero":"0.0264131095039889","bootstrap":"1"},{"T":"14.5","r":"0.0261810888217059","df":"0.679936851512666","spot":"0.0267815452185047","cczero":"0.0266038172790894","bootstrap":"1"},{"T":"15","r":"0.0263501089202927","df":"0.66905573928159","spot":"0.0269727909315822","cczero":"0.026792527000854","bootstrap":"1"},{"T":"15.5","r":"0.0265161268404737","df":"0.658243601373348","spot":"0.0271621604122059","cczero":"0.0269793678268592","bootstrap":"1"},{"T":"16","r":"0.0266792929453164","df":"0.647503151605563","spot":"0.0273497913686698","cczero":"0.0271644761297369","bootstrap":"1"},{"T":"16.5","r":"0.0268397575978886","df":"0.636836760153597","spot":"0.027535828513543","cczero":"0.0273479951361341","bootstrap":"1"},{"T":"17","r":"0.0269976711612579","df":"0.626246466126989","spot":"0.0277204233125805","cczero":"0.0275300746770576","bootstrap":"1"},{"T":"17.5","r":"0.0271531839984919","df":"0.615733990065585","spot":"0.0279037338265855","cczero":"0.0277108710300011","bootstrap":"1"},{"T":"18","r":"0.0273064464726582","df":"0.605300746335945","spot":"0.0280859246312164","cczero":"0.0278905468379739","bootstrap":"1"},{"T":"18.5","r":"0.0274576089468246","df":"0.594947855410687","spot":"0.0282671668033809","cczero":"0.0280692710941714","bootstrap":"1"},{"T":"19","r":"0.0276068217840585","df":"0.584676156015401","spot":"0.0284476379657139","cczero":"0.0282472191838323","bootstrap":"1"},{"T":"19.5","r":"0.0277542353474278","df":"0.574486217129607","spot":"0.0286275223828509","cczero":"0.0284245729770409","bootstrap":"1"},{"T":"20","r":"0.0279","df":"0.564378349829998","spot":"0.0288070111050245","cczero":"0.0286015209680034","bootstrap":"0"},{"T":"20.5","r":"0.0280442535322292","df":"0.554352815992767","spot":"0.0289862845672255","cczero":"0.0287782411203176","bootstrap":"1"},{"T":"21","r":"0.0281870834441155","df":"0.544410455022191","spot":"0.0291654587394792","cczero":"0.0289548477961677","bootstrap":"1"},{"T":"21.5","r":"0.0283285646630453","df":"0.534552138066569","spot":"0.029344636304697","cczero":"0.0291314422223368","bootstrap":"1"},{"T":"22","r":"0.0284687721164051","df":"0.524778581445506","spot":"0.0295239241894629","cczero":"0.0293081297717959","bootstrap":"1"},{"T":"22.5","r":"0.0286077807315813","df":"0.515090352756835","spot":"0.0297034336686255","cczero":"0.0294850200649027","bootstrap":"1"},{"T":"23","r":"0.0287456654359605","df":"0.505487876901516","spot":"0.0298832804954237","cczero":"0.0296622270956463","bootstrap":"1"},{"T":"23.5","r":"0.028882501156929","df":"0.495971442022316","spot":"0.0300635850564697","cczero":"0.0298398693822432","bootstrap":"1"},{"T":"24","r":"0.0290183628218734","df":"0.486541205352542","spot":"0.0302444725513626","cczero":"0.0300180701418369","bootstrap":"1"},{"T":"24.5","r":"0.0291533253581802","df":"0.47719719897156","spot":"0.0304260731971064","cczero":"0.0301969574894501","bootstrap":"1"},{"T":"25","r":"0.0292874636932358","df":"0.467939335464322","spot":"0.0306085224578938","cczero":"0.0303766646617112","bootstrap":"1"},{"T":"25.5","r":"0.0294208527544266","df":"0.458767413482482","spot":"0.030791961301166","cczero":"0.0305573302662292","bootstrap":"1"},{"T":"26","r":"0.0295535674691392","df":"0.44968112320514","spot":"0.0309765364812278","cczero":"0.0307390985578388","bootstrap":"1"},{"T":"26.5","r":"0.0296856827647601","df":"0.440680051697586","spot":"0.0311624008520379","cczero":"0.0309221197432856","bootstrap":"1"},{"T":"27","r":"0.0298172735686756","df":"0.431763688166788","spot":"0.0313497137111733","cczero":"0.0311065503162765","bootstrap":"1"},{"T":"27.5","r":"0.0299484148082724","df":"0.422931429112684","spot":"0.031538641177344","cczero":"0.0312925534252023","bootstrap":"1"},{"T":"28","r":"0.0300791814109367","df":"0.414182583374667","spot":"0.0317293566042589","cczero":"0.0314802992762358","bootstrap":"1"},{"T":"28.5","r":"0.0302096483040552","df":"0.40551637707291","spot":"0.031922041034079","cczero":"0.0316699655749504","bootstrap":"1"},{"T":"29","r":"0.0303398904150142","df":"0.396931958444467","spot":"0.0321168836941972","cczero":"0.0318617380100834","bootstrap":"1"},{"T":"29.5","r":"0.0304699826712004","df":"0.388428402574323","spot":"0.0323140825416406","cczero":"0.0320558107836023","bootstrap":"1"},{"T":"30","r":"0.0306","df":"0.380004716021799","spot":"0.0325138448599924","cczero":"0.0322523871918345","bootstrap":"0"}]}
Example #3
Using the same data from Example #2, but this time only selecting the rows where bootstrap is false.
SELECT T,
r / 100 as R
INTO #cmt
FROM ( VALUES (0.0833333333333333, 0.44),
(0.25, 0.51),
(0.5, 0.62),
(1, 0.85),
(2, 1.2),
(3, 1.47),
(5, 1.93),
(7, 2.25),
(10, 2.45),
(20, 2.79),
(30, 3.06)) n (T, r);
SELECT *
FROM wct.CMTCurve('
SELECT T,R FROM #cmt', --@Curve
NULL, --@InterpMethod
NULL --@Freq
)
WHERE bootstrap = 'False';
This produces the following result.
{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"r","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"df","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"spot","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cczero","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"bootstrap","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"0.0833333333333333","r":"0.0044","df":"0.9996334677285","spot":"0.0044","cczero":"0.00439919353046381","bootstrap":"0"},{"T":"0.25","r":"0.0051","df":"0.998726623554968","spot":"0.0051","cczero":"0.00509675151092199","bootstrap":"0"},{"T":"0.5","r":"0.0062","df":"0.996909580301067","spot":"0.0062","cczero":"0.00619040981460507","bootstrap":"0"},{"T":"1","r":"0.0085","df":"0.991549050817745","spot":"0.008504893121136","cczero":"0.00848686092195307","bootstrap":"0"},{"T":"2","r":"0.012","df":"0.976303508978139","spot":"0.012026901569032","cczero":"0.0119908842986231","bootstrap":"0"},{"T":"3","r":"0.0147","df":"0.956831708200122","spot":"0.0147634755387185","cczero":"0.0147092521645393","bootstrap":"0"},{"T":"5","r":"0.0193","df":"0.907543296017374","spot":"0.0194972232413644","cczero":"0.019402800974224","bootstrap":"0"},{"T":"7","r":"0.0225","df":"0.852913942295626","spot":"0.0228577213593164","cczero":"0.0227280892659744","bootstrap":"0"},{"T":"10","r":"0.0245","df":"0.780336549925816","spot":"0.0249574327322333","cczero":"0.0248029978092886","bootstrap":"0"},{"T":"20","r":"0.0279","df":"0.564378349829998","spot":"0.0288070111050245","cczero":"0.0286015209680034","bootstrap":"0"},{"T":"30","r":"0.0306","df":"0.380004716021799","spot":"0.0325138448599924","cczero":"0.0322523871918345","bootstrap":"0"}]}
Example #4
In this example we use the same data as in the previous 2 example, but no we are going to shift the par curve up and down by 25 basis points and then return the continuously compounded zeroes for par rates and the shifted rates.
SELECT T,
r / 100 as R
INTO #cmt
FROM ( VALUES (0.0833333333333333, 0.44),
(0.25, 0.51),
(0.5, 0.62),
(1, 0.85),
(2, 1.2),
(3, 1.47),
(5, 1.93),
(7, 2.25),
(10, 2.45),
(20, 2.79),
(30, 3.06)) n (T, r);
SELECT x.Ztype,
k.T,
k.cczero
INTO #z
FROM ( VALUES ('Z', 'SELECT T,r FROM #cmt'),
('Zplus', 'SELECT T,r+.0025 FROM #cmt'),
('Zminus', 'SELECT T,r-.0025 FROM #cmt')) x (Ztype, ZSQL)
CROSS APPLY wct.CMTCURVE(x.ZSQL, 'S', 2) k
WHERE bootstrap = 'False';
SELECT T,
Z,
zPlus,
Zminus
FROM (SELECT * FROM #z) pvt
PIVOT ( MAX(cczero)
for Ztype in (Z, Zplus, Zminus)) d;
This produces the following result.
{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Z","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"zPlus","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Zminus","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"0.0833333333333333","Z":"0.00439919353046381","zPlus":"0.00689801701010911","Zminus":"0.00189984959920848"},{"T":"0.25","Z":"0.00509675151092199","zPlus":"0.00759278913232108","Zminus":"0.00259915536598834"},{"T":"0.5","Z":"0.00619040981460507","zPlus":"0.00868113219684038","Zminus":"0.00369658171523514"},{"T":"1","Z":"0.00848686092195307","zPlus":"0.0109761580863858","Zminus":"0.00599446159481204"},{"T":"2","Z":"0.0119908842986231","zPlus":"0.0144799813743482","Zminus":"0.00949869226116942"},{"T":"3","Z":"0.0147092521645393","zPlus":"0.0172001850364985","Zminus":"0.0122152386862146"},{"T":"5","Z":"0.019402800974224","zPlus":"0.0219029442906336","Zminus":"0.0168996544427597"},{"T":"7","Z":"0.0227280892659744","zPlus":"0.0252389854809267","Zminus":"0.0202143283294154"},{"T":"10","Z":"0.0248029978092886","zPlus":"0.0273191563710672","Zminus":"0.0222841402210191"},{"T":"20","Z":"0.0286015209680034","zPlus":"0.0311559407535593","Zminus":"0.0260459053777959"},{"T":"30","Z":"0.0322523871918345","zPlus":"0.0349087541735821","Zminus":"0.0296005214805165"}]}
See Also
BONDPRICEFROMZEROES - Bond pricing from the zero coupon curve
LOGNORMALIRLATTICE - LogNormal Interest Rate Lattice
OAC - Option Adjusted Convexity
OAD - Calculate the option-adjusted duration on a bond.
PRICEFROMIRLATTICE - Bond Pricing using Option Adjusted Spread
PRICEFROMZEROESTVF - Zero Volatility spread details
ZSPREAD - Calculate the zero-volatility or static spread on a bond.