# Overnight Index Swap (OIS): Pricing and Understanding using Excel

*Overnight Index Swaps* (*OIS*) may be priced in Excel using the free and open source derivatives analytics QuantLib library through the Deriscope Excel interface.

An *OIS* contract is very similar to a plain vanilla interest rate swap, the only difference being that each payment in the floating leg is calculated according to a floating number **F** that equals some sort of average of past realized fixings of an agreed overnight index.

So the *OIS* contract specifies **n** payment times **T _{1}, T_{2}, …, Tn_{ }**in increasing order, where

**n**is the number of periods in the swap. In exchange traded swaps, the payment frequency is usually annual and the total length of the swap does not exceed one year, which means

**n = 1**.

At each payment time **T _{i}**, counterparty

**A**(the

*Receiver*) receives a pre-agreed fixed amount of

**R**(

**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)N**from counterparty

**B**(the

*Payer*) but pays a floating amount of

**F**(

_{i}**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)N**, which can be calculated only when the time

**T**, arrives, or shortly before in case of an agreed payment lag. (See diagram below)

_{i}Here **N** is the agreed swap notional (eg 10M $) and **T****΄**_{0}**, T****΄**_{1}**, T****΄**_{2}**, …, T****΄**** _{n}**, are the times in increasing order that define the accrual periods of the swap. So

**T**

**΄**

**is the end of the first accrual period,**

_{1}**T**

**΄**

**is the end of the second accrual period and**

_{2}**T**

**΄**

**is the end of the**

_{n}**n**accrual period. If

^{th}**T**

**΄**

**lies in the future then the swap is a forward starting overnight index swap. The slight – if any – difference between**

_{0}**T**

**΄**

**and**

_{i}**T**

**is determined by the date bump convention and a likely payment delay specified in the swap contract.**

_{i}Each time difference **T****΄**_{i}**-T****΄**** _{i-1}** is in annual units and calculated according to the agreed day count convention.

I turn now my attention on the floating numbers **F _{1}, F_{2}, …, F_{4}** that determine the floating payment flows according to the formulas

**F**(

_{i}**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)N**, where

**i = 1,2, …, n.**

Each **F _{i}** is defined as some sort of average of the fixings of the underlying overnight index over the time range between

**T**

**΄**

**and**

_{i-1}**T**

**΄**

**. For example, in the case of EONIA swaps, the referenced overnight index is the Euro Overnight Index Average (EONIA) calculated and published by the ECB every business day by 19:00 CET. In general, the referenced overnight index is an officially recognized average of the interest rates charged by banks as they lend unsecured money to other banks for a time interval that extends until the next business day.**

_{i}Let us use the capital letter **I** to refer to the underlying daily settled index.

Assume that at the end of a business date **t**** _{j-1}**, the index

**I**is fixed by the calculating agent (the ECB in the case of EONIA) to equal

**I**. Here I use the integer subscript

_{j}**j**to enumerate the consecutive business days, over which the index

**I**is fixed. So

**t**

**occurs exactly one business day after**

_{j}**t**

**.**

_{j-1}This means that – on average – inter-bank lending from **t**** _{j-1}** until

**t**

**incurs an interest amount of**

_{j}**I**

_{j}**τ**per unit of currency, where

_{j}**τ**

_{j}=**t**

_{j}**-**

**t**

**in accordance with the applicable day count convention. Most of the time**

_{j-1}**τ**represents the time period of 24 hours expressed in annual units, but if a holiday or a weekend exists between

_{j}**t**

**and**

_{j-1}**t**

**then**

_{j}**τ**can be much greater.

_{j}In order to understand how the number **Fi **is produced, we focus on the accrual period between **T****΄**** _{i-1}** and

**T**

**΄**

**of a specific Overnight Index Swap and set**

_{i}**t**

**=**

_{0}**T**

**΄**

**.**

_{i-1}Then an initial currency unit invested as an unsecured, overnight inter-bank loan at **t**** _{0}** would accrue to become

**1+I**

_{1}**τ**

**by the time**

_{1}**t**

**(one business day later). Rolling over this new amount through a subsequent overnight investment from**

_{1}**t**

**to**

_{1}**t**

**(another business day later) would result in a total capital of (**

_{2}**1+I**

_{1}**τ**

**)(**

_{1}**1+I**

_{2}**τ**

**). By the end of business at time**

_{2}**t**

**the capital would have grown to (**

_{3}**1+I**

_{1}**τ**

**)(**

_{1}**1+I**

_{2}**τ**

**) (**

_{2}**1+I**

_{3}**τ**

**). At the end of the swap accrual period**

_{3}**T**

**΄**

**the initial investment of one currency unit**

_{i}**would have grown to**

**Π**

