In the last post, Forward Rates Part 1: Gap Analysis, we calculated the forward rate for a two year fixed rate investment, five years from now. Here is a review of the math:

Why is everything multiplied by 2? That’s because we are assuming semiannual compounding.

- First we calculate the value of $1 in 5 years at a rate of .6931% to be $1.03520046.
- Next we calculate the value of $1 in 7 years at a rate of 1.0528% to be $1.07627145
- Then we calculate the rate of return it would take to grow from $1.03520046 to $1.07627145 in two years or 1.9549%.

You will find in the workbook a table that takes the spot rates for the Treasury yield curve (my next post will be calculating spot rates) and calculates the forward rates at future years (image at bottom of this post).

For this example table, in order to use Gap Analysis in quarterly increments, we need a spot rate from the 3-month bill to the 30-year bond, quarterly for a total of 120 spot rates. When you download the workbook your will see 111 hidden rows and 108 hidden columns. The quarterly spot rates were interpolated between the on-the-run Treasury spot rates. Is that an accurate indication of the current spot rates? No! Remember this is a workbook to help understand the calculation of forward rates. Brokerage firms that have access to all the yields on all the Treasury issues and STRIPs, will have better indications of actual spot rates.

Why the blank green cells? For Gap you need a longer term equal to the years in the future plus the years of the forward rate. Conditional formatting makes cells that don’t meet this criteria green.

Below is the formula that is used in the table to calculate the 1.9549% forward rate on the 2-year, 5 years from now. It look at the cell above it that holds the years forward (in this case 5). It does a lookup to find the 5-year yield. Then it looks to the left column that holds the forward term (in this case 2). It adds the 5 and the 2 to get 7 and looks up that yield, and the math is the same as the “2-year Forward Rate Math” image above.

Download workbook “Forward_Rate_Table” from:

http://www.pistulka.com/Excel_Shared/

Downloads Written in Excel 2013

Pingback: U.S. Treasury Forward Rate Curves | Excel@CFO