# Yield Curve Building in Excel using Futures

With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are futures prices. The futures convexity will be taken into account.

I explained how you may build a yield curve in Excel out of forward rates in my previous article.

In reality, forward rates are seldom used in practice for yield curve construction because

**a)** The respective forward contracts are mainly OTC (Over The Counter), meaning they are not that liquid and

**b) **Most derivatives traders use futures to hedge their interest rate risk, exactly because futures are exchange-based and therefore more liquid.

The second point is critical as the no-arbitrage-based fair price calculation is based on a hypothetical trading strategy that results in a delta-neutral hedge portfolio. This means one should build the yield curve exactly out of the same instruments that one uses to hedge the trading positions. Since these instruments typically include exchange-traded futures, it follows that knowing how to build the yield curve out of futures is of paramount importance.

### Table Of Contents

- Futures Contract Description
- Futures Quote Convention
- Creating the Yield Curve
- Understanding the formula
- Browsing through the Yield Curve Object's Contents
- Browsing through the Cash Flows
- Dealing with a Curve Bootstrapping Failure
- Second Alternative construction of the Futures Prices table
- Third Alternative construction of the Futures Prices table
- Fourth Alternative construction of the Futures Prices table
- Using the Yield Curve object
- Download Spreadsheet

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

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

### Futures Contract Description

A futures contract is basically a forward contract with the additional provision that each counterparty maintains a margin account, which is credited daily with the price change of the underlying deposit contract.

The effect is that a futures contract has always a value of zero, at least if we ignore for simplicity the intraday situation when the margin has not yet been posted.

Its mathematical valuation is based on the fact that the futures price evolves like a martingale (i.e. without drift) in a risk neutral world, which means it can be treated as a traded asset that pays a continuous dividend with yield equal to the risk-free short interest rate.

Below is a graphical comparison between a forward and a futures contract:

Futures Quote Convention

From a practical perspective, another important difference between futures and forwards concerns their quotation. While forward contracts are quoted by their forward rate, for example **1%**, futures contracts are quoted in a mystical way by some number **F**, for example **99**, that is defined to equal **100(1– R)** at the futures expiry **T1**, where **R** is the rate of the underlying deposit contract realized at **T1**.

Let's be more specific.

Let's say the underlying deposit contract is the one defined through the 3-month USD Libor. As a reminder, 3-month USD Libor is the rate agreed between London banks for 3 month spot deposits among them. By the way, the corresponding futures contract is then known as Eurodollar contract.

Let's also assume that our futures contract expires on 20 June 2018.

Then the quote convention demands that the quoted futures price **F** on 20 June 2018 equals exactly **100 – R**, where **R** is the 3-month USD Libor settled on 20 June 2018.

For example, if **R** happens to be **1%** then **F** will be **100(1–0.01) = 99** on that date.

Due to the fact that the margin account of each futures holder is credited daily by an amount proportional to **ΔF** (daily change in **F**), the quoted price **F** on any earlier date **T** will equal **100(1-f)**, where **f** is approximately equal to the forward rate **r** prevailing at time **T** for the 3-month Libor contract commencing on 20 June 2018.

**f **would equal exactly **r** if it were not for the daily margin feature.

But due to the margin requirement, **f = r + c**, where c is a small correction referred to as the *convexity correction*.

As you will see, Deriscope allows you to specify **c** as part of the input data to the yield curve construction function.

Creating the Yield Curve

As I did before with the deposit and forward rates, I will use the wizard to generate the correct formula.

After I select the *Yield Curve* type in *Type Selector* I must check the *Use Futures* flag inside the input parameters screen and finally hit the *Go* button, as this video demonstrates:

This is the result:

### Understanding the formula

As you see, **cell** **A1** contains the formula *=ds(A2:B5)*, which takes one input argument and returns the text ** &GBPCrv_A1:1.1**.

The prefix **&** indicates that ** &GBPCrv_A1:1.1** is the handle name of some object. In fact, it points to an object of type

*Yield Curve*and can be used in any context where a yield curve is needed, such as in pricing of options.

*The overall structure is similar to that of a yield curve created out of deposit rates as explained in my related post*.

*As with the deposit rate case, the above formulas include only the mandatory arguments. The object contents displayed in the wizard include all arguments, including the optional.*

Here the table of futures prices consists of three columns supplying the respective expiries, prices and futures convexity correction values.

The first column must bear the title #*Expiry* and contain the expiry dates of the futures contracts.

The second column must bear the title #*Price* and contain the respective futures prices.

The third column must bear the title #*Convexity* and contain the respective futures convexity correction values.

All futures contracts will then share the same length and conventions, which must be explicitly supplied through the *Ibor Rate* object in **cell B10**.

Linear interpolation and flat extrapolation is assumed for those maturities that do not appear in the table.

### Browsing through the Yield Curve Object's Contents

How to use the wizard to browse through the contents of the Yield Curve object is demonstrated by a video in the respective section of my post on curve creation from deposits, futures and swaps.

Browsing through the Cash Flows

The pair **_Cash Flows= $Set#9** shown in the wizard grid above can be very useful for diagnostic purposes, as it contains the details of the swap instruments used in the curve construction.

Detailed description is available at the cash flows section of my post on curve creation from deposits, futures and swaps.

Dealing with a Curve Bootstrapping Failure

The steps you need to take in case of failure are exemplified in the diagnostics section of my post on curve creation from deposits, futures and swaps.

### Second Alternative construction of the Futures Prices table

Rather than supplying an object of type *Ibor Rate* in **cell B10** that supplies a commonly shared underlying tenor for all futures contracts, I can use a more flexible setup that allows individual futures contracts to have different underlying tenors as well as custom defined expiry dates.

You may refer to this similar alternative deposit rates construction for a demonstration on how to use the wizard in this regard.

Below is the result:

The futures prices are specified by supplying the respective expiries, underlying maturities, prices and convexities through a table consisting of 4 columns.

The first column must bear the title **#Expiry** and contain the expiry dates of the futures contracts.

The second column must bear the title **#Maturity** and contain the maturity dates of the underlying interest rates. The dates here must be greater than those in the first column.

The third column must bear the title **#Price** and contain the respective futures prices.

The fourth column must bear the title **#Convexity** and contain the respective futures convexity correction values.

Here the futures contracts are allowed to have different underlying lengths, but they will still share the same conventions, which must be explicitly supplied.

Linear interpolation and flat extrapolation is assumed for those maturities that do not appear in the table.

Third Alternative construction of the Futures Prices table

A simpler variant of the previous construction makes use of only three columns as shown below:

The futures prices are specified by supplying the respective expiries, prices and convexities through a table consisting of 3 columns.

The first column must bear the title **#Expiry** and contain the expiry dates of the futures contracts.

The second column must bear the title **#Price** and contain the respective futures prices.

The third column must bear the title **#Convexity** and contain the respective futures convexity correction values.

All futures contracts will then share the same length, which must be explicitly supplied in number of months.

They will also share the same conventions, which must be also supplied.

Linear interpolation and flat extrapolation is assumed for those maturities that do not appear in the table.

### Fourth Alternative construction of the Futures Prices table

An even simpler construction makes use of the International Money Market (IMM) dates with the following result:

### Using the Yield Curve object

For this I would refer you to the respective section of the Yield Curve out of Deposit Rates article

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