9 US YIELD CURVE DYNAMICS

9.1 Dynamic Chart

How does the US yield curve change over time? What determines the volatility of changes in the yield curve? Are there differences in the volatility of short rates, medium rates, long rates, etc.? You can answer these questions and more using a Dynamic Chart of the yield curve, which is based on 32 years of monthly US zero-coupon, yield curve data. 

I have made a major exception for this spreadsheet model and provided the model already built. To load the model, click on Ycdyndyn.xls. I will update this spreadsheet model each month with the latest yield curve data and make it available for free in the "Free Samples" section of http://www.spreadsheetmodeling.com. 

The step-by-step instructions below explain how it you can build this model. The dynamic chart uses “spinners,” which are up-arrow / down-arrow buttons, that allow you to advance the yield curve graph from month to month. This allows you to see a dynamic "movie" or animation of the yield curve over time. Thus, you can directly observe the volatility of the yield curve and other dynamic properties. For details of what to look for, see the discussion below on "using the spreadsheet model."

 FIGURE 9.1  Spreadsheet Model of US Yield Curve Dynamics – Dynamic Chart.

How To Build This Spreadsheet Model.

1. Start with a Spreadsheet Containing the Yield Curve Database. Click on Ycdyndat.xls to open a spreadsheet containing the yield curve database (see Figure 2). Select the range A1:P1 and click on Insert | Columns. Columns Q, R, and S contain three sets of titles for the dataset. Columns S, T, and U contain yield data for bond maturities of one month, three months, and six months (0.833, 0.25, and 0.50 years, respectively). Columns V through AE contain yield data for bond maturities of 1, 2, 3, 4, 5, 7, 10, 15, 20, 25, and 30 years. Rows 2 through 9 contain examples of static features yield curve that can be observed from actual data in a particular month. For example, the yield curve is sometimes upward sloping (as it was in Nov 87) or downward sloping (in Nov 80) or flat (in Jan 70) or hump shaped (in Dec 78). Rows 10 through 401 contain monthly US zero-coupon, yield curve data from January 1970 through July 2002. For the period from January 1970 through December 1991, the database is based on the Bliss (1992) monthly estimates of the zero-coupon, yield curve. Bliss fits a parsimonious, nonlinear function that is capable of matching all of the empirically observed shapes of the zero-coupon, yield curve. For more details see Bliss, R., 1992, "Testing Term Structure Estimation Methods," Indiana University Discussion Paper #519. For the period from January 1992 to July 2001, the yield curve is directly observed from Treasury Bills and Strips in the Wall Street Journal. For the period from August 2001 to July 2002, the yield curve data comes from the U.S. Treasury Department.
   
   FIGURE 9.2  Spreadsheet Containing the Yield Curve Database.

2. Create a Spinner.  Click on View | Toolbars | Forms  from the main menu. Look for the up-arrow / down-arrow button on the Forms toolbar (which will display the word “Spinner” if you hover the cursor over it) and click on it. Then draw the box for a spinner in the range A4:A5.
3. Create The Cell Link.  Right click on the spinner and a small menu pops up. Click on  Format Control  and a dialog box pops up. Click on the Control tab, then enter the cell link A6 in the Cell link edit box, set the Minimum value equal to 2, and click on OK. Test your spinner by clicking on the up-arrows and down-arrows of the spinner to see how it changes the value in the linked cell.
4. Time To Maturity. Reference the Database's Time To Maturity values in the range T2:AG2, by entering =S2 in cell B21 and copy it to the range C21:O21.
5.  Yield To Maturity. Reference the Database's Yield To Maturity values using the Excel HLOOKUP function. The format is =HLOOKUP(Lookup value, Database, Row). The Lookup value is the corresponding Time To Maturity, the database is the range P2:AE600, which has already been given the range name "Database," and the Row is the linked cell A6. Enter =IF(HLOOKUP(B21,Database,$A$6)<0.0001,NA(),HLOOKUP(B21,Database,$A$6)) in cell B22 and copy it across.  
6. Graph the Yield To Maturity by Time To Maturity. Highlight the range B21:O22. Next choose  Insert | Chart from the main menu. Select an  XY(Scatter)  chart type and make other selections to complete the Chart Wizard.
7. Three Titles. Reference the Database's three columns of Title values using the Excel HLOOKUP function. The format is same as above, except that the Lookup value will be the column headings ("Title 1", etc.) that we wish to reference. Enter =HLOOKUP("Title 1",Database,$A$6) in cell B2, =HLOOKUP("Title 2",Database,$A$6) in cell E2, and =HLOOKUP("Title 3",Database,$A$6) in cell I2. To format the date title, select cell I2, click on Format | Cells, click on  Date in the Category list box, click on Mar-98 format in the Type list box, and click on OK.

Using The Spreadsheet Model.

To run the Dynamic Chart, click on the up arrow of the spinner. The movie / animation begins with some background on the yield curve's static features. In the 30 year database we observe: 

• four different shapes: upward-sloping, downward-sloping, flat, and hump-shaped, 
• the overall level of the yield curve ranges from low to high, and
• the amount of curvature at the short end ranges from a little to a lot. 

Keep clicking on the spinner and you will get to the section of the Dynamic Chart covering 32 years of the US yield curve history. This section shows the yield curve on a month by month basis. For example, Figure 3 shows the US yield curve in November 1970.

FIGURE 9.3  Spreadsheet Containing the Month By Month History – Dynamic Chart.

Keep clicking on the spinner and you will see the yield curve move around over time. By observing this movie / animation, you should be able to recognize the following key dynamic properties of the yield curve:

• short rates (the 0 to 5 year piece of the yield curve) are more volatile than long rates (the 15 to 30 year piece),
• the overall volatility of the yield curve is higher when the level is higher (especially in the early 80's), and
• sometimes there are sharp reactions to government intervention.

As an example of the later, consider what happened in 1980. Figure 4 shows the yield curve in January 1980.

 FIGURE 9.4  Spreadsheet Showing The Yield Curve in January 1980.

Short rates were around 12% and long rates were at 10.7%. President Jimmy Carter was running for re-election. He wished to manipulate the election year economy to make it better for his re-election bid. His strategy for doing this was to impose credit controls on the banking system. Click on the spinner to see what the reaction of the financial market was.

 FIGURE 9.5  Spreadsheet Showing The Yield Curve in March 1980.

In two months time, the short rate when up to 15.5%, an increase of 3.5%! What a disaster! This was the opposite of the reaction the Carter had intended. Notice that long rates when up to 11.7%, an increase of only 1%. Apparently, the market expected that this intervention would only be a short-lived phenomena. Carter quickly realized what a big political mistake he had made and announced that the credit controls were being dropped. Click on the spinner to see what the reaction of the financial market was.

 FIGURE 9.6  Spreadsheet Showing The Yield Curve in April 1980.

Short rates dropped to 10.9%! A drop of 4.6% in one month! The high interest rates went away, but the political damage was done. This is the single biggest change in the yield curve in 32 years.