Financial Risk Analysis in Excel: How I Used COUNTIFS, AVERAGEIF & Pivot Tables to Uncover Loan Default Patterns

Tool: Microsoft Excel | Dataset: 1,000 loans | Period: 2018–2023 Overview This project is a comprehensive loan portfolio analysis built entirely in Excel. The goal was to understand how borrower characteristics influence loan issuance, default behavior , and overall portfolio risk — and to answer five specific credit risk questions that financial institutions deal with daily: What Is the Overall Health of the Loan Portfolio? — 37.5% of 1,000 loans ended in default or charge-off. That number alone demands investigation. Does Loan Grade Reliably Predict Default Risk? Grade A and B loans recorded 0% bad loan rates. Grade D hit 79%. Grade E reached 100%. Which Borrower Characteristics Drive the Highest Risk? Credit score below 650 produced a 77–100% bad loan rate. DTI above 30% pushed default rates to nearly 45%. Does Repayment Period Influence Default Probability? 48-month loans carried the highest bad rate at 39.6%, marginally ahead of 36-month (38.8%) and 60-month (34.1%) loans. Has Und