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

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.1is 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

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:

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

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