Bootstrapping in Excel a Yield Curve to perfectly fit Bloomberg Price/Yield Quotes of US Treasury Bills, Notes and Bonds
I have already described the bootstrapping method for building a yield curve from various instrument types in the yield curve articles category. In particular, my post on Yield Curve Building in Excel using Bond Prices (QuantLibXL vs Deriscope) demonstrates how this is done in practice using a simplified theoretical setup consisting of three made-up German government bonds maturing in 2023, 2028 and 2044.
In the current post I want to show you how the bootstrapping can be carried out in a production environment with the help of the Deriscope Excel Add-In, where the aim is an exact fit of all US Treasury market quotes published by Bloomberg as of 6 June 2022. Bond prices and yields will be used alternately as the main input to the bootstrapping process and the corresponding curves will be compared.
From a user's perspective, the real challenge is setting up the various securities so that they truly represent the US Treasuries. To validate the setup's correctness, it is important to calculate all the yields and verify they match those displayed in the Bloomberg screen. As a side product, I will also refresh your knowledge of bond YTM (Yield-To-Maturity) by providing the manual calculation of the yields of a 1-month T-bill (Treasury Bill) and a 2-year T-Note (Treasury Note).
I will be only concerned with the so-called bootstrapping ( or non-parametric) method that leads to a curve that exactly fits the market quotes. This type of curve is favored in trading and relative value desks. The alternative parametric methods – favored by central banks and economists – have been described in earlier posts, such as the Parametric Yield Curve Fitting to Bond Prices: The Nelson-Siegel-Svensson method and the Parametric Yield Curve Fitting to Bond Prices under constraints: The National Bank of Georgia case.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
The Market Information
The following image corresponds to the Bloomberg screen as of 6 June 2022.
The top five rows with tenors less than two years concern the Treasury Bills, which pay no coupon and are therefore effectively zero bonds. Although their tenors appear as month multiples, they are actually week multiples and equal 4 weeks, 8 weeks, 13 weeks, 26 weeks, and 52 weeks respectively.
The rows with tenors from 2Y to 10Y correspond to the Treasury Notes and the bottom two rows with tenors 20Y and 30Y to the Treasury Bonds. Apart from the name difference, notes and bonds carry the same fundamental structure paying a fixed semiannual coupon.
The Price column contains the mid quotes for the respective securities, which are the mid discount yields for the T-bills and the mid clean prices for the T-notes and T-bonds.
Constructing the 1m Treasury Bill and calculating its Price and Yield-To-Maturity
Shown below is the formula =ds(C5:D17) in cell C4 that creates a Deriscope Object of which the associated Deriscope Type is Zero Bond identified by the returned handle name &Bill1m.1.
The important entry is the date 05-Jul-2022 in cell D14, which is the value associated with the key named Maturity=.
The value in cell D13 for the key Issue Date= is left blank since it has no impact on either the price or yield of the bond.
My next step will be to convert the quoted discount yield of 0.851% to the corresponding purchase price, which in a zero bond can be considered equal to a presumed notion of clean or dirty price that emerges if one views a zero bond as a coupon bond paying a coupon rate of 0% with whatever frequency.
The market convention that defines the quoted discount yield is as follows:
Below you see the part of the sheet that computes the bill's purchase price as 99.9338111.
The Yield-To-Maturity (y) is defined through the following formula:
P(1 + yt) = 100
where P is the bill's purchase price and t is the time from settlement to maturity in annual units calculated according to the ISMA and US Treasury convention, also known as Actual/Actual Bond and Actual/Actual ISMA.
The reason behind adopting this daycount convention is because one prefers that it is the same as the convention used in calculating the yield of Treasury notes and bonds. The latter are bonds paying a coupon calculated using the mentioned daycount and therefore its yield is conventionally based on that same daycount as well.
Also note the formula for y looks so simple because the bill's maturity does not exceed 6 months. The Yield-To-Maturity of the 1-year T-bill is defined differently based on the assumption that it is semiannually compounded.
Deriscope supports a compounding convention named Simple Then Compounded, which can be conveniently used to process all T-bills regardless of their tenor.
Below is the whole sheet at its final state where the formula =ds(H9:I15) in cell H8 returns a yield of 0.863%, which is only 0.06 bps less than the Bloomberg's 0.864%, the difference being due probably to rounding.
The entry Nominal CF Dates= TRUE in row 24 is less critical here as it is in the T-note and T-bond case, where it will be explained in detail.
The yield can be also calculated manually by solving the above formula for y:
y = (100/P – 1) / t
The only challenging part is the computation of t using the Actual/Actual ISMAdaycount convention, the rules of which are described at the link.
The image below shows the computation by means of the Deriscope function Time Length:
The tricky part of the input is the date associated with the key Ref start= in row 9. This daycount convention requires a reference period that normally equals the coupon period of the bond, but since the T-bill has no coupon period, the convention seems to be to use an assumed coupon period of one year, which is effected here by setting the Ref start to 05-Jul-2021.
The rest of the calculation is straightforward and shown below:
Constructing all Treasury Bills and calculating their Prices and Yields-To-Maturity
Having finished with the construction of the 1m T-bill, it is easy to produce all the remaining T-bills by cloning the 1m T-bill and modifying only the maturity.
Below you see the formula =ds("Clone",$E$6,"Maturity=",D7,"Handle=","Bill"&C7) in cell E7 that creates a T-bill object named &Bill2m.1 by cloning the previously created &Bill1m.1.
The formula is then pasted down to create all T-bills.
Filling up the rest of the table is straightforward, with the result looking as below:
There is clearly a very good agreement between the yields calculated by Deriscope in column J and those displayed in Bloomberg in column K (and also shown at the image appended below at row 12). All differences are likely due to rounding and less than 0.1 bp.
Constructing the 2y Treasury Note and calculating its Price and Yield-To-Maturity
Shown below is the formula =ds(C5:D19) in cell C4 that creates an object of Deriscope type Fxd Rate Bond identified by the returned handle name &Note2y.1.
The three most important entries that will vary among the different T-notes correspond to the keys Rate(%)=, Start Date= and End Date= in rows 15, 26 and 28 respectively.
The coupon is paid semiannually due to the entry Step= %6M in row 29.
It accrues according to the conventions defined in the Int Rate Spec object at the bottom.
The T-note's expected cash flows can be returned by the appropriate Deriscope function, as shown below:
The yield can be calculated by the same formula as with the T-bill case.
It is the formula =ds(H9:I15) shown below in cell H8 that returns 2.716% that fully agrees with the Bloomberg value. Note the clean price of 99.5859375 in cell I11 is coming from the Bloomberg quote 99-18¾, which is a symbolic representation of the sum 99+(18+¾)/32.
The entry Nominal CF Dates= TRUE in row 24 is important. It means the yield should be calculated on the assumption that all cash flows occur on the precise dates when the corresponding accrual intervals end, even if the latter happen to fall on a non-business day. This should be seen as a convention that is part of the yield's definition, even if it has the side effect of making the thus calculated yield less representative of what the actual cash flows "yield". Due to the payment dates coinciding with the accrual end dates in the table further above, this particular 2-year note is not affected by this convention. But some of the longer term notes are affected.
To ensure there is no misunderstanding about how exactly the yield is defined with the semiannual compounding convention, the following table contains the reverse calculation of the bond's clean price out of any assumed initial yield.
As shown below, if one starts with the yield 2.716% in cell AH12, the formulas in the table produce the resulting implied clean price of 99.5859375. This verifies that 2.716% is the correct yield.
Constructing all Treasury Notes and Bonds and calculating their Prices and Yields-To-Maturity
As done before with the 1m T-bill, the Deriscope Clone function may be used to easily create all remaining T-notes and T-bonds.
Without going over the details this time, below is the final table:
As mentioned above, a Bloomberg quote such as 99-18¾, is a symbolic representation of the sum 99+(18+¾)/32. Also, a Bloomberg quote such as 98-07+, is a symbolic representation of the sum 98+(7+½)/32.
The Deriscope-implied yields are practically the same with those displayed in Bloomberg, with any differences being less than 0.04 bps and likely due to rounding.
Building the Yield Curve implied by the Bloomberg quoted Clean Prices
The bootstrapping is done by the formula =ds(C5:D15) in cell C4.
The market prices of the US Treasuries are passed to this formula indirectly as part of the object &Prices.1 in cell D12.
The returned text &CrvFromPrices.1 is the handle name of a produced object of Deriscope type Yield Curve that contains the bootstrapped zero yield interpolated with a cubic spline method, as decided by the entries of the keys Modelled Qty= and Interp Method= in rows 13 and 14 respectively.
A quick viewing of the continuously compounded zero rates implied by this curve is afforded by the Deriscope function Implied Values, evoked through the formula in cell H4 as shown below:
The produced object &ImpliedValsA.1 contains the requested implied discount factors, zero rates and forward rates. They can be displayed on the spreadsheet with the help of the Deriscope function Show, evoked through the formula in cell K4 as shown below:
Here is the chart produced with the implied spot zero rates of column M:
Building the Yield Curve implied by the Bloomberg quoted Yields
The bootstrapping is done by the formula =ds(C5:D15) in cell C4.
The market yields of the US Treasuries are passed to this formula indirectly as part of the object &Yields.1 in cell D12.
The returned text &CrvFromYields.1 is now the Yield Curve object bootstrapped directly from the shown input yields, of which the conventions are defined in the supplied object &YieldConv.1 in row 22, which is constructed as below:
It is quite important here to use Compounding= Simple Then Compounding and not just Compounding because the shown conventions apply to all Treasury securities, including the T-Bills with maturity of less than one year where the yield is defined as Simple. Due to this choice, the value associated with the key Stepwise Disc= must be FALSE to ensure that the Compounding rather than the Simple convention applies during the discounting of the T-Note and T-bond cash flows.
It turns out that this curve implies the almost exact same continuously compounded zero rates as the previous curve. Below is the generated chart:
Click on YieldCurveBndBlbgUS.xlsx to download the spreadsheet produced with the above steps.
At https://www.deriscope.com you can find more information about Deriscope and download the Excel Add-In required to run some of the formulas in the spreadsheet.
