SQL Server Bond Pricing using Option Adjusted Spread

PriceFromIRLattice

Updated: 30 Nov 2016

Use the SQL Server scalar function PriceFromIRLattice to calculate the (clean) price of a corporate bond with a call or put option schedule given its Option-adjusted Spread. The OAS is entered in decimal format (i.e. 1 basis point = .0001)

Syntax

SELECT [wct].[PriceFromIRLattice](

<@Settlement,datetime,>

,<@Maturity,datetime,>

,<@Rate,sql_variant,>

,<@Spread,float,>

,<@Redemption,float,>

,<@Frequency,int,>

,<@Basis,nvarchar(4000),>

,<@LastCouponDate,datetime,>

,<@FirstCouponDate,datetime,>

,<@IssueDate,datetime,>

,<@CCZero,nvarchar(max),>

,<@CurveType,nvarchar(4000),>

,<@CurveStartDate,datetime,>

,<@CurveDayCount,nvarchar(4000),>

,<@CurveFrequency,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.

@Spread

The Option-adjusted spread as a decimal. 1 basis point = .0001

@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').

@CurveStartDate

The start date for the curve; used to calculate the time-in-years associated with the coupon dates.

@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.

@CurveFrequency

The compounding frequency used in the calculation of the discount factors when the supplied curve is the spot curve. Valid Values are (1,2,4).

@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').

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 are given the Option-adjusted Spread as 35 basis points 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 the volatility is 10%.

--The bond to be evaluated

SELECT wct.PriceFromIRLattice(

'2016-11-28'--@Settlement

,'2020-11-28'--@Maturity

,.065 --@Rate

,.0035 --@Spread

,NULL--@Redemption

,1 --@Frequency

,NULL--@Basis

,NULL--@LastCouponDate

,NULL--@FirstCouponDate

,NULL--@IssueDate

,'SELECT T, cczero FROM #z'--@CCZero

,NULL--@CurveType

,NULL--@CurveStartDate

,NULL--@CurveDayCount

,1 --@CurveFrequency

,'L'--@CurveInterpMethod

,0.10 --@Vol

,'SELECT ''2017-11-28'',100'--@OptionSched

)as PRICE

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 price for a step-up callable note with 4 years to maturity, callable in 2 years at 100 with a 10% volatility and an OAS of 88.7.

--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.PriceFromIRLattice(

'2016-11-28'--@Settlement

,'2020-11-28'--@Maturity

,'SELECT date_step, rate_step FROM #step'--@Rate

,.00887 --@Spread

,NULL--@Redemption

,1 --@Frequency

,NULL--@Basis

,NULL--@LastCouponDate

,NULL--@FirstCouponDate

,NULL--@IssueDate

,'SELECT T, cczero FROM #z'--@CCZero

,NULL--@CurveType

,NULL--@CurveStartDate

,NULL--@CurveDayCount

,1 --@CurveFrequency

,'L'--@CurveInterpMethod

,0.10 --@Vol

,'SELECT ''2018-11-28'',100'--@OptionSched

)as Price

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 OAS is 117.

--The bond to be evaluated

SELECT wct.PriceFromIRLattice(

'2016-11-28'--@Settlement

,'2020-11-28'--@Maturity

,.065 --@Rate

,.0117 --@Spread

,NULL--@Redemption

,1 --@Frequency

,NULL--@Basis

,NULL--@LastCouponDate

,NULL--@FirstCouponDate

,NULL--@IssueDate

,'SELECT T, cczero FROM #z'--@CCZero

,NULL--@CurveType

,NULL--@CurveStartDate

,NULL--@CurveDayCount

,1 --@CurveFrequency

,'L'--@CurveInterpMethod

,0.10 --@Vol

,'SELECT ''2017-11-28'',100, ''P'''--@OptionSched

)as Price

This produces the following result.

Example #4

In this example, we will calculate the price from the spread using 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 spread of 605.9. 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 asdatetime=CAST('2016-11-28'asdatetime)

DECLARE @StartDate asdatetime=CAST('2016-11-28'asdatetime)

DECLARE @Interp asCHAR(1)='S'

DECLARE @vol asfloat= 0.4248

DECLARE @rate_coupon asfloat= 0.07

DECLARE @date_maturity asdate=cast('2026-03-15'asdate)

DECLARE @Spread asfloat= 605.9 / 10000

DECLARE @dcc asvarchar(2)='1'

DECLARE @typeCurve aschar(2)='CC'

--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 asdatetime)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)

SELECTROUND(

wct.PriceFromIRLattice(

@Settlement --@Settlement

,@date_maturity --@Maturity

,@rate_coupon --@Rate

,@Spread --@Spread

,100 --@Redemption

,2 --@Frequency

,1 --@Basis

,NULL--@LastCouponDate

,NULL--@FirstCouponDate

,NULL--@IssueDate

,'SELECT t, cczero FROM #z'--@CCZero

,@typecurve --@CurveType

,@Settlement --@CurveStartDate

,@dcc --@CurveDayCount

,NULL--@CurveFrequency

,@Interp --@CurveInterpMethod

,@vol --@Vol

,'SELECT exdate,strike FROM #calls'--@OptionSched

), 3)as Price

This produces the following result.

Example #5

In this example we will calculate the OAS for multiple bonds with using 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 OAS for all the bonds in the SELECT.