ZEROCOUPON
Updated 2023-10-13 14:14:00.503000
Syntax
SELECT [westclintech].[wct].[ZEROCOUPON](
<@InputData_RangeQuery, nvarchar(max),>
,<@vDate, datetime,>
,<@ReturnValue, nvarchar(4000),>
,<@StartDate, datetime,>
,<@Frequency, float,>
,<@SpotDate, datetime,>
,<@CashBasis, nvarchar(4000),>
,<@FuturesBasis, nvarchar(4000),>
,<@SwapsBasis, nvarchar(4000),>
,<@InterpMethod, nvarchar(4000),>
,<@DateRoll, nvarchar(4000),>
,<@Holidays, nvarchar(max),>)
Description
Use the scalar function ZEROCOUPON to calculate an interpolated zero-coupon rate from a series of cash rates, futures prices, or swaps rates.
Arguments
@DateRoll
The rule to be used when a calculated date lands on a non-business day. The @DateRollRule values are:
{"columns":[{"field":"A","width":67},{"field":"actual day is returned with no adjustment.","width":523}],"rows":[{"A":"F","actual day is returned with no adjustment.":"next business day is returned."},{"A":"M","actual day is returned with no adjustment.":"next business day is returned unless it is in a different month in which case the previous business day is returned."},{"A":"P","actual day is returned with no adjustment.":"preceding business day is returned."},{"A":"MP","actual day is returned with no adjustment.":"preceding business day is returned unless it is in a different month in which in case the next business day is returned."}]}
@CashBasis
The interest basis code associated with the cash rates. Valid values are 0 (30/360), 1 (Actual/Actual), 2 (Actual/360), 3 Actual/365) and 4 (E30/360).
@InterpMethod
The interpolation method to be used with the swaps rates. Valid values are L (linear) and S (Spline).
@Frequency
the compounding frequency for the swaps rates. Permissible values are 1 (annually),2 (semi-annually),4 (quarterly) and 12 (monthly).
@vDate
the target date for interpolation purposes. @vDate must be of the type datetime or of a type that implicitly converts to datetime.
@ReturnValue
the return value; discount factor, zero-coupon rate, or continuously compounded zero coupon rate. Valid values are 'DF', 'ZC', and 'CC'.
@FuturesBasis
The interest basis code associated with the futures prices. Valid values are 0 (30/360), 1 (Actual/Actual), 2 (Actual/360), 3 Actual/365) and 4 (E30/360).
@SpotDate
The normal settlement date associated with the @StartDate. @SpotDate must be of the type datetime or of a type that implicitly converts to datetime.
@StartDate
the starting date associated with the cash rates. @StartDate must be of the type datetime or of a type that implicitly converts to datetime.
@InputData_RangeQuery
a T-SQL statement, as a string, the specifies the cash rates, futures prices, and swap rates to be used in the ZEROCOUPON calculation.
@SwapsBasis
The interest basis code associated with the swaps rates. Valid values are 0 (30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365) and 4 (E30/360).
@Holidays
a comma separated string containing the holiday (non-business) dates to be used in the calculation of the number of business days. You can use the aggregate function NBD to create an appropriately formatted string.
Return Type
float
Remarks
To calculate the zero coupon rate for the supplied points on the ‘yield curve’ use the SWAPCURVE table-valued function.
To interpolate the zero coupon or continuously compounded zero coupon rate from the discount factors use the DFINTERP aggregate function.
Use the TENOR2DATE scalar function to convert abbreviations like 1M and 1Y to dates based on the spot date.
Use the ED_FUT2DATE scalar function to convert futures contract codes to the correct settlement date based on the start date.
Use the ED_FUT_CONV_ADJ_HL scalar function to convert the futures price to a convexity-adjusted interest rate.
Examples
In this example we will take a series of cash and swaps rates and convert them into zero coupon rates for the same date using linear interpolation. Note that we use the XLeratorDB TENOR2DATE function to calculate the actual maturity dates from the input.
SET NOCOUNT ON;
SELECT wct.TENOR2DATE(mDate, '2013-03-07', '2013-03-11', '') as mDate,
cRate,
iType
into #zc
FROM
(
SELECT '1M',
.0023,
'C'
UNION ALL
SELECT '3M',
.0028,
'C'
UNION ALL
SELECT '6M',
.0044,
'C'
UNION ALL
SELECT '1Y',
.0031,
'S'
UNION ALL
SELECT '2Y',
.0039,
'S'
UNION ALL
SELECT '3Y',
.0054,
'S'
UNION ALL
SELECT '4Y',
.0074,
'S'
UNION ALL
SELECT '5Y',
.0100,
'S'
UNION ALL
SELECT '7Y',
.0150,
'S'
UNION ALL
SELECT '10Y',
.0207,
'S'
UNION ALL
SELECT '30Y',
.0304,
'S'
) n(mDate, cRate, iType);
SELECT k.mDate,
wct.ZEROCOUPON( 'SELECT * from #zc', --@InputData
k.mDate, --@vDate
'ZC', --@ReturnValue
'2013-03-07', --@StartDate
2, --@Frequency
'2013-03-11', --@SporDate
2, --@CashBasis
2, --@FuturesBasis
0, --@SwapsBasis
'L', --@InterpMethod
'MP', --@DateRoll
'' --@Holidays
) as [Zero Coupon]
FROM
(SELECT mDate FROM #zc) k(mDate);
DROP TABLE #zc;
This produces the following result.
{"columns":[{"field":"mDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Zero Coupon","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"mDate":"2013-04-11","Zero Coupon":"0.00233199722782183"},{"mDate":"2013-06-11","Zero Coupon":"0.00281783573019520"},{"mDate":"2013-09-11","Zero Coupon":"0.00441592127318121"},{"mDate":"2014-03-11","Zero Coupon":"0.00309301732585468"},{"mDate":"2015-03-11","Zero Coupon":"0.00390391086884504"},{"mDate":"2016-03-11","Zero Coupon":"0.00542991818661328"},{"mDate":"2017-03-13","Zero Coupon":"0.00751102226468132"},{"mDate":"2018-03-12","Zero Coupon":"0.0102986388496708"},{"mDate":"2020-03-11","Zero Coupon":"0.0160514643629505"},{"mDate":"2023-03-13","Zero Coupon":"0.0237723576078310"},{"mDate":"2043-03-11","Zero Coupon":"0.0580036324845382"}]}
In this example we include the Eurodollar’s futures strip, starting with the June 2013 contract and we eliminate the 1-year swaps contract. Note that we use the XLeratorDB ED_FUT2DATE function to convert the futures contract code to the appropriate settlement date.
SET NOCOUNT ON
SELECT CASE iType
WHEN 'F' THEN wct.ED_FUT2DATE(mDate, '2013-03-07')
ELSE wct.TENOR2DATE(mDate, '2013-03-07', '2013-03-11', '') END as mDate,
Case iType
WHEN 'F' THEN (100 - cRate) / 100
ELSE cRate END as cRate,
iType
into #zc
FROM ( SELECT '1M',
.0023,
'C'
UNION ALL
SELECT '3M',
.0028,
'C'
UNION ALL
SELECT '6M',
.0044,
'C'
UNION ALL
SELECT '2Y',
.0039,
'S'
UNION ALL
SELECT '3Y',
.0054,
'S'
UNION ALL
SELECT '4Y',
.0074,
'S'
UNION ALL
SELECT '5Y',
.0100,
'S'
UNION ALL
SELECT '7Y',
.0150,
'S'
UNION ALL
SELECT '10Y',
.0207,
'S'
UNION ALL
SELECT '30Y',
.0304,
'S'
UNION ALL
SELECT 'M3',
99.7050,
'F'
UNION ALL
SELECT 'U3',
99.6850,
'F'
UNION ALL
SELECT 'Z3',
99.6450,
'F'
UNION ALL
SELECT 'H4',
99.6100,
'F'
UNION ALL
SELECT 'M4',
99.5600,
'F'
UNION ALL
SELECT 'U4',
99.4950,
'F'
UNION ALL
SELECT 'Z4',
99.4050,
'F') n(mDate, cRate, iType)
SELECT k.mDate,
wct.ZEROCOUPON('SELECT * from #zc', --@InputData
k.mDate, --@vDate
'ZC', --@ReturnValue
'2013-03-07', --@StartDate
2, --@Frequency
'2013-03-11', --@SpotDate
2, --@CashBasis
2, --@FuturesBasis
0, --@SwapsBasis
'L', --@InterpMethod
'MP', --@DateRoll
'' --@Holidays
) as [Zero Coupon]
FROM (SELECT mDate FROM #zc) k(mDate)
ORDER BY 1
DROP TABLE #zc
This produces the following result.
{"columns":[{"field":"mDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Zero Coupon","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"mDate":"2013-04-11","Zero Coupon":"0.00233199722782183"},{"mDate":"2013-06-11","Zero Coupon":"0.00281783573019520"},{"mDate":"2013-06-19","Zero Coupon":"0.00283043330131911"},{"mDate":"2013-09-11","Zero Coupon":"0.00296280638269212"},{"mDate":"2013-09-18","Zero Coupon":"0.00297384581341651"},{"mDate":"2013-12-18","Zero Coupon":"0.00304542982146287"},{"mDate":"2014-03-19","Zero Coupon":"0.00318119716094555"},{"mDate":"2014-06-18","Zero Coupon":"0.00333402311416436"},{"mDate":"2014-09-17","Zero Coupon":"0.00352060707379495"},{"mDate":"2014-12-17","Zero Coupon":"0.00374840649925101"},{"mDate":"2015-03-11","Zero Coupon":"0.00390453741752816"},{"mDate":"2016-03-11","Zero Coupon":"0.00543050393821650"},{"mDate":"2017-03-13","Zero Coupon":"0.00751163636450903"},{"mDate":"2018-03-12","Zero Coupon":"0.01029932532814650"},{"mDate":"2020-03-11","Zero Coupon":"0.01605226713656590"},{"mDate":"2023-03-13","Zero Coupon":"0.02377326697843110"},{"mDate":"2043-03-11","Zero Coupon":"0.05800494477494450"}]}