bottom up and top down approach
07 Sep 2008
Feb
25
Excel solver performs non-linear interpolation to build a Treasury yield curve- 7 min. screencast
by David Harper, CFA, FRM, CIPM
Excel's solver has my vote for most under-rated feature (more information on premium version here, but regular solver is built into Excel). In the brief screencast below, I use it to perform non-linear interpolation. It's easier than you think.
We first need a term structure mode. A compact model is the Nelson and Siegel which has four parameters:
Then we only need the actual observations. I pulled recent U.S. Treasury yields from the U.S. Treasury. You can see why we need to interpolate; the U.S. Treasury only issues coupon-bonds at a few maturities:
With Excel's fabulous solver, we can fit the Nelson and Siegel function to the actual data. Here are the steps:
- Input four dummy parameters (blind estimates) for alpha1, alpha2, alpha3, and beta
- Calculate the squared residuals (the square of the difference between the actual Treasury yield and the predicted y(t) given by the Nelson and Siegel function)
- Sum the squared residuals. This is the target cell shown in orange. We want to minimize this sum. The minimum squared residual ought to force (optimize) a good fitting non-linear function
- Use solver. Set the target cell (the sum of squared residuals) equal to Min (i.e., minimize the value of the target cell) by changing the four parameters:
Solver optimizes and changes the parameters for us. The result is a nonlinear function that fits the Treasury yields quite well (the red dots are actual, the blue line is the Nelson & Siegel function with the "optimized" parameters):
Here is the screencast, step by step:
Comments
Thanks for making this so simple David. Excellent demonstration.
windfactor - Thanks for liking it! David
Leave a Comment