Interpolation

There has been an addition to the spreadsheet, explained in the post: 

Simplified Linear Interpolation of Treasury Rates

Back in the day, when I was building a pricing model for mortgages that would be sold to FNMA, I ran into a problem with their pricing tables. The table below shows the problem. The X column is the dollar price the agency would pay, at various yield levels (column y). Since we were underwriting new mortgages, we needed the yield for a par price (100), but it was not on the table.

Interpolate_XY

Since we were offering over a dozen or more different mortgages, we had a dozen or more tables to find the par price each day to set the days mortgage yields.

I built the formula with the option of finding the interpolated value, if the X column is ascending or descending:

Interpolate_Formula

Interpolate_input_output

Of course the x value need not be 100.

Nowadays, the agency interpolates the par price and posts it on the table, but it may come in handy for other  interpolations.

Interpolate_chart

Look for “Interpolate

http://www.pistulka.com/Excel_Shared/ 

 

 

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 *