Sales Commission Accelerator Simulator

Purpose and model boundary

This calculator runs a sales team's credited sales through up to three commission plan designs for one 12-month plan year and shows what each design would pay. You enter up to 10 employees, up to 100 sales and returns, and up to 200 credit-split rows that say who is credited with each sale and what share. Each design has its own tier table and its own rules for the measurement period, attainment, revenue or gross profit basis, marginal or retroactive rates, a threshold gate, a cap, a draw and a payment lag. The workbook posts commission period by period for each employee under the design assigned to them, and also runs every employee and every sale through every named design so the designs can be compared on the same year. The main results are the commission earned in the plan year and the cash paid in it.

A period ledger, a tier audit and two charts show one employee under one design in detail. In the Scenario grid mode an 11 by 11 table adds an annual what-if for that employee: credited revenue down the rows, and gross margin or quota across the columns.

Every figure on this page is computed by the spreadsheet; the page adds nothing to it. The model is educational: it applies the plan rules you describe, and it is not a payroll calculation, an accounting entry or legal advice about a commission agreement.

Inputs and units

• Plan year start: the first day of a month from 2000 to 2099. The plan year is the 12 months from it, and every credit date must fall inside it.
• Employees, one row per employee from the first row, up to 10: a synthetic employee ID (a capital letter followed by up to 15 capital letters or digits), the annual salary, the revenue quota and the gross-profit quota, optional hire and termination dates, optional eligibility start and end dates, the assigned design (1 to 3) and an optional opening recoverable draw balance that overrides the design's. A revenue design needs the revenue quota and a gross-profit design the gross-profit quota, each above 0.
• Sales and returns, up to 100 rows, in credit date order and then ID order: a transaction ID, the credit date, the revenue, the direct cost, an optional product group and, for a return only, the ID of the earlier sale it returns. A return has negative revenue and no direct cost or product group. The rows can be pasted from a spreadsheet.
• Credit splits, up to 200 rows in any order: a transaction ID, an employee ID and the share of the sale credited, above 0 and at most 1 (0.5 is 50%). Each sale's shares must total 1; credit no one is assigned to goes to the reserved ID UNALLOCATED, which earns nothing. A return uses its sale's splits and has none of its own.
• Product groups, optional, up to 20: a group code and the multiplier each design applies to the commission on that group. A blank multiplier means 1. A multiplier scales payout, never attainment.
• Each plan design: a name (a blank name switches designs 2 and 3 off), a three-letter currency label shared by every named design, the measurement period (monthly, quarterly or annual), the attainment basis (year to date or each period alone), the commission basis (revenue or gross profit), the payout method (marginal or retroactive), the tier table, the threshold gate (none, deductible or cliff) and its threshold as a share of quota, an optional cap per period or per plan year (blank means no cap; 0 caps everything), the draw (none, nonrecoverable or recoverable) with its amount per period, an opening recoverable balance and the most of one period's payable commission that may repay it, the payment lag in measurement periods (0 to 12), and an optional design base salary that replaces every employee's salary under that design (0 means pure commission).
• Tier table, up to 10 rows per design: each tier's lower and upper attainment bound as a share of quota and its rate. The first tier starts at 0, each tier starts where the last one ended, and only the last upper bound may be blank, meaning no limit. An upper bound on the last row is a hard stop: nothing accrues above it.
• Employee and design to inspect, the calculation mode, and the steps of the scenario grid: a revenue step as a share of base revenue, and a margin step or a quota step for the columns.

Amounts are in the currency you label; nothing is converted. In the tier tables the lower and upper bounds (as fractions of quota) and the rate are entered as decimal fractions, for example 0.05 for a 5% rate; the gate threshold and the recovery fraction are shown as percentages.

Governing relationships

Credit and basis. A split row credits an employee with its share of a sale. Under a revenue design the basis is that share of the revenue; under a gross-profit design it is that share of revenue less direct cost, never below 0 for a sale. A credit dated outside the employee's eligibility window (from the latest of the plan year start, hire date and eligibility start to the earliest of the plan year end, termination date and eligibility end) counts toward no attainment and earns nothing.

Attainment. Each employee's credits are taken in sale order. Year-to-date attainment is the credited basis so far divided by the annual quota. Period attainment measures each period alone against a quota prorated by the eligible days in the period over the eligible days in the year.

Commission on a sale. With the window quota Q, the attainment basis before the sale x₀ and after it x₁, and tiers with lower bound l, upper bound u and rate r:

