# 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.

### Table Of Contents

Understanding Tenor Basis Swaps

Using the Deriscope Wizard to build a Tenor Basis Swap Yield Curve in Excel

Building the 1-Month USD Libor Rate Forecasting Curve in Excel using Market Data from Bloomberg

Recommended for Deriscope starters: The Overview and Quick Guide pages.

Get a free Deriscope activation code that enables you to run the accompanied spreadsheet!

### 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 **t _{3}**.

At the beginning of the three sub-periods the **1-month Libor** is observed and leads to the three fixings **r _{1}, r_{2}** and

**r**.

_{3}Assuming a unit notional, the total amount of interest **I** accruing over **t** can be written as:

**I = I _{r} + I_{s}**

where the part **I _{r}** is generated by the rates

**r**and

_{1}, r_{2}**r**and given by

_{3}**I _{r} = (1 + r₁t₁)(1 + r₂t₂)(1 + r_{3}t_{3}) - 1** according to the compounding convention

The part **I _{s}** is generated by the spread

**s**and given by

**I _{s}**

**= 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