Mortgage Portfolio Risk

The question

A mortgage bank wanted to know how its loan costs are distributed, whether its risk clusters, how its loan consultants perform, and which borrower characteristics go with high-risk loans, across 2017 to 2020.

The data and the model

The data came in several Excel sheets covering five dimensions, with every transaction feeding one fact table. I cleaned it, modelled it as a star schema, and derived loan-to-value and loan-to-income for each loan. Then I combined them with the credit score into one risk score, each part scaled to 0–10:

Risk Score =
VAR normcreditscore = (([Total Credit Score] - 300) / (850 - 300)) * 10
VAR normLTI = (([Loan-to-income] - 0) / (25 - 0)) * 10
VAR normLTV = (([Loan to Value (LTV)] - 0) / (2.5 - 0)) * 10
VAR finalriskscore = (0.40 * normLTI) + (0.4 * normLTV) + (0.2 * normcreditscore)
RETURN finalriskscore

The score sorts every loan into low, medium or high risk, and a cluster analysis groups borrowers by the same factors.

What I found

• Most mortgages sit between $100K and $200K.
• Total loan cost grew 236.44% from 2017 to 2018, because more loans were issued, not larger ones.
• High-risk loans doubled in 2019, yet total loan cost fell only 10%: volume drives cost more than risk.
• Newark wrote 18 of the portfolio’s 19 high-risk loans and has the highest total loan cost. Its borrowers take large loans of $300K to $500K on the strength of high credit scores.
• Wilmington and Baltimore wrote no high-risk loans at all.
• The top consultant by loan cost, based in Newark, writes high- and medium-risk loans. The consultant in Wilmington writes only low and medium risk and ranks second lowest.

What I recommended

Stricter credit assessment for Newark applicants, a loan-to-income cap for high-risk applicants with good credit scores, interest rates priced by each city’s risk, and coaching that brings the two consultants’ approaches closer together.