When it comes to building a yield curve out of bond prices, QuantLib can handle both non-parametric and parametric methods, both deliverable to Excel through Deriscope.
The former have been demonstrated at my articles Yield Curve Building in Excel using Bond Prices (QuantLibXL vs Deriscope and Bootstrapping in Excel a Yield Curve to perfectly fit Bloomberg Price/Yield Quotes of US Treasury Bills, Notes and Bonds.
The latter impose a parametric form on some mathematical quantity implied by the produced yield curve.
The following parametric methods are currently implemented in QuantLib:
Some of these methods are extremely popular and in fact are used by central banks according the following table:
Unfortunately these methods are not yet part of QuantLibXL, the standard open source interface of QuantLib to Excel, as Vasily Nekrasov points out in his article. But they are available through Deriscope and I will show you how you may apply the most popular of them in Europe, the Nelson-Siegel-Svensson method.
Nelson-Siegel yield curve fit method
In 1987 Nelson and Siegel thought that by constraining the zero rate to be a special function of the time to maturity with enough free-to-choose parameters, then all actually occurring market curves could be fit by a suitable choice of these parameters.
The special function they came up with consisted of three parts and had the form:
Where β0, β1, β2 and τ are the constant parameters and T is the time to maturity in annual units.
(Note Deriscope refers to these parameters as α, β, γ and κ)
The first term is constant, equals β0 and defines the long term level of zero rates, because the contribution of the other two terms vanishes as T approaches infinity.
The second term introduces an exponential time decay that becomes slower the bigger τ is.
The third term produces either a hump (if β2 is positive) or a trough (if β2 is negative) that occurs at a time governed by τ.
Note also that short dated zero rates equal almost β0 + β1 because the second term converges to β1 and the third term to 0 as T goes to 0. You can easily check this by substituting the exponentials with their equivalent Taylor series.
ex = 1 + x + x2/2 + x3/6 + …
As an example, if I set β0 = 2%, β1 = 2%, β2 = 20% and τ = 2 I get the following time behavior of the individual terms and their sum:
If I change τ to 5 but keep all other parameters the same, the hump is shifted to the right as shown below:
Here is the very simple spreadsheet that I used to create the above charts:
Nelson-Siegel-Svensson yield curve fit method
The Nelson-Siegel method is famous for its simplicity, but it may fail to match the observed zero yields for all maturities in a stressed market environment.
In 1994 Svensson tried to create a more flexible version by adding an additional term to the existing Nelson-Siegel formula that contained two extra parameters.
His nice and simple idea was to create the additional term by reusing the last term of his predecessors' equation, whereby replacing the original parameters β2 and τ with new parameters β3 and τ2.
This is the final formula:
Creating a Yield Curve from bond prices using the Nelson-Siegel-Svensson method
The video below demonstrates how I may use the wizard to create prototype spreadsheet formulas that build a yield curve out of two bonds based on the Nelson-Siegel-Svensson method:
This browser does not support the video element.
The wizard has generated and pasted a bunch of formulas one below the other starting with the initially selected cell.
Below is a screenshot of my spreadsheet after I have removed a few less important optional entries and rearranged the formulas so that they are all visible:
The dependency arrows show the relationships clearly.
The red color cells are those containing the ds formula.
The green color cells contain only a link to some other sell.
All other cells contain no formulas, while blue color indicates the so-called value in a key-value pair as well as data that may be edited by the user.
As you see, cell A1 contains the formula =ds(A2:B8), which takes one input argument and returns the text &EURCrv_A1:1.1
The main structure is the same as that analyzed in my previous article on non-parametric yield curve fit
The object &YldCrvBnd_A10:1.1 created in cell A10 is of type Yield Curve Bnd and serves as a supplier of the referenced bonds and their market prices in rows 22 and 23.
It also sets the following important modelling parameters:
| Curve Fit Method=|| &BndCrvFitM_D1:1.1
| Max Evaluations=|| 10000|
| Max Flat Iterations=|| 100|
| Simplex Lambda=|| 1|
|Param Guess=|| none|
The &BndCrvFitM_D1:1.1 is itself an object that contains details of the chosen fit method. Among else, it contains a possible constraint applied on the Nelson-Siegel-Svensson parameters, which is now set to none in cell E13. This input can be exploited to create a curve where one of the parameters is constrained to equal a certain value, a theme that is explored in my post about curve fitting to bond prices under constraints.
Max Evaluations refers to the maximum number of evaluations of the parameters associated with the selected parametric fitting method that are allowed while trying to produce the optimal set of fitted model parameters.
It may be also set to 0, in which case no calculation takes place and the produced curve is based on the given initial guess parameters.
This turns the produced curve into an evaluator of the parametric curve.
For example, this would allow me to use the parameters for a credit spread curve calculated with bonds in one currency to be coupled to a discount curve in another currency.
Simplex Lambda refers to the scale in the default Simplex optimization routine and is used only if the Bond Curve Fit Method supplies no Optimization details.
Max Flat Iterations refers to the max number of evaluations where no improvement to solution is made.
Click on YieldCurveBndNSSCreate.xlsx to download the spreadsheet produced with the above steps.
Creating the German Government Yield Curve
As I did in my previous article I will replace the default bond data with actual ones corresponding to three German government maturing in 2023, 2028 and 2044 and carrying a fixed coupon of 0%, 0.5% and 2.5% respectively.
I will be also using the same valuation date of 13 Feb 2018.
The final spreadsheet with the yield curve object created in cell J2 is shown below:
Using the Yield Curve object
Similar to what I have done in the respective section of my previous article, I can now generate the discount factors for a series of maturity dates by using the Deriscope function DF:
You notice the discount factors for the top 5 maturities are greater than 1. This is due to the lack of data since our shortest dated bond matures on 14 Apr 2023. Apparently our fitted curve implies negative zero rates in that part of the maturity spectrum.
Comparing against the non-parametric yield curve
It would be interesting to also compare the generated discount factors against those implied by a curve that has been created using the non-parametric approach.
I create the non-parametric curve in cell K1 by recreating a Yield Curve object in cell J2 that uses a clone of the existing Yield Curve Bnd object in cell J14 that has its value associated with the key labelled Use Curve Fit= changed from TRUE to FALSE.
This is the formula that does the trick and creates the yield curve object labelled &EURCrv_K1:1.1.
Now I am in a position to produce the discount factors implied by the yield curve in cell K1:
and here is the chart containing both series of discount factors:
With the sole exception of the dates that fall before 14 Apr 2023, the two curves are in quite good agreement with each other.
Accessing the fitted parameters of the Nelson-Siegel-Svensson equation
The array formula shown below returns the 6 parameters used in the Nelson-Siegel-Svensson formula, with the precaution that the last two refer to the inverses of τ and τ2
and here is the video showing how the wizard generated that formula:
This browser does not support the video element.
Click on YieldCurveBndNSS.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