Home » Excel Spreadsheet » Spreads Between the 10-Year Treasury and 30-Year Mortgage

The relationship between 10-year U.S. Treasury Notes and 30-year mortgage rates has been used for years by investors. One of the obvious problems with using this relationship is that we are comparing an option-free Treasury note with an amortizing loan that is callable at any time. The average life of a 30-year mortgage with a 4.0% interest rate implies a CPR of approximately 6.09 in order for the average life (the time it takes to return the principal) to equal 10 years.

Using weekly data from The St. Louis Fed (FRED), going back to April of 1971, the spread has averaged 1.69%, has been as high as 5.35% and as low as .04%. These outliers are undoubtedly either bad data points or the results of markets in turmoil. Using smaller time periods that somewhat resemble your own forecast of interest rates, might give a better indication of what spread can be expected.

This last week I put together a spreadsheet you can download called “Mort_10y_Spread“. Not only is it not tested, I have not had a change to see if new downloaded data works correctly.  I will make necessary changes as problems appear.

New weekly data can be downloaded with the click of a button. As I mentioned above, the data comes from the FRED database. You will need to download the free add-in, if you wish to download future data, from the St. Louis Fed at this website:

https://research.stlouisfed.org/fred-addin/install_windows.html

After the add-in is loaded, with the workbook open to the sheet “Data”, click the “Update Data” button on the add-in. It may take a some time, depending on your computer. The spreadsheet does not need the add-in to run. Use it only when you want to update the newest data.

Spread1Go to the “Chart” sheet. The first thing you will notice is the large chart with the 10-Year Treasury yields and the 30-Year mortgage rates. On the far right and far left are two yellow input cells. They are drop down boxes with all the dates. Choose the starting date on the left and the ending date on the right. The gray bars are recessions.

Spread2

Just above the chart is a check box for spread. When the box is checked, the chart displays the spread between the rates:

Spread3

There are statistical data and charts shown below the chart. Remember, all the analysis below the chart is based on the time period you chose for the large chart.

Spread4

Along with the current spread, there are other breakdowns in the spreads for the period chosen. You can also exclude a percentage of outliers, from both the highest and lowest spreads. The R squared and correlation coefficient numbers give an indication of the fit between the two rates. Also, there is a forecast tool. For example, if you thought the 10-year Treasury yield would rise to 4.0%, given the data period, a 4.0% 10-year Treasury would indicate a 5.6% 30-year mortgage rate.

Spread5

The decile table is like a quartile table, except there are 10 intervals instead of four. Theoretically, each count should have the same amount of observations. This data is so concentrated, that many spreads are the same and they are clustered at the break points.

Spread6A scatter diagram shows all the spreads in the period you chose and the fit around a linear trend line. Below is a chart showing equally spaced bins of spreads and how many spreads are in each bin.

Spread7

An example of the Federal Reserve raising rates, was when I first joined California Federal Savings and Loan (CalFed) at the start of 1980. If we chose the dates 1/4/1980 to 9/5/1980, those spreads would forecast a 30-Year mortgage rate of 9.085%, given a 4.0% forecasted 10-Year Treasury yield.

Spread8

Play with the spreadsheet. Let me know of any error.

Download “Mort_10y_Spread

 

One thought on “Spreads Between the 10-Year Treasury and 30-Year Mortgage

  1. It’s difficult to find well-informed people in this particular subject, but you sound like you know what you’re talking
    about! Thanks

Leave a Reply

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

*
*