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:

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:

Recommended for Deriscope starters: The Overview and Quick Guide pages.

### 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:

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:

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