How is the delinquency ratio calculated?

Delinquency ratio = (Past-due receivables / Total receivables) × 100. Past-due receivables are all accounts more than 30 days without payment (some financial institutions use the 90+ day threshold under Basel). The calculation is made at the period-end cut-off (month-end or quarter-end). For a healthy B2B SMB the ratio should stay below 5–8%; above 15% indicates structural problems in credit or collections.

What is DSO (Days Sales Outstanding) and how is it calculated?

DSO = (Accounts receivable / Total sales) × Days in period. It measures the average number of days to convert a credit sale into cash. Example: $200,000 USD AR ÷ $1,000,000 USD quarterly sales × 90 days = DSO 18 days. A healthy DSO depends on the credit terms granted: if you sell on 30-day terms, a DSO of 35–40 is excellent; 50–55 acceptable; above 65 is a warning sign.

What is a healthy delinquency ratio for an SMB?

For B2B SMBs the healthy benchmark is below 5% in stable sectors (SaaS, professional services, pharma) and up to 8–10% in tight-margin sectors (construction, transportation, retail). Above 15% the company has liquidity exposure and must review origination and collections immediately. For regulated consumer lending (SOFIPOs, CNBV fintechs) regulators view 3–6% as an acceptable quality level.

What is Average Days Delinquent (ADD)?

ADD = DSO − Best Possible DSO. It measures the average number of days an invoice is overdue before collection. Best Possible DSO is calculated using current (non-overdue) AR over sales. ADD below 5 days is excellent; 5–15 acceptable; above 20 indicates a severe collections problem or poor portfolio quality. It is the complementary metric to DSO that separates a legitimately long collection cycle from actual delinquency.

What is the Collection Effectiveness Index (CEI)?

CEI = ((Beginning AR + Credit sales − Ending current AR) / (Beginning AR + Credit sales − Ending total AR)) × 100. It measures how effectively your collections team collects what it could have collected in the period. 100% means you collected all overdue items in the period; above 85% is considered top quartile. It is the efficiency metric for the collections function, not a portfolio quality metric.

How do I reduce DSO in my business?

The effective levers are: (1) invoice at the moment of delivery (not at month-end close), (2) offer an early payment discount 2/10 net 30 (2% if paid within 10 days), (3) request 20–30% advances on new contracts, (4) automate reminders from day 1 of the due date, (5) validate purchase order and invoice data before sending, (6) segment collections by aging bucket with differentiated scripts, (7) use factoring for high-quality AR.

What is Expected Loss (EL) in accounts receivable?

EL = PD × LGD × EAD applied to accounts receivable. PD is the probability the account becomes uncollectible (varies by bucket: 2–5% at 0–30 days, 55–80% at 90+ days). LGD is the percentage of loss once default occurs (70–90% in unsecured B2B). EAD is the exposed balance. The aggregate EL of the AR portfolio is the forward-looking basis for the allowance for doubtful accounts under MFRS C-3, IFRS 9, or CECL.

How do I report a receivable portfolio to Mexico's Buró de Crédito?

Regulated financial entities (banks, registered SOFOMs, Fintech Law fintechs, cooperatives) must report individualized portfolio data by RFC/CURP monthly to Buró de Crédito and Círculo de Crédito via a structured file including balance, days past due, credit limit, opening date, and payment history. The submission is regulated by CNBV and Banxico. Non-financial entities may join voluntarily as a commercial information source to both contribute and query.

When should I write off an uncollectible account?

Under Mexican MFRS C-3, an account is written off when it is 90+ days past due and there is reasonable evidence of non-recoverability after documented collection efforts (calls, letters, collection agency, or legal action). It requires approval from the board or designated officer. The account is removed from the balance sheet but remains in off-balance-sheet accounts for subsequent recoveries. Under US GAAP CECL, the provision is forward-looking from origination and write-off occurs at 120–180 days for consumer and 90 days for commercial.

What is the difference between current, delinquent, and past-due receivables?

