SQL Server SWAPCURVE Function

Updated 2023-10-13 13:59:13.957000

Description

Use the table-valued function SWAPCURVE to calculate discount factors, zero-coupon rates, and continuously compounded zero-coupon rates from a series of cash rates, futures prices or swaps rates.

Syntax

SELECT * FROM [westclintech].[wct].[SWAPCURVE](
  <@InputData_RangeQuery, nvarchar(max),>
 ,<@StartDate, datetime,>
 ,<@Frequency, float,>
 ,<@SpotDate, datetime,>
 ,<@CashBasis, nvarchar(4000),>
 ,<@FuturesBasis, nvarchar(4000),>
 ,<@SwapsBasis, nvarchar(4000),>
 ,<@Interpolation, 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 SWAPCURVE calculations.

@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).

@Interpolation

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:

@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

table

Remarks

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 #curves
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 *
FROM wct.SWAPCURVE(   'SELECT * FROM #curves', --@InputData_RangeQuery
                      '2013-03-07',            --@StartDate
                      2,                       --@Frequency
                      '2013-03-11',            --@SpotDate
                      2,                       --@CashBasis
                      2,                       --@FuturesBasis
                      0,                       --@SwapsBasis
                      'L',                     --@InterpMethod
                      'M',                     --@DateRoll
                      NULL                     --@Holidays
                  );
DROP TABLE #curves;

This produces the following result.

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. We also exclude the interpolated values from the resultant table.

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 #curves
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 *
FROM wct.SWAPCURVE(   'SELECT * FROM #curves', --@InputData_RangeQuery
                      '2013-03-07',            --@StartDate
                      2,                       --@Frequency
                      '2013-03-11',            --@SpotDate
                      2,                       --@CashBasis
                      2,                       --@FuturesBasis
                      0,                       --@SwapsBasis
                      'L',                     --@InterpMethod
                      'M',                     --@DateRoll
                      NULL                     --@Holidays
                  )
WHERE rsource <> 'I'; --Exclude interpolation
DROP TABLE #curves;

This produces the following result.