SQL Server bond convexity

Use 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.

Syntax

SELECT [wctFinancial].[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,>)

Arguments

@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.

@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.

@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.

@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.

@Basis

Day count basis

0 or omitted

US (NASD) 30/360

1

Actual/Actual

2

Actual/360

3

Actual/365

4

European 30/360

@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.

@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.

@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.

@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.

@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.

Return Type

float

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.

Example

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.

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.