SQL Server PriceFromZeroesTVF Function

Updated 2023-10-13 12:10:39.610000

Description

Use the SQL Server table-valued function PriceFromZeroesTVF to generate the details used to calculate the price from the Z-spread. PriceFromZeroesTVF is a way of researching or auditing the calculation of the price. The Z-spread is entered in decimal format (i.e. 1 basis point = .0001).

Syntax

SELECT * FROM [wct].[PriceFromZeroesTVF] (
   <@Settlement, datetime,>
  ,<@Maturity, datetime,>
  ,<@Rate, sql_variant,>
  ,<@CurveSpread, 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),>)

Arguments

@Settlement

Settlement date of the bond.

@Maturity

Maturity date of the bond.

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

@CurveSpread

Z-spread.

@Redemption

Redemption value of the bond.

@Frequency

Coupon frequency of the bond; the number of times that the coupon interest is paid per year.

@Basis

Interest basis code for the bond; the day-count convention used in the calculation of the accrued interest.

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

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

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

@CurveStartDate

The start date for the curve; used to calculate the time-in-years associated with the coupon dates.

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

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

@InterpMethod

The interpolation method to calculate the rate associated with the coupon dates; use 'L' for linear interpolation and 'S' for cubic spline interpolation.

Return Type

table

Remarks

If @Settlement is NULL then @Settlement = GETDATE().

If @Maturity is NULL then @Maturity = GETDATE().

If @Rate is NULL then @rate = 0.

If @CurveSpread is NULL then @CurveSpread = 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 generate the table for a bond maturing on 2023-10-01 with a coupon rate of 5.25% paying interest semi-annually. The Z-spread is 327.6 basis points.

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 date_pmt,
       T,
       delta,
       cczero,
       spot,
       pvf,
       fwd,
       spread,
       df,
       cf,
       dcf
FROM wct.PriceFromZeroesTVF(   '2016-11-28',         --@Settlement
                               '2023-10-01',         --@Maturity
                               0.0525,               --@Rate
                               .03276,               --@CurveSpread
                               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
                           );

This produces the following result.

Example #2

In this example we use the same curve as from the previous example, but we are going to generate the table 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 date_pmt,
       T,
       delta,
       cczero,
       spot,
       pvf,
       fwd,
       spread,
       df,
       cf,
       dcf
  FROM wct.PriceFromZeroesTVF('2016-11-28', --@Settlement
                              '2026-10-01', --@Maturity
                              'SELECT date_step, rate_step FROM #step', --@Rate
                              .00836, --@Spread
                              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
    );

This produces the following result.

Example #3

In this example we will generate a table 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 generate the table 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, 226.7),
        ('B', '2023-05-12', 0.0447, 252),
        ('C', '2029-07-17', 0.0654, 413.6),
        ('D', '2022-08-06', 0.0673, 264.8),
        ('E', '2030-02-18', 0.0649, 404.1),
        ('F', '2024-08-17', 0.047, 237.4),
        ('G', '2023-04-07', 0.0488, 253.6),
        ('H', '2026-05-29', 0.0584, 227.2),
        ('I', '2023-11-06', 0.0426, 245.1),
        ('J', '2027-04-20', 0.0572, 338.9)
) n (id_bond, maturity, rate, spread);
SELECT b.id_bond,
       k.date_pmt,
       k.T,
       k.delta,
       k.cczero,
       k.spot,
       k.pvf,
       k.fwd,
       k.spread,
       k.df,
       k.cf,
       k.dcf
FROM #bonds b
    CROSS APPLY wct.PriceFromZeroesTVF(   '2016-11-28',       --@Settlement
                                          b.maturity,         --@Maturity
                                          b.rate,             --@Rate
                                          b.spread / 10000,   --@Spread
                                          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                --@InterpMethod
                                      ) k
ORDER BY 1,
         2;

This produces the following result.

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.

OAS - Option Adjusted Spread

PRICEFROMIRLATTICE - Bond Pricing using Option Adjusted Spread

ZSPREAD - Calculate the zero-volatility or static spread on a bond.