Simplified Linear Interpolation of Treasury Rates

In August of 2014 I showed a spreadsheet for interpolating a yield, given a range of dollar prices. That post was call Interpolation. I thought of another way to use the same linear interpolation. Lets say you want to know what the Treasury yield would be for an investment. Unfortunately, you only have the current Treasury rates from a website showing only seven points along the curve, like below:

Interp_1

The formula below uses two OFFSET functions to find the months before and after the month you are solving for. Then the FORECAST function does the linear interpolation between the two months:

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

Below, we want the Treasury rate for a mortgage, under various PSA prepayment rates. We enter the average average life and we get back the interpolated Treasury rate for each PSA:

Interp_2Obviously, linearly interpolating the Treasury yield curve with only seven points is not the most accurate way to get Treasury rates, but for quick estimates it might save time.

Download the spreadsheet “Interpolation

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.

1 Comment

Leave a Reply

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