STEPACCINT
Updated 2023-10-05 15:58:00.727000
Syntax
SELECT [westclintech].[wct].[STEPACCINT](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>
,<@Coupons, nvarchar(max),>)
Description
Use the scalar function STEPACCINT to calculate the accrued interest for a stepped-coupon bond with a par value of 100.
Arguments
@Coupons
a SELECT statement, as a string, which identifies the coupon dates and rates to be used in the accrued interest calculations. 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 bond.
@Frequency
the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for monthly @Frequency = 12. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
@Maturity
the maturity date of the bond. @Settlement 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":114},{"field":"Day count basis","width":132}],"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 occurring within the coupon period of the bond. @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 @Basis > 4, STEPACCINT returns an error.
If @Maturity <= @Settlement 0 is returned.
If @Settlement is NULL, @Settlement = GETDATE().
If @Frequency is NULL, @Frequency = 2.
If @Basis is NULL, @Basis = 0.
If @Coupons is empty or NULL then 0 is returned.
Accrued interest is calculated from the previous coupon date to the settlement date.
Previous coupon date is calculated backwards from the maturity date. If the maturity date is the last day of the month, all the previous coupon dates are assumed to occur on the last day of the month.
Previous coupon date <= @Settlement < next coupon date
Examples
In this example we calculate the accrued interest for a bond maturing on 2019-01-15 with the following step-up schedule.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"column 2"}],"rows":[{"column 1":"2010-01-15","column 2":"5.0%"},{"column 1":"2013-01-15","column 2":"5.5%"},{"column 1":"2016-01-15","column 2":"6.0%"}]}
The settlement date is April 21, 2014.
SELECT wct.STEPACCINT(
'2014-04-21', --@Settlement
'2019-01-15', --@Maturity
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 ACCINT;
This produces the following result.
{"columns":[{"field":"ACCINT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ACCINT":"1.46666666666667"}]}
The SELECT statement in @Coupons can make reference to another table, as in the following example.
SELECT *
INTO #coups
FROM
(
SELECT '2010-1-15',
0.05
UNION ALL
SELECT '2013-1-15',
0.055
UNION ALL
SELECT '2016-1,15',
0.06
) n(coupdate, couprate);
SELECT wct.STEPACCINT( '2014-04-21', --@Settlement
'2019-01-15', --@Maturity
2, --@Frequency
0, --@Basis
'SELECT * FROM #coups' --@Coupons
) as ACCINT;
This produces the following result.
{"columns":[{"field":"ACCINT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ACCINT":"1.46666666666667"}]}
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.
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
) c(secid, coupdate, couprate);
SELECT secid,
wct.STEPACCINT(
'2014-04-21',
n.maturity,
frequency,
basis,
'SELECT
coupdate,
couprate
FROM
#coups
WHERE
secid = ' + '''' + n.secid + ''''
) as ACCINT
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":"ACCINT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"secid":"ABC","ACCINT":"1.46666666666667"},{"secid":"GHI","ACCINT":"1.47513812154696"},{"secid":"XYZ","ACCINT":"0"}]}
See Also
BONDINT - Accrued interest on a bond paying regular, periodic interest
PRICESTEP - Calculate the Price of a security with step-up rates
STEPCONVEXITY - Convexity of a stepped-coupon bond
STEPDURATION - Duration of a stepped-coupon bond
STEPMDURATION - Modified duration of a stepped coupon bond
YIELDSTEP - Calculate the Yield of a security with step-up rates