BondPriceFromZeroes
Updated 2023-10-12 15:37:20
Syntax
SELECT [westclintech].[wct].[BondPricefromZeroes](
<@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),>)
Description
Use the scalar function BondPriceFromZeroes to calculate the (clean) price from the z-spread of a bond based on the supplied curve. The Z-spread is entered in decimal format (i.e. 1 basis point = .0001)
Arguments
@Redemption
Redemption value of the bond.
@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.
@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).
@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.
@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.
@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.
@InterpMethod
The interpolation method to calculate the rate associated with the coupon dates; use 'L' for linear interpolation and 'S' for cubic spline interpolation.
@Frequency
Coupon frequency of the bond; the number of times that the coupon interest is paid per year.
@CurveStartDate
The start date for the curve; used to calculate the time-in-years associated with the coupon dates.
@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').
@Maturity
Maturity date of the bond.
@CurveSpread
Z-spread
@Basis
Interest basis code for the bond; the day-count convention used in the calculation of the accrued interest.
@Settlement
Settlement date of the bond.
Return Type
float
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 calculate the price 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 wct.BondPricefromZeroes( '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
) as [Price];
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"103.502027365186"}]}
Example #2
In this example we use the same curve as from the previous example, but we are going to calculate the price 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 wct.BondPricefromZeroes('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
) [Price];
This produces the following result.
{"columns":[{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Price":"103.501622687138"}]}
Example #3
In this example we will calculate the price 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 calculate the the price 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,
ROUND(wct.BondPricefromZeroes( '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
),
3
) as [Price]
FROM #bonds b;
This produces the following result.
{"columns":[{"field":"id_bond"},{"field":"Price","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"id_bond":"A","Price":"95.01"},{"id_bond":"B","Price":"102.649"},{"id_bond":"C","Price":"104.995"},{"id_bond":"D","Price":"113.757"},{"id_bond":"E","Price":"105.369"},{"id_bond":"F","Price":"104.604"},{"id_bond":"G","Price":"104.895"},{"id_bond":"H","Price":"114.426"},{"id_bond":"I","Price":"101.56"},{"id_bond":"J","Price":"104.505"}]}
See Also
CMTCURVE - Constant Maturity Treasury curve
LOGNORMALIRLATTICE - LogNormal Interest Rate Lattice
OAC - Option Adjusted Convexity
OAD - Calculate the option-adjusted duration on a bond.
PRICEFROMIRLATTICE - Bond Pricing using Option Adjusted Spread
PRICEFROMZEROESTVF - Zero Volatility spread details
ZSPREAD - Calculate the zero-volatility or static spread on a bond.