LogNormalIRLattice
Updated: 30 Nov 2016
Use the SQL Server tablevalued function LogNormalIRLattice to generate the details of the Lognormal Interest Rate Lattice used to calculate the price from the optionadjusted 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),>
,<@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 steppedrate bonds, an SQL statement which returns a result table consisting of the coupon start dates and the associated coupon rates.

@Spread

The Optionadjusted 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 daycount 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 timeinyears associated with the coupon dates.

@CurveDayCount

The daycount convention used in calculating the timeinyears 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').

Return Type
RETURNS TABLE(
[date_pmt] [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_pmt

Date of the cash flow

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 @CurveFrequency is NULL then @CurveFrequency = 2
Â· If @CurveInterpMethod is NULL then @CurveInterpMethod = 'L'
Â· If @Vol is NULL then @Vol = 0
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 Optionadjusted 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_pmt
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM
wct.LogNormalIRLattice(
'20161128' @Settlement
,'20201128' @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 ''20171128'',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 4016, p. 873 in Fabozzi) we want to calculate the price for a stepup 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
('20161128', .0425)
,('20181128', .0750)
)n(date_step, rate_step)
The bond to be evaluated
SELECT
date_pmt
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM
wct.LogNormalIRLattice(
'20161128' @Settlement
,'20201128' @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 ''20181128'',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_pmt
,num_step
,num_node
,rate_fwd
,rate_calibrated
,T
,delta
,df
,df_calibrated
,cczero
,PVCF
,coupon
,price_call
,price_put
FROM
wct.LogNormalIRLattice(
'20161128' @Settlement
,'20201128' @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 ''20171128'',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 semiannually. We use the CMTURVE function to convert the par rates into continuously compounded zeroes.
The bond matures on 20260315, has a coupon rate of 7.0% paid semiannually and a spread of 605.9. The bond is callable based on the following schedule.
exdate

strike

20170315

103.50

20180315

103.00

20190315

102.50

20200315

102.00

20210315

101.50

20220315

101.00

20230315

100.50

20240315

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('20161128' as datetime)
DECLARE @StartDate as datetime = CAST('20161128' 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('20260315' as date)
DECLARE @Spread as float = 605.9 / 10000
DECLARE @dcc as varchar(2) = '1'
DECLARE @typeCurve as char(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 as datetime) as exdate, strike
INTO
#calls
FROM (VALUES
('20170315',103.50)
,('20180315',103.00)
,('20190315',102.50)
,('20200315',102.00)
,('20210315',101.50)
,('20220315',101.00)
,('20230315',100.50)
,('20240315',100.00)
)n(exdate,strike)
SELECT
date_pmt
,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
,@Settlement @CurveStartDate
,@dcc @CurveDayCount
,NULL @CurveFrequency
,@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 lattive 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','20251103',0.0333, 219)
,('B','20230512',0.0447, 228.5)
,('C','20290717',0.0654, 329.4)
,('D','20220806',0.0673, 161.3)
,('E','20300218',0.0649, 329.9)
,('F','20240817',0.047, 210.8)
,('G','20230407',0.0488, 220.3)
,('H','20260529',0.0584, 94.8)
,('I','20231106',0.0426, 226.8)
,('J','20270420',0.0572, 172.2)
)n(id_bond,maturity,rate,price)
Create the call schedules for the bonds
SELECT
*
INTO
#calls
FROM (VALUES
('A','20191103',104)
,('A','20211103',102)
,('A','20231103',100)
,('B','20210512',100)
,('C','20190717',104)
,('C','20210717',103)
,('C','20230717',102)
,('C','20250717',101)
,('C','20270717',100)
,('D','20200806',100)
,('E','20200218',104.5)
,('E','20220218',103.5)
,('E','20240218',102.5)
,('E','20260218',101.5)
,('E','20280218',100)
,('F','20220817',100)
,('G','20210407',100)
,('H','20200529',104)
,('H','20220529',102)
,('H','20240529',100)
,('I','20211106',100)
,('J','20170420',103.5)
,('J','20180420',103)
,('J','20190420',102.5)
,('J','20200420',102)
,('J','20210420',101.5)
,('J','20220420',101)
,('J','20230420',100.5)
,('J','20250420',100)
)n(id_bond,exdate,strike)
SELECT
b.id_bond
,k.date_pmt
,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(
'20161128' @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
,NULL @CurveStartDate
,NULL @CurveDayCount
,NULL @CurveFrequency
,'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.