Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Option Adjusted Convexity


OAC
Updated: 24 Feb 2018
Use the SQL Server scalar function OAC to calculate the option-adjusted convexity for a corporate or municipal bond with a call or put option schedule.
The option adjusted convexity is derived by calculating the option-adjusted spread and then using the PriceFromIRLattice with a continuously compounded zero coupon based on par curve that has been shifted up and down by some small number of basis points. The shifted curves and the delta (the amount by which the curves were shifted) are passed into the function.
The option-adjusted convexity is calculated as
Where
V0 = value of the bond
V- = value of the bond using the V0 OAS and the par curve shifted down by d
V+ = value of the bond using the V0 OAS and the par curve shifted up by d
d = amount by which the par curve is shifted (.0001 = 1 basis point)
Syntax
SELECT [wct].[OAC](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, sql_variant,>
 ,<@Price, float,>
 ,<@Redemption, float,>
 ,<@Frequency, int,>
 ,<@Basis, nvarchar(4000),>
 ,<@LastCouponDate, datetime,>
 ,<@FirstCouponDate, datetime,>
 ,<@IssueDate, datetime,>
 ,<@CCZero, nvarchar(max),>
 ,<@CurveType, nvarchar(4000),>
 ,<@TradeDate, datetime,>
 ,<@CurveDayCount, nvarchar(4000),>
 ,<@Notice, int,>
 ,<@CurveInterpMethod, nvarchar(4000),>
 ,<@Vol, float,>
 ,<@OptionSched, nvarchar(max),>
 ,<@delta, float,>)
Arguments

Input Name
Description
@Settlement
The settlement date of the bond.
@Maturity
The maturity date of the bond.
@Rate
The coupon rate of the bond (.01 = 1%). For stepped-rate bonds, an SQL statement which returns a result table consisting of the coupon start dates and the associated coupon rates.
@Price
The (clean) price of the bond.
@Redemption
The redemption value of the bond.
@Frequency
The coupon frequency of the bond; the number of times that the coupon interest is paid per year.
@Basis
The interest basis code; the day-count convention used in the calculation of the accrued interest.
@LastCouponDate
For bonds where the last coupon period is either longer or shorter than the regular coupon period, the last coupon date prior to the maturity date.
@FirstCouponDate
For bonds where the first coupon period is either longer or shorter than a regular coupon period, the date of the first coupon payment.
@IssueDate
For bonds where the first coupon period is either longer or short than a regular coupon period, the start date for the first period coupon interest.
@CCZero
An SQL statement which produces a resultant table containing 2 columns; the time in years and the rates to be used in the OAS calculation.
@CurveType
Identifies the curve in @CCZero as either a spot curve (S) or a continuously compounded zero coupon curve (CC). Valid values are ('S', 'CC').
@TradeDate
The trade date of the of the transaction.
@CurveDayCount
The day-count convention used in calculating the time-in-years associated with the coupon dates. Valid values are (0,1,2,3,4,21); see YEARFRAC documentation for more details.
@Notice
The number of days’ notice the holder of the option gives when exercising the option.
@CurveInterpMethod
The interpolation method to calculate the rate associated with the coupon dates; use 'L' for linear interpolation and 'S' for cubic spline interpolation.
@Vol
The volatility associated with the forward rates where 1% = .01.
@OptionSched
An SQL statement which returns a resultant table containing the exercise date, the strike price, and a call / put indicator ('P' or 'C').
@delta
Shift applied to the supplied curve where .0001 = 1 basis point.

Return Type
float
Remarks
·         If @Settlement is NULL then @Settlement = GETDATE()
·         If @Maturity is NULL then @Maturity = GETDATE()
·         If @Rate is NULL then @rate = 0
·         If @Price is NULL then @Price = 100
·         If @Redemption is NULL then @Redemption = 100
·         If @Frequency is NULL then @Frequency = 2
·         If @Basis is NULL then @Basis = 0
·         If @CurveType is NULL then @CurveType = 'CC'
·         If @CurveDayCount is NULL then @CurveDayCount = 0
·         If @TradeDate IS NULL then @TradeDate = GETDATE()
·         If @CurveInterpMethod is NULL then @CurveInterpMethod = 'L'
·         If @Notice is NULL then @Notice = 30
·         If @Vol is NULL then @Vol = 0
·         If @delta is NULL then @delta = .0025
·         The earliest exercise date is the greater of the minimum exercise date in the option schedule and the @TradeDate + Notice (days)
·         Available in XLeratorDB / financial 2008 only
Examples
Example #1
This example is taken from The Handbook of Fixed Income Securities, Eighth Edition Edited by Frank J. Fabozzi with Steven V. Mann, Chapter 40 pp 876 – 878. We start with the following par curve.

