U.S. Treasury Forward Rate Curves

Note: A revised spreadsheet has been publish for this topic. Please see the new revised version here:

http://pistulka.com/Other/?p=3459

Back in September of last year I wrote three posts and spreadsheets about Gap Analysis, Spot Rates, and Forward Rates. I put all three together in this post and Excel workbook. I have connected the Treasury’s daily Treasury yield curve data web page on the sheet called “Market Data”. The default data for this site is a yield curve for each day of the month: 

 

The next sheet is called “Spot Rates”, where I use the bootstrapping method to calculate the spot rate curve, from the current yield curve. I used nine Treasury yields from the database, ranging from the 6-month bill to the 30-year bond. I interpolated the remaining incremental semiannual periods between these given yields. Below is a small part of a much larger sheet that calculates the spot rates.  The sheet automatically populates the curve and spot rates for the date you choose.

t_Curve3

The next sheet creates a table of forward rates. Below is a partial image of the table. Two things to point out. The top row (Years From Now) are drop down boxes. You can change any year in any cell on row four, but keep them in ascending order so that you get an actual yield curve. The NA() cells are due to GAP analysis. In order to calculate a forward rate with GAP, you need a current rate long enough to calculate the shorter years (i.e. we can’t calculate the forward 30-year rate because we don’t have any rate longer. Other forward rate tables you might have seen, such as Bloomberg’s, might have rates for longer maturities. I believe they use some kind of forecasting method for those years, which I chose not to do here. I needed the #n/a rather than blanks to do the charts.

t_Curve4

The red cell you see is the answer to the entries below:

t_Curve5 The chart sheet shows the current yield curve and any forward curve you pick from the yellow drop down cell (D9). This shows the current curve (blue) compared with the forward curve in 3 years (orange):

t_Curve6

 

This chart shows the change in rates from the current rate to the forward rate, in this case, 3 years from now:

t_Curve7

Download Excel workbook: “Forward_Curves.xlsx”

 

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

3 Comments

  1. Hi Don,
    Just a quick comment in the Spot Rates tab, should the spot rates be the same across the same row?
    For example, row 7 from column k to column BP spot rate for 1.5 year maturity should be all the same as 0.640545???

    1. Hi DC,
      I am not following your question. I will send you snippet of that area. Please point out your question on the snippet or send me back a copy of the spreadsheet with the area of your concern.

      Thanks
      Don

Leave a Reply

Your email address will not be published. Required fields are marked *