About portfolio-dd
A buyer of a loan book gets the seller’s tape in the seller’s format. Before any price is set, someone maps its columns, cleans its statuses, and runs the same strats and curves every diligence pass runs. This tool does that part, on your data, and shows its working.
Who it is for
Credit funds bidding on a whole-loan portfolio, banks buying or selling a tape, and auditors checking a seller’s numbers. Anyone who reads strat tables and vintage curves for a living and wants them on a new tape in minutes rather than days.
It does not price the book or forecast losses. It gives you the descriptive analyses that every model and every bid starts from, built the same way every time, so two tapes can be compared on the same terms.
Method
Every analysis is written once, against these field names, and never against a lender’s own column names.
| Field | Type | Meaning | Required |
|---|---|---|---|
| loan_id | identifier | Unique loan identifier. | Required |
| origination_date | date | Date the loan was issued or funded. | Required |
| original_balance | money | Principal amount originated. | Required |
| interest_rate | rate | Annual interest rate at origination, as a percentage. | Required |
| term_months | integer | Contractual term in months. | Required |
| status | status | Loan status at the snapshot date. | Required |
| installment | money | Scheduled periodic payment. | |
| grade | category | Lender risk grade or band at origination. | |
| sub_grade | category | Finer risk grade, if the lender has one. | |
| credit_score | integer | Bureau credit score at origination. | |
| dti | rate | Debt-to-income ratio at origination, as a percentage. | |
| annual_income | money | Borrower's stated annual income. | |
| employment_length_years | number | Years in current employment. | |
| home_ownership | category | Housing status: own, mortgage, rent, other. | |
| income_verified | category | Whether income was verified at underwriting. | |
| purpose | category | Stated purpose of the loan. | |
| region | category | Borrower geography: state, region or country. | |
| channel | category | Origination or listing channel. | |
| current_balance | money | Outstanding principal at the snapshot date. | |
| principal_paid | money | Cumulative principal received. | |
| interest_paid | money | Cumulative interest received. | |
| total_paid | money | Cumulative payments received, all components. | |
| recoveries | money | Cash recovered after charge-off. | |
| last_payment_date | date | Date of the most recent payment received. | |
| charge_off_date | date | Date the loan was charged off or defaulted. | |
| raw_status | text | The lender's own status label, kept verbatim. |
Statuses
Every lender labels status differently. Each raw label is normalised to one of current, paid, late, charged off or other; the lender’s own label is kept verbatim in raw_status. Labels that fall into other but carry real volume are shown to you for a decision rather than silently dropped.
Formulas
Charged off means status charged off at the snapshot. Loans still current may yet charge off, so every charge-off rate is to date, not lifetime.
- Weighted average coupon
- Each loan’s interest rate weighted by its original balance: the sum of rate times original balance, divided by the sum of original balance, over the loans in the segment.
- Charge-off rate
- Counted two ways. By count, charged-off loans over all loans in the segment. By balance, the original balance of charged-off loans over the segment’s total original balance. Each segment is read against the book rate: above 1.5 times on more than 2% of balance is flagged watch, above 2 times is a concern.
- Loss and recovery
- Gross loss on a charged-off loan is its original balance less principal received. Net loss is gross loss less post-charge-off recoveries. The recovery rate is recoveries over gross loss, and loss given default is net loss over the original balance of the charged-off loans.
- Vintage curve
- Cumulative charge-off by months on book. Each month’s increment is the charge-offs at that month divided by the loans originated in the cohorts that have been observed for at least that many months, so partly seasoned cohorts count only towards the months they have reached. A curve stops once fewer than 10% of its loans are still observed.
- Yield against loss
- Per grade, interest received over original balance beside net loss over original balance, both cumulative to the snapshot and balance-weighted. The difference is a rough loss-adjusted return, not annualised, before servicing and funding costs.
- Concentration
- Each class’s share of original balance, largest first, and the Herfindahl index of those shares on a 0 to 10,000 scale.
- Outliers
- For every segment holding at least 1% of balance, a one-sample binomial z-score of its count-weighted charge-off rate against the book rate. Segments at three standard errors or more are flagged, largest first. The local model can then write a paragraph for each, given only the statistics computed in SQL.
Roadmap
- Ingest any tape. CSV, TSV, Excel or Parquet, as the seller sent it.
- Map it to the standard schema. Column names are matched against known synonyms; a local LLM proposes matches for the ones it does not recognise, each with a confidence and a reason you can overrule.
- Standardise. Dates, rates, terms and statuses are cast to one form, and every value that would not cast is counted and reported.
- Run the known analyses. Strats, charge-off by segment, vintage curves, yield against loss, concentration and data quality.
- Explain outliers. Name the segments that sit outside the book and say why, in a sentence a credit committee would accept.
Stack
- DuckDB for aggregation directly on Parquet and uploaded files, out of core.
- Polars for row-level casting and cleaning, zero-copy with DuckDB through Arrow.
- FastAPI with server-rendered pages; the charts are plain SVG drawn by a small script, with no chart library.
- Ollama running qwen3:14b on a GPU box on the local network. It sees column names, sample values and computed statistics, never computes a figure, and no outside service is called. If it is unreachable, mapping falls back to the name matcher and the tool says so.
The sample dataset
Lending Club, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015 vintages: 886,837 loans originated January 2008 to December 2015, with outcomes as of March 2019. Unsecured US consumer instalment loans originated on the Lending Club platform, 36 and 60 month terms, from the public accepted-loans table.
Caveats
- The report covers the 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015 vintages: 886,837 loans issued Jan 2008 to Dec 2015.
- 2007 is excluded (603 loans across 7 issue months): incomplete or not seasoned 36 months at the snapshot.
- 2016 is excluded (434,407 loans across 12 issue months): incomplete or not seasoned 36 months at the snapshot.
- 2017 is excluded (443,579 loans across 12 issue months): incomplete or not seasoned 36 months at the snapshot.
- 2018 is excluded (495,242 loans across 12 issue months): incomplete or not seasoned 36 months at the snapshot.
- Status is as of the snapshot, taken as the latest last-payment date in the file (Mar 2019). Loans issued in 2017 and 2018 would be mostly current at that date, so their charge-off rates would not be comparable to seasoned vintages.
- The tape has no charge-off date. Vintage curves place each charge-off at the last payment date, which leads the actual charge-off by several months.
- Credit score is the low end of the FICO range at origination. Original balance is the requested loan amount (loan_amnt).
- Statuses 'Default' and 'Charged Off' both map to charged off; 'In Grace Period' maps to current; the two 'Late' buckets to late.
This site
This site runs a copy of main. Every push is deployed, and the commit it is running is in the footer, linked to the change itself.