Yield Curve Building in Excel using Deposit (LIBOR) Rates

With this article I want to show you how to create a yield curve in Excel using the open source QuantLib analytics library, when the input market data are deposit rates – such as Libor rates -, which are a special type of interest rates called zero rates.

As I describe in my article about interest rates, a deposit rate - effectively the interest rate on a loan contract - is a number that reflects the time value of money for a given time interval and a given pair of borrower and lender.

The associated deposit contract undertaken at time 0 between counterparty A (the Lender) and counterparty B (the borrower) for principal N and maturity T stipulates the following cash flows as seen by counterparty A: 

For any given currency there exist several types of deposit rates classified according to their publishing source.

In finance, the most commonly used are the so called IBOR rates, which apply to lending transactions among banks.

In particular, borrowing of London banks from each other is gauged daily by the published LIBOR (London Interbank Offered Rate) quotes with respect to 5 different currencies and 7 maturities (from overnight to 12 months).

For example, the following were the USD Libor rates on 21 Feb 2018: 

What is a Yield Curve?

 Answer: The collection of all "in principle" deposit rates for all possible maturities.

As simple as that.

The referred deposit rates are labelled "in principle" because for many maturities – especially longer ones – no actual deposit market exists. A better name is zero rates, which are defined as the theoretical deposit rates that - if they existed - would not lead to arbitrage with the existing market instruments.

There also exist other equivalent definitions, the most popular of which – at least among quants - is as the collection of all discount factors for all possible maturities, where a discount factor for a given maturity is simply defined as the present value of one currency unit paid at that maturity. 

Why do we need Yield Curves?

Curiosity is one thing, but the practical reason is that calculating the fair present value of any financial transaction – even one that does not directly entail exchange of cash – relies on knowing the discount factors – or equivalently the zero rates – that pertain to certain future dates.

For example, a contract granting one counterparty the right to exchange one stock share with another at some pre-defined future date T has a fair present value that depends on the discount factor maturing at T.

The fair price of an American option expiring at T goes one step further in that it requires the knowledge of all discount factors that have maturities between now and T.

When we price such instruments, it is clearly inefficient or even impossible to carry around a bunch of discount factors (or zero rates) and supply them as input to the respective pricing formulas.

The established approach is to

a) create an object – i.e. a memory footprint – that is capable of dynamically generating the correct discount factor for any enquired maturity date and

b) pass that object as input to the pricing formula of the financial instrument under consideration.

Such an object captures in an operational sense the content of a yield curve as defined above.

How can we build a Yield Curve?

There exist market traded instruments that imply the zero rates for a certain set of maturities.

In this article, I consider only the simplest group of such instruments, the traded deposit contracts – also known as borrowing or lending contracts - such as deposits between London banks, the prices of which are quoted daily through the published LIBOR rates.

For this type of instruments the quote rates equal the zero rates for the respective maturities so there is no need to apply mathematical techniques to derive the latter from the former.

For example, the table with the USD Libor quotes on 21 Feb 2018 pictured above leads directly to the following chart of zero rates until the maximum maturity of one year:

The question is, how we fill up the empty space between the dots. The easiest - but not smartest - approach would be to connect the dots with straight lines and produce the following yield curve:

Thankfully QuantLib is a bit more sophisticated and uses a two-step approach:

Step 1: Define the mathematical quantity – referred as Modelled Qty in Deriscope - that needs to be interpolated for points between the dots. The chosen quantity does not need to equal the zero rate there must exist a formula out of which the zero rate can be produced.

Step 2: Define the interpolation scheme that should be applied on the quantity above, which will produce a value for that quantity for maturities not in the grid. Afterwards the zero rate will be calculated out of that value. 

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

Let's create a Yield Curve then

Theory is good but practice is even better!

Typing a formula in a spreadsheet cell from scratch can take enormous time. You need to know how the formula is called, what parameters it takes and how to build these parameters in spreadsheet.

I will avoid all these problems by asking Deriscope to generate the formula for me.

To keep things simple I make no other selections or fine tunings. After pressing the Go button, Deriscope generates the respective formula and pastes it in the spreadsheet, as shown below:

Understanding the formula

As you see, cell A1 contains the formula =ds(A2:B5), which takes as a single input argument the range A2:B5 and returns the text &GBPCrv_A1:1.1.

Because the cell A1 happens to be selected, the Deriscope taskpane displays the contents of that object.

The prefix & indicates that &GBPCrv_A1:1.1 is the handle name of some object. In fact, it points to an object of type Yield Curve and can be used in any context where a yield curve is needed, such as in pricing of options.

The range A2:B5 contains 4 key-value pairs with obvious meanings.

The key Market Data= expects as value an array of objects that supply – what else – the market data.

Here this array of objects consists of only one object that is referenced by the handle name &YldCrvDep_A7:1.1.

This handle name is created by another formula in cell A7 and references an object of type Yield Curve Dep dedicated to holding the deposit rates and the associated conventions.

The above image shows the dependency arrows that indicate that the latter formula reads the two ranges A8:B9 and A12:B14.

The range A12:B14 contains the table of deposit rates.

The top row contains the labels #Maturity and #Rate, where the prefix # indicates that they are only column titles and not part of the table's values.

The actual values occupy two rows.

The first row holds the rate for the one-month deposit rate.

The second row holds the rate for the two-month deposit rate.

The shown values are the default values pasted by the wizard.

I may edit them on the spreadsheet and also insert additional rows.

