CONVEXITY
Updated 2023-10-06 13:48:20.553000
Syntax
SELECT [westclintech].[wct].[CONVEXITY] (
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Rate, float,>
,<@Yield, float,>
,<@Frequency, int,>
,<@Basis, nvarchar(4000),>
,<@Par, float,>
,<@Redemption, float,>
,<@IssueDate, datetime,>
,<@FirstInterestDate, datetime,>
,<@LastInterestDate, datetime,>)
Description
Use the scalar function CONVEXITY to calculate the convexity of an option free bond. The convexity of a bond is calculated as the second derivative of the price divided by the dirty price of the bond.
Arguments
@LastInterestDate
the last coupon date of the security prior to maturity date, if the last coupon period is an odd period. The period from the last interest date date until the maturity date defines the odd last interest period. All previous coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency. @LastInterestDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Basis
the day-count convention used in the calculation of the accrued coupon interest. @Basis is an expression of the character string data type category.
{"columns":[{"field":"@Basis","width":139},{"field":"Day count basis","width":396}],"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"}]}
@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. For interest-at-maturity securities, @Frequency = 0. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
@FirstInterestDate
the first coupon date of the security. The period from the issue date until the first coupon date defines the odd first interest period. All subsequent coupon dates are assumed to occur at regular periodic intervals as defined by @Frequency in relation to the @LastInterestDate (if entered) or @Maturity. @FirstInterestDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Rate
the coupon rate, as a decimal, for the financial instrument. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@Yield
the security’s annual yield. @Yield is an expression of type float or of a type that can be implicitly converted to float.
@Par
the par value of the financial instrument. @Par is an expression of type float or of a type that can be implicitly converted to float.
@IssueDate
the issue date of the security; the date from which the security starts accruing interest. @IssueDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Settlement
the settlement date of the transaction. @Settlement is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Maturity
the maturity date for the financial instrument. @Maturity is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Redemption
the redemption value of the financial instrument expressed in relation to the @Par. @Redemption is an expression of type float or of a type that can be implicitly converted to float.
Return Type
Remarks
@Settlement cannot be NULL.
@Maturity cannot be NULL.
@Settlement must be less than @Maturity.
If @Redemption is NULL, then @Redemption = @Par.
If @Par is NULL, then @Par = 100.
If @Frequency is NULL, then @Frequency = 2.
If @Basis is NULL, then @Basis = 0.
If @FirstInterestDate is NOT NULL, then @IssueDate cannot be NULL.
If @FirstInterestDate is NOT NULL, then @FirstInterestDate must be greater than @IssueDate.
If @LastInterestDate is NOT NULL, The @LastInterestDate must be less than @Maturity.
If @LastInterestDate is NOT NULL and @FirstInterestDate is NOT NULL, then @FirstInterestDate must be less than @LastInterestDate.
Examples
Calculate the convexity for a 2% semi-annual coupon with a yield of 2.17% with a maturity date of 20-Apr-2018 and a settlement date of 12-Dec-2011. The interest basis is Actual/Actual.
SELECT wct.CONVEXITY( '2011-12-12', --Settlement
'2018-04-20', --Maturity
.02, --Rate
.0217, --Yield
2, --Frequency
1, --Basis
NULL, --Par
NULL, --Redemption
NULL, --IssueDate
NULL, --FirstInterestDate
NULL --LastInterestDate
) as CONVEXITY;
This produces the following result.
{"columns":[{"field":"CONVEXITY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CONVEXITY":"39.3347575344594"}]}
Calculate the convexity for commercial maturing on 15-Aug-2012, settling on 25-May-2011 at a price of 99.75, with an interest rate of 0.2%. We need to use the YIELDMAT function to calculate the yield.
SELECT wct.CONVEXITY( '2012-05-25', --Settlement
'2012-08-15', --Maturity
.002, --Rate
wct.YIELDMAT( '2012-05-25', --Settelement
'2012-08-15', --Maturity
'2012-05-25', --IssueDate
.002, --Rate
99.75, --Price
0 --Basis
), --Yield
0, --Frequency
0, --Basis
NULL, --Par
NULL, --Redemption
'2012-05-25', --IssueDate
NULL, --FirstInterestDate
NULL --LastInterestDate
) as CONVEXITY;
This produces the following result.
{"columns":[{"field":"CONVEXITY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CONVEXITY":"0.0981849108282582"}]}