Mar 05

Fitting Treasury yield curve to cubic polynomial - 7 min screencast

by David Harper, CFA, FRM, CIPM

FRM |

The single cubic isn't good enough to describe the whole term structure, but it's a good place to start. First, I grabbed actual Treasury rates...

ustreasureyss

...into Excel. Second, I want to find the best fit cubic polynomial. That just means that I want to solve for r0, a, b, and c in the function:

cubicpoly

We can do this by minimizing the sum of squared errors; i.e., the sum of the squared difference between an actual Treasury yield and a yield predicted by the cubic polynomial. We can use Excel's solver for that! We ask it to minimize the sum of squared errors (the orange target) by changing the four parameters in the cubic.

cubicSolverssn

 solverScreenShot

Here is the seven minute screencast.

Comments

  1. windfactor 19 Jul 2008

    Question : Why are you using the yields directly and not the derived spot rates? I understand that the term structure should be composed of spot rates against time and not the treasury yields directly against time unless these treasuries are zero coupon, which I presume they are not. I will appreciate your response.

  2. David Harper, CFA, FRM, CIPM 20 Jul 2008

    windfactor,

    You are right. Most correct would be the theoretical spot rate curve (i.e., underling default-fee, zero coupon bonds) but I was just trying to illustrated the simple application of solver to a yield curve (as a mathy exercise). Taking the easy road but i agree with you… David

Leave a Comment