Both the *Nelson Siegel* method and its *Svensson* extension are very popular among central and other banks when the time spectrum of interest rates needs to be derived from market bond prices.

As I have explained in my related article, QuantLib is capable of solving the respective parametric equations treating the model parameters as unknown.

Due to the intuitive interpretation of the model parameters, it is often sensible to demand that one or more of them equal some a priori fixed values.

For example, the *Nelson Siegel* method relies on the four parameters *β**0, **β**1, **β**2* and *τ* according to the equation for the zero yield *r*:

from which it follows that the parameter *τ* controls **a)** the rate of decay of *r* as maturity *T* increases and **b)** the time location of the observed hump (or trough) introduced by the third term.

Thanks to a Deriscope user who let me know of the National Bank of Georgia practice of solving the *Nelson Siegel *equation under the constraint of a fixed, exogenously provided *τ*, I thought I could show you how to exploit QuantLib's constrained optimization routines to solve the same problem in Excel.

Creating a Yield Curve using the Nelson-Siegel-Svensson method under constraints

The video below demonstrates how I can use Deriscope to generate a *Yield Curve* object out of a set of market bond prices using the *Nelson-Siegel* method under a constraint applied on some of the model parameters.

In the picture below, I have shifted the formulas around so that most of them are visible. The *Yield Curve* object is created by the formula in cell **A1**.

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:B10;A12;A13:B15)*, which takes three input arguments and returns the text &ERROR!_A1:1.1

The third range *A13:B15* supplies the market bond prices.

The first range *A2:B10* includes the keys *Bond Curve Fit Method* and *Parameter Constraint*.

The former supplies the object *&BndCrvFitM_D1:1.1* of type *Bond Curve Fit Method* created in cell **D1** with the *Nelson Siegel* method.

The latter supplies the object *&Constr_D6:1.1* of type *Constraint* created in cell **D6** with the *Nonflat Boundary Constraint* specification.

This particular constraint specification needs to know the total number of parameters involved in the optimization problem plus the subset of parameters that are wished to be constrained within certain ranges.

As you see, the key *Parameter Count *is set with the value of **1** and the key *Boundary Set* contains as value the table in range *A13:B15*, which treats only the first model parameter (index = **1**) as constrained parameter with **[-1,1]** being the respective allowed range.

Resolving the error in cell A1

You may have guessed by now the reason why an error appears in cell **A1**.

If not, a simple cell **A1 **selection while the wizard is open would display the explanation, as this screenshot shows:

It must be noted here that the Deriscope wizard always creates a *Constraint* object with the *Parameter Count *set to **1** by default. It is my job of making the adjustments needed by the particular context where this object is used.

So I go ahead and set **4** in cell **E10**, since I know that the *Nelson Siegel* model uses **4** parameters.

The *Yield Curve* object is now created successfully in cell **A1** as shown below:

Defining the constraint so that the parameter τ is fixed

Note the default wizard-generated constraint affects only one parameter, the one referred by the index **1** as entered in the *#Params* column.

Which parameter is it?

All I need to do is selecting the cell **D12** containing the *Boundary Set* key and read the information off the wizard, as shown below:

The following excerpt is critical:

A column titled#Paramsthat contains the parameter indices, whereby the first parameter is indexed by 1.Τhe exact parameter ordering depends on the chosen parametric model and can be read off the information available at the respective object.

This tells me that **1** refers to the **first** parameter.

But which parameter is the **first**?

I am advised to lookup the information associated with the respective model.

So I select the cell **E4** containing the *Nelson Siegel* method and I see the following:

I thus realize that the parameter ordering is *α, β, γ, κ*, where *κ* is the multiplier of the time *T* and therefore *κ** = 1/**τ*.

Let's say I want *τ* = **2** years.

This means I want my fourth parameter *κ* to equal **½ = 0.5**.

So I should change the parameter index in cell **D14** from **1** to **4** and set the respective low and high values both equal to **0.5**.

Unfortunately QuantLib cannot currently process equal low and high values.

The simple trick is to set low = **0.5** and high = **0.50000001**.

This indeed works and I get a yield curve with *k* practically equal to **0.5**.

Accessing the fitted parameters

The following video shows how I can see the parameters resulted by the QuantLib optimization algorithm and also how I can create a spreadsheet formula that outputs these parameters in the spreadsheet.

Here is how the fitted parameters appear as the output of the array formula just generated by the wizard:

You notice the value of the fourth parameter equals (almost) **0.5** as expected due to the imposed constraint.

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

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