Commercial Loan Margin Analysis is a tool created for banking management to review the loan portfolio of the bank, I used the dummy dataset I created with certain logic applied. The dashboard I published here is a subset of the Loan Portfolio Analysis tools, used to provide insights to the decision-makers, like which industry did well last year, which customer made the most profit for the bank, what are the industries/borrowers were losing money, and need to take some further analysis or actions on.

For instance, the dashboard below is showing the commercial loan portfolio of 2020. This tool is providing more insights from the commercial borrowers’ industry perspective. During the COVID-19 crisis, economics took a big hit especially in the industries like Hospitality, Entertainment, Construction, etc. The companies in these industries had to shut down for a long time due to the quarantine. The bad business of those clients also caused the bank issues on their loan payments. Some of the clients I worked with provided some kind of support on loan interest or payments to help small businesses keep running. In contrast to this, the U.S house market in the last year grew very fast. Even right now, in Aug 2021, the house price still keeps rising, carrying other businesses like lumber, metal, etc.

More than showing commercial loan performance by industry, we can also dig deeper insights using those commercial loan transactional data. For example, for those low loan margin performance borrowers, we can do more analysis on the loan spread(interest rate), expected loss rate, loan fees (loan origination fee, services fee, etc.) to find what the root cause is. I did a Loan Spread & Fees Analysis for one of the clients I worked with and found out the issue that some Loan Officers provided a lower interest rate to the borrower, or waived too many fees that shouldn’t be waived in order to get more loans closed and get more cash incentives. Those are part of the reasons that the bank generates less profit than it is supposed to.

Note: Please click the button on the bottom right corner of the dashboard to enter the full-screen mode for a better experience.