Using Bloomberg Tenor Basis Swap Spreads in Excel to calculate 1M USD Libor Forward Rates
Until the 2007-08 financial crisis, forward interest rates of any tenor had been calculated off one single yield curve. In the US, traders had been building one USD yield curve out of market-traded deposits, futures and fixed-to-3M-Libor swaps and used that curve for all purposes involving interest rate calculations, such as extracting discount factors for various maturities or producing the fair rates for various forward Libor and swap contracts.
Note here that academics have been always knowing - at least in principle - that one curve was not enough for deriving diverse interest rate quantities.
The problem was not in the math, but rather in the market, as traders had been setting their bids and offers assuming largely tenor- and collateral-independent credit and liquidity risks. The ensuing differences in the market prices were so small (a fraction of a basis point) that were absorbed by the bid-offer spreads and did not necessitate the usage of several curves.
The financial crisis shattered these assumptions. For example, the traders in the USD Libor markets started to assign a far greater credit and liquidity risk on forward 3-month contracts than on forward 1-month contracts. No more were the former regarded as roll-over sequences of the latter. Lending 1 million USD over a period of 3 months carried a much greater risk than repeating thrice the lending of the same amount over a period of 1 month.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Understanding Tenor Basis Swaps
A Tenor Basis Swap is like a fixed-to-floating swap with the difference that the fixed leg is replaced with a floating leg linked to an index of a different tenor.
In other words, entering into a Tenor Basis Swap means you agree to pay one index, e.g. the 3-month USD Libor, against receiving a different tenor of the same index, e.g. the 1-month USD Libor.
Since, as discussed above, these two legs are not of equal value, a spread is added on one of the legs, the amount of which is chosen so that the two legs reach the exact same value and – accordingly – the swap has a zero NPV.
Typically, the spread is added on the short index and is also used for quotation purposes.
For example, when a trader makes a bid for 12 bps on a 1mv3m 5Y swap, he declares his willingness to enter a 5-year swap, whereby he regularly pays 1-month Libor plus 12 bps spread against receiving 3-month Libor.
Due to the different index tenors, the question arises on how often the short index and spread should be paid.
Several different conventions are possible, the details of which are described at this ISDA paper.
All of them are supported by Deriscope, with the default being that the short index is paid with the same frequency as that of the long index, as the following diagram in the case of a 1mv3m 5Y swap illustrates:
With regard to how the spread is calculated, the default setting corresponds to the ISDA Flat Compounding convention that works as follows:
In the case pictured above, each 3-month coupon accrual period has length t and consists of three consecutive 1-month sub-periods of lengths t₁, t₂ and t3.
At the beginning of the three sub-periods the 1-month Libor is observed and leads to the three fixings r1, r2 and r3.
Assuming a unit notional, the total amount of interest I accruing over t can be written as:
I = Ir + Is
where the part Ir is generated by the rates r1, r2 and r3 and given by
Ir = (1 + r₁t₁)(1 + r₂t₂)(1 + r3t3) - 1 according to the compounding convention
The part Is is generated by the spread s and given by
Is = st (no compounding here)
Using the Deriscope Wizard to build a Tenor Basis Swap Yield Curve in Excel
Deriscope supports the building of yield curves using several different types of input market instruments.
In particular, the function Create of the Deriscope type Yield Curve expects as input the key Market Data=, of which the associated value is an array of objects that supply the market data.
The types of all these objects derive from Yield Curve Input.
My post on yield curve creation out of deposits, futures and swaps shows the typical construction where that array consists of three objects, respectively of type Yield Curve Dep, Yield Curve Fut and Yield Curve Swp.
The market-traded set of Tenor Basis Swap spreads must be supplied as an object of type Yield Curve Tnb as input next to the Market Data= key.
By far the easiest construction is to simply ask the wizard to create a yield curve out of the desired instruments. Then the wizard generates all required objects and formulas.
The following video shows how this is done.
Here are the formulas generated and pasted by the wizard in the spreadsheet:
The final curve has been created in the selected cell A1 with the formula =ds(A2:B5) shown in the formula bar.
The key Market Data= is associated with the value &YldCrvTnb_D1:1.1 in cell B5, which is the handle name of the object of type Yield Curve Tnb created in cell D1.
As mentioned above, the latter object's job is to supply the Tenor Basis Swaps.
The wizard has generated two spreads, both equal to 20 bps, associated with swaps having tenor of 1 and 2 years respectively.
These swaps are of type 1Mv6M, meaning they exchange 1-month Libor versus 6-month Libor.
The wizard has also created the flat yield curve GBPCrv_A7:1.1 in cell A7 in order to be used as input for the required Index Curve in cell E6.
The latter curve is used to produce the forward rates associated with the short index, i.e. the 1-month Libor, due to the entry Curve On Short= TRUE.
This fact implies that the produced curve in cell A1 corresponds to the long index, i.e. the 6-month Libor.
The type Yield Curve Tnb comes with optional keys that are not shown above, which permit a more flexible specification of how the short index and the basis spread are paid.
Below is an alternative specification of the same Yield Curve Tnb object that shows all optional input keys:
Note the pair Regular Period= FALSE that indicates the coupon period associated with the short index is not regular, i.e. the short index is not paid every month at the end of its tenor period.
How often the short index gets paid is defined by the pair Short Accr Prd= %6M, which means the short index is averaged in some sense and paid every 6 months.
The exact type of averaging is further defined by the pair Subperiods Treatment= Compounding, which corresponds to the compounding treatment described above.
Finally, the pair Multiple Spread= FALSE means the spread is not applied multiple times over each one-month sub-period, but rather its interest contribution is calculated through a single calculation over the whole 6-month accrual period.
Building the 1-Month USD Libor Rate Forecasting Curve in Excel using Market Data from Bloomberg
I am now going to create the yield curve that models the forward 1-month USD Libor rates by using actual market data sourced from Bloomberg as of May 22, 2019.
The produced curve may then be used as input in the pricing of any derivative, the payoff of which depends on future fixings of the 1-month USD Libor rate.
As shown at the above image, the following two curves are prerequisite ingredients to the construction of the final curve:
1) The forecasting curve for the 3-month Libor index that must be supplied with the key Index Curve=
2) The discounting curve that must be supplied with the key Disc Curve=.
Both these prerequisite curves are constructed as described in my post about Exogenous Bootstrapping of Discounting and 3M USD Libor Forecasting curves and the respective formulas are found in the sheet Curves as shown below.
The final forecasting 1-month USD Libor curve is produced on the separate sheet USD-1M as shown below:
The rates are taken from Bloomberg as of May 22, 2019.
The curve is constructed by using two different sets of instruments corresponding to the two objects &YldCrvDep_A18:2.1 and &YldCrvTnb_D3:2.1 entered below the key Market Data= in cells C16 and C17.
The first of these objects, the &YldCrvDep_A18:2.1, is created in cell C19 and contains the 1-month deposit rate 2.436%.
The second of these objects, the &YldCrvTnb_D3:2.1, is created in cell F4 and contains the basis spreads of tenor basis swaps with maturities extending from 3 months to 30 years.
I may browse through the cash flows of all market instruments as explained in this post.
In the current case they look as follows:
The top row corresponds to the 1-month deposit instrument with rate 2.436%.
All remaining rows correspond to the tenor basis swaps.
More details about a specific swap can be displayed by clicking on the respective lens sign on the #CashFlows column.
For example, doing so by clicking on the $Set#6 item, I can see the cash flows of the 1-year swap as:
There are 8 rows with each pair corresponding to the received and paid cash flow at the end of each 3-month period.
The left column displays the payment date and we see that the first row corresponds to the 3-month Libor received on Aug 27, 2019 and the second row corresponds to the compounded 1-month Libor paid on that same date.
There are a few unfortunate glitches though, such as the accrual period end dates reported at the #AccrEnd column. Although these dates should not differ between the short and long index, the first two rows display 27/08/2019 and 24/08/2019.
This is a reporting bug that only affects this particular display.
The correct date is 27/08/2019, which applies to both legs and is used in all calculations.
This can be confirmed by inspecting even deeper in the details of the compounded 1-month cash flow by clicking on the respective lens sign of the last column:
Although the accrual end date is still displayed as 24/08/2019, the accrual fractions of the three 1-month sub-periods at the bottom are shown as 0.08611, 0.08333 and 0.09444.
In particular, the last accrual fraction corresponds to the sub-period from 24/07/2019 to 27/08/2019 as it should.
Pricing a fixed vs 1M Libor swap
As an example of how the produced curve may be used in a practical application, I am going now to price a vanilla fixed-to-floating swap, where the floating index is the 1-month USD Libor.
In order to set the swap contract, I follow the same steps as in this post.
Below is the formula that creates the swap as an object of type Vanilla IRS in cell H4:
The detailed cash flows of this swap may be inspected as described here.
Finally, the pricing formula is set with a separate discounting and forecasting curve in the same fashion as in this post and looks as below:
Click on YieldCurveBlbgTenorBasis1m3mUSD.xlsx to download the spreadsheet produced with the above steps.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com
