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. 

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

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