*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}, …, T_{n }**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 ranges from a few weeks to several years.

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 some agreed

*underlying overnight index*over the time range between

**T**

**΄**

**and**

_{i-1}**T**

**΄**

**.**

_{i}For example, in the case of EONIA swaps, the *underlying overnight index* is the Euro Overnight Index Average (EONIA) calculated and published by the European Central Bank (ECB) in Frankfurt every business day by 19:00 CET.

In all OIS types, the daily calculated *underlying overnight index* is an officially recognized average of the interest rates charged by banks as they lend - secured or unsecured, depending on the OIS type - money to other banks for a time interval that extends until the next business day.

In order to understand how the number **Fi **is produced, I restrict my analysis below to one specific accrual period, for example the period between **T****΄**** _{i-1}** and

**T**

**΄**

**.**

_{i}To simplify notation, I use the symbol **t**** _{0}** =

**T**

**΄**

**, since this is the earliest time involved in examining this accrual period.**

_{i-1}I will use the integer subscript **j** to enumerate the consecutive business days in the currently examined accrual interval. So, **t**** _{j}** occurs exactly one business day after

**t**

**.**

_{j-1}Sometime after the end of a business date **t**** _{j-1}**, the index calculating agent – for example the ECB in the case of EONIA – will have access to the lending rates prevailing at

**t**

**and thus be able to calculate the index**

_{j-1}**I**as the appropriate average of these rates.

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

**t**

**incurs an interest amount of**

_{j}**I**

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

_{j}**τ**

_{j}=**t**

_{j}**-**

**t**

**is calculated 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}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**

_{3}**T**

**΄**of the currently examined swap accrual period

_{i}**the initial investment of one currency unit**

_{}**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 equals the amount by which the initial investment (one currency unit) has grown, which is the difference **Π**(**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.

It turns out that **Fi** may be approximately viewed as the geometric average of the fixings **I _{j}**.

This statement would have been exact only if **a)** all fixing times **t**** _{j}** were equidistant (which is never the case due to the intervening weekends) and

**b)**

**Fi**had not been defined as a simply (once) compounded interest rate, but rather as a discretely compounded over the time partition set by

**t**

**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}*. In fact, we don't need to bother about the involved*

*forward**measure**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!*

*forward**measure* One more assumption is needed in order for the proof to work. The payment time must be assumed equal to the end of the accrual period, i.e. **T _{i}** =

**T**

**΄**

**.**

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

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

Here **E** is the expectation operator with respect to the forward measure with maturity **T****΄**** _{i}**.

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

**]**. But we found earlier that

Therefore

And finally

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

**τ**

**.**

_{j}At first glance, this task looks difficult because the future overnight indices **I _{j}** 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. Apparently then, today's observable forward rates are not enough, or are they?

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

The above equation holds only on the forward measure and is proved below.

Please note though that this exact substitution breaks down if a spread **s** is added to the overnight index **I _{j}**. Then we may still apply the above formula, albeit only in an approximate sense. In other words, a spread introduces convexity.

An interesting point is that the above equation means that an OIS has the same price as the corresponding fixed-to-floating swap where the floating index is the term (eg Libor) rate spanning the compounding period of the overnight index. This is true only for OIS on the compounded average index without a spread and does not hold for OIS on the arithmetic average index. It also concerns only the PV of the floating flows. The realized flows will generally differ, as the term rate is fixed at the beginning of the accruing interval whereas the compounded average index is fixed at the end!

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

**t**

**to**

_{j-1}**t**

**(one business day apart) as observed at**

_{j}**t**

**. Since**

_{j-1}**t**

**is in the future,**

_{j-1 }**I**is a random variable from today's perspective.

_{j}Next, we can express **I _{j}** in terms of the – also random variable - discount factor

**P(**

**t**

_{j-1}**,t**

_{j}**)**, observed at

**t**

**for maturity**

_{j-1}**t**

**:**

_{j} So, in terms of the **t**** _{j-1 }**-observed discount factor

**P(**

**t**

_{j-1}**, t**

_{j}**)**the above expectation takes the form:

where we used the tower law of conditional expectation, which states that the expectation of a random variable equals the expectation of its conditional expectation.

In the above case, the conditional expectation is taken with respect to the *filtration* **F****t**** _{n-2 }**as of time

**t**

**, which the fancy name for the information available as of time**

_{n-2}**t**

**.**

_{n-2}The first **n-1** factors of the product are non-random with respect to **F****t**** _{n-2}**, so they can be taken out of the conditional expectation:

We may now substitute:

which shows that the conditionally integrated quantity equals the ratio of the price as of **t**_{n-1}** **of the zero bond maturing at **t**_{n-1}** **divided by the value of our numeraire as of **t**** _{n-1}**.

It follows, this ratio must be a martingale, which means that its conditional expectation with respect to **F****t**** _{n-2}** must equal its value as of

**t**

**.**

_{n-2}Therefore:

and the unconditional expectation reduces to

Proceeding recursively, we reach the result:

where **R** is the term rate observed at **t**** _{0 }**for the interval from

**t**

**to**

_{0 }**t**

**and**

_{n}**τ**is the daycount fraction of that interval.

We may also write

Above, **E(****I _{j}**

**)**represents the forward of the overnight rate

**I**as observed today.

_{j}Recommended for Deriscope starters: The Overview and Quick Guide pages.

### 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.

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