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 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.
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:B8;A10;A11:C13), which takes three input arguments and returns the text &YldCrv_A1:1.1
The first input argument contains the accrual period and daycount convention of the fixed leg.
The last argument A11:C13 contains a table consisting of three 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.
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.
Because the cell A1 containing the handle name of the created Yield Curve object is selected, the full contents of that object are displayed in the Browse Area of the wizard.
There you see the following defaults that are not part of the spreadsheet input.
Swap Ibor Index= %EURLibor|6M
Swap Forward Start= %0D
Swap Discounting Curve= none
Swap Pillar Choice= Last Relevant Date
Let me next explain the last three pairs.
Swap Forward Start
Key Swap 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.
Swap Discounting Curve
Key Swap Discounting 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 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.
Swap Pillar Choice
Key Swap 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.
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.
The maturity date of the respective instrument should be used as curve building pillar associated with that instrument.
Creating a Yield Curve with Dual Bootstrapping and Custom Pillars
The following video shows how I set an exogenous yield curve in the wizard so that the generated spreadsheet formula creates a dually bootstrapped Yield Curve object.
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 dually bootstrapped Yield Curve object, 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.
You may download the spreadsheet produced with the above steps here
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