In commercial banking, corporate underwriting, and B2B treasury operations, two calculations dictate liquidity and creditworthiness:Accounts Receivable (AR) Aging Buckets (0–30, 31–60, 61–90, 90+ days)Debt Service Coverage Ratio (DSCR) & Cash-Flow Stress TestingMost finance teams still compute these manually in bloated Excel workbooks—or worse, write slow Python for / .iterrows() loops that choke when invoice ledgers scale past 100,000 rows.Here is how to vectorize both in pure Pandas and NumPy with zero external C-dependencies.1. Vectorized Accounts Receivable Aging Buckets (np.select)Instead of applying row-by-row conditional functions (df.apply), use np.select to evaluate all invoice overdue conditions simultaneously at C-speed.Pythonimport numpy as np
import pandas as pd
def compute_ar_aging(invoices_df: pd.DataFrame, as_of_date: str) -> pd.DataFrame:
"""
Vectorized 0-30-60-90+ day Accounts Receivable aging bucketing.
Expects columns: ['invoice_id', 'client_name', 'due_date', 'outstanding_amount']
"""
df = invoices_df.copy()
ref_date = pd.to_datetime(as_of_date)
df['due_date'] = pd.to_datetime(df['due_date'])
# Calculate days past due (negative means not yet due)
df['days_past_due'] = (ref_date - df['due_date']).dt.days
# Define vectorized bucket conditions
conditions = [
df['days_past_due'] <= 0,
(df['days_past_due'] > 0) & (df['days_past_due'] <= 30),
(df['days_past_due'] > 30) & (df['days_past_due'] <= 60),
(df['days_past_due'] > 60) & (df['days_past_due'] <= 90),
df['days_past_due'] > 90
]
buckets = ['Current', '1-30 Days', '31-60 Days', '61-90 Days', '90+ Days (Critical)']
df['aging_bucket'] = np.select(conditions, buckets, default='Unknown')
# Pivot summary by client
summary = pd.pivot_table(
df,
values='outstanding_amount',
index='client_name',
columns='aging_bucket',
aggfunc='sum',
fill_value=0.0
)
return summary
Why np.select wins: On a 250,000-invoice ledger, .apply() takes ~4.2 seconds. np.select finishes in ~18 milliseconds.2. Automated Corporate Credit Underwriting: DSCR CalculatorWhen evaluating corporate borrowers or stressed receivables, underwriters look at DSCR (Debt Service Coverage Ratio)—measuring whether Net Operating Income (NOI) comfortably covers annual debt obligations:$$\text{DSCR} = \frac{\text{Net Operating Income (EBITDA - Taxes)}}{\text{Total Debt Service (Principal + Interest)}}$$Here is a standalone vectorized credit screening function that flags borderline borrowers (DSCR < 1.25x):Pythondef evaluate_corporate_dscr(financials_df: pd.DataFrame, min_threshold: float = 1.25) -> pd.DataFrame:
"""
Vectorized DSCR computation and credit risk flagging for corporate borrowers.
Expects columns: ['borrower_id', 'ebitda', 'cash_taxes', 'annual_principal', 'annual_interest']
"""
df = financials_df.copy()
noi = df['ebitda'] - df['cash_taxes']
debt_service = df['annual_principal'] + df['annual_interest']
# Guard against division by zero
df['dscr'] = np.where(debt_service > 0, np.round(noi / debt_service, 2), np.inf)
df['credit_status'] = np.select(
[
df['dscr'] >= 1.50,
(df['dscr'] >= min_threshold) & (df['dscr'] < 1.50),
df['dscr'] < min_threshold
],
['Strong / Approved', 'Acceptable (Watchlist)', 'High Risk / Covenant Breach'],
default='Review'
)
return df
Explore 126+ Standalone Quant & Financial Risk ScriptsAfter 9 years in commercial banking and credit underwriting, I built PyQuant—an open-source collection of 126+ standalone, dependency-light Python scripts covering algorithmic trading (Wilder's Supertrend, Session VWAP, Black-Scholes Greeks, SLSQP Portfolio Optimization) and institutional credit risk models:GitHub Repository (All 126+ Scripts): github.com/ayushman4you-ctrl/awesome-python-quant-scriptsLive Interactive Browser Terminal & Calculators: pyquant-library.netlify.appNo C-compiler headaches, no bloated frameworks—just clean NumPy/Pandas math you can drop straight into production.
Top comments (0)