Parametric Yield Curve Fitting to Bond Prices under constraints: The National Bank of Georgia case

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.

If you are interested in non-parametric methods favored by relative value traders as they provide an exact fit to observed bond prices, these 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.

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. 

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

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:B5), which takes one input argument and returns the text &ERROR!_A1:1.1

Part of its input, concretely the handle name &YldCrvBnd_A7:1.1, is created by another formula in cell A7 and contains two bonds and their market prices.

It also contains the object &BndCrvFitM_D2:1.1 of type Bond Curve Fit Method created in cell D2 with the Nelson Siegel method and with the additional pair Parameter Constraint= &Constr_D8:1.1.

The latter is an object of type Constraint created in cell D8 with the Nonflat Boundary 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 D15:F16, 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 E12, 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 D14 containing the Boundary Set key and read the information off the wizard, as shown below: 

The following excerpt is critical:  

A column titled #Params that 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 E5 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 D16 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.

Click on YieldCurveBndNS-ConstParam.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