OAC
Updated: 30 Nov 2016
Use the SQL Server scalar function OAC to calculate the optionadjusted convexity for a corporate bond with a call or put option schedule.
The effective duration is derived by calculating the optionadjusted spread and then using the PriceFromIRLattice using 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 effective duration is calculated as
Where
V_{0} = value of the bond
V_{} = value of the bond using the V_{0} OAS and the par curve shifted down by d
V_{+} = value of the bond using the V_{0} 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),>
,<@CurveStartDate, datetime,>
,<@CurveDayCount, nvarchar(4000),>
,<@CurveFrequency, 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 steppedrate 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 daycount 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').

@CurveStartDate

The start date for the curve; used to calculate the timeinyears associated with the coupon dates.

@CurveDayCount

The daycount convention used in calculating the timeinyears associated with the coupon dates. Valid values are (0,1,2,3,4,21); see YEARFRAC documentation for more details.

@CurveFrequency

The compounding frequency used in the calculation of the discount factors when the supplied curve is the spot curve. Valid Values are (1,2,4).

@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 @CurveFrequency is NULL then @CurveFrequency = 2
Â· If @CurveInterpMethod is NULL then @CurveInterpMethod = 'L'
Â· If @Vol is NULL then @Vol = 0
Â· If @delta is NULL then @delta = .0025
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('20161128',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('20161128',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
,'20161128' @StartDate
,1 @Frequency
,'20161128' @SpotDate
,0 @CashBasis
,0 @FuturesBasis
,0 @SwapsBasis
,'S' @InterpMethod
,'A' @DateRoll
,NULL @Holidays
)k
WHERE
cczero IS NOT NULL
The temp table #z should contain the following values:
Using the bond from the Fabozzi example we calculate the Optionadjusted Spread 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(
'20161128' @Settlement
,'20201128' @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
,NULL @CurveStartDate
,NULL @CurveDayCount
,1 @CurveFrequency
,'L' @CurveInterpMethod
,0.10 @Vol
,'SELECT ''20171128'',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 4016, p. 873 in Fabozzi) we want to calculate the OAS for a stepup 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
('20161128', .0425)
,('20181128', .0750)
)n(date_step, rate_step)
The bond to be evaluated
SELECT wct.OAC(
'20161128' @Settlement
,'20201128' @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
,NULL @CurveStartDate
,NULL @CurveDayCount
,1 @CurveFrequency
,'L' @CurveInterpMethod
,0.10 @Vol
,'SELECT ''20171128'',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(
'20161128' @Settlement
,'20201128' @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
,NULL @CurveStartDate
,NULL @CurveDayCount
,1 @CurveFrequency
,'L' @CurveInterpMethod
,0.10 @Vol
,'SELECT ''20171128'',100, ''P''' @OptionSched
,0.0025 @delta
) as OAC
This produces the following result.
Example #4
In this example, we will calculate the optionadjusted duration off of the CMT curve, which unlike the previous examples, compounds semiannually. We use the CMTURVE function to convert the par rates into continuously compounded zeroes.
The bond matures on 20260315, has a coupon rate of 7.0% paid semiannually and a price of 94.75. The bond is callable based on the following schedule.
exdate

strike

20170315

103.50

20180315

103.00

20190315

102.50

20200315

102.00

20210315

101.50

20220315

101.00

20230315

100.50

20240315

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('20161128' as datetime)
DECLARE @StartDate as datetime = CAST('20161128' 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('20260315' as date)
DECLARE @price as float = 94.75
DECLARE @dcc as varchar(2) = '1'
DECLARE @typeCurve as char(2) = 'CC'
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
('20170315',103.50)
,('20180315',103.00)
,('20190315',102.50)
,('20200315',102.00)
,('20210315',101.50)
,('20220315',101.00)
,('20230315',100.50)
,('20240315',100.00)
)n(exdate,strike)
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
,@Settlement @CurveStartDate
,@dcc @CurveDayCount
,NULL @CurveFrequency
,@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 effective duration 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','20251103',0.0333,95.008)
,('B','20230512',0.0447,102.649)
,('C','20290717',0.0654,104.996)
,('D','20220806',0.0673,113.76)
,('E','20300218',0.0649,105.369)
,('F','20240817',0.047,104.604)
,('G','20230407',0.0488,104.893)
,('H','20260529',0.0584,114.427)
,('I','20231106',0.0426,101.56)
,('J','20270420',0.0572,104.506)
)n(id_bond,maturity,rate,price)
Create the call schedules for the bonds
SELECT
*
INTO
#calls
FROM (VALUES
('A','20191103',104)
,('A','20211103',102)
,('A','20231103',100)
,('B','20210512',100)
,('C','20190717',104)
,('C','20210717',103)
,('C','20230717',102)
,('C','20250717',101)
,('C','20270717',100)
,('D','20200806',100)
,('E','20200218',104.5)
,('E','20220218',103.5)
,('E','20240218',102.5)
,('E','20260218',101.5)
,('E','20280218',100)
,('F','20220817',100)
,('G','20210407',100)
,('H','20200529',104)
,('H','20220529',102)
,('H','20240529',100)
,('I','20211106',100)
,('J','20170420',103.5)
,('J','20180420',103)
,('J','20190420',102.5)
,('J','20200420',102)
,('J','20210420',101.5)
,('J','20220420',101)
,('J','20230420',100.5)
,('J','20250420',100)
)n(id_bond,exdate,strike)
SELECT
b.id_bond
,wct.OAC(
'20161128' @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
,NULL @CurveStartDate
,NULL @CurveDayCount
,NULL @CurveFrequency
,'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.
See Also