9.1 Dynamic Chart

К оглавлению 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97

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 30 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:O1

and click on Insert | Columns. Columns P, Q, and R 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, 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 376 contain monthly

US zero-coupon, yield curve data from January 1970 through June 2000. 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 June 2000, the yield

curve is directly observed from Treasury Bills and Strips in the Wall Street Journal.

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 uparrow

/ 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 S2:AE2, by

entering =S2 in cell B21 and copy it to the range C21:N21.

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

=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:N22. Next

choose Insert | Chart from the main menu. Select an XY(Scatter) chart type and make other

selections to complete the Chart Wizard. Place the graph in the range B3:I20.

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 30 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 reelection.

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 30 years.