STEPCONVEXITY
Updated: 21 April 2014
Use STEPCONVEXITY to calculate the convexity for a stepped-coupon bond. Convexity is defined as the second derivative of price with respect to yield divided by the dirty price of the bond multiplied by 100.
Syntax
SELECT [wctFinancial].[wct].[STEPCONVEXITY](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Yld, float,>
,<@Redemption, float,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>
,<@Coupons, nvarchar(max),>)
Arguments
@Settlement
the settlement date occurring within a coupon period 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. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@Yld
the yield for the maturity date passed into the function. @Yld is an expression of type float or of a type that can be implicitly converted to float.
@Redemption
the redemption value of the bond assuming a par value of 100. @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 monthly @Frequency = 12. @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.
|
Basis
|
Day count basis
|
|
0 or omitted
|
US (NASD) 30/360
|
|
1
|
Actual/Actual
|
|
2
|
Actual/360
|
|
3
|
Actual/365
|
|
4
|
European 30/360
|
@Coupons
a SELECT statement, as a string, which identifies the coupon dates and rates to be used in the duration 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 bond.
Return Type
float
Remarks
· If @Basis < 0 or @Basis > 4, STEPCONVEXITY 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 coupon rate is assumed to be zero.
· 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 convexity for 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 settlement date is April 21, 2014, the yield is 4.0% and the redemption value is 100.
SELECT wct.STEPCONVEXITY(
'2014-04-21', --@Settlement
'2019-01-15', --@Maturity
.04, --@Yld
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 CONVEXITY
This produces the following result
CONVEXITY
----------------------
0.202295869292095
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.STEPCONVEXITY(
'2014-04-21', --@Settlement
'2019-01-15', --@Maturity
.04, --@Yld
100, --@Redemption
2, --@Frequency
0, --@Basis
'SELECT * FROM #coups' --@Coupons
) as CONVEXITY
This produces the following result.
CONVEXITY
----------------------
0.202295869292095
In this example we have multiple securities with different step-up schedules and we only have the prices which need to be converted to yields for input into the convexity calculation. 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.STEPCONVEXITY(
'2014-04-21',
n.maturity,
wct.YIELDSTEP(
'2014-04-21',
n.maturity,
n.price,
n.redemption,
n.frequency,
n.basis,
'SELECT
coupdate,
couprate
FROM
#coups
WHERE
secid = ' + '''' + n.secid + ''''
),
n.redemption,
n.frequency,
n.basis,
'SELECT
coupdate,
couprate
FROM
#coups
WHERE
secid = ' + '''' + n.secid + ''''
) as CONVEXITY
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.
secid CONVEXITY
----- ----------------------
ABC 0.199686242775028
GHI 2.28385653810045
XYZ 1.11250327349395
See Also