Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Zero Volatility spread details


PriceFromZeroesTVF
Updated: 30 Nov 2016
Use the SQL Server table-valued function PriceFromZeroesTVF to generate the details used to calculate the price from the Z-spread. PriceFromZeroesTVF is a way of researching or auditing the calculation of the price. The Z-spread is entered in decimal format (i.e. 1 basis point = .0001)
Syntax
SELECT * FROM [wct].[PriceFromZeroesTVF](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, sql_variant,>
 ,<@CurveSpread, 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.
@CurveSpread
Z-spread
@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
RETURNS TABLE (
       [date_pmt] [datetime] NULL,
       [T] [float] NULL,
       [delta] [float] NULL,
       [cczero] [float] NULL,
       [spot] [float] NULL,
       [pvf] [float] NULL,
       [fwd] [float] NULL,
       [spread] [float] NULL,
       [df] [float] NULL,
       [cf] [float] NULL,
       [dcf] [float] NULL
)

Column
Description
date_pmt
Cash flow date
T
Time (in years)
delta
Change in T from the previous coupon
cczero
Continuously compounded zero coupon rate
spot
Spot rate; @Freq*(POWER(1/df,1/(@Freq*T))-1
pvf
Discount factor from cczero; EXP(-cczero*T)
fwd
Forward rate
spread
Z-spread
df
Discount factor for the forward period; 1/(1+(fwd + spread) * delta)
cf
Cash flow amount from the bond
dcf
Discounted cash flow value of all cash flows greater than or equal to date_pmt

Remarks
·         If @Settlement is NULL then @Settlement = GETDATE()
·         If @Maturity is NULL then @Maturity = GETDATE()
·         If @Rate is NULL then @rate = 0
·         If @CurveSpread is NULL then @CurveSpread = 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 generate the table for a bond maturing on 2023-10-01 with a coupon rate of 5.25% paying interest semi-annually. The Z-spread is 327.6 basis points
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
    date_pmt
   ,T
   ,delta
   ,cczero
   ,spot
   ,pvf
   ,fwd
   ,spread
   ,df
   ,cf
   ,dcf
FROM wct.PriceFromZeroesTVF(
        '2016-11-28' --@Settlement
       ,'2023-10-01' --@Maturity
       ,0.0525       --@Rate
       ,.03276       --@CurveSpread
       ,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
       )
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 generate the table 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
    date_pmt
   ,T
   ,delta
   ,cczero
   ,spot
   ,pvf
   ,fwd
   ,spread
   ,df
   ,cf
   ,dcf
FROM
   wct.PriceFromZeroesTVF(
        '2016-11-28'  --@Settlement
       ,'2026-10-01'  --@Maturity
       ,'SELECT date_step, rate_step FROM #step'       --@Rate
       ,.00836        --@Spread
       ,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
       )
This produces the following result.
Example #3
In this example we will generate a table 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 generate the table 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, 226.7)
   ,('B','2023-05-12',0.0447, 252)
   ,('C','2029-07-17',0.0654, 413.6)
   ,('D','2022-08-06',0.0673, 264.8)
   ,('E','2030-02-18',0.0649, 404.1)
   ,('F','2024-08-17',0.047, 237.4)
   ,('G','2023-04-07',0.0488, 253.6)
   ,('H','2026-05-29',0.0584, 227.2)
   ,('I','2023-11-06',0.0426, 245.1)
   ,('J','2027-04-20',0.0572, 338.9)
   )n(id_bond,maturity,rate,spread)
 
SELECT
    b.id_bond
   ,k.date_pmt
   ,k.T
   ,k.delta
   ,k.cczero
   ,k.spot
   ,k.pvf
   ,k.fwd
   ,k.spread
   ,k.df
   ,k.cf
   ,k.dcf
FROM
   #bonds b
CROSS APPLY
   wct.PriceFromZeroesTVF(
           '2016-11-28'    --@Settlement
          ,b.maturity      --@Maturity
          ,b.rate          --@Rate
          ,b.spread / 10000       --@Spread
          ,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            --@InterpMethod
          )k
ORDER BY
   1,2
This produces the following result.

 

See Also
·         OAS - Option Adjusted Spread

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service