SQL Server PriceFromIRLattice Function
Updated 2023-10-12 20:43:28.133000
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)
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),>)
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.
@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').
@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 @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.
| 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 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.
| 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.
| 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.
| 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.
| 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 @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.
| 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.
| ?id_bon | Pric |
|---|---|
| A | 95.009 |
| B | 102.647 |
| C | 104.998 |
| D | 113.761 |
| E | 105.374 |
| F | 104.603 |
| G | 104.893 |
| H | 114.428 |
| I | 101.562 |
| J | 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.
| 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.
| 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.