Login     Register

        Contact Us     Search

Menu

XLeratorDB/financial Documentation

SQL Server LogNormal Interest Rate Lattice


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)
·         Available in XLeratorDB / financial 2008 only
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
·         OAC - Option Adjusted Convexity
·         OAD - Option Adjusted Duration
·         OAS - Option Adjusted Spread

 



View Topic History
Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service