Predictive Modeling on Probability of Default – Logit Model Using Microsoft Excel and VBA

Logistic regression is often used to estimate the probability of default in commercial loans against different types of borrowers (obligor’s PD model). Microsoft Excel is probably the most popular used software in daily banking business work. However, the logistic regression model function is not captured by Excel. In this case study, I developed a Visual Basic Application (VBA) add-in in Excel macro environment to regress the linear logistic function with algorithms that capture the maximum likelihood procedure to predict probabilities of default in commercial lending. Using the dataset with five financial ratios that capture the widely known Z-score model developed by Altman (1968), the general drivers in terms of credit risk as well as the model estimation procedure was discussed. The developed VBA application can be expanded into retail banking (i.e., credit card, auto loan & home mortgages) and SME lending practices in small or medium commercial banking environment.

Key words: Excel, VBA, Logistic Regression, Risk Management

Download Link (Case Study and VBA)