**(**

**1+I**

_{j}**τ**

**), which is the product of all the daily accruing factors (**

_{j}**1 +**

**I**

_{j}**τ**

**) acting between**

_{j}**T**

**΄**

**and**

_{i-1}**T**

**΄**

**.**

_{i}The cumulative interest earned by this roll-over strategy is clearly **Π**(**1+I _{j}**

**τ**

**)**

_{j}**– 1**.

Now **Fi **is simply defined as the simply compounded interest rate that produces the same total accrual interest amount, i.e. **Fi **= [**Π**(**1+I _{j}**

**τ**

**)**

_{j}**– 1**

**]/**(

**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)**

It turns out that **Fi** equals the geometric average of the fixings **I _{j }**only in the case where

**a)**all fixing times

**t**

**are equidistant (which is never the case due to the intervening weekends) and**

_{j}**b)**

**Fi**is defined as a discretely compounded over the time partition set by

**t**

**interest rate rather than as a simply (once) compounded interest rate.**

_{j}The described derivation of the floating cash flow and the associated rate **Fi** is sketched in the following diagram:

### Mathematical Calculation of the OIS Present Value

The fair price (present value) of any stream of cash flows is the sum of their fair prices, provided that any non-linear contributions are considered too small to be taken into account. Therefore the price today of an OIS is just the sum of the prices of the fixed and floating cash flows.

The fixed cash flows pose no problem. The price today of the **i ^{th}** fixed flow paid at time

**T**equals its discounted value, i.e. the cash amount

_{i}**R**(

**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)N**multiplied by the appropriate discount factor

**P(T**.

_{i})Here **P(T _{i})** is the discount factor with maturity

**T**, which is by definition the value today of one currency unit received at time

_{i}**T**. (By the way, the previous statement about the today's price of the

_{i}**i**fixed flow is actually derived by this very definition!)

^{th}The floating cash flows are a bit more interesting. The **i ^{th}** floating flow represents an amount of

**F**(

_{i}**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)N**paid at time

**T**. Because from today's point of view

_{i}**F**is unknown, we cannot proceed like in the fixed flow case. At this point we must resort to the fundamental principle of mathematical finance according to which the present value today of any future cash flow must equal its discounted expected value under a special probability structure called

_{i}*risk neutral measure*. In fact, we don't need to bother about the involved

*risk neutral*probabilities because they will never appear in the final result. They will be imbedded in quantities (forward rates) that are – almost – directly observable in the real world. So we only need to calculate the expectation of each floating cash flow without worrying about the probabilities with which that expectation is defined!

So the price today of the **i ^{th}** floating flow equals

**P(T**

_{i})**E[**

**F**

_{i}(T**΄**

_{i}**-T**

**΄**

_{i-1}**)N**

**]**=

**P(T**

_{i})(T**΄**

_{i}**-T**

**΄**

_{i-1}**)N**

**E[**

**F**

_{i}**]**, where (

**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)N**has been taken out of the expectation due to being constant.

The only remaining task is to calculate the expectation **E[****F _{i}**

**]**. But we found earlier that

**Fi**=

**[**

**Π**

**(**

**1+I**

_{j}**τ**

_{j}**)**

**–1**

**]/(**

**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)**. Therefore

**E[**

**F**

_{i}**]**=

**[**

**1**

**/(**

**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)]E[**

**Π**(

**1+I**

_{j}**τ**

**)**

_{j}**–1**

**]**=

**[**

**1**

**/(**

**T**

**΄**

_{i}**-T**

**΄**

_{i-1}**)]E[**

**Π**(

**1+I**

_{j}**τ**

**)**

_{j}**]**

It all boils down to calculating the expectation of the product of the daily accruing factors **1 +** **I _{j}**

**τ**

**. This task is actually extremely difficult because the future overnight indices**

_{j}**I**are separate random variables, perhaps correlated with each other. The expectation of their product would therefore necessarily entail their individual standard deviations and pairwise correlations. The latter are impossible to be calculated without the assumption of a model that describes the stochastic evolution of interest rates. Certainly today's observable forward rates are not enough, or are they?

_{j}The answer is that we can safely ignore the random nature of the indices **I _{j}** and replace the

**E[**

**Π**

**(**

**1+I**

_{j}**τ**

_{j}**)]**with

**Π**

**[**

**1+**

**E(**

**I**

_{j}**)**

**τ**

_{j}**]**. This is because of

**τ**

**being so small that any contributions to the final expectation result stemming from the randomness of**

_{j}**I**become insignificant after they are multiplied with

_{j}**τ**

**. Remember also that practical finance is in a way not "exact science". For example, the daily published rates of**

_{j}**I**include only the first three decimals. It clearly makes no sense to devise models that extract more "accuracy" than is required.

