20 minutes reading time (4089 words)

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

Interest Rate All OIS

Ioannis Rigopoulos

Saturday, 23 July 2022

35570 Hits

Comments

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₁, T₂, …, T_nin 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_i, 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΄₀, T΄₁, T΄₂, …, 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, T΄₂ is the end of the second accrual period and T΄_n is the end of the n^th accrual period. If T΄₀ lies in the future then the swap is a forward starting overnight index swap. The slight – if any – difference between T΄_i and T_i 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 F₁, F₂, …, F₄ 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΄_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 t₀ = 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, 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_j-1 and thus be able to calculate the index I_j as the appropriate average of these rates.

This means that – on average – inter-bank overnight lending for the period from t_j-1 until t_j incurs an interest amount of I_jτ_j per unit of currency, where τ_j = t_j - t_j-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 t_j-1 and t_j then τ_j can be much greater.

An initial currency unit invested as an unsecured, overnight inter-bank loan at t₀ would accrue to become 1+I₁τ₁ by the time t₁ (one business day later). Rolling over this new amount through a subsequent overnight investment from t₁ to t₂ (another business day later) would result in a total capital of (1+I₁τ₁)(1+I₂τ₂). By the end of business at time t₃ the capital would have grown to (1+I₁τ₁)(1+I₂τ₂) (1+I₃τ₃). At the end T΄_i of the currently examined swap accrual period the initial investment of one currency unit would have grown to Π(1+I_jτ_j), which is the product of all the daily accruing factors (1 + I_jτ_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+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.

$F_{i}=\frac{\prod_{j=1}^{n}\left(1+I_{j}\tau_{j}\right)-1}{{T}'_{i}-{T}'_{i-1}}$

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_j 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 i^th fixed flow paid at time T_i equals its discounted value, i.e. the cash amount 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_i, which is by definition the value today of one currency unit received at time T_i. (By the way, the previous statement about the today's price of the i^th fixed flow is actually derived by this very definition!)

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_i. Because from today's point of view F_i 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. T_i = T΄_i.

The price today of the i^th floating flow equals

$P({T}'_{i})E\left\[Fi({T}'_{i}-{T}'_{i-1})N\right\] = P({T}'_{i})({T}'_{i}-{T}'_{i-1})NE[F_{i}]$

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

$F_{i}=\frac{ \prod_{j=1}^{n}\left ( 1+I_{j}\tau _{j} \right ) -1}{{T}'_{i}-{T}'_{i-1}}$

Therefore

$E\left(F_{i}\right)=\frac{1}{{T}'_{i}-{T}'_{i-1}}E\left[\prod_{j=1}^{n}\left(1+I_{j}\tau_{j}\right)-1\right]$

And finally

$E\left(F_{i}\right)=\frac{1}{{T}'_{i}-{T}'_{i-1}}\left(E\left[\prod_{j=1}^{n}\left(1+I_{j}\tau_{j}\right)\right]-1\right)$

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:

$E\left\[\prod_{j=1}^{n}(1+I_{j}\tau_{j})\right\]\rightarrow\prod_{j=1}^{n}(1+E[I_{j}]\tau_{j})$

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_j-1 to t_j (one business day apart) as observed at t_j-1. Since t_j-1is in the future, I_j is a random variable from today's perspective.

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

$I_{j}=\frac{\frac{1}{P(t_{j-1},t_{j})}-1}{\tau_{j}}$

So, in terms of the t_j-1-observed discount factor P(t_j-1, t_j) the above expectation takes the form:

$E\left\[\prod_{j=1}^{n}(1+I_{j}\tau_{j})\right\]=E\left\[\prod_{j=1}^{n}\frac{1}{P(t_{j-1},t_{j})}\right\]=E\left\[E\left\[\prod_{j=1}^{n}\frac{1}{P(t_{j-1},t_{j})}\mid F_{t_{n-2}}\right\]\right\]$

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 Ft_n-2as of time t_n-2, which the fancy name for the information available as of time t_n-2.

The first n-1 factors of the product are non-random with respect to Ft_n-2, so they can be taken out of the conditional expectation:

$E\left\[\left$\prod_{j=1}^{n-1}\frac{1}{P(t_{j-1},t_{j})}\right$E\left\[\frac{1}{P(t_{n-1},t_{n})}\mid F_{t_{n-2}}\right\]\right\]$

We may now substitute:

$\frac{1}{P(t_{n-1},t_{n})}=\frac{P(t_{n-1},t_{n-1})}{P(t_{n-1},t_{n})}$

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 Ft_n-2 must equal its value as of t_n-2.

Therefore:

$E\left\[\frac{1}{P(t_{n-1},t_{n})}\mid F_{t_{n-2}}\right\]=E\left\[\frac{P(t_{n-1},t_{n-1})}{P(t_{n-1},t_{n})}\mid F_{t_{n-2}}\right\]=\frac{P(t_{n-2},t_{n-1})}{P(t_{n-2},t_{n})}$

and the unconditional expectation reduces to

$E\left\[\left$\prod_{j=1}^{n-1}\frac{1}{P(t_{j-1},t_{j})}\right$E\left\[\frac{1}{P(t_{n-1},t_{n})}\mid F_{t_{n-2}}\right\]\right\]=E\left\[\left$\prod_{j=1}^{n-1}\frac{1}{P(t_{j-1},t_{j})}\right$\frac{P(t_{n-2},t_{n-1})}{P(t_{n-2},t_{n})}\right\]=E\left\[\left$\prod_{j=1}^{n-2}\frac{1}{P(t_{j-1},t_{j})}\right$\frac{1}{P(t_{n-2},t_{n})}\right\]$

Proceeding recursively, we reach the result:

$E\left\[\prod_{j=1}^{n}(1+I_{j}\tau_{j})\right\]=\frac{1}{P(t_{0},t_{n})}=1+R(t_{0},t_{n})\tau$

where R is the term rate observed at t₀for the interval from t₀to t_n and τ is the daycount fraction of that interval.

We may also write

$E\left\[\prod_{j=1}^{n}(1+I_{j}\tau_{j})\right\]=\frac{1}{P(t_{0},t_{n})}=\frac{1}{P(t_{0},t_{1})}\frac{P(t_{0},t_{1})}{P(t_{0},t_{2})}\cdots\frac{P(t_{0},t_{n-1})}{P(t_{0},t_{n})}=\\(1+E[I_{1}]\tau)(1+E[I_{2}]\tau)\cdots(1+E[I_{n}]\tau)=\prod_{j=1}^{n}(1+E[I_{j}]\tau)$

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

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.

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

About the author

Ioannis Rigopoulos

Detailed profile at my About page

Deriscope Resources

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

Mathematical Calculation of the OIS Present Value

Creating a 5-year OIS object in Excel under 18 seconds