Bank Loan Portfolio Analysis:
Is It Time to Move Away from Spreadsheets?

For years, community bank leaders have been using spreadsheets to calculate and store key analytics for commercial loan portfolio analysis and reporting for management, board members, and auditors. While some larger banks were able to take advantage of new technologies and build their own more powerful proprietary systems, for most community banks spreadsheets were the best available option. But the landscape is changing, and there is a better loan portfolio analysis option available that fits most community bank needs and budgets.

Spreadsheets were the best choice because:

  • They were the easiest way to import, organize, view, and manipulate data from the core system and other systems within the bank.
  • They can hold massive amounts of data and handle the numerous calculations banks require.
  • Each bank has a set of concentrations, ratios, and other loan portfolio analytics that are specific to them. An affordable solution that accommodated a bank’s unique needs wasn’t available.
  • The graphing or visualization capabilities spreadsheets offer is helpful for reports.
  • As economic and market factors change, pivot tables allow CRE lenders to view the data that is most relevant from month-to-month.

Technology Advancements Open New Doors

While spreadsheets have effectively served their purpose for community banks, technology advancements now make it possible to manage and analyze CRE portfolios in a more efficient and effective manner.

A few of the most relevant technology advancements include:

  • Most core systems now allow for data to be easily extracted.
  • Data mapping and storage technology make it easier to quickly derive value from a data export.
  • SaaS models and cloud-based systems are faster and more scalable than spreadsheets.
  • Data can be made interactive with products like Tableau and Power BI.
  • Most security experts now agree that when properly managed, the safest way to store data that needs to be accessed by more than one party is in the cloud.
  • Storing and backing up large amounts of data is now far more cost effective.
  • Processing improvements have made evaluating large amounts of historical data fast enough to be commonplace.

Benefits of Finding the Right SaaS Solution

More Time Analyzing

When concentration reports are automated, more time can be spent exploring the results, understanding the risk profile of the portfolio, and conducting critical loan portfolio analysis. CRE portfolios are often a material contributor to a bank’s revenue. Understanding how close we are to a regulatory threshold and being able to evaluate options quickly can mean more revenue for the bank.

Happy Regulators

Demonstrating to regulators that the bank proactively understands and is managing their risk profile can result in shorter audits and more flexible lending limits. Banks that have clear and consistent reports that allow them to drill in and show detail in a quick and consistent manner tend to have shorter audits and are granted more flexibility. The same tools that help management teams and boards stay on the same page and make sound strategic decisions make it possible for auditors to understand and sign off on bank strategies.

What Happens When Bob Gets Promoted?

Often, a single employee builds a complex set of spreadsheets for a bank’s commercial loan portfolio analysis needs. When that employee is promoted or moves on, it can result in other team members spending hours to get their arms around the logic of the spreadsheets, and more often it results in a new set of spreadsheets being generated. A SaaS solution, configured for the bank and with an intuitive user interface, means there is always a transition plan in place.

What to Look for When Moving Beyond Spreadsheets

While some national banks have been able to leverage these technology advancements by hiring teams and building their own proprietary systems, most community banks are locked out of that path. Hiring the internal resources to build and maintain systems often proves to be cost prohibitive. Meanwhile, most off the shelf software does not meet key loan portfolio analysis requirements for a community bank. So even if they have software in place, they end up pulling data into spreadsheets for to evaluate key loan portfolio analytics.

The key elements to look for in a non-spreadsheet solution:

  • Complex, industry-specific financial models must be built in and reliable. A software built specifically for the lending industry, by people who are focused on the lending industry’s challenges yields more reliable results than a broad-based solution.
  • Configurability. The software needs to allow an individual bank to measure and manage their specific policy-driven concentration limits and ratios. Most banks have a unique set of strategies and objectives. Those unique strategies set them apart and need to be measured and reported. Having the flexibility to generate bank-specific ratios and reports can prevent time-intensive manual work.
  • The system must be highly secure. Clean SOC 2 Type II reports and systems that are designed by industry professionals who stay up to date on the latest in system security are essential.

For too long, spreadsheets have been the only viable option for community banks managing CRE loan portfolios. Now, the right SaaS solution can give banks deeper insight on key loan portfolio analytics, open opportunities for increased revenue, shorten audits, automate monthly reporting, and save time spent finding and organizing data – all at a price point that works for community banks. It’s time to re-evaluate your CRE loan portfolio analysis solution.

Realtime Analysis Software for CRE Loan Portfolios

Feature Spreadsheets Qualtik
Populate via CSV download
Customizable to the bank’s specific criteria
Sort and organize data
Fits community banks’ budgets
Identify loans with missing information
Generate CRE concentration reports in minutes
Intuitive interface easily understood by multiple team members
Create and run stress test in under 10 minutes
Demonstrate to regulators that the bank proactively understands and is managing their risk profile
Shorten audits
Ensure smooth transitions when analysts get promoted or move on
Display CRE holdings geographically with maps
Reduce risk of human error and inconsistent calculations
Reduce version control challenges
Easily map data from multiple sources