A maturity entry must be formatted as %NU, where N is a number and U a time unit specifier. Available specifiers are D (Day), B (Business day), W (Week), L (Lunar month, i.e. 28 days), M (Month), Y (Year).

For example, I can replace the %1M entry with %1B to denote an overnight deposit rate.

Browsing through the Yield Curve Object's Contents 

How to use the wizard to browse through the contents of the Yield Curve object is demonstrated by a video in the respective section of my post on curve creation from deposits, futures and swaps.  

Browsing through the Cash Flows 

The pair _Cash Flows= $Set#9 shown in the wizard grid above can be very useful for diagnostic purposes, as it contains the details of the swap instruments used in the curve construction.

Detailed description is available at the cash flows section of my post on curve creation from deposits, futures and swaps. 

Dealing with a Curve Bootstrapping Failure 

The steps you need to take in case of failure are exemplified in the diagnostics section of my post on curve creation from deposits, futures and swaps.  

More Detailed Specification 

The wizard has created the formulas with their optional parameters missing.

It is possible to create the formulas so that all parameters, both optional and mandatory, are explicitly shown, as the following video shows: 

The wizard has now pasted the formula =ds(D2:E12) in cell D1 that creates the Yield Curve object with handle name &GBPCrv_D1:1.1, as shown below:  

Although there are now many more input parameters, the contents of this new object are exactly the same with those of the previous object &GBPCrv_A1:1.1, as the taskpane in both cases indicates.

I can now see the already discussed Modelled Qty explicitly supplied as the input key-value pair Modelled Qty= Zero Yield in cells D8 and E8.

If I select the cell E8, I see a dropdown that lets me choose a different value: 

Similarly, I see the key-value pair Fixing= %0D in cells D18 and E18.

This is actually an important rate that defines the settlement date of the deposit rates.

It is often the case that the correct settlement is two business days, in which case I should replace %0D -> %2B.

But even this replacement would not be precise, since the notion of business day relies on the definition of a calendar. Setting %2B means a trivial calendar that treats only the weekends as non-business days.

Deriscope supports a special syntax that allows the calendar and date bump specification that looks as %2B{GB|F} for a GB (Great Britain) calendar and a F (Following) bump convention.

I do not need to remember this syntax, since the wizard can paste the correct format for me, as shown in the video below: 

Alternative Deposit Rates Specification 

The following video demonstrates how I can use the wizard to paste an alternative formula that also creates an object of type Yield Curve Dep.  

Below is the alternative formula in cell G14: 

What you see on the left column are the handle names of objects that hold the details of the respective Libor contracts.

The prefix % indicates that these are so-called trivial objects, meaning that their contents are fully derived from their handle name so that they do not need to be created by some formula elsewhere in the spreadsheet. 

Understanding the Ibor Rate objects

Having the wizard open, I select cell G21 containing the handle name %GBPLibor|1M

The contents of the respective trivial object are then displayed in the Browse Area of the wizard: 

The top two rows contain the Ibor Type= and Tenor= of the selected object.

The respective values are GBPLibor and %1M and their green color indicates that even though they are the values of an object the handle name of which is at the currently selected cell, they can still be edited within the wizard. This is possible because this is a trivial object, which means the edited values can be immediately reflected in the handle name displayed on the spreadsheet cell.

For example, I can change the Ibor Type to something else by clicking on the three lines to the left of GBPLibor and choose any one from the list below:

Aonia         DKKLibor   Mosprime   SGDSor

AUDbbsw  Eonia          MXNTiie     Shibor

AUDLibor   Euribor       MYRKlibor  SKKBribor

Bbsw          EURLibor   NOKNibor   Sofr

Bkbm         FedFunds   NZDBKBM  Sonia

CADLibor  GBPLibor     NZDLibor   THBFIX

Cdor          HKDHibor    Nzocr         Tibor

CHFLibor   HUFBubor   PLNWibor  Tonar

CHFTois    IDRIdrfix       Pribor         TRLibor

CORRA     INRMifor       Robor         TWDTaibor

Custom     Jibar              SEKLibor    USDLibor

CZKPribor JPYLibor       SEKStibor  Wibor

DKKCibor  KRWKoribor  SGDSibor  Zibor

The remaining rows contain the Currency, Settlement and DayCount. In this case the respective values are in black color indicating that they cannot be edited. This is so because these values are implied by the Ibor Type and Tenor entries.

For example, the Settlement= value is displayed as %0B{GB_LSX|F}

If I click on the lens sign I see its contents: 

which tells me that the Libor rate settles with zero business days delay and a Following date bump convention, whereby the holiday calendar is %GB_LSX.

If I do not understand these entries, I only need to click on them to display their exact meaning in the Info Area.

Editing the Libor Rate table

I can obviously edit the spreadsheet data in place, delete any rows or insert additional rows.

For example, I can add two more rows, where I type by hand %GBPLibor |6M and %GBPLibor|1Y in the cells of the #Ibor column to end up with a curve that extends to one year in the future:

Using the Yield Curve object

After I have created a Yield Curve object in some spreadsheet cell, I can invoke functions that return spot and forward discount factors as well as spot and forward zero rates for any future dates.

This is what the wizard displays when I select a cell containing a Yield Curve object and click on the Function Selector: 

In particular the function Implied Values is very handy in that it creates a table, the columns of which report the discount factors, zero rates, forward discount factors and forward rates respectively for a list of selected maturities, as shown below:

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