Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server bond convexity


CONVEXITY

Updated: 25 May 2012


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

    0.0981849108282582



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service