📊 Installment Loan Portfolio Analysis (SQL)

🔍 Project Overview

This project analyzes an installment loan portfolio to evaluate payment behavior, detect overdue debt, and segment customers by risk.

The goal is to simulate a real financial reporting scenario and generate a full analytical report as of a specific date (30.04.2020).

---

💼 Business Questions

• How much should clients have paid vs actually paid?
• What is the total overdue debt?
• Which customers are at risk based on missed payments?
• How is the loan portfolio distributed by risk segments?

---

📈 Key Metrics Calculated

• Total contract amount
• Expected payments (should be paid)
• Actual payments
• Overdue debt
• Remaining balance
• Number of missed payments
• Customer risk segmentation

---

🧠 Analytical Logic

The report calculates:

1. Expected payments based on contract duration
2. Actual payments made by each client
3. Overdue debt (difference between expected and actual)
4. Missed payment months
5. Customer segmentation:
   • 0 missed payments (low risk)
   • 1–2 missed payments (medium risk)
   • 3+ missed payments (high risk)

---

📂 Files

• 📊 Debt Report Query
• 🧱 Database Schema

🏗️ Data Model

Two main tables:

installment_plan

• contract_number
• merchant_id
• purchase date
• number of installments
• installment amount

payments

• contract_number
• merchant_id
• payment date
• payment amount

---

⚙️ Implementation

• CTEs for step-by-step calculations
• CASE logic for segmentation
• Date-based calculations
• Aggregations for financial metrics

---

📊 Final Output

The final report provides a structured view of the loan portfolio, including:

• payment status (paid / unpaid)
• overdue debt
• customer segmentation by missed payments

This type of report can be used by financial teams to monitor portfolio quality and identify risky clients.

---

🛠️ Tools

• Microsoft SQL Server
• T-SQL
• SSMS

Author

Djon — Junior Data Analyst (SQL)

Final Report Result

Below is the final analytical report generated using SQL: