A reader asked for help in creating the spot rates for the Federal Home Loan Banks market reference curve, used for amortizing advances. I have posted four ways to calculate spot rates for U.S. Treasury securities (A Forth Way To Bootstrap Spot Rates), but this is the first non-uses the same basic calculations, but uses a 360 X 360 cell table, representing the 360 months in 30 years. That table is why the Excel file is so large and will take a little longer to download.
Download Excel File: http://pistulka.com/Excel_Shared/FHLB_spot_rates.xlsx
I used the formula below to interpolate the rates, and copied it down. The x in the formula is the yield curve range in the market reference curve.
X in the formula is the market reference curve
The FHLB may use a different method for interpolation and may offer loans between the dates on their market reference curve at different rates, but for purposes of this post the, above formula was used.
Treasury yield curve I have attempted.
This is also the first spot rate post with monthly payments, rather than the Treasury’s constant semiannual payments and a corpus.
Each month of the market reference curve was converted to years.
I used the first and forth methods that I listed in my Treasury spot rate post, to calculate the spot rates. The cash flows are calculated the same way as a loan, using the pmt() Excel function. The two methods I used are both bootstrap methods. Sheet “Method 1” calculates a cumulative discount factor each month, that is used to calculate the present value of all previous cash flows to that point. That present value is then multiplied by the interpreted yield at that maturity. Sheet “Method 2”