8.1 Obtaining It From Bond Listings

Problem. Given bond prices and yields as published by the financial press or other information sources,

obtain the U.S. Treasury Yield Curve.

Solution Strategy. Collect information about Treasury Bills and Treasury Strips for a variety of different

maturity dates. Graph the ask yield of these bonds against their time to maturity. See the figure below.

FIGURE 8.1 Spreadsheet Model of The Yield Curve – Obtaining It From Bond Listings.

How To Build This Spreadsheet Model.

1. Inputs. Enter the today's date in cell B4.We wish to graph the zero-coupon yield curve, so we

will use zero coupon bonds (i.e., bonds that make a single payment on the maturity date and

nothing before then). We will use U.S. Treasury Bills for maturities of less than one year and U.S.

Treasury Strips for maturities of one year or more. In the financial press, identify 1, 3, and 6

month Treasury Bills and the 1, 2, 3, 4, 5, 10, 15, 20, 25, and 30 year Treasury Strips. We use

more frequent maturities at the short end (1, 3, and 6 month), because often there is more

curvature in the yield curve for short maturities. For each Treasury Bill or Treasury Strip, enter

the maturity date in the range B5:B17 and yield to maturity (the "ask yield" column in the Wall

Street Journal) in the range D5:D17 . When entering the maturity date, be sure to use four-digit

years ("2030"), rather than two-digit years ("30"). Excel assumes that two-digit years in the range

00 to 29 are really 2000 to 2029, but that years in the range 30 to 99 are really 1930 to 1999! This

distinction doesn't matter for most applications, but it does matter for long-term bonds maturing

in 2030 and beyond!

2. Time To Maturity. For a given bond, Time To Maturity = Maturity Date - Today's Date. We can

calculate the fraction of a year between two calendar dates using Excel’s Analysis ToolPak Add-

In YEARFRAC function. Excel’s Analysis ToolPak Add-In contains several advanced date

functions that are useful in finance.

Click on Tools, Add-Ins, check the Analysis ToolPak checkbox on the Add-Ins dialog

box (see Figure 2 below), and click on OK.

FIGURE 2. The Add-Ins dialog box.

The date function we will use is YEARFRAC(Today's Date, Maturity Date). Enter

=YEARFRAC($B$4,B5) in cell C5. The two $ in $B$4 lock in the row and column when

the cell formula is copied. Copy cell C5 to the range C6:C17.

3. Graph the Yield Curve. Highlight the range C5:D17 and then choose Insert Chart from the

main menu. Select an XY(Scatter) chart type and make other selections to complete the Chart

Wizard.

The November 22nd, 1999 U.S. Treasury Yield Curve demonstrates some frequently-observed properties

of the yield curve. Often, there is a sharp rise at the short-end (up to 1 year to maturity), a gentle rise after

that, and a small dip at the long-end (past 20 years to maturity).

Problem. Given bond prices and yields as published by the financial press or other information sources,

obtain the U.S. Treasury Yield Curve.

Solution Strategy. Collect information about Treasury Bills and Treasury Strips for a variety of different

maturity dates. Graph the ask yield of these bonds against their time to maturity. See the figure below.

FIGURE 8.1 Spreadsheet Model of The Yield Curve – Obtaining It From Bond Listings.

How To Build This Spreadsheet Model.

1. Inputs. Enter the today's date in cell B4.We wish to graph the zero-coupon yield curve, so we

will use zero coupon bonds (i.e., bonds that make a single payment on the maturity date and

nothing before then). We will use U.S. Treasury Bills for maturities of less than one year and U.S.

Treasury Strips for maturities of one year or more. In the financial press, identify 1, 3, and 6

month Treasury Bills and the 1, 2, 3, 4, 5, 10, 15, 20, 25, and 30 year Treasury Strips. We use

more frequent maturities at the short end (1, 3, and 6 month), because often there is more

curvature in the yield curve for short maturities. For each Treasury Bill or Treasury Strip, enter

the maturity date in the range B5:B17 and yield to maturity (the "ask yield" column in the Wall

Street Journal) in the range D5:D17 . When entering the maturity date, be sure to use four-digit

years ("2030"), rather than two-digit years ("30"). Excel assumes that two-digit years in the range

00 to 29 are really 2000 to 2029, but that years in the range 30 to 99 are really 1930 to 1999! This

distinction doesn't matter for most applications, but it does matter for long-term bonds maturing

in 2030 and beyond!

2. Time To Maturity. For a given bond, Time To Maturity = Maturity Date - Today's Date. We can

calculate the fraction of a year between two calendar dates using Excel’s Analysis ToolPak Add-

In YEARFRAC function. Excel’s Analysis ToolPak Add-In contains several advanced date

functions that are useful in finance.

Click on Tools, Add-Ins, check the Analysis ToolPak checkbox on the Add-Ins dialog

box (see Figure 2 below), and click on OK.

FIGURE 2. The Add-Ins dialog box.

The date function we will use is YEARFRAC(Today's Date, Maturity Date). Enter

=YEARFRAC($B$4,B5) in cell C5. The two $ in $B$4 lock in the row and column when

the cell formula is copied. Copy cell C5 to the range C6:C17.

3. Graph the Yield Curve. Highlight the range C5:D17 and then choose Insert Chart from the

main menu. Select an XY(Scatter) chart type and make other selections to complete the Chart

Wizard.

The November 22nd, 1999 U.S. Treasury Yield Curve demonstrates some frequently-observed properties

of the yield curve. Often, there is a sharp rise at the short-end (up to 1 year to maturity), a gentle rise after

that, and a small dip at the long-end (past 20 years to maturity).