SQL Server STEPCF Function

Updated 2023-10-05 16:11:00.287000

Description

Use the table-valued function STEPCF to return the cash flows of a stepped-rate bond. Stepped-rate bonds have different interest rates for different coupon periods and may even have a zero-rate period, usually at the commencement of the bond.

The first row in the resultant table is dated with settlement date passed into the function and is for the amount of the accrued interest. All the remaining rows are dated for the subsequent coupon dates and the amounts are the coupon amount. The row for the maturity date includes the coupon amount and the redemption amount.

The resultant table also includes the discount factor for each period as well as the discounted cash flow value for each period. The sum of the discounted cash flow values across all the periods is equal to the clean price of the bond.

Syntax

SELECT * FROM [westclintech].[wct].[STEPCF](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Steps, nvarchar(max),>
 ,<@Yield, float,>
 ,<@Redemption, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>
 ,<@Issue, datetime,>
 ,<@FirstCoupon, datetime,>
 ,<@LastCoupon, datetime,>)

Arguments

@Settlement

the settlement date of the bond. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@Maturity

the maturity date of the bond. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@Steps

an SQL statement which when executed will return the dates and rates associated with the step periods. The dates are the beginning of the coupon period is which the rate becomes effective. The rates are the annual coupon rates.

@Yield

the bond's yield. @Yield is an expression of type float or of a type that can be implicitly converted to float.

@Redemption

the bond's redemption value. Since the coupon amounts are calculation using a face amount of 100 the redemption value should be entered in relation to a 100 face value. @Redemption is an expression of type float or of a type that can be implicitly converted to float.

@Frequency

the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for bi-monthly, @Frequency = 6; for monthly, @Frequency = 12. For bonds with @Basis = 'A/364' or 9, you can enter 364 for payments made every 52 weeks, 182 for payments made every 26 weeks, 91 for payments made every 13 weeks, 28 for payments made every 4 weeks, 14 for payments made every 2 weeks, and 7 for weekly payments. @Frequency is an expression of type float or of a type that can be implicitly converted to float.

@Basis

is the type of day count to use. @Basis is an expression of the character string data type category.

@Issue

the issue date of the bond; the date from which the bond starts accruing interest. @Issue is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@FirstCoupon

the first coupon date of the bond. The period from the issue date until the first coupon date defines the odd interest period. All subsequent coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency. @FirstCoupon is an expression that returns a datetime or smalldatetime value, or a character string in date format.

@LastCoupon

the last coupon date of the bond prior to the maturity. The period from the last interest date until the maturity date defines the odd interest period. All coupon dates from @FirstCoupon to @LastCoupon are assumed to occur at regular periodic intervals as defined by @Frequency. @LastCoupon is an expression that returns a datetime or smalldatetime value, or a character string in date format.

Return Type

table

Remarks

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

If @Step is NULL or returns no rows then0 is used.

If @Redemption is NULL then @Redemption = 100.

If @Frequency is NULL then @Frequency = 2.

If @Basis is NULL then @Basis = 1.

If @Maturity <= @Settlement then no rows are returned.

If @Frequency invalid STEPCF returns an error.

If @Basis invalid (see above list), STEPCF returns an error.

If @Maturity is NULL then no rows are returned.

To calculate the cash flows for a bond paying regular periodic interest just enter @Maturity and @Settlement.

To calculate the cash flows for bond with an odd first coupon where the settlement date is before the first coupon date, enter @Issue, @FirstCoupon, @Settlement, and @Maturity. If the settlement date is on or after the first coupon date just enter @Maturity and @Settlement.

To calculate the cash flows for a bond with an odd last coupon enter @LastCoupon, @Settlement, and @Maturity.

To calculate the cash flows for bond with an odd first coupon and an odd last coupon where the settlement date is before the first coupon date, enter @Issue, @FirstCoupon, @LastCoupon, @Settlement, and @Maturity. If the settlement date is on or after the first coupon date just enter @LastCoupon, @Maturity and @Settlement.

Examples

Example #1

In this example we generate the cash flows for bond maturing on 2034-06-15. The settlement date is 2014-05-01, the yield is 2.76%. The coupon is paid twice-yearly, the redemption value is 100 and the day-count convention is actual/actual. The step-rate schedule is passed in using a derived table.

