ZSPREAD
Updated: 30 Nov 2016
Use the SQL Server scalar function ZSPREAD to calculate the zerovolatility spread of a bond based on the supplied curve. The Zspread is returned in decimal format (i.e. 1 basis point = .0001)
Syntax
SELECT [wct].[ZSPREAD](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Rate, sql_variant,>
,<@Price, float,>
,<@Redemption, float,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>
,<@LastCouponDate, datetime,>
,<@FirstCouponDate, datetime,>
,<@IssueDate, datetime,>
,<@CCZero, nvarchar(max),>
,<@CurveType, nvarchar(4000),>
,<@CurveStartDate, datetime,>
,<@CurveDayCount, nvarchar(4000),>
,<@CurveFrequency, int,>
,<@InterpMethod, nvarchar(4000),>)
Arguments
Input Name

Description

@Settlement

Settlement date of the bond.

@Maturity

Maturity date of the bond.

@Rate

Coupon rate of the bond (.01 = 1%). For steppedrate bonds, an SQL statement which returns a result table consisting of the coupon start dates and the associated coupon rates.

@Price

Clean price of the bond

@Redemption

Redemption value of the bond.

@Frequency

Coupon frequency of the bond; the number of times that the coupon interest is paid per year.

@Basis

Interest basis code for the bond; the daycount convention used in the calculation of the accrued interest.

@LastCouponDate

For bonds where the last coupon period is either longer or shorter than the regular coupon period, the last coupon date prior to the maturity date.

@FirstCouponDate

For bonds where the first coupon period is either longer or shorter than a regular coupon period, the date of the first coupon payment.

@IssueDate

For bonds where the first coupon period is either longer or short than a regular coupon period, the start date for the first period coupon interest.

@CCZero

An SQL statement which produces a resultant table containing 2 columns; the time in years and the rates to be used in the OAS calculation.

@CurveType

Identifies the curve in @CCZero as either a spot curve (S) or a continuously compounded zero coupon curve (CC). Valid values are ('S', 'CC').

@CurveStartDate

The start date for the curve; used to calculate the timeinyears associated with the coupon dates.

@CurveDayCount

The daycount convention used in calculating the timeinyears associated with the coupon dates. Valid values are (0,1,2,3,4,21); see YEARFRAC documentation for more details.

@CurveFrequency

The compounding frequency used in the calculation of the discount factors when the supplied curve is the spot curve. Valid Values are (1,2,4).

@InterpMethod

The interpolation method to calculate the rate associated with the coupon dates; use 'L' for linear interpolation and 'S' for cubic spline interpolation.

Return Type
float
Remarks
Â· If @Settlement is NULL then @Settlement = GETDATE()
Â· If @Maturity is NULL then @Maturity = GETDATE()
Â· If @Rate is NULL then @rate = 0
Â· If @Price is NULL then @Price = 0
Â· If @Redemption is NULL then @Redemption = 100
Â· If @Frequency is NULL then @Frequency = 2
Â· If @Basis is NULL then @Basis = 0
Â· If @CurveType is NULL then @CurveType = 'CC'
Â· If @CurveStartDate is NULL then @CurveStartDate = @Settlement
Â· If @CurveDayCount is NULL then @CurveDayCount = 1
Â· If @CurveFrequency is NULL then @CurveFrequency = 2
Â· If @CurveInterpMethod is NULL then @CurveInterpMethod = 'S'
Examples
Example #1
In this example we put the continuously compounded zeroes into the #ccz temp table and then calculate the Zspread for a bond maturing on 20231001 with a coupon rate of 5.25% paying interest semiannually.
SELECT
*
INTO
#ccz
FROM (VALUES
(0.011,0.0031936941106789)
,(0.25,0.00319251640948496)
,(0.50,0.0042807233899723)
,(1,0.00544437632630534)
,(2,0.00727274510130153)
,(3,0.00859818036980457)
,(4,0.0101034030456584)
,(5,0.0116083325279126)
,(7,0.0144258819802952)
,(10,0.0163078262966277)
,(20,0.0203301487469184)
,(30,0.0250383019093584)
)n(T,z)
SELECT wct.ZSPREAD(
'20161128' @Settlement
,'20231001' @Maturity
,0.0525 @Rate
,103.5 @Price
,100 @Redemption
,2 @Frequency
,0 @Basis
,NULL @LastCouponDate
,NULL @FirstCouponDate
,NULL @IssueDate
,'SELECT * FROM #ccz' @ZeroRate
,NULL @CurveType
,NULL @CurveStartDate
,NULL @CurveDayCount
,NULL @CurveFrequency
,NULL @InterMethod
) * 10000 as [Z Spread]
This produces the following result.
Example #2
In this example we use the same curve as from the previous example, but we are going to calculate the Zspread for a stepped rate bond.
Put the step information into the #step table
SELECT
date_step
,rate_step
INTO
#step
FROM (VALUES
('20201001', .0425)
,('20241001', .0625)
)n(date_step, rate_step)
The bond to be evaluated
SELECT wct.ZSPREAD(
'20161128' @Settlement
,'20261001' @Maturity
,'SELECT date_step, rate_step FROM #step' @Rate
,103.5 @Price
,100 @Redemption
,2 @Frequency
,0 @Basis
,NULL @LastCouponDate
,NULL @FirstCouponDate
,NULL @IssueDate
,'SELECT * FROM #ccz' @ZeroRate
,NULL @CurveType
,NULL @CurveStartDate
,NULL @CurveDayCount
,NULL @CurveFrequency
,NULL @InterpMethod
) * 10000 as [Z Spread]
This produces the following result.
Example #3
In this example we will calculate the Zspread for multiple bonds using a single SQL statement. We will use the same CMT curve as in the previous example, which is stored in the temp table #z. We populate the #bonds table with some information about the bonds. We then calculate the the ZSpread for all the bonds in the SELECT.
Establish the CMT curve
SELECT
*
INTO
#par
FROM (VALUES
(0.25,0.00396)
,(0.5,0.00520)
,(1,0.00614)
,(2,0.00823)
,(3,0.00987)
,(4,0.01138)
,(5,0.01290)
,(7,0.01605)
,(10,0.01839)
,(20,0.02216)
,(30,0.02593)
)n(T,r)
Convert the CMT curve to continuously compounded zeroes
SELECT
*
INTO
#z
FROM
wct.CMTCURVE('SELECT * FROM #par','S',2)
WHERE
bootstrap = 'False'
Enter some bonds into a table
SELECT
*
INTO
#bonds
FROM (VALUES
('A','20251103',0.0333,95.008)
,('B','20230512',0.0447,102.649)
,('C','20290717',0.0654,104.996)
,('D','20220806',0.0673,113.76)
,('E','20300218',0.0649,105.369)
,('F','20240817',0.047,104.604)
,('G','20230407',0.0488,104.893)
,('H','20260529',0.0584,114.427)
,('I','20231106',0.0426,101.56)
,('J','20270420',0.0572,104.506)
)n(id_bond,maturity,rate,price)
SELECT
b.id_bond
,ROUND(
wct.ZSPREAD(
'20161128' @Settlement
,b.maturity @Maturity
,b.rate @Rate
,b.price @Price
,NULL @Redemption
,2 @Frequency
,0 @Basis
,NULL @LastCouponDate
,NULL @FirstCouponDate
,NULL @IssueDate
,'SELECT * FROM #z' @ZeroRate
,NULL @CurveType
,NULL @CurveStartDate
,NULL @CurveDayCount
,NULL @CurveFrequency
,NULL @InterMethod
) * 10000
,1) as [Z Spread]
FROM
#bonds b
This produces the following result.
See Also