Current: account up to date with no arrears. Delinquent (past due): account with 1–89 days of arrears, still recoverable with standard collection efforts. Past-due (under Basel and CNBV): 90+ days of arrears, reclassified for loan loss reserve purposes. Written-off: past-due account already deemed uncollectible and removed from the balance sheet. Each segment has different provisions, accounting treatment, and recovery strategy.

Collections & Delinquency Simulator

Methodology

Variable Expenses = Revenue × (% Variable ÷ 100)

Monthly Net Flow = Revenue − Fixed Expenses − Variable Expenses

Accounts Receivable = Revenue × (Collection Days ÷ 30)

Balance[month 1] = Reserve + Net Flow − Accounts Receivable

Balance[month N] = Balance[month N-1] + Net Flow

Runway = Reserve ÷ (Fixed Expenses + Variable Expenses)

Practical example

B2B distributor of institutional cleaning products selling to modern retail chains: invoiced revenue $500,000/month, fixed costs $220,000 (DC rent, base payroll, vehicles), variable costs 35% of revenue ($175,000), 75 collection days (Soriana, Walmart, Chedraui pay 60-90), $300,000 cash reserve.

Monthly accounting profit: $500,000 − $220,000 − $175,000 = $105,000. The P&L looks healthy. Cash reality is entirely different.

75 collection days = 2.5 months lag. The first 3 invoices don't hit cash before you spend them on operations. Outstanding receivables: $500,000 × 75/30 = $1,250,000 locked with clients.

Cash projection at those parameters: month 1 balance = $300,000 − $395,000 = −$95,000. Month 2: −$490,000. Month 3: −$885,000 (the minimum, just before month 1 collection lands). Even being profitable, you need a $1,000,000+ credit line to survive Q1.

If you negotiate collection days down to 30 (50% deposit at order + balance at 30 days): month 1 cash = $300,000 − $395,000 = −$95,000. Month 2: $10,000. Month 3: $115,000. Operation is self-funding from month 3 with no credit line.

Operating recommendation: every 15 days of collections reduced frees up half a month of revenue ($250,000 in this profile) from receivables to available cash. Before chasing bank financing (12-18% APR), exhaust: (1) 2-3% early-payment discount, (2) factoring with BBVA/HSBC at 9-13%, (3) order deposits. Only then a formal credit line.

Interpretation

A positive monthly net flow doesn't guarantee liquidity. If your customers pay late, you can have cash problems even though you're profitable.

Runway tells you how many months your company survives without revenue. Less than 3 months is a risk zone; at least 6 is recommended.

If the projection line dips below zero, your business will need external financing at that point.

Review this projection monthly against actuals. The gap between projected and actual measures your cash management.

Assumptions and limitations

Assumes constant monthly revenue and expenses (no seasonality).

Accounts receivable impact only the first month of the projection.

Does not consider credit lines, financing or capital injections.

Does not include taxes, depreciation or financial expenses.

Runway assumes a complete revenue stop; in practice it would be partial.

When to use this calculator

When starting a new business. Before signing leases or hiring staff, project your cash flow to know how many months you can operate before revenue covers expenses. Many profitable businesses fail due to early-months illiquidity.

When your customers pay on credit. If you sell at 30, 60 or 90 days, invoicing doesn't reflect cash on hand. Cash flow shows when you actually receive the money and whether you can meet obligations while waiting.

To decide whether you can take on a major expense. Before buying equipment, hiring an employee or investing in inventory, simulate how it affects your cash balance over the next 6-12 months.

When negotiating payment terms with suppliers. If your flow is positive but tight in certain months, you can negotiate paying at 60 days instead of 30 to smooth expense peaks.

To determine how much working capital you need. Cash flow shows the lowest point of your balance — that's the minimum reserve you need to operate without surprises.

When a bank or investor asks for financial projections. The cash flow statement is one of the three basic financial statements they evaluate to grant credit or investment.

Common mistakes

Confusing profit with cash. You can have positive accounting profit and still run out of cash. If you sold $100,000 but your customers haven't paid yet, you don't have $100,000 in the bank. Cash flow measures real money, not payment promises.

Not accounting for seasonality. Most businesses have strong and weak months. A restaurant in a tourist area may bill twice as much in December as in September. Projecting constant revenue creates a false sense of security.

