6 minutes reading time (1277 words)

Yield Curve Building in Excel using Futures

cover

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.

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:B7;A9;A10:B12), which takes three input arguments and returns the text &YldCrv_A1:1.1

The first input argument contains the same kind of data as that already explained at the respective section of the Yield Curve out of Forward Rates article, where you may refer for details.

The last argument A10:B12 contains the table of futures prices.

They are specified by 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 B7.

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


Second Alternative construction of the Futures Prices table

Rather than supplying an object of type Ibor Rate in cell B7 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. This is how the wizard can help me do that:

and this 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 demonstrated by this video:

with this result: 

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.

Using the Yield Curve object

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


  You may download the spreadsheet produced with the above steps here

File Name: YieldCurveFut
File Size: 13 kb
Download File

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

Yield Curve Building in Excel using Swap Rates
Yield Curve Building in Excel using Forward Rates