Mortgage Pricing Model

Smaller credit unions and banks may not have access to mortgage pricing models. This post is an example that might get some institutions started in building their own models. Fannie Mae now posts their commitment rates for 15- and 30-year mortgages daily. The commitment rates are for 10, 30, 60, and 90 days. This Excel workbook downloads the commitment tables from Fannie Mae and incorporates them into the model. Likewise, the ARM option calls in the U.S. Treasury rates each day, used for pricing adjustable-rate mortgages tied to Treasury rates. Commitments are priced in the morning and Treasuries are priced at the end of the trading day.

 

The rates will update each time the workbook is opened, and there also are green buttons to refresh the rates.

All tables start over each month, so at the start of each month you could get an error when loading, because the last date you used is not in the new table. Ignore the error and simply change the date options on the dropdown boxes.

 

Yellow cells are input cells and dark yellow cells are dropdown boxes to choose data from.

Start the pricing by first choosing the date, term, and commitment period for fixed rate loans:

For ARMs start with this input:

Both fixed rate and ARM sheets have separate sheets for “Assumptions”. You will need to enter your own assumptions and add other risk measurements you need.

The rates are rounded off to three places, however you have five options as to how to round when you click this box:

The amortizations on each sheet are used only to calculate the APR on the loan and other statistics such as average life. The rule for APR on ARMs is basically to change the rates until the fully indexed rate is reached and continuing on until maturity.

This Excel workbook has not been tested in “real world” applications, so use at your own risk.

Download: http://pistulka.com/Excel_Shared/Mortgage_Pricing_Model.xlsm

Note: The first time you download, it will take longer, because it is calling in the pricing tables from their web sites.

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.

Leave a Reply

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