ZSPREAD
Updated: 30 Nov 2016
Use the SQL Server scalar function ZSPREAD to calculate the zero-volatility spread of a bond based on the supplied curve. The Z-spread 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 stepped-rate 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 day-count 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 time-in-years associated with the coupon dates.
|
|
@CurveDayCount
|
The day-count convention used in calculating the time-in-years 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 Z-spread for a bond maturing on 2023-10-01 with a coupon rate of 5.25% paying interest semi-annually.
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(
'2016-11-28' --@Settlement
,'2023-10-01' --@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 Z-spread for a stepped rate bond.
--Put the step information into the #step table
SELECT
date_step
,rate_step
INTO
#step
FROM (VALUES
('2020-10-01', .0425)
,('2024-10-01', .0625)
)n(date_step, rate_step)
--The bond to be evaluated
SELECT wct.ZSPREAD(
'2016-11-28' --@Settlement
,'2026-10-01' --@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 Z-Spread 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','2025-11-03',0.0333,95.008)
,('B','2023-05-12',0.0447,102.649)
,('C','2029-07-17',0.0654,104.996)
,('D','2022-08-06',0.0673,113.76)
,('E','2030-02-18',0.0649,105.369)
,('F','2024-08-17',0.047,104.604)
,('G','2023-04-07',0.0488,104.893)
,('H','2026-05-29',0.0584,114.427)
,('I','2023-11-06',0.0426,101.56)
,('J','2027-04-20',0.0572,104.506)
)n(id_bond,maturity,rate,price)
SELECT
b.id_bond
,ROUND(
wct.ZSPREAD(
'2016-11-28' --@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