Credit Risk Analytics & Underwriting Engine

A comprehensive, hands-on credit risk analytics project designed to bridge the gap between statistical credit modeling (Python) and executive business decision-making (Excel & Streamlit).

This repository contains a full credit risk workflow, including a rules-based underwriting scorecard, a statistical Probability of Default (PD) model, an Expected Loss (EL) framework, economic stress testing simulations, and an interactive decision-support web application.

---

📂 Project Structure

Financial Credit Risk Analyst/
│
├── data/                         # Database storage
│   ├── loan_applicants_raw.csv   # Raw generated synthetic applicant data (175 rows)
│   ├── loan_applicants_with_pd.csv # Modeling outputs with Predicted PD values
│   └── loan_applicants_stressed.csv # Stress testing dataset (baseline vs. stressed PD/EL)
│
├── excel/                        # Interactive spreadsheets
│   ├── loan_applicants.xlsx      # Underwriting database with live calculated formulas
│   ├── loan_applicants_with_pd.xlsx # Underwriting database with appended PD column
│   └── loan_applicants_portfolio.xlsx # Executive Portfolio Dashboard with native Excel charts
│
├── python/                       # Script source code
│   ├── generate_data.py          # Data generation and Excel styling script (Phase 1)
│   ├── train_model.py            # Logistic Regression classifier and ML evaluation (Phase 2)
│   ├── build_dashboard.py        # Portfolio aggregations and dashboard compiler (Phase 3)
│   ├── stress_test.py            # Macroeconomic income shock simulation script (Phase 4)
│   └── app.py                    # Streamlit interactive web application (Phase 6)
│
├── outputs/                      # Models, figures, and committee memos
│   ├── roc_curve.png             # Classifier diagnostic ROC curve
│   ├── stress_test_comparison.png # Stress test dual-bar chart comparison
│   ├── stress_test_summary.txt   # Stressed expected loss report
│   ├── executive_memo.md         # Formal risk memorandum for the Credit Committee
│   └── resume_framing.md         # Resume bullet points and project descriptions for BA/PO roles
│
├── requirements.txt              # Project dependencies
└── README.md                     # Main documentation page (this file)

---

⚙️ Setup and Installation

Prerequisites

• Python 3.11+
• Command-line shell (PowerShell or Bash)

1. Initialize Virtual Environment

Navigate to the project root directory and create a local virtual environment:

python -m venv .venv

2. Activate Virtual Environment

• Windows (PowerShell):

  .\.venv\Scripts\Activate.ps1

• macOS/Linux:

  source .venv/bin/activate

3. Install Dependencies

Install all required libraries using requirements.txt:

pip install -r requirements.txt

---

🚀 Running the Pipeline

You can run the project phase-by-phase or execute the interactive dashboard directly.

Step 1: Generate the Underwriting Ledger

Synthesizes the borrower database and styles the initial Excel sheet with live formulas:

python python/generate_data.py

Creates: data/loan_applicants_raw.csv and excel/loan_applicants.xlsx

Step 2: Fit the Default Model

Preprocesses features, trains a Logistic Regression classifier, evaluates model metrics, and plots the ROC curve:

python python/train_model.py

Creates: outputs/roc_curve.png, data/loan_applicants_with_pd.csv, and excel/loan_applicants_with_pd.xlsx

Step 3: Build the Portfolio Dashboard

Sorts the database by risk, adds LGD/EAD/EL formulas, and compiles the Excel dashboard:

python python/build_dashboard.py

Creates: excel/loan_applicants_portfolio.xlsx

Step 4: Execute Stress Testing

Simulates a 20% macroeconomic income shock and outputs downturn risk profiles:

python python/stress_test.py

Creates: outputs/stress_test_comparison.png, outputs/stress_test_summary.txt, and data/loan_applicants_stressed.csv

Step 5: Start the Interactive Web Application

Launches the local Streamlit server for a hands-on, sandbox experience:

streamlit run python/app.py

Once launched, open http://localhost:8501 in your web browser.

---

🧮 Credit Risk Framework Reference

Here is the mathematical framework implemented in this project:

1. Borrower Debt-to-Income (DTI) Ratio

Incorporates existing obligations and the payment for the new requested loan: $$\text{New Annual Payment} = \text{PMT}(\text{Rate}=8%, \text{Term}=5, \text{PV}=-\text{Requested Loan})$$ $$\text{DTI} = \frac{\text{Existing Annual Debt} + \text{New Annual Payment}}{\text{Gross Annual Income}}$$

2. Loan-to-Value (LTV) Ratio

$$\text{LTV} = \frac{\text{Loan Amount Requested}}{\text{Collateral Value}}$$

3. Borrower Interest Coverage Ratio (ICR)

Estimates interest expenses assuming a 5% rate on existing debt and 8% on the new loan: $$\text{ICR} = \frac{\text{Annual Income}}{\text{Existing Debt} \times 5% + \text{Requested Loan} \times 8%}$$

4. Expected Loss (EL) Calculation

$$\text{Expected Loss (EL)} = \text{Probability of Default (PD)} \times \text{Loss Given Default (LGD)} \times \text{Exposure at Default (EAD)}$$

• PD: Output from the Python Logistic Regression model.
• LGD: Set at a flat 45% (industry standard for senior credit, representing 55% recovery).
• EAD: Represented by the requested loan amount.

---

💻 Web App Core Features

The Streamlit web app serves as an interactive underwriting cockpit:

1. 📊 Portfolio Dashboard: Aggregates total exposure, expected losses, average PDs, and displays interactive Plotly charts.
2. ⚡ Stress Testing Sandbox: Allows adjusting recessionary sliders (0% to 50% income shocks) and dynamically visualizes baseline vs. stressed losses.
3. 🔍 Underwriter Sandbox: Search any applicant by ID and modify their values (FICO, income, loan size) to see their risk scores, scorecard points, grades, and statistical PDs update in real-time.