8 minutes reading time (1598 words)

Overnight Index Swap (OIS): Observation Lags, Lookbacks, Rate Cutoffs and step-by-step Prising in Excel


I have covered in quite exhausting detail the mathematical description of an Overnight Index Swap (OIS) in my earlier post about Pricing and Understanding OIS using Excel.

Since then, OIS have increased in significance due to the cessation of Libor that affected primarily the USD and GBP currencies to the effect that today hardly anyone still trades interest rate swaps that reference the respective Libors.

In this article I will try to shed some light on three technical OIS features the exact meaning of which can be a source of misunderstanding even to experienced practitioners in the field. These are the Observation Lag, Lookback and Rate Cutoff, all expressed in numbers of business days.

While I will supply the respective definitions, the real added value of this post is the attached spreadsheet that contains a manual calculation of the price of the one-month USD OIS on SOFR, the rate of which was quoted on 13 May 2021 by Bloomberg as 0.0663%. The price will of course turn out to be very close to 0 as it should, due to the corresponding swap being traded at par. But the spreadsheet is useful because allows one to change the values of the three quantities mentioned above and immediately see their effect on the cash flow dates and the final swap price. Also, all formulas use only built-in Excel functions so that the sheet runs without the need of any external add-in.

I had first presented the OIS pricing Excel sheet in one of my lectures to BTRM students – mostly bank professionals - as part of a six-month, part-time course that leads to a Certificate of Bank Treasury Risk Management and I have received approval by BTRM to make this sheet publicly available.

So, let's start with a few definitions: 

Table Of Contents 

Recommended for Deriscope starters: The Overview and Quick Guide pages.

Get a free Deriscope activation code that enables you to run the accompanied spreadsheet!

OIS Definition 

The following description is an excerpt from my earlier post on OIS, mentioned above.

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 (also known as the oi term rate) that equals some sort of average of past realized fixings of an agreed overnight index.

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

Constructing the floating OI Term Rate F with a given Observation Lag and Lookback

The OI Term F represents some sort of average of the overnight index observed over a given period [T₀,Tₙ] , referred below as the "observation period".

The observation period is context-dependent and depends on the floating accrual period [Tₛ,Tₑ] associated with the considered term rate F

For example, a 5Y OIS usually has 5 floating accrual periods and each of them is associated with a corresponding oi term rate.

If one of these 5 periods is denoted as [Tₛ,Tₑ] , the corresponding oi term rate F will have an observation period [T₀,Tₙ] that is constructed by shifting the [Tₛ,Tₑ] backwards by o business days, where o is a non-negative integer known as observation lag.

Given the observation period [T₀,Tₙ], F is defined as a certain average of the values of a referenced overnight rate I observed over that period.

The type of average is specified to be either arithmetic or geometric (compound).

In particular, assume the observation period consists of n+1 consecutive business dates T₀, T₁,..., Tₙ and for each i = 0,...,n-1 , the overnight value Iᵢ is assumed to apply on the interval [Tᵢ,Tᵢ₊₁]

The value Iᵢ may have been set prior to Tᵢ.

In fact, if the overnight index I has an inherent fixing lag period of ε business days, the value Iᵢ will have been set on Tᵢ - ε.

If an additional non-negative number of lookback business days λ is specified, the value Iᵢ will have been set on Tᵢ - ε – λ.

F 's definition depends on the selected type of average.

In the geometric (compound) average case, F is defined as:

F = [Π(1 + IᵢΔᵢ) - 1] / Δt

In the arithmetic average case, F is defined as:

F = [Σ(IᵢΔᵢ)] / Δt

Above, Π is the product over all i and Σis the sum over all i, in both cases for i = 0,...,n-1.

Δᵢ is the length of the interval [Tᵢ,Tᵢ₊₁] in annual units.

Δt is the period's [T₀,Tₙ] length in annual units. 

Adding a possible Rate Cutoff

The Rate Cutoffrefers to the number C of business days, which when subtracted from the end Tₙ of the referenced period [T₀,Tₙ] over which the realized overnight index is observed, leads to a date Tₑ such that the overnight index value Iₑ set on that date is deemed to apply to all subsequent daily intervals until Tₙ for the purpose of calculating the average index over the period [T₀,Tₙ] as explained above.

Note the value Iₑ is generally published with some delay at some later date after Tₑ

It follows that the last C of the effective overnight index fixings in the observation period [T₀,Tₙ] are identical and all equal Iₑ.

In the vanilla case where the effective overnight index value always equals the corresponding realized value, the number C equals 1.

Note C cannot exceed the number n of all overnight fixings occurring in the period [T₀,Tₙ]. 

The Market Data 

The curve is constructed in the sheet named "Curve" using the following USD-SOFR OIS rates quoted by Bloomberg as of 13 May 2021: 

The curve implies the following discount factors that are needed in the calculation of the price of the one-month OIS:  

The Term Sheet Data 

The main input data are summarized in the sheet named "OIS-Manual" by the following table: 

The Price Calculation

The calculation of the OIS price is achieved through simple formulas organized in two tables.

The first table supplies quantities that do not vary across the business days spanning the life of the swap and looks as below. It includes cell comments that explain the meaning of the corresponding cell values. 

The second table contains the detailed cash flows for the overnight index as it is averaged over the business days across the observation period. It looks as below:  


At the bottom of the first table, the swap's NPV is shown as:

Given the fact that the swap's notional is 100,000,000 USD, one can safely say that this result practically equals 0.

For validation purposes, the same swap is also priced in sheet "OIS-Deriscope" using the Deriscope pricing Add-In and the result there comes out exactly 0.

The swap's NPV must be 0 because it has been setup with a fixed rate of 0.0663%, which is the 1-month rate appearing in the Bloomberg market data, out of which the curve – and therefore the discount factors used in pricing – is derived.

Now the effect of the quantities Observation Lag, Lookback and Rate Cutoff can be readily seen by changing their values and observing the effect on the displayed dates and the final swap NPV. 

Click on OISDetailsExcel.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 

Bootstrapping in Excel a Yield Curve to perfectly ...
Libor Transition Impact on Portfolio Pricing: A Co...