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 previous article.

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:

Simple Polynomial

Nelson-Siegel

Exponential Splines

Cubic Splines

Spread

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

**β**

**is negative) that occurs at a time governed by**

_{2}**τ**.

Note also that short dated zero rates equal almost **β**_{0}** + ****β**** _{1}** because the second term converges to

**β**

**and the third term to**

_{1}**0**as

**T**goes to

**0**. You can easily check this by substituting the exponentials with their equivalent Taylor series.

e^{x} = 1 + x + x^{2}/2 + x^{3}/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

**β**

**and**

_{3}**τ**.

_{2}This is the final formula:

### Creating a Yield Curve from bond prices using the Nelson-Siegel-Svensson method

As the video further below demonstrates, I search within the *Type Selector* of the Deriscope wizard for a type called *Yield Curve*, check the *Use Bonds* flag inside the input parameters screen and then check the *Use Bond Curve Fit Method* flag. This last action generates the following additional input keys:

Bond Curve Fit Method= |
&BndCrvFitM_A33:1.1 |

Bond Max Evaluations= |
10000 |

Bond Param Guess= |
none |

Simplex Lambda= | 1 |

Bond Max Flat Iterations= | 100 |

The *&BndCrvFitM_A33:1.1 *is itself an object that contains details of the chosen fit method.

*Bond 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.

*Bond Max Flat Iterations* refers to the max number of evaluations where no improvement to solution is made.

I finally go inside the *Bond Curve Fit Method* object where I choose the *Svensson* method.

There I also have the option to overwrite the default *Simplex* optimization method with a custom one and also overwrite with custom numbers the default bond weights based on inverse duration.

The video below shows the complete sequence of steps:

After pressing the *Go* button, Deriscope generates the respective formulas – a total of 7 - and pastes them in the spreadsheet.

In the picture below, I have removed optional entries and shifted the formulas around so that they are all visible.

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.

The dependency arrows show the relationships clearly.

Understanding the formula

As you see, cell **A1** contains the formula *=ds(A2:B18;A20;A21:B23)*, which takes three input arguments and returns the text *&YldCrv_A1:1.1*

Apart from the following extra keys, the structure is the same as that analyzed in my previous article on non-parametric yield curve fit

*Bond Curve Fit Method*=

*Bond Max Evaluations*=

*Simplex Lambda=*

*Bond Max Flat Iterations=*

### 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 cloning the existing curve object of cell **J2** and changing to **FALSE** the value associated with the key labelled *Use Bond Curve Fit Method=*.

This is the formula that does the trick and creates the yield curve object labelled *&YldCrv_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:

You may download the spreadsheet produced with the above steps here.

Below is a video with voice narration on the current subject:

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