SQL Server Constant Maturity Treasury curve

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:

T

R

1

.035

2

.042

3

.047

4

.052

We are able to directly calculate the discount factors for each point by setting up the following equation:

Which can be solved directly in SQL Server using the existing XLeratorDB math functions Matrix2String_q, MUPDATE, EYE, FWDSUB, MTRIL, and Matrix.

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

RETURNS TABLE(

[T] [float] NULL,

[r] [float] NULL,

[df] [float] NULL,

[spot] [float] NULL,

[cczero] [float] NULL,

[bootstrap] [bit] NULL

)

Column

Description

T

Time (in years) associated with the returned rate

r

Par rate

df

Discount factor

spot

Spot rate; @Freq*(POWER(1/df,1/(@Freq*T))-1

cczero

Continuously compounded zero rate; -LOG(df)/T

bootstrap

identifies T and r as having come from @Curve (1) or having been interpolated (0)

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.

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.

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.