ZSPREAD
Updated 2023-10-13 12:18:04.700000
Syntax
SELECT [westclintech].[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),>)
Description
Use the 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).
Arguments
@Price
Clean price of the bond
@Redemption
Redemption value of the bond.
@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.
@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).
@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.
@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.
@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.
@InterpMethod
The interpolation method to calculate the rate associated with the coupon dates; use 'L' for linear interpolation and 'S' for cubic spline interpolation.
@Frequency
Coupon frequency of the bond; the number of times that the coupon interest is paid per year.
@CurveStartDate
The start date for the curve; used to calculate the time-in-years associated with the coupon dates.
@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').
@Maturity
Maturity date of the bond.
@Basis
Interest basis code for the bond; the day-count convention used in the calculation of the accrued interest.
@Settlement
Settlement date of the bond.
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.
{"columns":[{"field":"Z Spread","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Z Spread":"327.634273880571"}]}
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.
{"columns":[{"field":"Z Spread","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Z Spread":"83.6172037573253"}]}
Example #3
In this example we will calculate the Z-spread 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.
{"columns":[{"field":"id_bond"},{"field":"Z Spread","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"id_bond":"A","Z Spread":"226.7"},{"id_bond":"B","Z Spread":"252"},{"id_bond":"C","Z Spread":"413.6"},{"id_bond":"D","Z Spread":"264.8"},{"id_bond":"E","Z Spread":"404.1"},{"id_bond":"F","Z Spread":"237.4"},{"id_bond":"G","Z Spread":"253.6"},{"id_bond":"H","Z Spread":"227.2"},{"id_bond":"I","Z Spread":"245.1"},{"id_bond":"J","Z Spread":"338.9"}]}
See Also
BONDPRICEFROMZEROES - Bond pricing from the zero coupon curve
CMTCURVE - Constant Maturity Treasury curve
LOGNORMALIRLATTICE - LogNormal Interest Rate Lattice
OAC - Option Adjusted Convexity
OAD - Calculate the option-adjusted duration on a bond.
PRICEFROMIRLATTICE - Bond Pricing using Option Adjusted Spread