Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Zero volatility spread


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'
·         Available in XLeratorDB / financial 2008 only
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
·         OAC - Option Adjusted Convexity
·         OAD - Option Adjusted Duration
·         OAS - Option Adjusted Spread

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service