# Risk free Yield Curve Building in Excel using Negative Overnight Index Swap (OIS) Rates

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.

### 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, …, nFor each **i**, **V _{i}** is just a known number, whereas

**S**is a random variable that represents the stream of cash flows received under the respective swap. They are related through the equation:

_{i}**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 _{1}, x_{2}, …, x_{k}}** with respective probabilities

**{p**, is defined as:

_{1}, p_{2}, …, p_{k}}**E(****X****)**** = p _{1}x_{1}+ p_{2}x_{2}+…+p_{k}x_{k}**

**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:

**T**is the latest time occurring in

**X**and

**N**is an arbitrarily chosen numeraire stochastic process, i.e. for each time

_{t}**t, N**is a random variable that represents the price of some arbitrarily chosen tradable asset as of time

_{t}**t**. Omitting the intermediate steps, the conclusion is that the price

**V**of the first swap

_{0}**S**depends only on the discount factor as of that swap's maturity. This stems from the fact that the expectation

_{0}**E[**

**F**of the compounded rate

_{i}]**F**on which the present value of the

_{i}**i**floating payment depends is given by the – almost – geometric average of the daily forward rates.

^{th} 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

**is given by:**

**E[****I**_{ j}]_{}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 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 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.

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