_{j}**E(I**with the forward of the overnight rate

_{j})**I**as observed today. Since

_{j}**I**represents the overnight rate from

_{j}**t**to

_{j-1}**t**(one business day apart), and noticing that investing one currency unit today will grow to

_{j}**1/P(**

**t**by the time

_{j-1})**t**and further to

_{j-1}**1/P(**

**t**by the time

_{j})**t**, the extra interest amount generated from

_{j}**t**to

_{j-1}**t**must equal

_{j}**1/P(**

**t**-

_{j})**1/P(**

**t**. By definition, the corresponding forward rate equals this generated interest amount divided by the time interval

_{j-1})**t**.

_{j}-t_{j-1}### Creating a 5-year *OIS* object in Excel under 18 seconds

I can use the wizard to produce a formula that creates an OIS object in only 18 seconds. By default, the OIS object is built so that it references the local overnight index, which in my case – Germany location – is EONIA. It also sets the swap tenor to 5 years. Please keep in mind that although most exchange traded overnight index swaps do not extend beyond one year, there exist over-the-counter custom tailored swaps that span several years. The following video shows the creation steps:

The wizard strives for simplicity and therefore uses the simplest set of input parameters as default. In my case, the formula that creates the OIS object in cell **A1** is **=ds(A2:B6)** and takes as input the single range **A2:B6**, as you see in the image below.

Apart from the usual *Type=* and *Function=* entries, this range also includes the specifications of the three keys *Index*=, *Fixed Rate*= and *Schedule*=. The value associated with the *Schedule*= key is itself an object, which is created by a separate formula in cell **A8**.

The taskpane on the right displays the contents of the currently selected object, which is the OIS object of cell **A1**.

The taskpane reveals a few OIS data that are not part of the spreadsheet input ranges **A2:B6** and **A9:B17**. For example, the key *Spread*= appears in the taskpane but not on the spreadsheet. Why this?

If you notice carefully, several taskpane entries – such as the mentioned *Spread*= - appear with a dimmed-out grey color. This is a visual indication that these entries are *optional*, in the sense that if they are not explicitly supplied in the input ranges then they assume certain Deriscope-defined default values. The assumed default values are then shown by the wizard when the object's contents are displayed. You can overwrite the default values by expanding the input range **A2:B6** to include the corresponding keys and their associated custom values.

*Fixed Cash Flows*= and ~

*Overnight Cash Flows*at the bottom are different. The ~ prefix indicates that these two keys point to read-only data that are impossible to be specified as input data in the spreadsheet level.

### Getting Information about the Various Input Data

I can easily find out what the various spreadsheet entries exactly mean by selecting the cells containing the respective keys. For example, when I select the cell A5 containing the key *Fixed Rate*=, the bottom info area of the taskpane displays the related information, as shown below:

I may even access information related to cells that contain no keys. For example, if I select the cell **B2** that contains the value *Overnight Index Swap* associated with the *Type*= key, the following appears:

The blue colored labels are hyperlinks to more information. For example, when I click on the blue colored label *Key Overnight Index Swap::Index*, the following appears:

### Understanding the Index Input

The overnight index referenced by my OIS is defined in cell **B4** as **%Eonia**. The **%** prefix indicates that this is the handle name of a so called *Trivial Object*. *Trivial* objects are objects that can be created without explicit input data anywhere in the spreadsheet. They do contain data just like all other objects, but all their data are fully inferred by their handle name. The **%Eonia** object is trivial because its data are fully specified by the text label "**Eonia**". These data are revealed when the cell **B4** gets selected, as shown below:

Now I can tell that the Eonia index is denominated in **EUR**. The ~*Settlement*= entry of **%0B{TARGET|F}** means the Eonia rate settles on same day - since **0B** means zero business days -, provided the reference day is a proper business day. Otherwise it is shifted to the next business day according to the **Following** convention (letter **F**) and the **TARGET** calendar. Also the day count convention used to determine the associated overnight interest amount is **ACT/360**. At this point, I can change the *Ibor Type* by clicking on the green colored **Eonia** element and choose one of the presented types shown below:

Apart from the pre-defined types **Eonia, FedFunds, Sonia, Aonia** and **Nzocr**, I can also choose **Custom** to create an overnight index with custom conventions.

###
Browsing through the Swap Cash Flows

You saw earlier the two grey keys ~*Fixed Cash Flows*= and ~*Overnight Cash Flows=* in the wizard display of the OIS object contents. If I now click on the lens icon to the right of the ~*Fixed Cash Flows*= key, I see the following:

These are the details associated with the **5** annual payments incurred through the fixed leg of the overnight index swap. When I click on the lens icon to the right of the ~* Overnight Cash Flows*= key, I see the following:

