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.

 

Enable Macros Excel
  • Gmail
  • Facebook
  • Twitter
  • Google+
  • LinkedIn

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

  • Gmail
  • Facebook
  • Twitter
  • Google+
  • LinkedIn

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

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

  • Gmail
  • Facebook
  • Twitter
  • Google+
  • LinkedIn

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.