Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Option Adjusted Spread


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)
·         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 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
·         OAC - Option Adjusted Convexity
·         OAD - Option Adjusted Duration

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service