These are the details associated with the **5** annual payments incurred through the floating leg of the overnight index swap. I notice that the **#Amount** column is blank, as expected due to the fact that the projected (i.e. expected) amounts of the floating leg cannot be computed on the absence of a forecasting yield curve. For the same reason, also the **#Rate** column appears blank. Nevertheless I would expect to see data in the **#OvernightDetails **column. Due to the potentially large number of data, this column stays by default blank, but I can instruct Deriscope to populate it by modifying the input to my OIS object creation so that the optional key *Full Cash Flow Details*= is supplied with a value of **TRUE**. This is what I do next.

### Creating an OIS Object with all Optional Input Data Supplied

First I select the empty cell **D1** where I plan to paste the new formula that will create another OIS object, this time with all available input data explicitly supplied within the input range. The following short video shows the undertaken simple steps:

The image below shows the new function **=ds(D2:E12)** created by the wizard in cell **D1**. The new input range **D2:E12** is much bigger than the earlier input range **A2:B6** because it now includes all possible optional keys. You may notice that no second formula has been created in order to generate the *Schedule* object consumed in cell **E11**. The wizard is smart enough to realize that the required *Schedule* object happens to be exactly the same as an already existing object, the one in cell **A8**. So rather than creating an additional instance of yet another *Schedule* object, the wizard simply places in cell **E11** a link to the pre-existing object in cell **A8**.

###
Observing the contents of the created OIS Object through a Web Browser

Deriscope is capable of exporting the full contents of any object as a text file in xml format, by simply clicking on **Go** and selecting the **Export Object to Text File** menu item as shown in the below screenshot.

But I am also able to create a full scale, interactive html representation of any object as well.

For example, the OIS object created in cell **D1 **has the web representation shown below. Click on the image to visit the source web page, where you can explore all the contents of this object.

### Browsing through the Projected Daily Details of the Overnight Index

Now I can keep my promise and change the value of cell **E12** to **TRUE**. When I afterwards observe the object contents associated with the ~* Overnight Cash Flows*= key, I see the following:

The only difference from the earlier screen is that now the **#OvernightDetails** column is populated. In fact, all cells in that column contain an object, as indicated by the lens icons. When for example I click on the first lens icon, I see all the dates and accrual times associated with the daily resets of the underlying Eonia index. Only the projected fixings are missing, since no forecasting yield curve is provided yet.

### The Price function

After I have created an OIS object, I can ask the wizard to generate the spreadsheet formulas that calculate its fair price. The following video shows how this is done in only **8** seconds, after the cell **D1** containing my second OIS object has been selected:

Below is the spreadsheet image with the pasted formula **=ds(G2:H4)** in cell **G1** that returns an OIS price of 0.012393468. Apart from the usual input keys of **Object**= and *Function*=, the pricing routine requires the additional input key *Markets*=. Its associated value in cell **H4** is **&YldCrv_G6:2.1**, which is the handle name of the object of type *Yield Curve* generated further below by another **ds** formula in cell **G6**. As explained above, the wizard always generates the simplest possible structures that are needed to fulfil the requested task. Since the pricing task can be accomplished with a flat yield curve, such a curve is generated by the wizard in cell **G6**. But you can easily replace the pricing input in cell **H4** with any other curve, presumably one created with real market traded instruments, such as exchange traded OIS and otc traded longer term OIS. My articles about creating various types of yield curves (<=https://blog.deriscope.com/index.php/en/categories/yield-curve) provide more details.

### Additional output data

During the price calculation, QuantLib also computes certain values that can be of interest. When cell **G1** is selected, the wizard displays the following in its *Info Area*:

If I click on the *here* hyperlink of the top sentence "*To display extra pricing data click here"*, I see the following:

Thus I can also see the *Fair Rate *and *Fair Spread* of my swap plus the *NPV* and *BPS* per each leg.

The *Fixed Cash Flows*= entry is not going to tell me anything that I don't know already, since the flows of the fixed leg are independent of the yield curve input to this pricing routine and are still the same with those visited earlier.

But the *Overnight Cash Flows*= entry now contains the projected forward fixings of both the overnight index (on a daily basis) and the average index on which the floating payment is based. Below you see all these details as displayed by the wizard after I click on the respective lens icon:

And below you see the contents of the first item in the **#OvernightDetails** column, which now include the #IndexFixing column:

I have already explained here how I may transfer these data in the spreadsheet.

### Advanced Topic: Following the pricing algorithm step-by-step

It is also possible to track the pricing algorithm that has been run in order to produce the observed output and also see the actually used intermediate QuantLib structures and numerical outputs as described in the respective section of my Interest Rate Swap article.

You may download the spreadsheet produced with the above steps here.

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