Underestimating average collection days. Customers rarely pay on agreed terms. If the contract says 30 days, the real average is usually 45-60 days. Use real collection data to project.

Forgetting irregular expenses. Year-end bonuses, annual insurance, license renewals, equipment maintenance and annual tax payments are expenses that don't appear every month but can empty your cash when they arrive.

Not maintaining an emergency reserve. The minimum rule is 3 months of fixed expenses in reserve. For businesses with corporate customers paying on credit, the recommendation rises to 6 months. Without reserves, any collection delay or unexpected expense creates a crisis.

Projecting growth without considering the cost of growth. If sales rise 50%, you probably need more inventory, more staff and more space. Growth consumes cash before generating it.

Industry use cases

Modern retail distribution

Chains pay in 60-90 days. Negotiate an early-payment discount (2-3%) — often cheaper than financing.

Professional services

Collection days 45-75. Recommended policy: 50% upfront at signing and balance 30 days post-delivery. Cuts effective collection days to 20-30.

Construction and projects

Billing by work progress. Collection days can rise above 90 due to disputes or validations. Requires working capital or a significant advance.

Insurance

Collections can take 90-120 days due to validations. Model aging by age bucket and provision for historical default rate.

Collections and delinquency calculator: from manual aging to integrated AR diagnosis

At collections fintechs, SOFIPOs, savings and credit cooperatives, B2B distributors of pharmaceutical goods, food, construction materials, and B2B SaaS with long collection cycles, the delinquency indicator stopped being a monthly board metric and became the operational metric that decides liquidity, credit policies and accounting write-off decisions. CFOs, credit managers and collection ops analyze their past-due portfolio with a precise technical vocabulary: DSO, CEI, ADD, aging buckets, roll rate, AR expected loss, charge-off and allowance for doubtful accounts. An improvised spreadsheet reports the average; the right calculator decomposes the delinquency index, projects recovery and models the impact on cash flow.

A serious collections calculator solves the three core AR management equations:

Delinquency rate = (Past-due portfolio / Total portfolio) × 100

DSO (Days Sales Outstanding) = (Accounts receivable / Total sales) × Days in the period

CEI (Collection Effectiveness Index) = ((Beginning AR + Credit sales − Current AR at period end) / (Beginning AR + Credit sales − Current AR current)) × 100

The delinquency rate is the static thermometer; DSO is the speed of converting sale into cash; CEI is the real collection-team efficiency — how close they are to collecting everything they should have collected in the period. The three are read together, not in isolation. A 97% CEI with rising DSO indicates the team is working the past-due well but new origination is deteriorating. Stable DSO with falling CEI means sales compensate but collection practices are loosening.

Numeric example: Mexican B2B distributor

Assume a pharmaceutical-products distributor with quarterly sales of USD 2.35M, total accounts receivable of USD 1.29M and past-due portfolio (more than 30 days) of USD 482K.

Delinquency rate = 482,000 / 1,290,000 × 100 = 37.3%

DSO = 1,290,000 / 2,350,000 × 90 = 49.5 days

With a standard credit term of 45 days, DSO should sit at 38-42 days; the current 49.5 implies 7-11 additional days of frozen capital — approximately USD 235-353K that should be in the bank and is in AR. The 37% delinquency rate is critical for the sector (LatAm B2B pharma median runs 12%-18%), signaling a systemic issue in origination, collections or both.

A disaggregated aging bucket reveals the operational truth: 0-30 days (USD 824K, 64%), 31-60 days (USD 265K, 20%), 61-90 days (USD 124K, 10%), 90+ days (USD 82K, 6%). The 16% in 60+ buckets is the credit alert: statistically, customers going 60 days without paying have a 62%-75% probability of reaching 90+, and the recovery rate after 90 days drops to 35%-50%. That convergence defines the collection team's priority target for the next six weeks.

Aging buckets and roll rate: the physics of deterioration

The aging report segments the portfolio into standard buckets: current (not past-due), 1-30 days, 31-60, 61-90 and 90+ days. The roll rate — the speed at which an account migrates from one bucket to the next — is the most predictive indicator of future delinquency that exists. A 30→60 roll rate of 40% means 4 out of every 10 accounts that enter the first delinquency bucket end up in the second. Healthy B2B portfolios keep that rate below 25%; well-managed LatAm consumer fintech below 32%; troubled distributors exceed 50%.