SELECT *
FROM wct.STEPCF(   '2014-05-01', --@Settlement
                   '2034-06-15', --@Maturity
                   'SELECT
          *
        FROM (VALUES
          (''2029-12-15''' + ', .015),
          (''2024-12-15''' + ', .014),
          (''2020-12-15''' + ', .013),
          (''2016-12-15''' + ', .012),
          (''2012-12-15''' + ', .011)
          )n(date_step, rate_step)',
                                 --@Step
                   0.0276,       --@Yield
                   100,          --@Redemption
                   2,            --@Frequency
                   1,            --@Basis
                   NULL,         --@Issue
                   NULL,         --@FirstCoupon
                   NULL          --@LastCoupon
               );

This produces the following result.

Example #2

In this example we use the same data but select the step rate data from a temporary table rather than a derived table.

SELECT *
INTO #t
FROM
(
    VALUES
        ('2029-12-15', .015),
        ('2024-12-15', .014),
        ('2020-12-15', .013),
        ('2016-12-15', .012),
        ('2012-12-15', .011)
) n (date_step, rate_step);
SELECT *
FROM wct.STEPCF(   '2014-05-01',       --@Settlement
                   '2034-06-15',       --@Maturity
                   'SELECT * FROM #t', --@Step
                   0.0276,             --@Yield
                   100,                --@Redemption
                   2,                  --@Frequency
                   1,                  --@Basis
                   NULL,               --@Issue
                   NULL,               --@FirstCoupon
                   NULL                --@LastCoupon
               );

This produces the following result.

Example #3

In this example, we have a bond with zero first step.

SELECT *
INTO #t
FROM
(
    VALUES
        ('2040-12-15', .018),
        ('2036-12-15', .017),
        ('2032-12-15', .016),
        ('2028-12-15', .015),
        ('2024-12-15', .014)
) n (date_step, rate_step);
SELECT *
FROM wct.STEPCF(   '2016-07-01',       --@Settlement
                   '2044-06-15',       --@Maturity
                   'SELECT * FROM #t', --@Step
                   0.0290,             --@Yield
                   100,                --@Redemption
                   2,                  --@Frequency
                   1,                  --@Basis
                   NULL,               --@Issue
                   NULL,               --@FirstCoupon
                   NULL                --@LastCoupon
               );

This produces the following result.

Example #4

In this example we use the same bond but the settlement is in the final coupon period.

SELECT *
INTO #t
FROM
(
    VALUES
        ('2040-12-15', .018),
        ('2036-12-15', .017),
        ('2032-12-15', .016),
        ('2028-12-15', .015),
        ('2024-12-15', .014)
) n (date_step, rate_step);
SELECT *
FROM wct.STEPCF(   '2044-04-01',       --@Settlement
                   '2044-06-15',       --@Maturity
                   'SELECT * FROM #t', --@Step
                   0.0290,             --@Yield
                   100,                --@Redemption
                   2,                  --@Frequency
                   1,                  --@Basis
                   NULL,               --@Issue
                   NULL,               --@FirstCoupon
                   NULL                --@LastCoupon
               );

This produces the following result.

Example #5

In this example we have a bond with an odd first coupon period.

SELECT *
INTO #t
FROM
(
    VALUES
        ('2040-12-15', .018),
        ('2035-12-15', .016),
        ('2030-12-15', .015),
        ('2025-12-15', .014),
        ('2020-12-15', .013),
        ('2015-12-01', .012)
) n (date_step, rate_step);
SELECT *
FROM wct.STEPCF(   '2016-04-01',       --@Settlement
                   '2044-06-15',       --@Maturity
                   'SELECT * FROM #t', --@Step
                   0.03125,            --@Yield
                   100,                --@Redemption
                   2,                  --@Frequency
                   1,                  --@Basis
                   '2015-12-01',       --@Issue
                   '2016-06-15',       --@FirstCoupon
                   NULL                --@LastCoupon
               );

This produces the following result.

Example #6

In this example we have a bond with an odd last coupon period.

SELECT *
INTO #t
FROM
(
    VALUES
        ('2040-12-01', .018),
        ('2035-12-01', .016),
        ('2030-12-01', .015),
        ('2025-12-01', .014),
        ('2020-12-01', .013),
        ('2015-12-01', .012)
) n (date_step, rate_step);
SELECT *
FROM wct.STEPCF(   '2016-04-01',       --@Settlement
                   '2044-06-15',       --@Maturity
                   'SELECT * FROM #t', --@Step
                   0.02125,            --@Yield
                   100,                --@Redemption
                   2,                  --@Frequency
                   1,                  --@Basis
                   NULL,               --@Issue
                   NULL,               --@FirstCoupon
                   '2043-12-01'        --@LastCoupon
               );

This produces the following result.

date_pmt                          amt_cashflow                      N                    PVF                   PVCF                cumPVCF
----------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
2016-04-01 00:00:00.000                   -0.4                      0                      1                   -0.4                   -0.4
2016-06-01 00:00:00.000                    0.6      0.333333333333333      0.996483214643168      0.597889928785901      0.197889928785901
2016-12-01 00:00:00.000                    0.6       1.33333333333333      0.986006891421812      0.591604134853087      0.789494063638988
2017-06-01 00:00:00.000                    0.6       2.33333333333333      0.975640708889857      0.585384425333914        1.3748784889729
2017-12-01 00:00:00.000                    0.6       3.33333333333333       0.96538350910561      0.579230105463366       1.95410859443627
2018-06-01 00:00:00.000                    0.6       4.33333333333333       0.95523414630116      0.573140487780696       2.52724908221696
2018-12-01 00:00:00.000                    0.6       5.33333333333333      0.945191486754394      0.567114892052636        3.0943639742696
2019-06-01 00:00:00.000                    0.6       6.33333333333333      0.935254408662357      0.561152645197414       3.65551661946701
2019-12-01 00:00:00.000                    0.6       7.33333333333333      0.925421802015937      0.555253081209562       4.21076970067658
2020-06-01 00:00:00.000                    0.6       8.33333333333333      0.915692568475881      0.549415541085528        4.7601852417621
2020-12-01 00:00:00.000                    0.6       9.33333333333333      0.906065621250098      0.543639372750059       5.30382461451216
2021-06-01 00:00:00.000                   0.65       10.3333333333333      0.896539884972268      0.582750925231974       5.88657553974414
2021-12-01 00:00:00.000                   0.65       11.3333333333333      0.887114295581712      0.576624292128113       6.46319983187225
2022-06-01 00:00:00.000                   0.65       12.3333333333333      0.877787800204539       0.57056207013295        7.0337619020052
2022-12-01 00:00:00.000                   0.65       13.3333333333333      0.868559357036031       0.56456358207342       7.59832548407862
2023-06-01 00:00:00.000                   0.65       14.3333333333333      0.859427935224273      0.558628157895778        8.1569536419744
2023-12-01 00:00:00.000                   0.65       15.3333333333333      0.850392514755001      0.552755134590751       8.70970877656515
2024-06-01 00:00:00.000                   0.65       16.3333333333333      0.841452086337663      0.546943856119481       9.25665263268463
2024-12-01 00:00:00.000                   0.65       17.3333333333333      0.832605651292679      0.541193673340241       9.79784630602487
2025-06-01 00:00:00.000                   0.65       18.3333333333333       0.82385222143988      0.535503943935922       10.3333502499608
2025-12-01 00:00:00.000                   0.65       19.3333333333333      0.815190818988131      0.529874032342285       10.8632242823031
2026-06-01 00:00:00.000                    0.7       20.3333333333333      0.806620476426104      0.564634333498273       11.4278586158014
2026-12-01 00:00:00.000                    0.7       21.3333333333333      0.798140236414203      0.558698165489942       11.9865567812913
2027-06-01 00:00:00.000                    0.7       22.3333333333333      0.789749151677628      0.552824406174339       12.5393811874656
2027-12-01 00:00:00.000                    0.7       23.3333333333333      0.781446284900559      0.547012399430391        13.086393586896
2028-06-01 00:00:00.000                    0.7       24.3333333333333      0.773230708621456      0.541261496035019        13.627655082931
2028-12-01 00:00:00.000                    0.7       25.3333333333333      0.765101505129456      0.535571053590619       14.1632261365217
2029-06-01 00:00:00.000                    0.7       26.3333333333333      0.757057766361861      0.529940436453303        14.693166572975
2029-12-01 00:00:00.000                    0.7       27.3333333333333      0.749098593802707      0.524369015661895       15.2175355886369
2030-06-01 00:00:00.000                    0.7       28.3333333333333      0.741223098382394      0.518856168867676       15.7363917575045
2030-12-01 00:00:00.000                    0.7       29.3333333333333      0.733430400378374      0.513401280264862       16.2497930377694
2031-06-01 00:00:00.000                   0.75       30.3333333333333      0.725719629316882      0.544289721987661       16.7940827597571
2031-12-01 00:00:00.000                   0.75       31.3333333333333      0.718089923875703      0.538567442906777       17.3326502026638
2032-06-01 00:00:00.000                   0.75       32.3333333333333      0.710540431787955      0.532905323840967       17.8655555265048
2032-12-01 00:00:00.000                   0.75       33.3333333333333      0.703070309746895      0.527302732310171        18.392858258815
2033-06-01 00:00:00.000                   0.75       34.3333333333333      0.695678723311708      0.521759042483781       18.9146173012988
2033-12-01 00:00:00.000                   0.75       35.3333333333333      0.688364846814306      0.516273635110729       19.4308909364095
2034-06-01 00:00:00.000                   0.75       36.3333333333333      0.681127863267093       0.51084589745032       19.9417368338598
2034-12-01 00:00:00.000                   0.75       37.3333333333333      0.673966964271706      0.505475223203779       20.4472120570636
2035-06-01 00:00:00.000                   0.75       38.3333333333333      0.666881349928713      0.500161012446535       20.9473730695101
2035-12-01 00:00:00.000                   0.75       39.3333333333333      0.659870228748263      0.494902671561197       21.4422757410713
2036-06-01 00:00:00.000                    0.8       40.3333333333333       0.65293281756167      0.522346254049336       21.9646219951207
2036-12-01 00:00:00.000                    0.8       41.3333333333333      0.646068341433935      0.516854673147148       22.4814766682678
2037-06-01 00:00:00.000                    0.8       42.3333333333333      0.639276033577177      0.511420826861742       22.9928974951295
2037-12-01 00:00:00.000                    0.8       43.3333333333333      0.632555135264987      0.506044108211989       23.4989416033415
2038-06-01 00:00:00.000                    0.8       44.3333333333333      0.625904895747668      0.500723916598134       23.9996655199397
2038-12-01 00:00:00.000                    0.8       45.3333333333333      0.619324572168378      0.495459657734703       24.4951251776744
2039-06-01 00:00:00.000                    0.8       46.3333333333333      0.612813429480152      0.490250743584121       24.9853759212585
2039-12-01 00:00:00.000                    0.8       47.3333333333333      0.606370740363787      0.485096592291029       25.4704725135495
2040-06-01 00:00:00.000                    0.8       48.3333333333333      0.599995785146604      0.479996628117283       25.9504691416668
2040-12-01 00:00:00.000                    0.8       49.3333333333333      0.593687851722057      0.474950281377646       26.4254194230444
2041-06-01 00:00:00.000                    0.9       50.3333333333333      0.587446235470186      0.528701611923167       26.9541210349676
2041-12-01 00:00:00.000                    0.9       51.3333333333333       0.58127023917891      0.523143215261019       27.4772642502286
2042-06-01 00:00:00.000                    0.9       52.3333333333333      0.575159172966145       0.51764325566953       27.9949075058982
2042-12-01 00:00:00.000                    0.9       53.3333333333333      0.569112354202741      0.512201118782467       28.5071086246806
2043-06-01 00:00:00.000                    0.9       54.3333333333333      0.563129107436231      0.506816196692608       29.0139248213732
2043-12-01 00:00:00.000                    0.9       55.3333333333333       0.55720876431538      0.501487887883842       29.5154127092571
2044-06-15 00:00:00.000       100.968852459016       56.4098360655738       0.55090504709795       55.6242504193603       85.1396631286174

Example #7

In this example we have a bond with odd first coupon and an odd last coupon period.

SELECT *
INTO #t
FROM
(
    VALUES
        ('2040-12-01', .018),
        ('2035-12-01', .016),
        ('2030-12-01', .015),
        ('2025-12-01', .014),
        ('2020-12-01', .013),
        ('2015-12-01', .012)
) n (date_step, rate_step);
SELECT *
FROM wct.STEPCF(   '2016-04-01',       --@Settlement
                   '2044-06-15',       --@Maturity
                   'SELECT * FROM #t', --@Step
                   0.02125,            --@Yield
                   100,                --@Redemption
                   2,                  --@Frequency
                   1,                  --@Basis
                   '2015-11-14',       --@Issue
                   '2016-06-01',       --@FirstCoupon
                   '2043-12-01'        --@LastCoupon
               );

This produces the following result.

See Also

BONDCF - Cash flows for a bond paying regular periodic interest