OAS
Updated: 24 Feb 2018
Use the SQL Server scalar function OAS to calculate the Option-adjusted Spread for a corporate bond with a call or put option schedule. The return value should be multiplied by 10,000 to get a result expressed in basis points (i.e. 1 basis point = .0001).
Syntax
SELECT [wct].[OAS](
<@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),>)
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').
|
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 @Vol is NULL then @Vol = 0
· If @Notice is NULL then @Notice = 30
· 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 875 – 876. We start with the following par curve.
|
T
|
par
|
|
1
|
0.035
|
|
2
|
0.042
|
|
3
|
0.047
|
|
4
|
0.052
|
The following SQL puts the data into a temp table, #z, along with the associated spot rate, discount factor, and continuously compounded zero rate.
--Rates used in the OAS calculation
SELECT
T
,par
,spot
,df
,-LOG(df)/T as ccZero
INTO
#z
FROM (
SELECT
T
,par
,spot
,POWER(1+spot,-T) as df
FROM (VALUES
(1,0.035,0.035)
,(2,0.042,0.0421480257395637)
,(3,0.047,0.0473524471924105)
,(4,0.052,0.0527059539733534)
)n(T,par,spot)
)nn
The temp table #z should contain the following values:
Using the bond from the Fabozzi example we calculate the Option-adjusted 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.OAS(
'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, cczero FROM #z' --@CCZero
,NULL --@CurveType
,'2016-11-23' --@TradeDate
,NULL --@CurveDayCount
,30 --@Notice
,'L' --@CurveInterpMethod
,0.10 --@Vol
,'SELECT ''2017-11-28'',100' --@OptionSched
) * 10000 as OAS
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.OAS(
'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, cczero FROM #z' --@CCZero
,NULL --@CurveType
,'2016-11-23' --@TradeDate
,NULL --@CurveDayCount
,30 --@Notice
,'L' --@CurveInterpMethod
,0.10 --@Vol
,'SELECT ''2018-11-28'',100' --@OptionSched
) * 10000 as OAS
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.OAS(
'2016-11-28' --@Settlement
,'2020-11-28' --@Maturity
,.065 --@Rate
,102.3125 --@Price
,NULL --@Redemption
,1 --@Frequency
,NULL --@Basis
,NULL --@LastCouponDate
,NULL --@FirstCouponDate
,NULL --@IssueDate
,'SELECT T, cczero FROM #z' --@CCZero
,NULL --@CurveType
,'2016-11-23' --@TradeDate
,NULL --@CurveDayCount
,30 --@Notice
,'L' --@CurveInterpMethod
,0.10 --@Vol
,'SELECT ''2017-11-28'',100, ''P''' --@OptionSched
) * 10000 as OAS
This produces the following result.
Example #4
In this example, we will calculate the OAS 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
*
INTO
#z
FROM
wct.CMTCURVE('SELECT * FROM #par','S',2)
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)
SELECT ROUND(
wct.OAS(
@Settlement --@Settlement
,@date_maturity --@Maturity
,@rate_coupon --@Rate
,@price --@Price
,100 --@Redemption
,2 --@Frequency
,1 --@Basis
,NULL --@LastCouponDate
,NULL --@FirstCouponDate
,NULL --@IssueDate
,'SELECT t, cczero FROM #z' --@CCZero
,@typecurve --@CurveType
,@TradeDate --@TradeDate
,@dcc --@CurveDayCount
,@Notice --@Notice
,@Interp --@CurveInterpMethod
,@vol --@Vol
,'SELECT exdate,strike FROM #calls' --@OptionSched
) * 10000, 1) as OAS
This produces the following result.
Example #5
In this example we will calculate the OAS 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
*
INTO
#z
FROM
wct.CMTCURVE('SELECT * FROM #par','S',2)
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
,ROUND(
wct.OAS(
'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, cczero FROM #z' --@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
) * 10000, 1) as OAS
FROM
#bonds b
This produces the following result.
See Also