The math behind is a Markov chain: each bucket has a transition probability to the next, to current (when the customer pays) or to accounting write-off (90+ unpaid after interventions). Calibrating roll rate with your 12-24 month history, the calculator projects the formation of past-due portfolio over the next 60-90 days before it happens — the operational equivalent of seeing the ice crack before falling through.

ADD (Average Days Delinquent): the critical complement to DSO

ADD = DSO − Best Possible DSO

Best Possible DSO = (Current AR, not past-due / Sales) × Days. ADD measures the average days an invoice spends past-due before collection. A DSO of 60 with BPDSO of 55 (ADD = 5) is healthy: delinquency is marginal. A DSO of 60 with BPDSO of 35 (ADD = 25) reveals that 41% of the collection time is delinquency days — that is not a long collection cycle, it is a collections problem. Veteran CFOs look at ADD before DSO precisely for this reason.

AR expected loss: PD × LGD × EAD applied to receivables

The expected loss on accounts receivable applies the same logic as bank credit risk, adjusted to the commercial context:

EL_AR = Σ (PD_bucket × LGD × EAD_bucket)

PD by bucket: 0-30 days 2%-5%, 31-60 days 10%-18%, 61-90 days 30%-45%, 90+ days 55%-80%. Commercial B2B LGD without collateral: 70%-90% (very high because legal and agency collection costs consume a good share of residuals). EAD = balance per account. This framework allows forward-looking AR provisioning aligned with Mexican NIF C-3, IFRS 9 and US GAAP (CECL).

In the example distributor, applying standard PDs and 80% LGD, expected loss is roughly USD 106K — 8% of total portfolio. If the company has an allowance for doubtful accounts of 4%, it is under-reserved by half. Under NIF C-3 or CECL this generates an immediate P&L adjustment, and under NIF B-3 a cash flow adjustment.

Mexican regulation: CONDUSEF, Buró de Crédito and REDECO

In Mexico, any financial entity (including fintechs regulated under the 2018 Fintech Law) must report portfolio to Buró de Crédito and Círculo de Crédito monthly with traceability by RFC/CURP. CONDUSEF operates REDECO (Collections Firm Registry), where since 2022 it is mandatory for every collection firm to be registered, use an authorized script and respect the 'non-abusive practices' defined by the Federal Consumer Protection Law. Calling outside hours, threatening non-existent legal action, contacting family or personal references, or using dishonest language are sanctioned violations. For SOFIPOs, the Single Circular for Financial Entities (CUEFI) additionally regulates the preventive provision of credit risk with rating tables.

Accounting write-off in Mexico is governed by NIF C-3 (Accounts Receivable): an account is considered of difficult collection when it passes 90+ days without payment and there are signs of uncollectibility; it is written off with board approval or that of the delegated officer. Under NIF D-1 (revenue from contracts), written-off accounts leave the balance sheet but remain in off-balance-sheet accounts for later recovery attempts — it is common to find recoveries of 5%-15% on written-off portfolio in the following 18 months with a specialized agency.

US: FCRA, FDCPA, CFPB and write-off

In the United States, any B2C collections practice is governed by the FDCPA (Fair Debt Collection Practices Act, 1977), the FCRA (Fair Credit Reporting Act) for bureau reporting (Equifax, Experian, TransUnion) and CFPB (Consumer Financial Protection Bureau) rules. Contact limits, debt validation, prohibition of deceptive practices and the consumer's right to request written communication are mandatory. B2B commercial collections operate under the Uniform Commercial Code and state statutes.

Charge-off in US GAAP usually happens at 120-180 days for revolving consumer credit and at 90 days for commercial; under CECL (Current Expected Credit Losses, 2020+), the provision is estimated forward-looking over the life of the asset from day one, not when it deteriorates. That changed reserve logic for banks and non-banks from a reactive to a proactive method — the same direction the calculator is designed to reach.

