Long are the dates when all-purpose risk free yield curves were produced out of deposits, futures and swaps. Since the credit crisis of 2007/08, banks realized they should use different curves for different purposes. In the intervening years, quants have proven mathematically that uncollateralized contingent flows should be discounted using a curve that incorporates the default risk associated with these flows, whereas collateralized flows should be discounted using a curve that reflects the risk of the collateral account. Also the bank's own default risk, as reflected in its cost of funding, should be taken into account.

Without entering into details in the present article, the current derivatives pricing practice by most major banks today is to discount expected values of collateralized future flows using the curve implied by the overnight index related to the denomination currency of the posted collateral. The future flows themselves are being projected using a different curve, the so called "forecasting curve", which is obtained by the current prices of market traded instruments, the flows of which are related to the flows of the referred derivative.

It turns out, the proper generation of the discounting yield curve is of paramount importance since it is involved in the pricing and risk management of diverse types of financial instruments.

With the current 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 OIS (Overnight Index Swap) rates. As an example I will build a curve out of negative EONIA (European Overnight Index Average) swap rates, despite the fact that such swaps will likely seize to exist after January 1^st 2020 due to new European regulation that intends to replace the EONIA index with the new ESTER index. The example will still remain valid, since one may replace the EONIA index with any other overnight index.

Please note that currently QuantLib provides direct support for the five overnight indices shown below along with their respective currencies:

EONIA for EUR

FedFunds and SOFR for USD

SONIA for GBP

AONIA for AUD

Nzocr for NZD

Nevertheless one may still build a custom overnight index with user-specified conventions. 

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

The Bootstrapping Process

We assume we are given the current market values (prices) V_i of an increasing-maturity series of Overnight Index Swaps S_i, i = 1, …, n.

For each i, V_i is just a known number, whereas S_i is a random variable that represents the stream of cash flows received under the respective swap. They are related through the equation:

V_i = PV(S_i)

where the function PV takes as input a random variable that represents a stream of future cash flows and returns a single number being interpreted as the arbitrage-free Present Value of that cash flow stream.

It is a mathematical fact that the PV(.) function can be written in terms of the expectation (probability average) function E(.), where the dot . is a placeholder of the random variable being expected as input to the corresponding function.

I remind you here that the expectation E(X) of any random variable X, the realization of which may assume a value out of a discrete set of numbers {x₁, x₂, …, x_k} with respective probabilities {p₁, p₂, …, p_k}, is defined as:

E(X) = p₁x₁+ p₂x₂+…+p_kx_k

If X assumes infinite possible values taken out of a continuous interval [a,b], with a probability density p(.), then its expectation E(X) is defined through an integral as:

The exact equation for the PV function on the random variable X is: 

where T is the latest time occurring in X and N_t is an arbitrarily chosen numeraire stochastic process, i.e. for each time t, N_t is a random variable that represents the price of some arbitrarily chosen tradable asset as of time t. Omitting the intermediate steps, the conclusion is that the price V₀ of the first swap S₀ depends only on the discount factor as of that swap's maturity. This stems from the fact that the expectation E[F_i] of the compounded rate F_i on which the present value of the i^th floating payment depends is given by the – almost – geometric average of the daily forward rates. ​

As explained in my article about Overnight Index Swaps, E[F_i] is given by:

where j enumerates the daily time intervals within the i^th accrual period and the forecasted daily index E[I _j] is given by: 

So, cancellations occur and the product takes the form:  

The unknown discount factors P(t_n) needs to be calculated out of one single equation involving the known market price of the first swap.

One then proceeds iteratively solving for the unknown discount factors involved in the remaining swaps, in a process conventionally referred as bootstrapping.

Creating the Yield Curve

I will use the wizard to generate the correct formula that creates an object of type Yield Curve out of OIS market rates. After I select the Yield Curve type in Type Selector I must check the Use OIS flag inside the input parameters screen and finally hit the Go button, as this video demonstrates:  

This browser does not support the video element.

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 overnight index and the currency. They are shown as %Eonia and %EUR respectively because the wizard is aware of my location in Germany and accordingly used my local settings for creating the default index and currency. Had I been located in the US, these would have been set as %FedFunds and %USD respectively.

The last argument A10:B12 contains a table consisting of two columns.

The first column bears the title #Maturity and contains the lengths of the supplied swaps, expressed as time intervals.

The second column bears the title #Rate and contains the respective swap rates in fractional units, so that 0.01 means a rate of 1%.

The wizard sets the swap rates to 1% because it has no knowledge of actual market data. It is my responsibility to enter rates that are actually traded in the market.

On the global-rate.com web site I can see the following recent historical EONIA fixings: 

Due to the unfortunate fact that I have no access to current prices of actually traded Eonia OIS, for the sake of demonstration I will overwrite the cells B11 and B12 with the values -0.349% and -0.330%, which are at least conformant to the actual historical fixings.

I decide also to insert two more rows with maturities of 18 months and 2 years with respective slightly increasing rates so that I can generate a curve up to 2 years.

After these manual edits, my spreadsheet looks like in the image below. Due to cell A1 being selected, the wizard displays the contents of the referred object, i.e. the contents of the created OIS.

You may notice the three entries:

OIS Settlement Days= 2

OIS Payment Lag= %2D{TARGET|F}

OIS Payment Frequency= Annual.

These are the default conventions affecting the floating legs of the swaps that are usually associated with the chosen EONIA index. I could overwrite these settings by supplying the respective key-value pairs as part of the spreadsheet range that constitutes the first input argument to my ds() spreadsheet formula. 

Creating a Yield Curve out of Forward Starting OIS that carry a Spread

QuantLib supports a yield curve construction out of OIS that start in the future and the floating leg of which pays the compounded index plus a spread. The following video shows how I may instruct the wizard to generate the appropriate spreadsheet formula, which this time references a table consisting of four rather than two columns:  

This browser does not support the video element.

This is the result:  

Using the Yield Curve Object and Constructing a Zero Rate Curve​

With regard to how a Yield Curve object may be used in general, I would refer you to the respective section of the Yield Curve out of Deposit Rates article.

As an example here, I may use the function Zero Rate made available by all objects of type Yield Curve to set up a table with increasing maturities and associated calculated zero rates. As usually, the easiest way of creating the zero rate spreadsheet formula is by first selecting the cell containing the Yield Curve object and then choosing through the wizard the Zero Rate function.

The video below demonstrates how I can create such a table with monthly (30 day) maturity increments and also construct the associated zero rate chart in only 90 seconds.

Watching the video, you will notice that the default input of the Zero Rate function within the wizard consists of one maturity date, but I decide to add one more date. The only reason I do that is to inform the wizard that my intention is to supply more than one input maturities. Then the wizard generates the spreadsheet formula with a syntax designed to accept an array of input maturities rather than a single maturity. The resulting formula can be then manually edited by me much easier than if it were handling a single maturity input. 

This browser does not support the video element.

The resulting zero rate table and the associated chart are shown below: 

Click on YieldCurveOis.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