Yield Curve Building in Excel using Deposits, Futures and Swaps
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 a mixture of deposit rates, futures prices and swap rates.
I have already written how you may build a yield curve using a single type of market instruments, such as deposits, futures or swaps.
The usual practice though is to combine all these instruments as input data to a single curve generation.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Creating a Yield Curve
The Deriscope wizard lets you select multiple instruments types as demonstrated in the following video.
To keep things simple I make no other selections or fine tunings.
This is the generated spreadsheet formula, after I have reshuffled the formulas so that they fit in the visible area:
Understanding the formula
As you see, cell A1 contains the formula =ds(A2:B4,A6:A9), which takes 2 input arguments and returns the text &YldCrv_A1:1.1
The first input argument is the range A2:B4 which contains global conventions that apply to all deposit, futures and swap input data.
Here you see only the Currency= %GBP because the wizard has pasted only the mandatory input parameters.
The second argument is the range A6:A9 which contains links to the three objects:
&YldCrvDep_A11:1.1
&YldCrvFut_D1:1.1
&YldCrvSwp_D11:1.1
which are generated by separate formulas and contain the tables with the deposit, futures and swap market quotes respectively.
Browsing through the Yield Curve Object's Contents
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 even default content that is not part of the spreadsheet input as demonstrated below:
Browsing through the Cash Flows
The above video also shows how I am able to see all the details relating to the market instruments I use as input to the curve creation, by simply clicking on the lens icon to the right of the key _Cash Flows= in the wizard.
Then the wizard displays the cash flows as seen below:
I can even see these data directly in the spreadsheet as the output of a special formula that links to the Yield Curve handle in cell A1 so that they will be dynamically adjusted if the Yield Curve changes.
I do so by selecting an empty cell where I want to paste the generated formula and clicking on the Go button.
Here is the resulting array formula, which makes use of the Deriscope function Show and references the cell A1:
The column with the #CashFlows title contains handle names of objects of type Set, as one can tell from their $ prefix followed by the word Set, which indicates that these objects are not created by some ds() spreadsheet formula, but they are rather controlled directly by Deriscope. They have a so-called System access type and a transient lifetime so that they do not consume permanent RAM space.
Nevertheless, Deriscope recreates them on demand, a fact that enables me to browse through their contents as soon as I select a cell with their handle name.
For example, if I select the cell M7 containing the handle name $Set#10, I can see in the wizard the detailed cash flows of my last swap, as below:
I may also repeat the previous step and "export" to the spreadsheet the shown object contents as a dynamic output of a spreadsheet formula:
You may notice a few 0 values in the #Amount and #Rate columns. The respective rows refer to cash flows of the fixed leg of the referenced swap, as you can tell from the corresponding FixedRate entries in the #Type column. This is obviously wrong and represents a minor nuisance stemming from the way these cash flows are produced by QuantLib.
Concretely, QuantLib starts the curve calibration by resetting all market instruments to have zero quotes before feeding them to a solver that attempts to find quotes that lead to a curve that implies the original market quotes as closely as possible.
The reason for the appearing zeros is that the cash flows are generated from the perturbed instruments before the calibration is completed!
Dealing with a Curve Bootstrapping Failure
Deriscope's wizard can help you locate the source of bootstrapping failures.
As a demonstration, let me set the first swap rate to 100% in order to cause a failure with the yield curve creation in cell A1.
If I then select the cell A1, I can read the diagnostic message at the bottom of the wizard, as shown below:
Normally, I would then click on the blue word here in the phrase "Details here", which would take me to this screen:
This message is generated straight from QuantLib and informs me that the fifth alive instrument with maturity March 1st, 2019 failed. My fifth instrument is exactly the one-year swap with the 100% rate, while the previous four instruments are the two deposits and the two futures.
If for any reason I cannot understand the reason of that instrument's failure, it might be helpful to inspect the detailed cash flows of my market instruments.
Deriscope supplies the detailed cash flows of all involved instruments, even in the case of a bootstrapping failure, as soon as I click on the blue hyperlink word "[Set]".
This is what I see, if I do so:
You notice, this is the same cash flow table as before, with the difference that the #ImpliedQuote column is now not filled, since the bootstrapping has failed.
Using the Yield Curve object
For this I would refer you to the respective section of the Yield Curve out of Deposit Rates article
Click on YieldCurveDepFutSwp.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
