SQL Server OAS Function
Updated 2023-10-12 20:19:01.427000
Description
Use the 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 [westclintech].[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
@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:
| T | par | spot | df | ccZero |
|---|---|---|---|---|
| 1 | 0.035 | 0.0350000000000000 | 0.9661835748792272 | 0.0344014267173322 |
| 2 | 0.042 | 0.0421480257395637 | 0.9207488386325070 | 0.0412839924927361 |
| 3 | 0.047 | 0.0473524471924105 | 0.8704051352100749 | 0.0462655010233704 |
| 4 | 0.052 | 0.0527059539733534 | 0.8142760907475915 | 0.0513639481661993 |
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.
| OAS |
|---|
| 35.0004728271661 |
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.
| OAS |
|---|
| 88.680668251944 |
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.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.
| OAS |
|---|
| 117.0020175466 |
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 CMTCURVE 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.
| OAS |
|---|
| 605.9 |
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.
| ?id_bon | OA |
|---|---|
| A | 219 |
| B | 228.5 |
| C | 329.4 |
| D | 161.3 |
| E | 330 |
| F | 210.8 |
| G | 220.3 |
| H | 94.8 |
| I | 226.8 |
| J | 172.2 |
See Also
BONDPRICEFROMZEROES - Bond pricing from the zero coupon curve
CMTCURVE - Constant Maturity Treasury curve
LOGNORMALIRLATTICE - LogNormal Interest Rate Lattice
OAC - Option Adjusted Convexity
OAD - Calculate the option-adjusted duration on a bond.
PRICEFROMIRLATTICE - Bond Pricing using Option Adjusted Spread
PRICEFROMZEROESTVF - Zero Volatility spread details
ZSPREAD - Calculate the zero-volatility or static spread on a bond.