LogNormalIRLattice
Updated: 30 Nov 2016
Use the SQL Server table-valued function LogNormalIRLattice to generate the details of the Lognormal Interest Rate Lattice used to calculate the price from the option-adjusted spread. LogNormalIRLattice is a way of researching or auditing the calculation of the price. The OAS is entered in decimal format (i.e. 1 basis point = .0001)
Syntax
SELECT * FROM [wct].[LogNormalIRLattice](
<@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
|
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').
|
|
@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
RETURNS TABLE(
[date_step] [datetime] NULL,
[num_step] [int] NULL,
[num_node] [int] NULL,
[rate_fwd] [float] NULL,
[rate_calibrated] [float] NULL,
[T] [float] NULL,
[delta] [float] NULL,
[df] [float] NULL,
[df_calibrated] [float] NULL,
[cczero] [float] NULL,
[PVCF] [float] NULL,
[coupon] [float] NULL,
[price_call] [float] NULL,
[price_put] [float] NULL
)
|
Column
|
Description
|
|
date_step
|
Date of the step
|
|
num_step
|
Step number
|
|
num_node
|
Number of node within the step
|
|
rate_fwd
|
Forward rate calculated for the step
|
|
rate_calibrated
|
Calibrated rate calculated for the node
|
|
T
|
Time (in years)
|
|
delta
|
The change in T from the previous step
|
|
df
|
Discount factor for the node calculated using the calibrated rate plus the spread
|
|
df_calibrated
|
Discount factor for the node calculated from the calibrated rates
|
|
PVCF
|
Present value of the cash flow at the node
|
|
coupon
|
Coupon amount at that step
|
|
price_call
|
Exercise price of the call option at that step
|
|
price_put
|
Exercise price of the put option at that step
|
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:
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
date_step
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM
wct.LogNormalIRLattice(
'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
,'2020-11-23' --@TradeDate
,NULL --@CurveDayCount
,30 --@Notice
,'L' --@CurveInterpMethod
,0.10 --@Vol
,'SELECT ''2017-11-28'',100' --@OptionSched
)
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
date_step
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM
wct.LogNormalIRLattice(
'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
,'2020-11-23' --@TradeDate
,NULL --@CurveDayCount
,30 --@Notice
,'L' --@CurveInterpMethod
,0.10 --@Vol
,'SELECT ''2018-11-28'',100' --@OptionSched
)
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
date_step
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM
wct.LogNormalIRLattice(
'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
,'2020-11-28' --@TradeDate
,NULL --@CurveDayCount
,30 --@Notice
,'L' --@CurveInterpMethod
,0.10 --@Vol
,'SELECT ''2017-11-28'',100, ''P''' --@OptionSched
)
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 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
date_step
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM
wct.LogNormalIRLattice(
@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
)
This produces the following result.
Example #5
In this example we will generate the interest rate lattice 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 generate the the interest rate Lattice 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,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
,k.date_step
,k.num_step
,k.num_node
,k.rate_fwd
,k.rate_calibrated
,k.T
,k.delta
,k.df
,k.df_calibrated
,k.cczero
,k.PVCF
,k.coupon
,k.price_call
,k.price_put
FROM
#bonds b
CROSS APPLY
wct.LogNormalIRLattice(
'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
)k
This produces the following result.
Example #6
In this example we calculate the OAS 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
date_step
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM wct.LogNormalIRLattice (
'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)'
) * 10000, 1) as OAS;
This produces the following result.
Example #7
In this example we calculate the OAS 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
date_step
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM wct.LogNormalIRLattice(
'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)'
) * 10000, 1) as OAS;
This produces the following result.
See Also