A data-driven yield decomposition framework that separates payoff selection effects from true pricing compression, revealing a 17 bps forward pipeline gap concentrated in CRE lending.
Leadership noticed portfolio yield appeared to be declining and asked: "Is our portfolio yield compressing, and if so, what's driving it?" The answer required separating genuine pricing pressure from compositional effects and natural portfolio turnover.
Rather than accepting the premise at face value, I built a yield decomposition framework that isolates mix effects (changing segment weights) from rate effects (within-segment pricing changes), while accounting for payoff selection bias in the active portfolio.
Before touching data, I identified the key business questions that would shape the analysis.
A structured five-step framework to diagnose yield dynamics.
Calculate portfolio-level spread (simple average vs balance-weighted) across all vintages to determine if yield is actually declining.
Slice by segment, risk rating, and vintage. Decompose aggregate changes into mix effects vs within-segment rate effects.
Compare spread of paid-off loans to active portfolio to identify survivorship/selection bias in portfolio yield.
Benchmark pipeline spreads against existing portfolio by segment to quantify expected forward compression.
Combine findings into actionable recommendations for portfolio management and pricing strategy.
Interactive analysis of portfolio yield dynamics across segments, vintages, and cohorts.
Comparing simple average vs balance-weighted average spread across origination years.
Box plots showing the spread range, median, and quartiles for each lending segment.
Comparing average spread of loans that have paid off vs those still active in the portfolio.
How segment mix has shifted across origination years (% of balance).
Balance-weighted spread by segment and origination year. Darker green = higher spread.
Side-by-side comparison of current portfolio spreads vs expected pipeline spreads.
Breaking down the portfolio-to-pipeline spread change into compositional (mix) and pricing (rate) components.
How spreads vary across the internal risk rating scale (S200 = lowest risk, S400 = highest).
Pipeline deals grouped by stage (Early, Mid, Late) with spread and balance metrics.
Key findings and actionable recommendations for portfolio management.
| Segment | Portfolio Spread | Pipeline Spread | Gap | Portfolio Share |
|---|
Filter the portfolio by segment, vintage, and risk rating. Explore what-if scenarios for pipeline impact.
Adjust sliders to see how portfolio yield changes under different assumptions.
Intellectual honesty about what the data can and cannot tell us.
120 loans across 5 vintages and 4 segments means some segment-vintage cells contain only 2-3 loans. Aggregate patterns are more reliable than granular cuts.
Current balance only — no time-series of how balances evolved. Cannot track drawdown patterns or amortization schedules over time.
No fixed vs variable rate indicator. Spread dynamics may differ significantly between fixed and floating-rate facilities.
18 of 40 pipeline deals are Early stage. Historical close rates by stage would significantly improve forward yield projections.
Deal P004 (CRE, $18.1M, 70 bps) is 150+ bps below the next CRE pipeline deal. This single deal materially impacts CRE pipeline averages and needs validation.
All portfolio-level metrics use current balance weighting. Simple averages are shown alongside for comparison. Decomposition uses standard shift-share methodology.
Tools and frameworks powering this analysis.
120-loan portfolio dataset with 40-deal pipeline across 4 segments, 6 risk grades, and 5 vintage years.
Balance-weighted yield decomposition using shift-share methodology. Mix-vs-rate attribution across segments.
Interactive Plotly.js charts with custom dark theme. Static site with pre-generated JSON data pipeline.
The goal was not just to analyze yield, but to tell a clear credit risk story — separating noise from signal in portfolio performance data and providing actionable recommendations backed by rigorous decomposition analysis.
This project demonstrates the ability to take an ambiguous business question ("Is our yield declining?"), structure an analytical framework, perform rigorous quantitative analysis, and deliver actionable insights — exactly the kind of thinking commercial lending teams need.