Note: I have made some changes to the spreadsheet since the original post. The Treasury data source was not pulling in the new rates as I had expected, so I hard wired a table of rates for February of 2016. A link to the Treasury page is still there, so you can manually update the data with a copy/paste into the table.
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.
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.
The red cell you see is the answer to the entries below:
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):
This chart shows the change in rates from the current rate to the forward rate, in this case, 3 years from now:
Download Excel workbook: “Forward_Curves.xlsx”