ZEROCOUPON
Updated: 11 Mar 2013
Use the scalar function ZEROCOUPON to calculate an interpolated zero-coupon rate from a series of cash rates, futures prices, or swaps rates.
Syntax
SELECT [wctFinancial].[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),>)
Arguments
@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.
@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'.
@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.
@Frequency
the compounding frequency for the swaps rates. Permissible values are 1 (annually),2 (semi-annually),4 (quarterly) ,and 12 (monthly).
@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.
@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).
@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).
@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).
@InterpMethod
The interpolation method to be used with the swaps rates. Valid values are L (linear) and S (Spline).
@DateRoll
The rule to be used when a calculated date lands on a non-business day. The @DateRollRule values are:
|
A
|
actual day is returned with no adjustment.
|
|
F
|
next business day is returned.
|
|
M
|
next business day is returned unless it is in a different month in which case the previous business day is returned.
|
|
P
|
preceding business day is returned.
|
|
MP
|
preceding business day is returned unless it is in a different month in which in case the next business day is returned.
|
@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.
mDate Zero Coupon
---------- ----------------------
2013-04-11 0.00233199722782183
2013-06-11 0.00281783573019520
2013-09-11 0.00441592127318121
2014-03-11 0.00309301732585468
2015-03-11 0.00390391086884504
2016-03-11 0.00542991818661328
2017-03-13 0.00751102226468132
2018-03-12 0.0102986388496708
2020-03-11 0.0160514643629505
2023-03-13 0.0237723576078310
2043-03-11 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.
mDate Zero Coupon
---------- ----------------------
2013-04-11 0.00233199722782183
2013-06-11 0.00281783573019520
2013-06-19 0.00283043330131911
2013-09-11 0.00296280638269212
2013-09-18 0.00297384581341651
2013-12-18 0.00304542982146287
2014-03-19 0.00318119716094555
2014-06-18 0.00333402311416436
2014-09-17 0.00352060707379495
2014-12-17 0.00374840649925101
2015-03-11 0.00390453741752816
2016-03-11 0.00543050393821650
2017-03-13 0.00751163636450903
2018-03-12 0.01029932532814650
2020-03-11 0.01605226713656590
2023-03-13 0.02377326697843110
2043-03-11 0.05800494477494450