I built this project to practice how credit risk is actually measured and reported in a lending/banking setup — not just calculating PD, LGD and EAD in isolation, but taking a full loan portfolio and turning it into something a risk team could use to make decisions. I took a simulated portfolio of 200 companies across 15 sectors, calculated Expected Loss for each borrower, wrote SQL to segment and rank the risk, and then built a Power BI dashboard on top of it to visualize where the portfolio's risk is actually concentrated.
- Portfolio exposure
- PD
- LGD
- Expected Loss
- Credit ratings
- Risk classification
- Sector-wise risk
SQL | MySQL | Power BI | DAX | Power Query | Excel
Credit_Risk_Portfolio_final.csv contains 200 borrowers with the following fields: Company, Sector (15 sectors like Financial Services, IT, Power, Metals & Mining), Size (Small/Mid/Large Cap), Loan Amount, Rating (AAA–CCC), PD, LGD, EAD, Expected Loss, DPD, and DPD Bucket (Current, 1-30, 31-60, 61-90, 90+ Days). Expected Loss is calculated using the standard formula: EL = PD × LGD × EAD.
Five queries, all run on the credit_risk_portfolio_final table:
- Top_Credit_Risk_Exposures.sql – Ranks the top 10 companies by absolute Expected Loss, to identify which accounts carry the biggest rupee risk on the book.
- Risk_Adjusted_Expected_Loss.sql – Ranks the top 10 companies by EL as a percentage of EAD, to catch smaller accounts that are disproportionately risky relative to their size.
- High_Risk_Portfolio_Segmentation.sql – Groups the portfolio by credit rating and calculates exposure, average PD, and EL rate per rating band.
- Sector_risk_concentration.sql – Groups the portfolio by sector to see which industries are driving the most exposure and expected loss.
- Delinquency_Analysis.sql – Groups the portfolio by DPD bucket to compare exposure and EL between current accounts and those already past due.
The dashboard has 5 pages:
- Portfolio Overview – KPI cards for total EAD, total Expected Loss, average PD, and company count, plus exposure by sector and Expected Loss by rating.
- Sector & Exposure Risk – Exposure by company size, top 10 companies by exposure, and a combo chart of exposure vs. Expected Loss by sector.
- Credit Quality Analysis – Portfolio distribution by rating, average PD by rating, and a PD vs. Expected Loss scatter plot colored by risk classification.
- Delinquency Analysis – Companies and exposure by DPD bucket, and Expected Loss by DPD bucket.
- Expected Loss & Risk Contributors – Expected Loss by sector, top 10 companies by Expected Loss, and an exposure vs. Expected Loss scatter to flag outlier accounts.
The dashboard consists of five pages covering different aspects of portfolio credit risk.
- The portfolio carries ₹20,174 in total EAD against ₹526 in total Expected Loss, an overall EL rate of about 2.6%.
- Risk is heavily concentrated in the CCC-rated bucket: just 42 companies (21% of the portfolio) account for ₹340 of Expected Loss — roughly 65% of total portfolio EL — with an average PD of 20%.
- Investment-grade names (AAA, AA, A) make up a large share of exposure (₹9,930, about 49% of EAD) but contribute almost nothing to Expected Loss, confirming the rating scale is doing its job of separating risk.
- Healthcare stands out as the riskiest sector on a relative basis — only 10 companies and ₹429 in exposure, but a 15.5% EL rate, well above every other sector.
- Fast Moving Consumer Goods carries the highest absolute Expected Loss (₹118.5) of any sector, driven mostly by a handful of CCC-rated names like Patanjali Foods and Bikaji Foods.
- By delinquency, the 31-60 day bucket holds the most exposure and the highest Expected Loss (₹175), suggesting early-stage delinquency is where the bulk of at-risk exposure currently sits, rather than accounts already at 90+ days.
- The single largest risk contributors by Expected Loss are Patanjali Foods, Lloyds Metals and Energy, and Railtel Corporation — all CCC-rated, spanning three different sectors, which shows the biggest risks aren't confined to one industry.
Credit-Risk/
│
├── Dashboard/
│ ├── Page 1.png
│ ├── Page 2.png
│ ├── Page 3.png
│ ├── Page 4.png
│ └── Page 5.png
│
├── Data/
│ └── Credit_Risk_Portfolio.csv
│
├── Power BI/
│ ├── Credit Risk Dashboard.pbix
│ └── Credit Risk.pdf
│
├── SQL/
│ ├── Delinquency_Analysis.sql
│ ├── High_Risk_Portfolio_Segmentation.sql
│ ├── Risk_Adjusted_Expected_Loss.sql
│ ├── Sector_risk_concentration.sql
│ └── Top_Credit_Risk_Exposures.sql
│
└── README.md
Keshav Patwari B.Com (H) | FRM Certified