# OIS Discounted USD Libor Curve Production in Excel for Front Office Trading

I have already explained in my earlier yield curve building post how one can use the Excel Deriscope addin to derive the discount factors and zero rates implied by any given market rates of deposits, futures and swaps.

At another post about dual bootstrapping, I calculated in Excel the price of an actual **50-year EUR** *collateralized swap* held by one of my clients and compared it against Bloomberg. The initial NPV discrepancy of **5 bps **went down to only **0.5 bps** when exogenous OIS discounting was taken into account.

In the current post I will not attempt to price any instrument but rather focus on the quality of the curve calibration. The target rate will be the forward **3-month USD Libor** rate that will be forecasted using actual market rates from Bloomberg.

Concretely:

My input data consist of a **3-month** spot *deposit* rate, **6** contiguous *futures* prices and *swap* rates ranging from **2** to **50 years**, all recorded by Bloomberg as of **22 May 2019**.

Then I use these data to build a yield curve that implies discount factors that deviate from those implied by Bloomberg by up to **0.30 bps**.

Then I rebuild the curve using an exogenous *OIS* curve generated out of Bloomberg *OIS* and *FedFunds*/*Libor* *Basis* spreads.

The latter curve implies discount factors in the swaps' maturities region with a discrepancy against Bloomberg that does not exceed **0.03 bps**!

This extremely good fit bestows confidence to using the Deriscope curve not only for risk management, but also for trading and even market making. I will explain in later posts how a trader may use the produced curves to calculate the price and DV01 of actual deals.

### Step 1: Collect in Excel the USD Libor Market Data from Bloomberg

Below is a Bloomberg screen with USD deposit, futures and swap rates with the trade date of **06 May 2019** shown at the top right.

In fact, I will be using below the same screen but with market rates as of **22 May 2019** in order to facilitate cross reference among other posts where the latter rates are already used.

The three columns at the *Cash Rates* section on the left side display the bid and ask rates for spot deposit contracts with maturities ranging from **overnight** to **12 months**.

Since I am interested primarily in swap pricing, I will be using only the highlighted **3-month** rate (the average of the shown bid and ask rates).

The next four columns at the *Contiguous Futures* section display the price, convexity adjustment and implied rate of futures contracts on the **3-month USD Libor** (shown as *3M IMM Eurodollar*) with consecutive expiries falling on the third Wednesday of the shown months.

Here I will use only the six top highlighted contracts.

Finally. I will use all the swap rates shown on the right, except of the first swap that matures in one year.

The screen displays only the first **11** maturities, but the scrollable section contains swap maturities up to **50** **years**.

The curve at the bottom is the *zero rate* implied by Bloomberg as a function of maturity.

In fact, I will need the more detailed screen shown below that contains both the zero rates and discount factors for selected maturity dates:

Note all rates are shown with a precision of **5 decimals** and all discount factors with a precision of **6 decimals**, but the full numerical precision is maintained within Bloomberg.

Below is what I get in my Excel spreadsheet after I export the above data and merge the two tables together. As mentioned, the numbers are slightly different because they are sourced from screens captured a few days later on **22 May 2019**:

### Step 2: Produce the USD Libor Yield Curve without Exogenous OIS Discounting using the Deriscope Bootstrapper

If you don't know how to use the Deriscope wizard to generate spreadsheet formulas that create an object of type *Yield Curve* out of *deposits*, *futures* and *swaps*, you are advised to have a look at my yield curve building article.

The wizard creates the required set of formulas and fills the cells with default market rates.

My job is to replace these default rates with the actual data shown above.

After shifting the various blocks around, the final result looks like below:

The *Yield Curve* object is created in **cell B7** and appears as the text **&USD-3M.1** because of the setting **Handle= USD-3M** three rows below.

The yellow-highlighted sections contain the Bloomberg rates.

Note I have chosen **Interp Method= Nat Cubic** (meaning *Natural Cubic*) in **cell C15** as this seems to give me results closer to those from Bloomberg.

Also note the **DayCount= %ACT/360** settings in **cells F12** and **F23** for *deposits* and *futures* respectively, while **DayCount= %30/360** in **cell K10** for swaps.

The deposit fixing of **%2B{GB_LSX|F}** in **cell F11** means the spot **3-month** deposit contract settles with a two business days (**2B**) delay, whereby a "business day" is defined according to the **GB_LSX **calendar and the date shifting follows the *Following* (**F**) convention.

All *futures* expiries fall on the *International Money Market *dates, as specified by the **Dates= IMM** entry in **cell F24**.

All swap contracts have a *semi-annual* fixed leg according to the entry **Fxd Ref** **Period= %6M** in **cell K9**.

Their floating leg references the index **%USDLibor|3M** entered in **cell K11**. It obviously refers to the 3-month (**3M**) USD Libor rate.

Finally, note that the **cell K13** next to the key **Disc Curve=** is empty.

This is very important and means that the curve is constructed without taking any exogeneous OIS curve into account. Later I will improve the curve construction by setting in that cell a link to a previously constructed OIS curve.

### Step 3: Produce the Implied Forecasting Discount Factors and Zero Rates and Compare them against Bloomberg

Given the *Yield Curve* object **&USD-3M.1** in **cell B7**, I can easily calculate the *discount factor* for any maturity date by using the Deriscope function *DF*.

But Bloomberg assumes a spot settlement of **two business days**, which means I must calculate the forward discount factor from **24 May 2019** to the given maturity date.

*Forward discount factors* can be calculated by the Deriscope function *ForwardDF*.

I also include the corresponding *continuously compounded zero rates*, as they are trivially calculated from the corresponding *discount factors*.

Below is the table with the final numbers:

The first yellow-highlighted column contains the differences between the Deriscope and Bloomberg discount factors.

Starting with the **15-year** maturity of **24/05/2034**, the DF discrepancy begins to affect the **fourth** **decimal** **digit**.

The last column contains the differences between the Deriscope and Bloomberg *continuously compounded zero rates* in *basis point* units, i.e. the rate differences multiplied by **10,000**.

While the discrepancy is in general less than **0.20 bps** (except the last maturity where it jumps to **0.35 bps**), we will soon see that it can be substantially improved by incorporating the OIS discounting, as we should, due to the collateralized nature of the market swap instruments used in the curve calibration.

### Step 4: Collect in Excel the USD OIS Market Data from Bloomberg

Below is a Bloomberg screen with USD OIS rates with the trade date of **06 May 2019** shown at the top right.

As I did with the Libor data, I will be using below the same screen but with market rates as of **22 May 2019**.

The screen displays only the first maturities up to **12 months**, but the scrollable section contains swap maturities up to **50** **years**.

Same as before, I will also need the more detailed screen shown below that contains both the *zero rates* and *discount factors* for selected maturity dates:

Below are the data exported in my Excel spreadsheet:

The top row refers to the overnight deposit rate. Its maturity date is shown as **28 May 2019** because the trade date of **22 May (Wednesday)** implies the settlement date of **24 May (Friday)** due to the **two business days** settlement period. This means the corresponding deposit contract starts on **24 May** and ends **one business day** later, i.e. on **28 May (Tuesday)** due to **27 May 2019** being a bank holiday (Memorial Day) in the US.

The remaining rows, up to and including the **5-year** maturity, contain the observed market rates of the traded OIS swaps.

My article on *Overnight Index Swaps* describes the mechanism and conventions involved in this type of contract.

In summary, an *Overnight Index Swap* here involves the annual exchange of a fixed amount against a floating amount.

The fixed amount is calculated by multiplying the quoted OIS rate shown above with the length of the corresponding accrual period.

The floating amount is calculated in a similar fashion, but with the fixed rate replaced with the compounded overnight index, i.e. the geometric average of the daily published Fed Funds indices during the accrual period.

The most interesting section of the shown OIS rates is the one containing maturities of **7 years** or above.

These rates are not directly observed in the market because no such OIS contracts are traded.

The **Rate Type** column shows the label **Synthetic**, which means, the shown OIS rates are mathematically synthesized from the observed rates of market-traded *Libor swaps* and *Fed Funds Libor Basis swaps*. This is the standard practice for constructing an OIS curve that extends to long maturities.

A *Fed Funds Libor Basis swap* involves the **quarterly** exchange of USD **3-month** Libor against the *arithmetic average* of the Fed Funds index over the respective **3-month** accrual period.

Given a *Libor swap *quote rate **L** and a *Fed Funds Libor Basis swap* spread quote **S**, Bloomberg calculates the inferred equivalent OIS rate using the formula:

The corresponding Excel formula for the **7-year** maturity case in copy/paste format is:

=4 * (((1.0 + ((1.0 + ((((1.0 + USSWAP7/100*(360.0/365.0)/2.0)^(2.0/4.0) - 1.0) * 4.0)-USBG7/10000)/4.0)^4.0 - 1.0)/360.0)^90.0 - 1.0)

Here **USSWAP7** and **USBG7** are the Bloomberg tickers for the **7-year** *USD Libor swap* and the **7-year** *Fed Funds Libor Basis swap* respectively. They contain the respective quoted rate/spread in % units, for example **USSWAP7 = 1.8** if the USD *Libor swap* rate is **1.8%**.

The derivation of the above formula is straightforward, albeit a bit cumbersome, and is based on the idea that the *Fed Funds Libor Basis swap* can be viewed as being equivalent to a package of two swaps: A *Libor swap* and an OIS.

This is indeed possible by constructing the two component swaps carefully so that the fixed leg of the *Libor swap* cancels out the fixed leg of the OIS, while the remaining floating legs exactly match the floating legs of the original *Basis swap*.

The reason our formula looks so complicated lies with the difference among the various swaps with regard to the payment frequency and index conventions.

For example, while the *Basis swap* spread quote refers to a **quarterly** payment frequency, the *Libor swap* rate quote refers to a **semiannual** payment frequency.

Also, while the *Basis swap* floating index equals the **arithmetic** **average** of the daily published Fed Funds quotes, the OIS floating index equals the **geometric average** of the same quotes. This explains the expression with the exponent of **90**, which simply calculates the total compounding factor over **90 days** out of the daily compounding factor (the base quantity inside the parentheses).

Below is the chart of these Bloomberg market and market-implied OIS rates:

### Step 5: Produce the USD OIS Discounting Yield Curve using the Deriscope Bootstrapper

If you don't know how to use the Deriscope wizard to generate spreadsheet formulas that create an object of type *Yield Curve* out of OIS rates, you may want to visit my article on OIS yield curves.

The wizard creates the required set of formulas and fills the cells with default market rates. As before, I must replace these default rates with the actual data shown above.

After shifting the various blocks around, the final result looks like below:

The *Yield Curve* object is created in **cell B7** and appears as the text **&USD-OIS.1** because of the setting **Handle= USD-OIS** three rows below.

The yellow-highlighted sections contain the Bloomberg rates.

All OIS contracts have an *annual* fixed leg according to the entry **Pmt Freq= Annual** in **cell F13**.

Their floating leg references the index **%FedFunds** entered in **cell F10**.

The setting **Tel Days=** **TRUE** (Telescopic Days) provides efficiency by having all daily index values implied from a calculated front and back stub. You would want to set it to **FALSE** only if you needed to view all projected daily index values for diagnostic purposes.

### Step 6: Produce the Implied Discount Factors and Zero Rates and Compare them against Bloomberg

As I did with the Libor curve earlier, I may now use the *Yield Curve* object **&USD-OIS.1** in **cell B7** to calculate the corresponding *discount factors* and *zero rates* so that I can compare them against Bloomberg.

I also include the corresponding *continuously compounded zero rates*, as they are trivially calculated from the corresponding *discount factors*.

Below is the table with the final numbers:

The comparison results are excellent.

Most of *discount factors* agree up to and including the **sixth** **decimal** **digit**.

The last column contains the differences between the Deriscope and Bloomberg *continuously compounded zero rates* in *basis point* units.

With minor exceptions, the zero rates agree within **0.01 bps**.

### Step 7: Produce the Forecasting Implied Discount Factors and Zero Rates with Exogenous OIS Discounting and Compare them against Bloomberg

Now I can go back to where I created my Libor curve and set **Disc Curve= &USD-OIS.1** in **cell K13** as shown:

This results to a new comparison table as below:

The improvement versus the earlier treatment without OIS Discounting is obvious.

Now the Deriscope-implied *zero rates* match those from Bloomberg even within 0.01 bps!

The next table shows the Deriscope-Bloomberg *zero rate* discrepancies between the two methods side by side:

### Check Calibration Fit by Pricing a Market Libor Swap

If the Deriscope curve has been built correctly, it should imply a zero price for any USD *Libor swap* that is part of the set of market instruments used in the curve construction.

You may review the pricing of any *interest rate swap *through Deriscope in my post about *non-standard interest rate swaps*.

Below is the part of my spreadsheet that is devoted in the pricing of the **5-year** *swap*:

The swap NPV in **cell H5** equals exactly **0**.

Note the two different curves **&USD-OIS.1** and **&USD-3M.1** are supplied as market input in **cells I10** and **J10**.

But how does Deriscope know which of these two curves should be used for forecasting, i.e. for generating the forward Libor rates used in the computation of the expected floating leg cash flows?

This question is resolved by the model entry **Forecasting Curve Issuer= %Risky Issuer** in **cell F24**, which means that the curve with Issuer equaling **%Risky Issuer** should be used for generating the forward Libor rates.

As the following image shows, the **&USD-3M.1** curve has been created with **Issuer= %Risky Issuer** in **cell C11** and therefore is the curve that is chosen for forecasting purposes.

Click on **YieldCurveBlbg3mUSD.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