9 minutes reading time (1767 words)

Yield Curve Building in Excel using Deposit (LIBOR) Rates

cover

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. 


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.

All I need to do is to search in Type Selector for a type called Yield Curve and then check the Use Deposits flag inside the input parameters screen, as this video demonstrates: 

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:B6,A8,A9:B11), which takes three input arguments and returns the text &YldCrv_A1:1.1

The prefix & indicates that &YldCrv_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 first input argument is the range A2:B6 that consists of 5 key-value pairs with obvious meanings.

More interesting is the last argument A9:B11 that contains the table of deposit rates.

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

The actual values occupy two rows.

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

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

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 A10 containing the handle name %EURLibor|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 EURLibor 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 I am dealing with a trivial object so that the edited values can be immediately reflected in the handle name at the spreadsheet cell.

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

AUDLibor

CADLibor

Cdor

CHFLibor

Custom

DKKLibor

Eonia

Euribor

EURLibor

FedFunds

GBPLibor

Jibar

JPYLibor

NZDLibor

SEKLibor

Sonia

Tibor

TRLibor

USDLibor

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 %2B{GB_LSX&&TARGET|MF}

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

which tells me that the Libor rate settles with two business days delay and an %MF date bump convention, whereby the holiday calendar is %GB_LSX&&TARGET.

If these entries look like Greek to me, I only need to click around them in order to expose their hidden contents and display their exact meaning in the Info Area.

The following video shows how I discover that %MF stands for Modified Following, how I learn what Modified Following exactly means, how I become aware that the sign && means intersection of two calendars and what these two calendars are.

So for example I find out that the holiday calendar that applies to my Euro Libor rates is the intersection of

a) London stock exchange and

b) Trans-European Automated Real-time Gross settlement Express Transfer


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 %EURLibor|6M and %EURLibor|1Y in the cells of the #Ibor column to end up with a curve that extends to one year in the future:


Alternative construction of a Libor Rate table

Rather than supplying an object of type Ibor Rate at each row of the Libor Rate table, I can define all relevant rate conventions in one place outside the table.

This is how the wizard can help me do that:

and this is the result:


Using a 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:


You may download the spreadsheet produced with the above steps here

File Name: YieldCurveDep
File Size: 14 kb
Download File

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 

Yield Curve Building in Excel using Forward Rates
Beyond Black Scholes: American Option Price Depend...