SQL Server OAD Function
Updated 2024-02-26 16:38:46.397000
Description
Use the 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
OAD=\frac{V_{-}-V_{+}}{2*V_0*\delta}
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 [westclintech].[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
@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 OAD 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:
| Ztype | T | cczero |
|---|---|---|
| Z | 1 | 0.0344014267173323 |
| Z | 2 | 0.041283992492736 |
| Z | 3 | 0.0462655010233704 |
| Z | 4 | 0.0513287914597201 |
| Zplus | 1 | 0.0368139731227164 |
| Zplus | 2 | 0.0436884450370821 |
| Zplus | 3 | 0.0486676850230092 |
| Zplus | 4 | 0.0537321856528068 |
| Zminus | 1 | 0.0319830458530508 |
| Zminus | 2 | 0.0388737445529729 |
| Zminus | 3 | 0.043857552986118 |
| Zminus | 4 | 0.0489196831057578 |
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.
| OAD |
|---|
| 2.23446550457953 |
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.
| OAD |
|---|
| 2.36777056704636 |
Example #3
This is similar to Example #1, except that we make the bond puttable 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.
| OAD |
|---|
| 1.71715476842106 |
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.
| OAD |
|---|
| 6.52815152084067 |
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.
| ?id_bon | OA |
|---|---|
| A | 7.51448525726423 |
| B | 5.20886129173865 |
| C | 6.33338428044228 |
| D | 3.75345904509533 |
| E | 6.69012147235638 |
| F | 6.01287956803623 |
| G | 4.84793650227274 |
| H | 5.22633948306104 |
| I | 5.62042776422799 |
| J | 2.21508209841427 |
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.
| OAD |
|---|
| 0.6805 |
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.
| OAD |
|---|
| 3.9588 |
See Also
BONDPRICEFROMZEROES - Bond pricing from the zero coupon curve
CMTCURVE - Constant Maturity Treasury curve
LOGNORMALIRLATTICE - LogNormal Interest Rate Lattice
OAC - Option Adjusted Convexity
PRICEFROMIRLATTICE - Bond Pricing using Option Adjusted Spread
PRICEFROMZEROESTVF - Zero Volatility spread details
ZSPREAD - Calculate the zero-volatility or static spread on a bond.