Loan Analysis for Dummies
November 2017

You might remember a day when everyone who defaulted on a loan you knew by name, or which businesses owners down the street had a line of credit. As institutions mature and grow these scenarios become few and far between. Five defaults become hundreds and simple business credit lines become complicated. The goal of profitability, good service, and continued growth are still the same, but as institutions evolve the methodologies they employ change.

Data analysis is one such methodology that is changing every lender today. Governing agencies such as FASB are using data analysis to aid them in creating new guidelines such as how financial institutions calculate expected loss, the CFPB is using data analysis to re-examine best practices in fair lending, and the NCUA is requiring multi-dimensional portfolio analysis from credit unions nationwide. These large agencies probably have a team of analysts at their disposal to aggregate and analyze data for them.

But what about loan analysis for lenders? What about those of us who don’t have a team of analysts or a statistician on our team? What about those of us who aren’t data geeks, where do we start?

This guide should serve as a stepping stone for those of you who aren’t trained in analysis but desire to improve your data skills.

1 – Get Your Data into a Digital Format

Over the years I’m amazed at how often I hear the phrase “We don’t track that”, or “That’s in our paper files”. Whenever I hear this I cringe a little bit. That’s because any data point that is not in a database can not be used at scale with analytics for making good decisions. If you still have mounds of data sitting on a piece of paper in a filing cabinet it’s time to give that data a voice. Get a team together to extract that knowledge and get it into a digital format. For many transforming paper data to digital data can be a roadblock and a tough time commitment to make but trust me it will be worth it. Any historical information you have can be very valuable in helping making predictions of your future risk. The reality is you’re going to have to do it sometime why not just rip off the band-aid and get it over with.

2 – Clean and Document Your Data

Everyone has heard the term Garbage In, Garbage Out and this applies to your data. If you don’t have documentation on what every column of data represent, how are you suppose to understand or use that data for analysis? Have someone on your team spend some time documenting what your data is and making sure it’s in the correct format for analysis.

3 – Start Tracking Every Piece of Data Possible

If you are doing HELOC loans don’t tell me you don’t have information on the first mortgage. Every lender that is making second mortgages should be able to calculate a true CLTV and understand their true exposure, information on the first is a key component for this analysis. Just like this HELOC example there are many other data points on your customers/members that you should be gathering. Here are a few data points to consider:

  • Branch visits
  • Household information
  • Transactional data
  • Credit scores over time (Original, Updated, etc.)
  • Loan type code prior to charge-off
  • Original collateral value
  • Property address & mailing address as separate fields
  • TDR indicator
  • Additional attributes provided on soft credit pulls
  • Website visits and pages visited
  • Marketing campaigns offline and on
  • Online banking information (logins, transfers, time of day, time of year, pages visited, etc.)
  • Communication sent and responses
  • Customer meetings with loan officers or other staff members
  • Demographics of customers who visit branches vs. banking online

4 – Use a Data Warehouse

These days lenders are sitting on mountains of data. CORE data, L.O.S. data, Credit Bureau data, servicing data, Credit Card data, etc. are all common among lenders. Now add in emails, cross-selling, social media, marketing campaigns, mobile banking, and website visits. It’s easy to see how spread out your data can become. The key is to have a central source where all of your data can reside and be used for analysis. Using a system such as Visible Equity’s software for data warehousing can be instrumental for a complete loan analysis strategy.

5 – Use a Loan Portfolio Software System

It used to be a great practice to use Excel for all your data analysis needs. Using pivot tables and vlookups to make your data spit out the information you needed was probably a staple for years. I think all of us have spent our fair share of time in Excel and there is no doubt it still has it’s uses today. However, with the amount of data used for analysis Excel simply won’t cut it anymore. More robust databases have been developed such as Oracle, SQL, or Hadoop to aid in housing your data. By using a software platform such as Visible Equity’s analytics you get the best of both worlds by combining all of your data into a centralized database and giving you a friendly user interface you can use to manipulate the data and perform a full loan analysis.

6 – Familiarize Yourself With Best Practices

It’s easy to get into analysis paralysis where there are so many options you don’t know where to begin. To make sure you don’t get overwhelmed learn from those that are already using data for loan analysis. At Visible Equity we hold regional user groups, weekly trainings, weekly webinars, and hold a national analytics conference to ensure you get the networking and training needed to succeed. Attending these meetings can be extremely helpful and help you advance the ball much quicker than by trying to do all the analysis by yourself.

In addition to networking, it will probably be helpful to understand common reports many institutions use and why they use them. To get you started here are some of the most common reports used in Visible Equity’s software:

  • Concentrations / Grading
  • Delinquency Aging Table
  • Credit Score Migration
  • Trends
  • New Production
  • Statistics
  • Static Pool Comparison
  • Performance Consolidated

7 – Learn Advanced Techniques

After you get the basics down above you are probably ready to move on to more advanced methodologies such as mutl-variate linear regression. Historically, many of the more advanced techniques were reserved for larger institutions with the resources available to write the necessary code for the algorithms to manipulate the data, but these days with software like Visible Equity provides, all sizes of lenders can use the same loan analysis techniques to make better decisions.

Related Blog Posts
Further Your Education