T
par
1
0.035
2
0.042
3
0.047
4
0.052

The following SQL will convert the par curve and shift it up and down by 25 basis points, calculate the corresponding continuously compounded zero rates and store the result in the #z temp table.
--Establish the curve
SELECT
    wct.EDATE('2016-11-28',12*T) as mdate
   ,r
   ,iType
INTO
   #par
FROM (VALUES
    (1,0.0350,'C')
   ,(2,0.0420,'S')
   ,(3,0.0470,'S')
   ,(4,0.0520,'S')
   )n(T,r,iType)
  
--Convert the par curve to continuously compounded zeroes,
--shift by 25 basis points and store in #z
SELECT
    x.Ztype
   ,wct.YEARFRAC('2016-11-28',k.mat_date,0) as T
   ,k.cczero
INTO
   #z
FROM (VALUES
    ('Z', 'SELECT mDate,r,iType FROM #par')
   ,('Zplus','SELECT mDate,r+.0025,iType FROM #par')
   ,('Zminus','SELECT mDate,r-.0025,iType FROM #par')
   )x(Ztype,ZSQL)
CROSS APPLY
   wct.SWAPCURVE(
       x.ZSQL                       --@InputData_RangeQuery
      ,'2016-11-28'                 --@StartDate
      ,1                            --@Frequency
      ,'2016-11-28'                 --@SpotDate
      ,0                            --@CashBasis
      ,0                            --@FuturesBasis
      ,0                            --@SwapsBasis
      ,'S'                          --@InterpMethod
      ,'A'                          --@DateRoll
      ,NULL                         --@Holidays
      )
WHERE
   cczero IS NOT NULL
