ZEROCOUPON
Updated: 11 Mar 2013
Use the scalar function ZEROCOUPON to calculate an interpolated zerocoupon 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 TSQL 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, zerocoupon 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 (semiannually),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 nonbusiness 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 (nonbusiness) 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 tablevalued 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 convexityadjusted 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,'20130307', '20130311','') 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
,'20130307' @StartDate
,2 @Frequency
,'20130311' @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
 
20130411 0.00233199722782183
20130611 0.00281783573019520
20130911 0.00441592127318121
20140311 0.00309301732585468
20150311 0.00390391086884504
20160311 0.00542991818661328
20170313 0.00751102226468132
20180312 0.0102986388496708
20200311 0.0160514643629505
20230313 0.0237723576078310
20430311 0.0580036324845382
In this example we include the Eurodollarâ€™s futures strip, starting with the June 2013 contract and we eliminate the 1year 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, '20130307')
ELSE wct.TENOR2DATE(mDate,'20130307', '20130311','')
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
,'20130307' @StartDate
,2 @Frequency
,'20130311' @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
 
20130411 0.00233199722782183
20130611 0.00281783573019520
20130619 0.00283043330131911
20130911 0.00296280638269212
20130918 0.00297384581341651
20131218 0.00304542982146287
20140319 0.00318119716094555
20140618 0.00333402311416436
20140917 0.00352060707379495
20141217 0.00374840649925101
20150311 0.00390453741752816
20160311 0.00543050393821650
20170313 0.00751163636450903
20180312 0.01029932532814650
20200311 0.01605226713656590
20230313 0.02377326697843110
20430311 0.05800494477494450