PriceFromZeroesTVF
Updated: 30 Nov 2016
Use the SQL Server table-valued function PriceFromZeroesTVF to generate the details used to calculate the price from the Z-spread. PriceFromZeroesTVF is a way of researching or auditing the calculation of the price. The Z-spread is entered in decimal format (i.e. 1 basis point = .0001)
Syntax
SELECT * FROM [wct].[PriceFromZeroesTVF](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Rate, sql_variant,>
,<@CurveSpread, float,>
,<@Redemption, float,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>
,<@LastCouponDate, datetime,>
,<@FirstCouponDate, datetime,>
,<@IssueDate, datetime,>
,<@CCZero, nvarchar(max),>
,<@CurveType, nvarchar(4000),>
,<@CurveStartDate, datetime,>
,<@CurveDayCount, nvarchar(4000),>
,<@CurveFrequency, int,>
,<@InterpMethod, nvarchar(4000),>)
Arguments
|
Input Name
|
Description
|
|
@Settlement
|
Settlement date of the bond.
|
|
@Maturity
|
Maturity date of the bond.
|
|
@Rate
|
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.
|
|
@CurveSpread
|
Z-spread
|
|
@Redemption
|
Redemption value of the bond.
|
|
@Frequency
|
Coupon frequency of the bond; the number of times that the coupon interest is paid per year.
|
|
@Basis
|
Interest basis code for the bond; 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').
|
|
@CurveStartDate
|
The start date for the curve; used to calculate the time-in-years associated with the coupon dates.
|
|
@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.
|
|
@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).
|
|
@InterpMethod
|
The interpolation method to calculate the rate associated with the coupon dates; use 'L' for linear interpolation and 'S' for cubic spline interpolation.
|
Return Type
RETURNS TABLE (
[date_pmt] [datetime] NULL,
[T] [float] NULL,
[delta] [float] NULL,
[cczero] [float] NULL,
[spot] [float] NULL,
[pvf] [float] NULL,
[fwd] [float] NULL,
[spread] [float] NULL,
[df] [float] NULL,
[cf] [float] NULL,
[dcf] [float] NULL
)
|
Column
|
Description
|
|
date_pmt
|
Cash flow date
|
|
T
|
Time (in years)
|
|
delta
|
Change in T from the previous coupon
|
|
cczero
|
Continuously compounded zero coupon rate
|
|
spot
|
Spot rate; @Freq*(POWER(1/df,1/(@Freq*T))-1
|
|
pvf
|
Discount factor from cczero; EXP(-cczero*T)
|
|
fwd
|
Forward rate
|
|
spread
|
Z-spread
|
|
df
|
Discount factor for the forward period; 1/(1+(fwd + spread) * delta)
|
|
cf
|
Cash flow amount from the bond
|
|
dcf
|
Discounted cash flow value of all cash flows greater than or equal to date_pmt
|
Remarks
· If @Settlement is NULL then @Settlement = GETDATE()
· If @Maturity is NULL then @Maturity = GETDATE()
· If @Rate is NULL then @rate = 0
· If @CurveSpread is NULL then @CurveSpread = 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 @CurveStartDate is NULL then @CurveStartDate = @Settlement
· If @CurveDayCount is NULL then @CurveDayCount = 1
· If @CurveFrequency is NULL then @CurveFrequency = 2
· If @CurveInterpMethod is NULL then @CurveInterpMethod = 'S'
Examples
Example #1
In this example we put the continuously compounded zeroes into the #ccz temp table and then generate the table for a bond maturing on 2023-10-01 with a coupon rate of 5.25% paying interest semi-annually. The Z-spread is 327.6 basis points
SELECT
*
INTO
#ccz
FROM (VALUES
(0.011,0.0031936941106789)
,(0.25,0.00319251640948496)
,(0.50,0.0042807233899723)
,(1,0.00544437632630534)
,(2,0.00727274510130153)
,(3,0.00859818036980457)
,(4,0.0101034030456584)
,(5,0.0116083325279126)
,(7,0.0144258819802952)
,(10,0.0163078262966277)
,(20,0.0203301487469184)
,(30,0.0250383019093584)
)n(T,z)
SELECT
date_pmt
,T
,delta
,cczero
,spot
,pvf
,fwd
,spread
,df
,cf
,dcf
FROM wct.PriceFromZeroesTVF(
'2016-11-28' --@Settlement
,'2023-10-01' --@Maturity
,0.0525 --@Rate
,.03276 --@CurveSpread
,100 --@Redemption
,2 --@Frequency
,0 --@Basis
,NULL --@LastCouponDate
,NULL --@FirstCouponDate
,NULL --@IssueDate
,'SELECT * FROM #ccz' --@ZeroRate
,NULL --@CurveType
,NULL --@CurveStartDate
,NULL --@CurveDayCount
,NULL --@CurveFrequency
,NULL --@InterpMethod
)
This produces the following result.
Example #2
In this example we use the same curve as from the previous example, but we are going to generate the table for a stepped rate bond.
--Put the step information into the #step table
SELECT
date_step
,rate_step
INTO
#step
FROM (VALUES
('2020-10-01', .0425)
,('2024-10-01', .0625)
)n(date_step, rate_step)
--The bond to be evaluated
SELECT
date_pmt
,T
,delta
,cczero
,spot
,pvf
,fwd
,spread
,df
,cf
,dcf
FROM
wct.PriceFromZeroesTVF(
'2016-11-28' --@Settlement
,'2026-10-01' --@Maturity
,'SELECT date_step, rate_step FROM #step' --@Rate
,.00836 --@Spread
,100 --@Redemption
,2 --@Frequency
,0 --@Basis
,NULL --@LastCouponDate
,NULL --@FirstCouponDate
,NULL --@IssueDate
,'SELECT * FROM #ccz' --@ZeroRate
,NULL --@CurveType
,NULL --@CurveStartDate
,NULL --@CurveDayCount
,NULL --@CurveFrequency
,NULL --@InterpMethod
)
This produces the following result.
Example #3
In this example we will generate a table 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 then generate the table 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, 226.7)
,('B','2023-05-12',0.0447, 252)
,('C','2029-07-17',0.0654, 413.6)
,('D','2022-08-06',0.0673, 264.8)
,('E','2030-02-18',0.0649, 404.1)
,('F','2024-08-17',0.047, 237.4)
,('G','2023-04-07',0.0488, 253.6)
,('H','2026-05-29',0.0584, 227.2)
,('I','2023-11-06',0.0426, 245.1)
,('J','2027-04-20',0.0572, 338.9)
)n(id_bond,maturity,rate,spread)
SELECT
b.id_bond
,k.date_pmt
,k.T
,k.delta
,k.cczero
,k.spot
,k.pvf
,k.fwd
,k.spread
,k.df
,k.cf
,k.dcf
FROM
#bonds b
CROSS APPLY
wct.PriceFromZeroesTVF(
'2016-11-28' --@Settlement
,b.maturity --@Maturity
,b.rate --@Rate
,b.spread / 10000 --@Spread
,NULL --@Redemption
,2 --@Frequency
,0 --@Basis
,NULL --@LastCouponDate
,NULL --@FirstCouponDate
,NULL --@IssueDate
,'SELECT * FROM #z' --@ZeroRate
,NULL --@CurveType
,NULL --@CurveStartDate
,NULL --@CurveDayCount
,NULL --@CurveFrequency
,NULL --@InterpMethod
)k
ORDER BY
1,2
This produces the following result.
See Also