The temp table #z should contain the following values:
Using the bond from the Fabozzi example we calculate the option-adjusted convexity for a bond that matures in 4 years that is redeemable at par starting with the next coupon date. The bond has 6.5% coupon which is paid annually and is trading at a price of 102.218. The volatility is 10%.
--The bond to be evaluated
SELECT wct.OAC(
    '2016-11-28' --@Settlement
   ,'2020-11-28' --@Maturity
   ,.065         --@Rate
   ,102.218      --@Price
   ,NULL         --@Redemption
   ,1            --@Frequency
   ,NULL         --@Basis
   ,NULL         --@LastCouponDate
   ,NULL         --@FirstCouponDate
   ,NULL         --@IssueDate
   ,'SELECT T, Z, zPlus, Zminus
    FROM (SELECT * FROM #z) pvt
    PIVOT (MAX(cczero) for Ztype in (Z,Zplus,Zminus))d'     --@CCZero
   ,NULL         --@CurveType
   ,'2016-11-23' --@TradeDate
   ,NULL         --@CurveDayCount
   ,30           --@Notice
   ,'L'          --@CurveInterpMethod
   ,0.10         --@Vol
   ,'SELECT ''2017-11-28'',100' --@OptionSched
   ,0.0025       --@delta
   ) as OAC
This produces the following result.
Example #2
We will use the same curve information as from the previous example. In this example (base on Exhibit 40-16, p. 873 in Fabozzi) we want to calculate the OAS for a step-up callable note with 4 years to maturity, callable in 2 years at 100 with a 10% volatility. The price of the note is 98.031
--Put the step information into the #step table
SELECT
    date_step
   ,rate_step
INTO
   #step
FROM (VALUES
    ('2016-11-28', .0425)
   ,('2018-11-28', .0750)
   )n(date_step, rate_step)
  
--The bond to be evaluated
SELECT wct.OAC(
    '2016-11-28' --@Settlement
   ,'2020-11-28' --@Maturity
   ,'SELECT date_step, rate_step FROM #step'    --@Rate
   ,98.031       --@Price
   ,NULL         --@Redemption
   ,1            --@Frequency
   ,NULL         --@Basis
   ,NULL         --@LastCouponDate
   ,NULL         --@FirstCouponDate
   ,NULL         --@IssueDate
   ,'SELECT T, Z, zPlus, Zminus
    FROM (SELECT * FROM #z) pvt
    PIVOT (MAX(cczero) for Ztype in (Z,Zplus,Zminus))d'     --@CCZero
   ,NULL         --@CurveType
   ,'2016-11-23' --@TradeDate
   ,NULL         --@CurveDayCount
   ,30           --@Notice
   ,'L'          --@CurveInterpMethod
   ,0.10         --@Vol
   ,'SELECT ''2017-11-28'',100' --@OptionSched
   ,0.0025       --@delta
   ) as OAC
This produces the following result.
Example #3
This is similar to Example #1, except that we make the bond putable rather than callable and the price is 102.3125.
--The bond to be evaluated
SELECT wct.OAC(
    '2016-11-28'    --@Settlement
   ,'2020-11-28'    --@Maturity
   ,.065            --@Rate
   ,102.218         --@Price
   ,NULL            --@Redemption
   ,1               --@Frequency
   ,NULL            --@Basis
   ,NULL            --@LastCouponDate
   ,NULL            --@FirstCouponDate
   ,NULL            --@IssueDate
   ,'SELECT T, Z, zPlus, Zminus
    FROM (SELECT * FROM #z) pvt
    PIVOT (MAX(cczero) for Ztype in (Z,Zplus,Zminus))d'     --@CCZero
   ,NULL            --@CurveType
   ,'2016-11-23'    --@TradeDate
   ,NULL            --@CurveDayCount
   ,30              --@Notice
   ,'L'             --@CurveInterpMethod
   ,0.10            --@Vol
   ,'SELECT ''2017-11-28'',100, ''P''' --@OptionSched
   ,0.0025          --@delta
   ) as OAC
This produces the following result.
Example #4
In this example, we will calculate the option-adjusted duration off of the CMT curve, which unlike the previous examples, compounds semi-annually. We use the CMTURVE function to convert the par rates into continuously compounded zeroes.
The bond matures on 2026-03-15, has a coupon rate of 7.0% paid semi-annually and a price of 94.75. The bond is callable based on the following schedule.

exdate
strike
2017-03-15
103.50
2018-03-15
103.00
2019-03-15
102.50
2020-03-15
102.00
2021-03-15
101.50
2022-03-15
101.00
2023-03-15
100.50
2024-03-15
100.00

We have called the function using variables simply to demonstrate another way to pass parameters into the function.
--Variables to guarantee consistency in the function calls
DECLARE @Settlement as datetime = CAST('2016-11-28' as datetime)
DECLARE @StartDate as datetime = CAST('2016-11-28' as datetime)
DECLARE @Interp as CHAR(1) = 'S'
DECLARE @vol as float = 0.4248
DECLARE @rate_coupon as float = 0.07
DECLARE @date_maturity as date = cast('2026-03-15' as date)
DECLARE @price as float = 94.75
DECLARE @dcc as varchar(2) = '1'
DECLARE @typeCurve as char(2) = 'CC'
DECLARE @TradeDate as datetime = CAST('2016-11-23' as datetime)
DECLARE @Notice as int = 30
 
--Establish the CMT curve
SELECT
   *
INTO
   #par
FROM (VALUES
    (0.25,0.00396)
   ,(0.5,0.00520)
   ,(1,0.00614)
   ,(2,0.00823)
   ,(3,0.00987)
   ,(4,0.01138)
   ,(5,0.01290)
   ,(7,0.01605)
   ,(10,0.01839)
   ,(20,0.02216)
   ,(30,0.02593)
   )n(T,r)
 
--Convert the CMT curve to continuously compounded zeroes
SELECT
    x.Ztype
   ,k.T
   ,k.cczero
INTO
   #z
FROM (VALUES
    ('Z', 'SELECT T,r FROM #par')
   ,('Zplus','SELECT T,r+.0025 FROM #par')
   ,('Zminus','SELECT T,r-.0025 FROM #par')
   )x(Ztype,ZSQL)
CROSS APPLY
   wct.CMTCURVE(x.ZSQL,'S',2)k
WHERE
   bootstrap = 'False'
  
--Put the call schedules into a table
SELECT
   CAST(exdate as datetime) as exdate, strike
INTO
   #calls
FROM (VALUES
    ('2017-03-15',103.50)
   ,('2018-03-15',103.00)
   ,('2019-03-15',102.50)
   ,('2020-03-15',102.00)
   ,('2021-03-15',101.50)
   ,('2022-03-15',101.00)
   ,('2023-03-15',100.50)
   ,('2024-03-15',100.00)
   )n(exdate,strike)      
  
--Calculate the option-adjusted convexity
SELECT wct.OAC(
    @Settlement    --@Settlement
   ,@date_maturity --@Maturity
   ,@rate_coupon   --@Rate
   ,@price         --@Price
   ,100            --@Redemption
   ,2              --@Frequency
   ,1              --@Basis
   ,NULL           --@LastCouponDate
   ,NULL           --@FirstCouponDate
   ,NULL           --@IssueDate
   ,'SELECT T, Z, zPlus, Zminus
    FROM (SELECT * FROM #z) pvt
    PIVOT (MAX(cczero) for Ztype in (Z,Zplus,Zminus))d'     --@CCZero
   ,@typecurve     --@CurveType
   ,@TradeDate    --@TradeDate
   ,@dcc           --@CurveDayCount
   ,@Notice    --@Notice
   ,@Interp        --@CurveInterpMethod
   ,@vol           --@Vol
   ,'SELECT exdate,strike FROM #calls'   --@OptionSched
   ,NULL           --@delta
   )as OAC
This produces the following result.
Example #5
In this example we will calculate the option adjusted convexity for multiple bonds with a single SQL statement. We will use the same CMT curve as in the previous example, which is stored in the temp table #z. We populate the #bonds table with some information about the bonds. We populate the #calls table with some information about the call schedules associated with each bond. We then calculate the the price for all the bonds in the SELECT.
--Establish the CMT curve
SELECT
   *
INTO
   #par
FROM (VALUES
    (0.25,0.00396)
   ,(0.5,0.00520)
   ,(1,0.00614)
   ,(2,0.00823)
   ,(3,0.00987)
   ,(4,0.01138)
   ,(5,0.01290)
   ,(7,0.01605)
   ,(10,0.01839)
   ,(20,0.02216)
   ,(30,0.02593)
   )n(T,r)
 
--Convert the CMT curve to continuously compounded zeroes
SELECT
    x.Ztype
   ,k.T
   ,k.cczero
INTO
   #z
FROM (VALUES
    ('Z', 'SELECT T,r FROM #par')
   ,('Zplus','SELECT T,r+.0025 FROM #par')
   ,('Zminus','SELECT T,r-.0025 FROM #par')
   )x(Ztype,ZSQL)
CROSS APPLY
   wct.CMTCURVE(x.ZSQL,'S',2)k
WHERE
   bootstrap = 'False'
  
--Enter some bonds into a table
SELECT
   *
INTO
   #bonds
FROM (VALUES
    ('A','2025-11-03',0.0333,95.008)
   ,('B','2023-05-12',0.0447,102.649)
   ,('C','2029-07-17',0.0654,104.996)
   ,('D','2022-08-06',0.0673,113.76)
   ,('E','2030-02-18',0.0649,105.369)
   ,('F','2024-08-17',0.047,104.604)
   ,('G','2023-04-07',0.0488,104.893)
   ,('H','2026-05-29',0.0584,114.427)
   ,('I','2023-11-06',0.0426,101.56)
   ,('J','2027-04-20',0.0572,104.506)
   )n(id_bond,maturity,rate,price)
  
--Create the call schedules for the bonds
SELECT
   *
INTO
   #calls
FROM (VALUES
    ('A','2019-11-03',104)
   ,('A','2021-11-03',102)
   ,('A','2023-11-03',100)
   ,('B','2021-05-12',100)
   ,('C','2019-07-17',104)
   ,('C','2021-07-17',103)
   ,('C','2023-07-17',102)
   ,('C','2025-07-17',101)
   ,('C','2027-07-17',100)
   ,('D','2020-08-06',100)
   ,('E','2020-02-18',104.5)
   ,('E','2022-02-18',103.5)
   ,('E','2024-02-18',102.5)
   ,('E','2026-02-18',101.5)
   ,('E','2028-02-18',100)
   ,('F','2022-08-17',100)
   ,('G','2021-04-07',100)
   ,('H','2020-05-29',104)
   ,('H','2022-05-29',102)
   ,('H','2024-05-29',100)
   ,('I','2021-11-06',100)
   ,('J','2017-04-20',103.5)
   ,('J','2018-04-20',103)
   ,('J','2019-04-20',102.5)
   ,('J','2020-04-20',102)
   ,('J','2021-04-20',101.5)
   ,('J','2022-04-20',101)
   ,('J','2023-04-20',100.5)
   ,('J','2025-04-20',100)
   )n(id_bond,exdate,strike)
  
SELECT
    b.id_bond
   ,wct.OAC(
    '2016-11-28' --@Settlement
   ,b.maturity   --@Maturity
   ,b.rate       --@Rate
   ,b.price      --@Price
   ,NULL         --@Redemption
   ,NULL         --@Frequency
   ,NULL         --@Basis
   ,NULL         --@LastCouponDate
   ,NULL         --@FirstCouponDate
   ,NULL         --@IssueDate
   ,'SELECT T, Z, zPlus, Zminus
    FROM (SELECT * FROM #z) pvt
    PIVOT (MAX(cczero) for Ztype in (Z,Zplus,Zminus))d'     --@CCZero
   ,NULL        --@CurveType
   ,'2016-11-23' --@TradeDate
   ,NULL        --@CurveDayCount
   ,30      --@Notice
   ,'S'         --@CurveInterpMethod
   ,0.45        --@Vol
   ,'SELECT
      exdate
       ,strike
    FROM
       #calls c
    WHERE
       c.id_bond = ''' + CAST(b.id_bond as varchar(max)) + ''''      --@OptionSched
   ,NULL         --@delta
   ) as OAC
FROM
   #bonds b
This produces the following result.
Example #6

In this example we calculate the OAC for a bond where the exercise dates do not occur on the coupon dates.

SELECT
    *
INTO
    #z
FROM (VALUES
     (0.25,0.004088,0.006585,0.00159)
    ,(0.5,0.009069,0.011557,0.006579)
    ,(1,0.014058,0.016542,0.011571)
    ,(2,0.01907,0.021554,0.016583)
    ,(3,0.029476,0.031971,0.026977)
    ,(4,0.034622,0.037123,0.032117)
    ,(5,0.043608,0.046138,0.041075)
    ,(7,0.051362,0.053912,0.048809)
    ,(10,0.057243,0.059822,0.054663)
    ,(20,0.067937,0.070658,0.06522)
    ,(30,0.080957,0.084369,0.077605)
    )n(zT,z,Zplus,Zminus)
   
SELECT ROUND(wct.OAC(
     '2018-07-31'
    ,'2020-12-16'
    ,0.0434
    ,103.3414
    ,100
    ,2
    ,0
    ,NULL
    ,NULL
    ,NULL
    ,'SELECT zT,z,Zplus,Zminus FROM #z'
    ,'CC'
    ,'2018-07-27'
    ,0
    ,30
    ,'S'
    ,0.2662
    ,'SELECT * FROM (VALUES (''16-Nov-18'', 102.17),(''16-Nov-19'', 100))n(exDate,Strike)'
    ,.0025
    ), 4) as OAC;

This produces the following result.

Example #7

In this example we calculate the OAC for a bond where the first exercise date is less than the trade date plus the notice days.

SELECT
    *
INTO
    #z
FROM (VALUES
     (0.25,0.002499,0.004997,0)
    ,(0.5,0.008283,0.010771,0.005792)
    ,(1,0.01459,0.017074,0.012103)
    ,(2,0.020681,0.023164,0.018194)
    ,(3,0.025928,0.028416,0.023438)
    ,(4,0.031815,0.034313,0.029314)
    ,(5,0.039018,0.041539,0.036495)
    ,(7,0.047095,0.049645,0.044542)
    ,(10,0.057609,0.060234,0.054983)
    ,(20,0.063486,0.066171,0.060804)
    ,(30,0.102064,0.107924,0.096639)
    )n(zT,z,Zplus,Zminus)
 
SELECT ROUND(wct.OAC(
     '2018-11-14'
    ,'2023-09-10'
    ,0.0676
    ,93.0011
    ,100
    ,2
    ,0
    ,NULL
    ,NULL
    ,NULL
    ,'SELECT zT, z FROM #z'
    ,'CC'
    ,'2018-11-12'
    ,0
    ,30
    ,'L1'
    ,0.4526
    ,'SELECT * FROM (VALUES (''10-Sep-18'',110.14),(''10-Sep-19'',106.76),(''10-Sep-20'',103.38),(''10-Sep-21'',100))n(exDate,Strike)'
    ,.0025
    ), 4) as OAC;

This produces the following result.

 

See Also
·         OAD - Option Adjusted Duration
·         OAS - Option Adjusted Spread

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service