PriceFromZeroesTVF
Updated: 30 Nov 2016
Use the SQL Server tablevalued function PriceFromZeroesTVF to generate the details used to calculate the price from the Zspread. PriceFromZeroesTVF is a way of researching or auditing the calculation of the price. The Zspread 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 steppedrate bonds, an SQL statement which returns a result table consisting of the coupon start dates and the associated coupon rates.

@CurveSpread

Zspread

@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 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).

@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

Zspread

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 20231001 with a coupon rate of 5.25% paying interest semiannually. The Zspread 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(
'20161128' @Settlement
,'20231001' @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
('20201001', .0425)
,('20241001', .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(
'20161128' @Settlement
,'20261001' @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','20251103',0.0333, 226.7)
,('B','20230512',0.0447, 252)
,('C','20290717',0.0654, 413.6)
,('D','20220806',0.0673, 264.8)
,('E','20300218',0.0649, 404.1)
,('F','20240817',0.047, 237.4)
,('G','20230407',0.0488, 253.6)
,('H','20260529',0.0584, 227.2)
,('I','20231106',0.0426, 245.1)
,('J','20270420',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(
'20161128' @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