20 minutes reading time (4079 words)

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 T1, T2, …, 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 ranges from a few weeks to several years.

At each payment time Ti, 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 Fi(T΄i-T΄i-1)N, which can be calculated only when the time Ti, arrives, or shortly before in case of an agreed payment lag. (See diagram below)

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΄1 is the end of the first accrual period, T΄2 is the end of the second accrual period and T΄n is the end of the nth accrual period. If T΄0 lies in the future then the swap is a forward starting overnight index swap. The slight – if any – difference between T΄i and Ti is determined by the date bump convention and a likely payment delay specified in the swap contract.

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

This setup is sketched in the following diagram:

I turn now my attention on the floating numbers F1, F2, …, F4 that determine the floating payment flows according to the formulas Fi(T΄i-T΄i-1)N, where i = 1,2, …, n.

Each Fi is defined as some sort of average of the fixings of some agreed underlying overnight index over the time range between T΄i-1 and 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 t0 = T΄i-1, since this is the earliest time involved in examining this accrual period.

I will use the integer subscript j to enumerate the consecutive business days in the currently examined accrual interval. So, tj occurs exactly one business day after tj-1.

Sometime after the end of a business date tj-1, the index calculating agent – for example the ECB in the case of EONIA – will have access to the lending rates prevailing at tj-1 and thus be able to calculate the index Ij as the appropriate average of these rates.

This means that – on average – inter-bank overnight lending for the period from tj-1 until tj incurs an interest amount of Ijτj per unit of currency, where τj = tj - tj-1 is calculated in accordance with the applicable day count convention. Most of the time τj represents the time period of 24 hours expressed in annual units, but if a holiday or a weekend exists between tj-1 and tj then τj can be much greater.

An initial currency unit invested as an unsecured, overnight inter-bank loan at t0 would accrue to become 1+I1τ1 by the time t1 (one business day later). Rolling over this new amount through a subsequent overnight investment from t1 to t2 (another business day later) would result in a total capital of (1+I1τ1)(1+I2τ2). By the end of business at time t3 the capital would have grown to (1+I1τ1)(1+I2τ2) (1+I3τ3). At the end T΄i of the currently examined swap accrual period the initial investment of one currency unit would have grown to Π(1+Ijτj), which is the product of all the daily accruing factors (1 + Ijτj) acting between T΄i-1 and 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+Ijτ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 Ij

This statement would have been exact only if a) all fixing times tj 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 tj interest rate.

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 ith fixed flow paid at time Ti equals its discounted value, i.e. the cash amount R(T΄i-T΄i-1)N multiplied by the appropriate discount factor P(Ti).

Here P(Ti) is the discount factor with maturity Ti, which is by definition the value today of one currency unit received at time Ti. (By the way, the previous statement about the today's price of the ith fixed flow is actually derived by this very definition!)

The floating cash flows are a bit more interesting. The ith floating flow represents an amount of Fi(T΄i-T΄i-1)N paid at time Ti. Because from today's point of view Fi 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 forward measure. 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!

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. Ti = T΄i.

The price today of the ith 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[Fi]. But we found earlier that 


And finally  

It all boils down to calculating the expectation of the product of the daily accruing factors 1 + Ijτj.

At first glance, this task looks difficult because the future overnight indices Ij 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 Ij 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 Ij. 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!

Ij represents the overnight rate from tj-1 to tj (one business day apart) as observed at tj-1. Since tj-1 is in the future, Ij is a random variable from today's perspective.

Next, we can express Ij in terms of the – also random variable - discount factor P(tj-1,tj), observed at tj-1 for maturity tj:

So, in terms of the tj-1 -observed discount factor P(tj-1, tj) 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 Ftn-2 as of time tn-2, which the fancy name for the information available as of time tn-2.

The first n-1 factors of the product are non-random with respect to Ftn-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 tn-1 of the zero bond maturing at tn-1 divided by the value of our numeraire as of tn-1.

It follows, this ratio must be a martingale, which means that its conditional expectation with respect to Ftn-2 must equal its value as of tn-2.


and the unconditional expectation reduces to  

Proceeding recursively, we reach the result:  

where R is the term rate observed at t0 for the interval from t0 to tn and τ is the daycount fraction of that interval.

We may also write 

Above, E(Ij) represents the forward of the overnight rate Ij as observed today.

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.

The two grey keys ~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 

Risk free Yield Curve Building in Excel using Nega...
How to compute the VaR: Step-by-Step Excel Guide