YIELDSTEP
Updated 2023-10-05 21:32:28.777000
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)
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.
Arguments
@Price
the security’s price per 100 face value. @Price is an expression of type float or of a type that can be implicitly converted to float.
@Redemption
the security’s redemption value per 100 face value. @Redemption is an expression of type float or of a type that can be implicitly converted to float.
@Coupons
a SELECT statement, as a string, which identifies the coupon dates and rates to be used in the price calculation. The coupon rate is assumed to be in effect from the associated coupon date to the next greater coupon date returned by the select statement. The last rate is assumed to be in effect from the last date until the maturity date of the security.
@Frequency
the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
@Maturity
the maturity date of the security. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@Basis
is the type of day count to use. @Basis is an expression of the character string data type category.
{"columns":[{"field":"@Basis","width":139},{"field":"Day count basis","width":174}],"rows":[{"@Basis":"0 or omitted","Day count basis":"US (NASD) 30/360"},{"@Basis":"1","Day count basis":"Actual/Actual"},{"@Basis":"2","Day count basis":"Actual/360"},{"@Basis":"3","Day count basis":"Actual/365"},{"@Basis":"4","Day count basis":"European 30/360"}]}
@Settlement
the settlement date of the security. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.
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