marginal commission = multiplier × [C(x₁) − C(x₀)], where C(x) = Σ r × max(0, min(x, Q × u) − Q × max(l, g))

Here g is the gate threshold under a deductible gate and 0 otherwise, so a deductible pays only on credit above the gate. Under the retroactive method the whole eligible basis of the window earns the rate of the tier reached, so commission jumps when a boundary is crossed; a value exactly on a boundary takes the tier that begins there. A cliff gate pays nothing in a window until attainment reaches the gate: year to date, it holds commission until the gate is reached and then releases it; with period attainment, a period that misses the gate forfeits that period's commission.

Returns. A return reverses its sale's commission in proportion: the returned revenue over the sale's revenue, at the sale's own tier and multiplier, measured before caps, posted in the return's period. Returns of one sale are capped at 100% in total; any excess is ignored and reported. Attainment keeps the original credit.

Caps and posting. A period cap limits each period's increase in entitlement; a year cap limits the running total. Commission removed by a cap or held back by a cliff is reported, not hidden. Each period's commission is rounded to the cent, half away from zero, including negative amounts, and every total adds the rounded postings.

Draws and payment. Commission becomes payable the lag number of periods after it is earned; commission earned so late in the year that it is payable after the plan year is reported separately. A nonrecoverable draw pays the larger of the draw and the payable commission. A recoverable draw pays the same, and the amount of draw above commission becomes an advance:

recovery = min(open balance, recovery limit × payable commission, max(0, payable commission − draw))

cash paid = max(draw, payable commission) − recovery

A draw is prorated by eligible days in a partial period and is paid in its own period, without lag. A period whose commission is negative after a return is carried as a disclosed negative balance; it is never taken from cash or from final pay.

Salary. Salary for a period is the annual salary, or the design base salary when one is set, times the days employed in the period over the days in the plan year.

Scenario grid. Each cell is an annual what-if for the employee and design inspected: credited revenue R down the rows, moved by −5 to +5 revenue steps from the employee's own credited revenue, and across the columns either the gross margin (on a gross-profit design, moved in margin steps) or the quota (on a revenue design, moved in quota steps). The basis is R, or R times the margin, against the quota; the commission is the tier formula at the employee's average product multiplier, the cliff test and the yearly cap. A cell whose margin is below 0% or above 100%, or whose quota is not above 0, has no figure, and if the employee has no credited revenue no cell has one; the reason for each such cell is in the reasons table beside the grid, which has the same layout. The first column of the grid gives each row's credited revenue, the column headings count steps from −5 to +5, and the margin or quota of each step is listed beside the grid. The centre cell equals the base case.

Calculation sequence

The spreadsheet first checks the plan year, the designs and their tier tables, the product groups and each row of the three tables. It then resolves every split into a credit line, computes each line's basis and the attainment before and after it, the commission it earns under each design, the returns, the period entitlements, gates and caps, the posted commission, salary, draws, recovery and cash for each employee and period, and the totals by employee, by design and for the team. A block of consistency checks confirms the arithmetic, including that every positive commission is paid either in the year or after it and that the scenario centre equals the base case, before the status is returned.

Outputs and interpretation

The headline is the commission earned in the plan year by every employee under the assigned design, and the commission cash paid in the plan year. The cards also show commission payable after the year, salary, total compensation on the earned and on the cash basis, commission removed by caps, withheld by cliffs and reversed by returns, the recoverable draw advanced, recovered and still outstanding, the guaranteed draw paid above commission, negative adjustments, credited revenue and gross profit, commission as a share of each, and revenue that earns nothing because it has a negative margin, falls outside eligibility or is unallocated. The tables compare the designs, list each employee's results, give the inspected employee's period ledger and tier audit and, in the Scenario grid mode, the 11 by 11 what-if.

Any warnings appear under the results. The model version and the other run details are in the downloaded CSV and JSON files. The per-row check tables and row counts are returned to the API and MCP, and the first problem they find is named in the status.

Under the default inputs, three employees on three designs make 21 sales in 2026 and one return, which partly reverses a sale, and the employee inspected is the first. The team earns 75,585.50 in commission in the plan year and is paid 71,225.50 in commission cash in it; 11,130.00 earned late in the year is payable after it because of the payment lag. A cliff withholds 1,880.00, and the return reverses 270.00. The status is OK, with notes on the cliff, the lag, the negative period and the outstanding draw balance.

