Executive Summary
A synthesis of key findings, strategic recommendations, methodology notes, and analyst credentials for this loan product profitability analysis.
Key Findings
The following findings represent the most actionable insights derived from the comprehensive analysis of Baokim's consumer loan portfolio during the January–November 2022 period.
Portfolio Health Is Improving
The weighted average default rate declined from 3.36% to 3.14% over the analysis period, driven by tighter underwriting standards and improved forecasting models. Net interest margin expanded from 4.51% to 4.82%, reflecting better pricing discipline and favorable funding conditions.
Personal Loans Drive Outsized Returns
Despite carrying the highest default rate (5.8%), personal loans generate the strongest risk-adjusted return at 8.12% — well above the 5% target threshold. Their premium pricing (14.5% avg. rate) more than compensates for elevated credit losses, making them the portfolio's primary profit engine.
Operating Efficiency Gains Are Substantial
A 30% reduction in operating expenses was achieved through automated reporting workflows (VBA/Macros), streamlined data consolidation processes, and elimination of redundant manual reconciliation steps. This improvement directly contributed to margin expansion across all product lines.
Vintage Quality Is Trending Favorably
Post-2021 origination cohorts consistently track 30–40bps below earlier vintages at equivalent seasoning points. The Q1 2022 vintage shows a 3-month default rate of just 0.3%, the lowest in the dataset, validating recent underwriting improvements.
Portfolio Can Withstand Rate Stress
Sensitivity modeling demonstrates the portfolio maintains positive margins across all products under a +200bps interest rate increase. The primary vulnerability is default rate stress — at 2.0x multiplier, student loans and HELOCs become the first products to turn unprofitable.
Forecast Accuracy Improved by 30%
Driver-based forecasting models incorporating historical trend analysis and operational KPIs improved prediction accuracy by 30%, enabling more precise provisioning and capital allocation decisions.
Strategic Actions
Maintain Current Product Mix
The risk-return profile across products is well-balanced. Personal loans provide high returns while secured products (auto, HELOC) provide portfolio stability. No rebalancing is recommended at this time.
Expand Personal Loan Originations Selectively
Given their 8.12% risk-adjusted return and improving vintage performance, targeted expansion of personal loan originations to creditworthy segments could increase portfolio profitability without proportionally increasing risk.
Institutionalize Automated Reporting
The 30% OpEx reduction demonstrates the value of automation. Recommend formalizing the VBA/Macro reporting suite into a production-grade BI platform (Power BI) to ensure sustainability and scalability.
Implement Vintage-Based Provisioning
The cohort analysis provides a robust framework for forward-looking loss estimation. Integrating vintage curves into the ALLL process would improve reserve adequacy and regulatory alignment.
Build Rate Hedging Strategy
While the portfolio can absorb +200bps, a rising rate environment beyond that threshold would compress margins on fixed-rate products. Consider interest rate swaps or adjustable-rate product features for new originations.
Analytical Approach
Data Sources: Financial and operational data were sourced from Baokim's ERP system, loan management platform, and internal reporting databases. Data was extracted using SQL queries, cleaned and validated through structured transformation processes, and consolidated into analytical datasets using Excel and Python (pandas, numpy, statsmodels).
Profitability Framework: Product-level profitability was calculated using a full margin waterfall methodology: Gross Interest Income → less Cost of Funds → Net Interest Margin → less Expected Credit Losses (Default Rate × Loss Given Default) → less Operating Costs → Net Margin. Risk-adjusted returns add back operating costs to isolate credit-specific profitability.
Vintage Analysis: Cohort-based default tracking follows industry-standard methodology, grouping loans by origination quarter and measuring cumulative default rates at fixed seasoning intervals (3, 6, 9, 12, 18, 24, 30, 36 months). Defaults are defined as 90+ days past due or charge-off, whichever occurs first.
Sensitivity Modeling: Scenario analysis applies parallel shifts to key parameters (interest rates, default rates, cost of funds, operating expenses) and recalculates product-level and portfolio-level margins. The model assumes linear pass-through of rate changes and proportional scaling of default rates, which may understate non-linear effects in extreme scenarios.
Limitations: All data in this dashboard is simulated for analytical demonstration purposes. The analysis framework, methodologies, and analytical techniques are representative of the work performed during the analyst's tenure at Baokim. Actual portfolio figures, product details, and performance metrics have been modified to protect proprietary information.
Competencies Demonstrated
Financial Analysis
- ·Profitability analysis & margin decomposition
- ·Variance analysis & cost driver identification
- ·Budgeting & forecasting frameworks
- ·Scenario & sensitivity modeling
Data & Technical
- ·SQL data extraction & validation
- ·Excel financial modeling (advanced)
- ·VBA/Macro automation
- ·Power BI dashboard design
Credit Risk
- ·Vintage/cohort analysis
- ·Default rate tracking & provisioning
- ·Loss given default estimation
- ·Portfolio risk assessment
Business Partnership
- ·Cross-functional collaboration
- ·Translating data into actionable insights
- ·Executive reporting & communication
- ·Process optimization & efficiency gains
About the Analyst
Raj Ravi
Financial Analyst
Baokim Financial Services — Loan Profitability Analysis
Disclaimer: This dashboard was created as an analytical demonstration project. All financial data, portfolio figures, and performance metrics are simulated and do not represent actual Baokim company data. The analytical frameworks, methodologies, and visualization techniques demonstrated are representative of the analyst's professional capabilities and experience.