Bootstrapping Spot Rates For FHLB Amortizing Advance Rates

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.

=FORECAST(G8,OFFSET($C$7,MATCH(G8,x,1)-1,1,2,1),OFFSET($C$7,MATCH(G8,x,1)-1,0,2,1))

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”

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

2 Comments

  1. Hey Don,

    Hope things are well.

    Do you have any material on building a profitability analysis for variable rate loans or LOC? The are based on prime plus a margin.

    Thanks,
    Jordan

Leave a Reply

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