This is a stochastic tool that uses forecasts of what the Treasury yield curve will look like in six months, and generates holding period returns (total returns) at various points out the curve. The results are based entirely on the users best-guess estimates of the shape of future yield curves. This kind of analysis can be helpful in a number of ways. First, it forces those who are responsible for fixed income management to express themselves in concrete terms about the whole yield curve, not just what the Fed might do. Next, it allows the user to convert their forecasts into actual returns, which might not be obvious by concentrating just on the future shape of the yield curve. In addition, it allows for probability analysis. Enter a required return and the probability of receiving that return is generated for each term.
Even though the Treasury curve is used, it can be used for other fixed income products by applying a spread to the Treasury curve. Keep in mind that when you first download the spreadsheet, calculate is set to “Automatic Except for Data Tables”. This will allow inputs (in the yellow cells) without the delay of calculating the data table each time. After your inputs are complete, click “Calculate Now” or push the F9 button to calculate the whole worksheet, including the data table. Wait until the status bar in the bottom left hand side of the screen changes from Calculate to Ready. If your computer is fast enough, you can ignore the above warning, switch the calculation to “Automatic”, and leave it there.
The spreadsheet will fetch the current Treasury yields from Yahoo Finance®, by clicking the green button marked “Refresh Current Yields”:
To the upper right is the time stamp for the last time the button was clicked:These yields are the starting points on which the six-month forecasts are based. To simplify calculations, it is assumed that these are all par securities (price = 100), with the yield equal to the coupon rate. The next step will be to enter three forecast ranges. I suggested a forecast range for the Federal Reserve raising, holding steady, and dropping rates, but those assumptions are arbitrary and the forecasts are not required to reflect those suggestions:
Don’t forget to enter the probability of each forecast range being achieved. The sum of all three probabilities must equal 100%.
Before we go on, we can take a look at what the ranges of each forecast look like. You can click on each forecast range by picking the forecast in the upper right hand side of the chart. Forecast #1 is a “Fed Raises Rates” scenario. The first curve (blue) shows an aggressive Fed that raises the short end quickly, but has the effect of “flipping the curve”, with longer rates holding or actually dropping. The second curve depicts a curve that moves higher, taking the short to intermediate terms higher, but longer terms not moving that much. I wont go into describing each scenario, since they all will be changed by the user.
Now we move on to calculating the rates of return on each term. I realize that the holding period returns could have been all calculated in a single table, but I wanted to explain the process at each step. Here are some assumptions used for the calculations. Although Treasuries are based upon an actual/actual day count, I wanted to make this example as generic as possible and not tied to actual dates.
The first step is to calculate the prices at the end of six months for each term, given the yields provided. Note that the 6-month term price will always be 100 and so will the holding period return. Even though the returns will be the same for each scenario, it is still an alternative that needs to be considered. I used the PRICE function to calculate the prices. Over to the left are greyed out cells with dates that I used for this function, which depict a six month holding period. Next, we determine the coupon payments that will be received in six months:
The above formula is used to calculate the next table to get the annualized return for the six month period. The denominator will always be 100, since all starting prices were assumed to be par.
In order to compare these returns to other alternative securities that are quoted on a semiannual basis, we than convert the annualized returns to semiannual returns:
These are the six month returns and you would expect this to be the last table, except that I used the RANDBETWEEN function which requires the lowest value first. Therefore we have one more table to show the MIN and MAX return for each forecast:
Now we are ready to generate random returns, between the MIN and MAX return for each forecast and each term. These random returns are calculated 1000 times for each term and are weighted by the probability of each forecast, with the use of a data table. I am not going to take the time to explain how the data table works, but a Google search of “Monte Carlo Data Table” will show plenty of examples.
Now, what do we do with all of these random calculations? I am going to show the results for the 10-year note. First, we can enter an expected return in the yellow input cell (example 1.0%). Under these conditions, there is only a 15% chance that the 10-year note will earn a total return of 1.00% or more. In addition, we can see the standard deviation of returns for the 10-year note (1.235%).
Next, we can see the returns broken down into 12 statistical bins. From that we calculate the probability of the returns falling into each bin.Now we chart the bins, along with the probability of falling in each bin as the data label.
For example, there is a 27.4% chance that the 10-year note return will be greater than or equal to -.90% and less than .17%.
Excel Spreadsheet Download Below. Excel 2016