PriceFromIRLattice
Updated 2023-10-12 20:43:28.133000
Syntax
SELECT [westclintech].[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),>
,<@TradeDate, datetime,>
,<@CurveDayCount, nvarchar(4000),>
,<@Notice, int,>
,<@CurveInterpMethod, nvarchar(4000),>
,<@Vol, float,>
,<@OptionSched, nvarchar(max),>)
Description
Use the scalar function PriceFromIRLattice to calculate the (clean) price of a corporate or municipal 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)
Arguments
@CurveInterpMethod
The interpolation method to calculate the rate associated with the coupon dates; use 'L' for linear interpolation and 'S' for cubic spline interpolation.
@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.
@OptionSched
An SQL statement which returns a resultant table containing the exercise date, the strike price, and a call / put indicator ('P' or 'C').
@Basis
The interest basis code; the day-count convention used in the calculation of the accrued interest.
@Frequency
The coupon frequency of the bond; the number of times that the coupon interest is paid per year.
@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.
@TradeDate
The trade date of the of the transaction.
@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.
@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.
@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.
@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.
@Notice
The number of days’ notice the holder of the option gives when exercising the option.
@Maturity
The maturity date of the bond.
@Settlement
The settlement date of the bond.
@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').
@Spread
The Option-adjusted spread as a decimal. 1 basis point = .0001
@Redemption
The redemption value of the bond.
@Vol
The volatility associated with the forward rates where 1% = .01.
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 @Spread is NULL then @Spread = 0.
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.
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.
{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"par","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"1","par":"0.035"},{"T":"2","par":"0.042"},{"T":"3","par":"0.047"},{"T":"4","par":"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:
{"columns":[{"field":"T","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"par","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"spot","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"df","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ccZero","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"T":"1","par":"0.035","spot":"0.0350000000000000","df":"0.9661835748792272","ccZero":"0.0344014267173322"},{"T":"2","par":"0.042","spot":"0.0421480257395637","df":"0.9207488386325070","ccZero":"0.0412839924927361"},{"T":"3","par":"0.047","spot":"0.0473524471924105","df":"0.8704051352100749","ccZero":"0.0462655010233704"},{"T":"4","par":"0.052","spot":"0.0527059539733534","df":"0.8142760907475915","ccZero":"0.0513639481661993"}]}
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
'2016-11-23', --@TradeDate
NULL, --@CurveDayCount
30, --@Notice
'L', --@CurveInterpMethod
0.10, --@Vol
'SELECT ''2017-11-28'',100' --@OptionSched
) as PRICE;
This produces the following result.
{"columns":[{"field":"PRICE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PRICE":"102.218010972802"}]}
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
'2016-11-23', --@TradeDate
NULL, --@CurveDayCount
30, --@Notice
'L', --@CurveInterpMethod
0.10, --@Vol
'SELECT ''2018-11-28'',100' --@OptionSched
) as Price;
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"98.0305708223556"}]}
Example #3
This is similar to Example #1, except that we make the bond puttable 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
'2016-11-23', --@TradeDate
NULL, --@CurveDayCount
30, --@Notice
'L', --@CurveInterpMethod
0.10, --@Vol
'SELECT ''2017-11-28'',100, ''P''' --@OptionSched
) as Price;
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"102.312535329306"}]}
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.
{"columns":[{"field":"exdate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"strike","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"exdate":"2017-03-15","strike":"103.50"},{"exdate":"2018-03-15","strike":"103.00"},{"exdate":"2019-03-15","strike":"102.50"},{"exdate":"2020-03-15","strike":"102.00"},{"exdate":"2021-03-15","strike":"101.50"},{"exdate":"2022-03-15","strike":"101.00"},{"exdate":"2023-03-15","strike":"100.50"},{"exdate":"2024-03-15","strike":"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 @Spread as float = 605.9 / 10000;
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.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
@TradeDate, --@TradeDate
@dcc, --@CurveDayCount
@Notice, --@Notice
@Interp, --@CurveInterpMethod
@vol, --@Vol
'SELECT exdate,strike FROM #calls' --@OptionSched
),
3) as Price;
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"94.751"}]}
Example #5
In this example we will calculate the price from spread for multiple bonds 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.
--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, 219),
('B', '2023-05-12', 0.0447, 228.5),
('C', '2029-07-17', 0.0654, 329.4),
('D', '2022-08-06', 0.0673, 161.3),
('E', '2030-02-18', 0.0649, 329.9),
('F', '2024-08-17', 0.047, 210.8),
('G', '2023-04-07', 0.0488, 220.3),
('H', '2026-05-29', 0.0584, 94.8),
('I', '2023-11-06', 0.0426, 226.8),
('J', '2027-04-20', 0.0572, 172.2)) n (id_bond, maturity, rate, OAS);
--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.PriceFromIRLattice(
'2016-11-28', --@Settlement
b.maturity, --@Maturity
b.rate, --@Rate
b.OAS / 10000, --@Spread
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
),
3) as Price
FROM #bonds b;
This produces the following result.
{"columns":[{"field":"?id_bon"},{"field":"Pric","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"?id_bon":"A","Pric":"95.009"},{"?id_bon":"B","Pric":"102.647"},{"?id_bon":"C","Pric":"104.998"},{"?id_bon":"D","Pric":"113.761"},{"?id_bon":"E","Pric":"105.374"},{"?id_bon":"F","Pric":"104.603"},{"?id_bon":"G","Pric":"104.893"},{"?id_bon":"H","Pric":"114.428"},{"?id_bon":"I","Pric":"101.562"},{"?id_bon":"J","Pric":"104.507"}]}
Example #6
In this example we calculate the Price from spread for a bond where the exercise dates do not occur on the coupon dates.
SELECT *
INTO #z
FROM
(
VALUES
(0.25, 0.004088),
(0.5, 0.009069),
(1, 0.014058),
(2, 0.01907),
(3, 0.029476),
(4, 0.034622),
(5, 0.043608),
(7, 0.051362),
(10, 0.057243),
(20, 0.067937),
(30, 0.080957)
) n (zT, z);
SELECT ROUND(
wct.PriceFromIRLAttice(
'2018-07-31',
'2020-12-16',
0.0434,
-0.00367,
100,
2,
0,
NULL,
NULL,
NULL,
'SELECT zT, z 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)'
),
4
) as Price;
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"103.3414"}]}
Example #7
In this example we calculate the price from spread 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.5, 0.008283),
(1, 0.01459),
(2, 0.020681),
(3, 0.025928),
(4, 0.031815),
(5, 0.039018),
(7, 0.047095),
(10, 0.057609),
(20, 0.063486),
(30, 0.102064)
) n (zT, z);
SELECT ROUND(
wct.PriceFromIRLAttice(
'2018-11-14',
'2023-09-10',
0.0676,
0.04869,
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)'
),
4
) as Price;
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"93.0017"}]}
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.
PRICEFROMZEROESTVF - Zero Volatility spread details
ZSPREAD - Calculate the zero-volatility or static spread on a bond.