Yield Curve Building in Excel using Swap 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 swap rates. I will also show you how to apply dual bootstrapping when an exogenous yield curve is present.
For short term maturities – typically less than a year – the yield curve may be built out of deposit rates, forward rates or futures prices.
For longer maturities up to and beyond 30 years the market instrument of choice is the interest rate swap.
There are many different types of interest rate swaps, but by far the most liquid is the fixed-to-floating swap that is a contract between two counterparties to exchange periodically cash flows up to some final date called the swap's maturity.
In its simplest version, the contract specifies N payment times T1, T2, …, TN at which, counterparty A (the Receiver) receives a pre-agreed fixed amount from counterparty B (the payer).
This stream of cash flows that are known in advance is called the fixed leg of the swap.
In addition and in order to justify the name "swap", A also pays B a floating amount at times that may or may not coincide with T1, T2, …, TN.
The floating amount is determined by the settlement of a floating interest rate – such as LIBOR – at the start of each interval.
This second stream of cash flows that are not known in advance is called the floating leg of the swap.
The diagram below assumes for simplicity that floating payments are concurrent with fixed payments:
Swaps Quote Convention
Swaps are OTC instruments. They are traded over broker screens where they are quoted by the fixed rate R that determines the periodic payments on the fixed leg.
More specifically, a quoted swap rate R means that the fixed payment at time Ti equals R*N*DCFi, where N is the notional and DCFi is the so called Day Count Fraction of the interval over which the rate R accrues between Ti-1 and Ti and which approximately equals ΔT = Ti - Ti-1.
If for example ΔT = 1 (one year) and assuming that the respective DCFi equals also 1, then a swap on a notional of 10,000,000$ (N = 10,000,000) with R = 1% would stipulate that the receiver counterparty A should receive 0.01*10,000,000*1 = 100,000$ from counterparty B at time Ti.
Assuming the floating rate at the start of the interval has been settled to 0.9%, the receiver counterparty A should also pay the floating amount 0.009*10,000,000*1 = 90,000$ to counterparty B.
In reality, only a single cash flow takes place, in our example a net amount of 10,000$ from B to A.
When it comes to swap valuation, the devil is in the detail because the slightest miscalculation may well mean several thousand dollars lost due to the notional N being often very big.
A miscalculation in the order of 0.01 in DCFi – which could arise by forgetting to count as little as 3 days (3/365 ~ 0.01) - would cause a valuation error of R*N*0.01.So I would feel guilty if I failed to mention that DCFi actually relates to the interval between τi-1 and τi, where the times τi-1, τi define the start and end of the ith accruing interval and do not necessarily coincide with Ti-1, Ti.
Even when τi-1 and τi are known, DCFi cannot be calculated before the so called Day Count Convention of the fixed leg has been specified.
Deriscope allows you to specify all rules and conventions that fully determine both Ti and DCFi so that the swap price can be calculated precisely. The precise swap price calculation is prerequisite to accurate yield curve construction as the latter relies on the former.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Creating the Yield Curve
I will use the wizard to generate the correct formula.
After I select the Yield Curve type in Type Selector I must check the Use Swaps flag inside the input parameters screen and finally hit the Go button, as this video demonstrates:
This is the result:
Understanding the formula
As you see, cell A1 contains the formula =ds(A2:B5), which takes one input argument and returns the text &GBPCrv_A1:1.1.
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 overall structure is similar to that of a yield curve created out of deposit rates as explained in my related post.
As with the deposit rate case, the above formulas include only the mandatory arguments. The object contents displayed in the wizard include all arguments, including the optional.
The keys Fxd Ref Period= and Fxd DayCount= refer to the accrual period and daycount convention of the fixed leg respectively.
The pair Ibor Index= %GBPLibor|6M supplies the conventions of the floating leg index.
The table at the bottom supplies the swap rates and consists of two columns.
The first column must bear the title #Tenor and contain the lengths of the supplied swaps, expressed as time intervals.
The second column must bear the title #Rate and contain the respective swap rates in fractional units, such as 0.04 for a 4% 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.
Alternative Construction of the Table of Swap Rates
It is possible to add a spread column in the table that supplies the swap rates.
You may refer to this similar alternative deposit rates construction for a demonstration on how to use the wizard in this regard.
Below is the result:
The third column must bear the title #Spread and contain the respective floating leg spreads over the index in fractional units, such as 0.01 for a 1% spread over the index.
Information on the meaning of each column can be retrieved by selecting the cell A15 containing the key Set= and have the wizard display the associated information as shown below:
The object &YldCrvSwp_A7:1.1 has been created with a formula that references only the mandatory arguments.
I can see all arguments – including the optional – by selecting the cell A7, as shown below:
Forward Start
Key Forward Start refers to a forward start time interval.
If it is greater than zero, the input swap rates refer to forward starting swaps.
If not specified, the default is zero.
Discounting Curve
Key Disc Curve refers to an optional exogenous discounting yield curve used to perform "dual" bootstrapping.
On its absence, a regular bootstrapping is performed, whereby the input rates are used for both discounting and forecasting.
On its presence, a "dual" bootstrapping takes place, whereby the input rates are used for forecasting forward rates but discounting is done with the exogenous curve supplied here.
This results in a final curve that does not assume that the input market swap rates are risk-free.
This curve can be used to forecast forward rates that take into account the risk premium associated with the market swap rates.
If not specified, the default is none.
Pillar Choice
Key Pillar Choice refers to a list of possible choices that prescribe which date should be used as the respective pillar, when an instrument is used in curve building.
If not specified, the default is Last Relevant Date.
Available types:
Custom Date
A custom date provided by the user should be used as curve building pillar associated with that instrument.
Under this choice, the swap data table should contain an extra column that provides the custom pillar date for each swap.
Last Relevant Date
The last relevant date of the respective instrument should be used as curve building pillar associated with that instrument.
Here the qualifier "relevant" means that the referred date plays a role in the curve construction.
It is therefore possible that the relevant date does not necessarily coincide with the instrument's maturity date.
Maturity Date
The maturity date of the respective instrument should be used as curve building pillar associated with that instrument.
Creating a Forecasting Yield Curve with Exogenous Discounting and Custom Pillars
The following video shows how I instruct the wizard to generate a spreadsheet formula that create a Yield Curve object that has been bootstrapped using an exogeneous discounting yield curve.
I also use the opportunity to show you how to add custom pillars so that the produced table consists of four columns that include a custom pillar date input.
This is the result:
Using the Yield Curve object
With regard to how a Yield Curve object may be used in general, I would refer you to the respective section of the Yield Curve out of Deposit Rates article
With regard to the Yield Curve object with exogenous discounting, the following applies:
The associated curve has been produced with "dual" bootstrapping, where an exogenous discounting yield curve has been used as input and therefore implies rates that are pure forecasting rates.
In particular the implied swap rates will not generally match the input market rates seen here.
Such a yield curve may be also used as input in various Deriscope functions where a provision exists for a forecasting yield curve input, as for example in the functions that calculate the price and yield of a floating rate bond.
Click on YieldCurveSwp.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
