OAD
Updated: 24 Feb 2018
Use the SQL Server scalar function OAD to calculate the option-adjusted effective duration for a corporate or municipal bond with a call or put option schedule.
The option adjusted duration 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 effective duration 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].[OAD](
<@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)
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
)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 option-adjusted duration 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.OAD(
'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 OAD
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.OAD(
'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 OAD
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.OAD(
'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 OAD
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 duration
SELECT wct.OAD(
@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 OAD
This produces the following result.
Example #5
In this example we will calculate the option adjusted 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','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.OAD(
'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 OAD
FROM
#bonds b
This produces the following result.
Example #6
In this example we calculate the OAD 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.OAD(
'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 OAD;
This produces the following result.
Example #7
In this example we calculate the OAD 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.OAD(
'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 OAD;
This produces the following result.
See Also