SQL Server YIELDSTEP Function

Updated 2023-10-05 21:32:28.777000

Description

Use the scalar function YIELDSTEP to calculate the yield from price per 100 face value of a security with multiple interest coupon rates, also known as step-up rates.

Syntax

SELECT *
INTO #coups
FROM (
      SELECT 'ABC', '2010-01-15', 0.050 UNION ALL
      SELECT 'ABC', '2013-01-15', 0.055 UNION ALL
      SELECT 'ABC', '2016-01-15', 0.060 UNION ALL
      SELECT 'GHI', '2031-07-22', 0.070 UNION ALL
      SELECT 'GHI', '2026-07-22', 0.0675 UNION ALL
      SELECT 'GHI', '2021-07-22', 0.0650 UNION ALL
      SELECT 'GHI', '2016-07-22', 0.0625 UNION ALL
      SELECT 'GHI', '2011-07-22', 0.0600 UNION ALL
      SELECT 'XYZ', '2023-03-01', 0.0600 UNION ALL
      SELECT 'XYZ', '2019-03-01', 0.0575 UNION ALL
      SELECT 'XYZ', '2015-03-1', 0.0550 UNION ALL
      SELECT 'XYZ', '2011-03-1', 0.0
      )n(secid, coupdate, couprate)
 
SELECT secid
,wct.YIELDSTEP('2013-04-09'
,maturity
,price
,redemption
,frequency
,basis
,'SELECT coupdate
 ,couprate
 FROM #coups
 WHERE secid = ''' + secid + ''''
 ) as YIELD
 
FROM (
      SELECT 'ABC', '2019-01-15', 103.670988, 100, 2, 0 UNION ALL
      SELECT 'GHI', '2036-07-22', 120.467994, 103, 2, 1 UNION ALL
      SELECT 'XYZ', '2027-03-01', 97.478325, 101, 1, 0
      )n(secid, maturity, price, redemption, frequency, basis)

Arguments

Return Type

float

Remarks

If @Basis < 0 or if @Basis > 4, then PRICESTEP returns an error.

@Settlement must be <= @Maturity.

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

If @Frequency is NULL, @Frequency = 2.

If @Basis is NULL, @Basis = 0.

If @Redemption is NULL, @Redemption = 100.

Examples

In this example we calculate the yield of a bond maturing on 2019-01-15 with the following step-up schedule:

    2010-01-15          5.0%

    2013-01-15          5.5%

    2016-01-15          6.0%

The price is 103.665743 and the settlement date is April 15, 2013.

SELECT wct.YIELDSTEP(
                        '2013-04-15', --@Settlement
                        '2019-01-15', --@Maturity
                        103.665743,   --@Price
                        100,          --@Redemption
                        2,            --@Frequency
                        0,            --@Basis
                        'SELECT wct.CALCDATE(2010,1,15), 0.05 UNION ALL
 SELECT wct.CALCDATE(2013,1,15), 0.055 UNION ALL
 SELECT wct.CALCDATE(2016,1,15), 0.06' --@Coupons
                    ) as YIELD;

This produces the following result.

{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"0.0500000004862878"}]}

The SELECT statement in @Coupons can make reference to another table, as in the following example.

SELECT *
INTO #coups
FROM
(
    SELECT wct.CALCDATE(2010, 1, 15),
           0.05
    UNION ALL
    SELECT wct.CALCDATE(2013, 1, 15),
           0.055
    UNION ALL
    SELECT wct.CALCDATE(2016, 1, 15),
           0.06
) n(coupdate, couprate);
SELECT wct.YIELDSTEP(   '2013-04-15',          --@Settlement
                        '2019-01-15',          --@Maturity
                        103.665743,            --@Price
                        100,                   --@Redemption
                        2,                     --@Frequency
                        0,                     --@Basis
                        'SELECT * FROM #coups' --@Coupons
                    ) as YIELD;

This produces the following result.

{"columns":[{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"YIELD":"0.0500000004862878"}]}

In this example we have multiple securities with different step-up schedules. For purposes of this example, the coupon schedules are stored in a temporary table, #coups, and the rest of the pricing information is stored in the derived table n.

SELECT *
INTO #coups
FROM
(
    SELECT 'ABC',
           '2010-01-15',
           0.050
    UNION ALL
    SELECT 'ABC',
           '2013-01-15',
           0.055
    UNION ALL
    SELECT 'ABC',
           '2016-01-15',
           0.060
    UNION ALL
    SELECT 'GHI',
           '2031-07-22',
           0.070
    UNION ALL
    SELECT 'GHI',
           '2026-07-22',
           0.0675
    UNION ALL
    SELECT 'GHI',
           '2021-07-22',
           0.0650
    UNION ALL
    SELECT 'GHI',
           '2016-07-22',
           0.0625
    UNION ALL
    SELECT 'GHI',
           '2011-07-22',
           0.0600
    UNION ALL
    SELECT 'XYZ',
           '2023-03-01',
           0.0600
    UNION ALL
    SELECT 'XYZ',
           '2019-03-01',
           0.0575
    UNION ALL
    SELECT 'XYZ',
           '2015-03-1',
           0.0550
    UNION ALL
    SELECT 'XYZ',
           '2011-03-1',
           0.0
) n(secid, coupdate, couprate);
SELECT secid,
       wct.YIELDSTEP(
                        '2013-04-09',
                        maturity,
                        price,
                        redemption,
                        frequency,
                        basis,
                        'SELECT coupdate
 ,couprate
 FROM #coups
 WHERE secid 
          = '' + secid+'''
                    ) as YIELD
FROM
(
    SELECT 'ABC',
           '2019-01-15',
           103.670988,
           100,
           2,
           0
    UNION ALL
    SELECT 'GHI',
           '2036-07-22',
           120.467994,
           103,
           2,
           1
    UNION ALL
    SELECT 'XYZ',
           '2027-03-01',
           97.478325,
           101,
           1,
           0
) n(secid, maturity, price, redemption, frequency, basis);

This produces the following result.

{"columns":[{"field":"secid"},{"field":"YIELD","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"secid":"ABC","YIELD":"0.0499999996168971"},{"secid":"GHI","YIELD":"0.0499999997417517"},{"secid":"XYZ","YIELD":"0.0499999997535682"}]}

See Also

PRICESTEP - Calculate the Price of a security with step-up rates