I have posted spreadsheets on forward yield curves in the past, but this one is less cumbersome in that the spot rate calculations are simplified, compared to the previous spreadsheets. In addition, I added a forward math sheet to explain the calculations.
Like my other spreadsheets that require spot rates, I start with a source for current Treasury yields. You can copy/paste them directly into the spot rate sheet in the yellow cells:
Next, because we don’t have a yield at every coupon payment, we need to interpolate a yield at each of these periods. This is accomplished through a linear interpolation. This linear interpolation looks for periods that we do have a yield and the next period that we have a yield and interpolates between the two. The formula below is entered next to the first 6 month yield and copied down:
This method differs from a linear interpolation that calculates a straight line through the data points. The chart below shows the difference. Note how the above method follows the actual yields:
Now that we have a yield at each coupon payment (every six months) we can calculate the spot rates. Spot rates are considered zero-coupon bonds. The spot rate calculations assume the given yields represent par bonds. A discount factor is calculated for each coupon payment, that is actually a present value calculation. At each coupon payment all the previous discount factors are summed and multiplied by half the yield (which is also a coupon payment). We can now calculate the present value of all the previous coupon payments. The last cash flow is the 100 par value (or corpus) plus the last coupon payment. Since we now have the present value of the coupon payments and the future value of the last payment, we can calculate the semiannual return or spot rate. Below shows an example of the calculation of spot rates:
Next we consider the math behind forward yields. Implicit in every spot rate curve is a forecast of future yields. Assume that we want to know what the current yield curve is implying about the two year Treasury yield, five years from now.
The calculations for forward yields are actually break even calculations, or what Bloomberg used to call “gap rates”.
If investors are willing to pay 2.4393% for a 5-year note, and forego earning a higher yield of 2.5463% on a seven year note, the five year investors are implying that when the five year note matures, there is a two year yield that the funds can be reinvested at which will equal the total return of the seven year yield. The calculations for that two year yield are shown above and result in a forecast of 2.814% for the two year note, five years from now.
Now we convert the above formulas into a yield curve table:
The columns, going down from the six month to the 30-year represents a yield curve in the future. The column with the one in the dark blue cell, is the forward curve, one year from now. Likewise, the column with a five in the dark blue cell, represents the yield curve five years from now. Each of the years in the dark blue cells are dropdown boxes that allow for any year you wish to view.
The rows on the other hand, represent the forward yield for that particular term. The one year row shows how the one year yield will change at each year going out into the future.
Next to the table are two yellow input cells that allow you to find an implied future yield at some year in the future.
In the chart sheet, you can compare any future yield curve, with the current spot rate curve:
Before you download a spreadsheet, note that there are two spreadsheet versions. One has a format extension of .xlsm. That is because there is a very small VBA program included that formats the Y axis of one of the charts and makes the chart easier to read. Some firms will not allow downloads with VBA included. If you find this to me a problem, you can download a second version with no VBA and adjust the chart manually.