The first chart draws each named design's commission over a range of attainment, so the designs' shapes can be compared directly. The second shows commission earned and cash paid by period for the employee inspected, and the third compares what each design would cost for the whole team.

Validation and status logic

The workbook returns one status, chosen in this order: NOT VALID, then CHECK, then OK. A short status code names the reason.

• NOT VALID names the first problem it finds: the plan year start; a choice outside its list; no named design; the first invalid field of a named design; different currency labels; the first tier-table problem (no tiers, a first tier not starting at 0, a gap, a tier not starting where the last ended, an upper bound not above its lower bound, a missing upper bound before the last row, or a rate outside 0 to 1); the product groups; no employee rows; then the first problem in the employee, sale and split rows, in that order (gaps, IDs, duplicates, dates, quotas, the sale order, signs, direct cost, product groups, return links, split shares that do not total 1, and a return with split rows); and finally the employee or design to inspect and the scenario steps. NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• CHECK accompanies a computed result that needs the stated review: returns of one sale total more than 100% and the excess was ignored; a gross-profit design meets a sale with negative margin, which adds no basis; or a credit falls outside an employee's eligibility dates and was excluded.
• OK otherwise.

Notes in the warnings list say when a cap reduced pay, a cliff withheld or forfeited commission, payments fall after the plan year, a negative period is carried as a balance, a recoverable draw balance is outstanding, unallocated credit is present, scenario cells were not computed, or the grid ignores the design's period windows or period cap. A note does not change the status.

Assumptions and limitations

• One plan year of 12 calendar months. Commission periods are monthly, quarterly or annual; there are no weekly or semimonthly commission periods, and the payment lag counts measurement periods.
• Capacities are 10 employees, 100 sales and returns, 200 split rows, 10 tiers per design and 20 product groups.
• Only the sales table has a required order (credit date, then transaction ID). The page puts pasted rows in that order; an API caller must send them in it, and the workbook refuses rows out of order rather than sorting them.
• A return reverses the commission its sale earned before caps, so under a period cap it can reverse more than was paid for that sale. Under a period-attainment cliff, a sale whose period missed the gate reverses nothing.
• Negative periods are never netted against later commission or deducted from final pay; they are disclosed as a balance.
• Every named design must share one currency label; nothing is converted.
• The scenario grid is an annual what-if for one employee and design. It leaves out returns, draws, payment lag, period caps and period windows, and it uses the employee's average product multiplier.
• Cents are rounded half away from zero, including negative amounts.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A gap between rows, a sale dated outside the plan year, a split total other than 1, a return to an unknown or later sale, a product group missing from the table, and an employee ID used twice are refused by the workbook. A blank second or third design name switches that design off and hides its fields. The scenario grid is left blank in the Calculate mode.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure, a timeout or a cancelled run cannot establish a fresh workbook answer, and the last result stays on the page marked as out of date. Review the workbook's References and Disclaimer, and your actual commission plan documents, before using a result for a real decision.

Sources and further reading

Your team, sales, credit splits and plan rules come from the inputs; the calculator holds no statutory data, and its arithmetic follows the plan you describe. The public sources below explain how commission pay is treated by federal wage and tax rules. They do not endorse this calculator.

• IRS Publication 15 (2026), (Circular E), Employer's Tax Guide, section 7, "Supplemental Wages", lists commissions among supplemental wages, which sets how income tax is withheld from them when they are paid (retrieved 2026-10-26).
• IRS Publication 525 (2025), Taxable and Nontaxable Income, "Advance commissions and other earnings", explains how advance commissions and later repayments of unearned commissions are treated, the situation a recoverable draw creates (retrieved 2026-10-26).
• U.S. Department of Labor, Fact Sheet #56A: Overview of the Regular Rate of Pay Under the Fair Labor Standards Act explains that earnings may be paid on a commission basis and that the regular rate used for overtime includes all remuneration for employment (retrieved 2026-10-26).
• U.S. Department of Labor, Fact Sheet #20: Employees Paid Commissions by Retail Establishments Who are Exempt Under Section 7(i) from Overtime under the FLSA sets out when commission-paid retail and service employees, including those paid draws and commissions, are exempt from overtime (retrieved 2026-10-26).

This is an educational model of the plan rules you enter, not payroll, accounting, tax or legal advice. Commission agreements, state wage-payment laws and accounting rules differ; check a result against your actual plan documents and a qualified professional.