Sales commission calculation

Purpose

To ensure sales commission payouts are accurate, policy-compliant, and audit-ready.

Commission calculation is a payroll-critical process that determines how much each sales rep is owed for closed deals within a given period. The process includes reconciling CRM exports against accounts receivable payment data, applying rate tables in Excel, and tracking held commissions in a separate register. If done manually, it’s prone to errors and can end up creating payroll disputes, inaccurate payments, and compliance risk.

Data sources

1. Closed deal data: CRM export containing closed deals, sales rep IDs, deal values, close dates, and product lines. Filtered to deals closed within the commission period.
2. Payment terms per deal: Per-deal record of the agreed payment terms, used to determine whether a deal is eligible for commission payout in the current period.
3. Commission rate tiers: A rate table per rep or team with attainment thresholds and the commission percentage applied at each tier. Maintained as a reference file and updated when needed.
4. Accounts receivable payment status: Accounts receivable data showing which invoices have been paid, partially paid, or remain outstanding.

Key processing steps

1. Filter deals eligible for commission

The workflow begins by filtering the closed deal dataset to retain only deals eligible for commission in the current period. A deal is eligible if it was closed within the period boundary and falls within its payment term window.

2. Cumulative attainment calculation

For each rep, deals are sorted chronologically by close date within the period. Deal values are summed cumulatively to determine attainment against quota at the point each deal was closed. This cumulative position determines which tier rate applies to each deal.

3. Commission hold on unpaid invoices

The workflow joins each eligible deal to the AR payment status. Deals where the invoice is unpaid or partially paid are flagged for hold. The held commission amount is calculated and written to a separate register, not included in the current payroll file.

4. Commission aggregation per rep

Payable commission amounts are summed at the rep level across all eligible, non-held deals. The deal-level detail is preserved in the commission statement and the aggregated total flows to the payroll summary.

Outputs

1. Commission statement per rep: Deal-level breakdown showing deal ID, deal value, attainment at point of close, tier rate applied, held amount (if any), and net payable commission.
2. Payroll summary file: Aggregated total payable commission per rep for the period, formatted for upload to the payroll system. Includes rep ID, name, and total payable amount.
3. Held commission register: All deals where commission was withheld, with rep ID, deal ID, deal value, hold reason (unpaid, partial, no invoice match), and held commission amount. This gets updated each run and carried forward until it’s resolved.