Expected collections projection and payment-behavior curve

The most valuable part of the tool is projecting expected collection for the next 30, 60 and 90 days based on each bucket's historical payment pattern. If historically 82% of the 0-30 bucket is collected in 30 days, 45% of the 31-60 bucket in 30 additional days, and 22% of the 61-90 bucket in 30 more days, next month's collection projection is deterministic with a confidence interval. That feeds directly into the cash flow model — the reason this tool naturally crosses with the SMB cash flow simulator.

Collection strategies by bucket: what moves the CEI

Levers vary by bucket. 0-30 days: automated friendly reminder (email/SMS), receipt confirmation of the invoice, PO validation and sales contact — this recovers 80%-90% without friction. 31-60 days: call from the account executive, validation of operational issues (wrong invoice, quality problem, dispute), payment plan if applicable. 61-90 days: escalation to the credit manager, formal collection letter, consideration of credit suspension for new orders. 90+ days: send to a specialized collections firm (typical fees 15%-30% of recovery) or decide on a lawsuit (costs 8%-15% plus professional fees). Factoring is a parallel option to monetize active AR — not past-due — at a 1.5%-4% monthly discount. Each lever must be evaluated by its effective cost of recovery: a lawsuit on a USD 1,765 account rarely closes positive.

Differentiation vs Excel

Excel computes the delinquency rate with a division. It does not calculate CEI or ADD, does not build the roll-rate Markov chain, does not project expected collection with confidence intervals, does not align with NIF C-3/IFRS 9/CECL, does not produce the aging report segmented by customer and sector, and does not integrate MX/US regulatory context. The calculator closes that gap for the CFO or credit manager who needs a quantitative briefing in minutes, not an analysis that takes a week of a senior analyst and an external accounting firm.

Conclusion

Collections is not a telephonist; it is commercial risk management. The delinquency rate, DSO and CEI read together with aging buckets and roll rates turn a USD 482K portfolio into manageable debt decomposition with actions prioritized by impact. For LatAm collections fintechs, B2B distributors with 30/60/90-day credit customers, SOFIPOs reporting to regulators, B2B SaaS with long collection cycles and CFOs who must present credible board projections, the calculator is the bridge between the billing system and the quarter's financial projection. The next time the committee asks "of those USD 1.29M receivable, how much actually comes in over the next 60 days?", the answer must be in minutes, not conjecture.

Metric	Value	Source
B2B manufacturing LatAm — DSO median	47 days	APQC Open Standards AR Benchmarks 2024
B2B SaaS global — DSO median	38 days	HighRadius AR Benchmarks SaaS 2024
Mexico consumer credit delinquency index	3.1%	CONDUSEF Fintech Sector Report 2024
Healthy CEI — top-quartile companies	≥85%	ACA International Collections Benchmarks 2024
Recovery rate — 90+ day B2B receivables	35-50%	Atradius Payment Practices LatAm 2024
Commercial B2B LGD (unsecured)	70-90%	Moody's Commercial Credit Recovery Studies
Average payment delay above agreed terms — LatAm 2024	18 days past due	Atradius Payment Practices Barometer LatAm 2024
Mexico commercial credit delinquency rate — Dec 2024	1.9%	CNBV Portafolio de Información Bancaria

Metric

Value

Source

B2B manufacturing LatAm — DSO median

47 days

APQC Open Standards AR Benchmarks 2024

B2B SaaS global — DSO median

38 days

HighRadius AR Benchmarks SaaS 2024

Mexico consumer credit delinquency index

3.1%

CONDUSEF Fintech Sector Report 2024

Healthy CEI — top-quartile companies

≥85%

ACA International Collections Benchmarks 2024

Recovery rate — 90+ day B2B receivables

35-50%

Atradius Payment Practices LatAm 2024

Commercial B2B LGD (unsecured)

70-90%

Moody's Commercial Credit Recovery Studies

Average payment delay above agreed terms — LatAm 2024

18 days past due

Atradius Payment Practices Barometer LatAm 2024

Mexico commercial credit delinquency rate — Dec 2024

1.9%

CNBV Portafolio de Información Bancaria

Collections and delinquency simulator