Yield Curve Smoothing With The Nelson-Siegel-Svensson Model

Note: I found an error in the spreadsheet and repaired it, so if you downloaded the spreadsheet before 3/21/19, download it again.

 

This post starts by entering the U.S Treasury Yield curve by copy/pasting the rates from the Treasury web site.

The yields are then interpolated in order to calculate spot rates using the bootstrap method.

 

The spot rates are then linked into sheet “NSS”, and are used to smooth the yield curve using the Nelson-Siegel-Svensson Model.


Excel’s Solver is used to calibrate the model:

Download: NSS: http://pistulka.com/Excel_Shared/NSS.xlsx

Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

4 Comments

  1. Thank you for this Don. I was wondering how you were able to arrive at the values for the betas and the taus. I’m planning to include more time period to this model and was wondering if this will affect the initial figures for the variables you initially defined.

    1. Hi John,
      Thank you for your comment.

      The values in cells S9 to S14 are the results of running the Solver function in Excel. The initial values can be arbitrary. If you were to set all those values to one, and then run Solver, each N-S-S-M formula in column F will be recalculated until the sum of each calculation is equal to the minimum amount. (You may have to run solver twice).

      So, the answer to your question as to how I arrive at the values is that I did not arrive at the values, solver did.

      Thanks again
      Don

  2. Hi Don,
    Thank you for the clear explanation and Excel example about the Svensson Model implementation.
    Kind regards,
    Eduardo

Leave a Reply

Your email address will not be published. Required fields are marked *