How to generate a 2018 HMDA TXT file using the CFPB LAR Formatting Tool

by | May 8, 2018 | blog, CFPB, HMDA | 0 comments

If you have a loan origination system extract handy, and you want to generate a 2018 HMDA TXT file for analysis in LendingPatterns™ or Fair Lending Magic™, here is a how-to guide on preparation of the file.

1) Download the macro-enabled spreadsheet from the CFPB URL below (accessed May 7, 2018):

https://www.consumerfinance.gov/data-research/hmda/static/lar-formatting-tool/2018/HMDA_2018_LAR_Formatting_Tool.xlsm

2) Within Excel, it may be necessary to “enable content”, as your Excel may be blocking macros from functioning. If so, go File | Enable Content.

 3) Enter the appropriate transmittal sheet information into row 3 such as the Financial Institution name.

Within LendingPatterns™, this information is critical for trend charts.                                  

4) Copy and paste your data into the spreadsheet from the loan origination system extract.

As you go along, ensure that formatting in all fields matches the Screen Tips. For example, for Loan Purpose, as shown below, you’d input a “1” for home purchase. Typing in “home purchase” will not work!

Also, one tip that is not explicit in the Screen tips: the dollar amounts, such as in Loan Amount, Discount Points, Lender Credits, and Total Loan Costs, should contain neither dollar signs nor commas as thousands separators.

For example, $23,250 would be written as “23250”.

5) When all your data are in the LAR Formatting Tool, click the “Create LAR File” button. You will then save a TXT file to your computer for upload into LendingPatterns™ or Fair Lending Magic™.

6) The next step for Fair Lending Magic™ subscribers is to run Data Validation reports to confirm that the percentage of values within expected ranges is as you thought.

A couple notes:

  1. The LAR Formatting Tool has run successfully with as many as 30,000 records. Larger reporters may need to split the data and re-combine following import to LendingPatterns™ or Fair Lending Magic™, though very large reporters would find this too tedious.
  2. Did you know that the CFPB tool can also be used to generate a LAR file for non-HMDA loans that can then be uploaded into LendingPatterns™ for analysis? You will want to populate a few key fields like Income, Loan Amount, Action Taken, County, and Census Tract, but the rest will be populated with garbage. Be sure to carefully document what is garbage data.