Excel@CFO

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:

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:

Obviously, 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“