Commission Tracker Template for Small Business

The first commissioned salesperson I hired turned month end into an event. I rebuilt the same file every month, retyped closed deals out of the invoicing system, applied a rate I was confident about, and sent a number. That held up until the rep asked why one deal paid less than expected, and I could not answer without reconstructing three weeks of edits to a file that had no history in it.

A commission tracker is not hard to build. It is hard to keep honest, because it has to survive refunds, split credit, a plan change halfway through a quarter, and a payroll deadline that does not move. Almost every dispute I have seen came from a missing column rather than a broken formula.

There are six trackers here: a deal log with a per-rep summary, a tiered calculator, a draw reconciliation ledger, a chargeback and adjustment log, a per-rep statement built for the payroll handoff, and a setup and audit checklist. Each downloads as an editable Excel workbook, free and without an email.

What a Commission Tracking Spreadsheet Does

A commission tracking spreadsheet records every sale credited to a salesperson, applies the rate from your plan, and totals what each rep is owed for a pay period. It works in two layers: a deal-level log where one row is one sale, and a per-rep summary that turns those rows into a single payable number.

What it does not do is decide anything. Whether a commission is earned at signature or at payment, whether a refund claws money back, how two reps split a deal: those come from the commission agreement, and a tracker built before they are settled ends up encoding whatever the person building it assumed.

The Columns Every Commission Tracker Needs

A tracker that holds up needs ten fields: a deal identifier, the close date, the customer, the rep credited, the commission base, the rate, the commission earned, the earning trigger, the pay period, and the approver. Add an eleventh, the split share, the moment two people can work the same deal. Everything past that, a status column included, is convenience.

Two of those columns get skipped constantly. The commission base is not the invoice total in many plans, since freight, taxes, installation, and discounts are commonly excluded, so a file storing only the invoice pays on the wrong number every time. The split share is the other: the rows for one deal have to total 100 percent, and the number belongs in the file when the deal closes rather than when the payout is questioned.

Which Template Should You Use?

Plan shape decides most of it. A flat percentage on closed deals needs only the deal log and its summary. Tiers, draws, and chargebacks each add a workbook, and the setup checklist is worth ten minutes whichever of them you use.

Hiring your first commissioned rep

If the tracker is new because the role is new, build the plan before the offer goes out rather than after. The compensation plan settles the rate, the commission base, the earning trigger, the payout cadence, and the tier method, and those five decisions are what a sales representative job description is quietly promising when it quotes on-target earnings.

Then treat the plan as an onboarding document, because that is what it is. The signed agreement, the first-month ramp targets, and the tracker row for the first deal all belong to the same week. FirstHR covers that side: e-signature on the plan, the sales onboarding tasks laid out before the start date, and the signed version stored against the employee profile. Applicant tracking is coming soon to FirstHR.

6 Free Commission Trackers

Download all six together or take the one you need. Each opens in Excel, Google Sheets, or Numbers. Sample rows are included so the structure is obvious; delete them and enter your own.

Template 1: Commission Deal Log and Rep Summary

One row per credited sale, a summary sheet that rolls those rows into a payable figure per rep per period, and a third tab holding the exact formulas to paste into the calculated columns. This is the tracker most small businesses need and the only one some ever will.

Template 2: Tiered Commission Calculator

A tier table plus the same 120,000 dollars in sales worked twice, once marginal and once retroactive, so the gap between the two methods is a figure rather than an argument. The settings tab pins the measurement period and the reset date.

Template 3: Draw Against Commission Ledger

Draw paid, commission earned, amount applied to the draw, cash paid out, and the balance carried forward, with three sample months showing a ramp balance building and then clearing. The settings tab records whether the draw is recoverable and how recovery is capped.

Template 4: Chargeback and Adjustment Log

Every refund, cancellation, downsize, and rate correction as its own dated row against the original deal, with the approver named. The policy tab holds the chargeback window, the triggers you will honor, and the cap on how much a single paycheck absorbs.

Template 5: Rep Commission Statement and Payroll Handoff

A per-period statement in a format you can send without editing, a handoff sheet carrying hours and the regular rate for nonexempt reps, and a sign-off tab recording when each statement was acknowledged and when any question was resolved.

Template 6: Setup and Audit Checklist

The ten decisions to settle before you enter a single deal, a monthly reconciliation routine that catches the errors these files actually develop, and an honest list of the signals that a spreadsheet has stopped being the right tool.

How to Calculate Commission in a Spreadsheet

Multiply the commission base by the rate on each deal row, then sum by rep and pay period. The arithmetic is trivial. The definitions are where money goes missing, which is why the worked month below tracks the base separately from the invoice.

Those invoices carry $4,400 of freight and installation that the plan excludes. Paying 6 percent on invoice totals rather than on the base would have produced $2,679 for the month instead of $2,415, an extra $264 nobody would have caught. Across a year and three reps, that is real money moving on a definition nobody wrote down.

Store rates as decimals rather than as text, so 0.06 rather than the words six percent, because a text value will not multiply. The workbooks ship with plain values instead of live formulas, so nothing breaks when the file opens in another spreadsheet program, and the Formulas to Paste tab in template 1 gives the exact string for each calculated column, including the SUMIFS that rolls the log up per rep and period.

Marginal or Retroactive Tiers

A tiered plan raises the rate as cumulative sales climb, and it has to state whether the higher rate applies only to sales inside that band or retroactively to every dollar from the first. The same quarter pays very differently under each.

That is $3,000 of difference on one rep in one quarter, produced entirely by a word in the plan. Marginal is the cheaper of the two; retroactive is a stronger incentive to push past a threshold and should be priced deliberately rather than adopted because it sounds generous.

Whichever you use, the tracker has to record the method and the reset date, because a tier is meaningless without knowing when cumulative sales return to zero. Monthly resets pay faster and reward consistency, quarterly resets smooth a lumpy pipeline, and switching between them mid-year rewrites what people thought they were earning.

Commission, Overtime, and the Regular Rate

Commission paid to a nonexempt employee is pay for hours worked and must be included in the regular rate used to figure overtime. The rule is explicit in 29 CFR 778.117, which covers commissions whether they are the only pay or supplement a salary. Paying overtime on the hourly rate alone underpays every week a rep earned commission.

The gap in that week is $26.09, which looks trivial until you multiply it by a year of weeks and a couple of reps, and it is exactly the kind of arrears a wage claim is built from. Classification decides whether the calculation applies at all, so confirm each rep against the exempt and nonexempt tests rather than assuming a commissioned role is exempt.

When a commission is paid after the regular payday and covers several workweeks, 29 CFR 778.119 requires it to be apportioned back over the workweeks of the period in which it was earned, with extra overtime paid for any week that ran past the maximum hours. That retroactive step is the reason the payroll handoff sheet in template 5 carries hours and the regular rate per workweek rather than one monthly figure.

Withholding on a Commission Payout

Commission is wages, so the same taxes apply as on salary, with one shortcut on federal income tax. The IRS treats commissions as supplemental wages, which lets you withhold at an optional flat rate when the commission is paid separately from regular pay instead of running it through the withholding tables.

The point reps misread, and the one worth explaining before the first payout rather than after, is that a withholding method changes what comes out of a particular check and not what is owed for the year. A large flat-rate deduction on a big month is not a penalty on commission, and the same misunderstanding shows up with bonus withholding.

Your tracker should carry gross commission and stop there. Net pay, tax deposits, and the figures that end up on the pay stub belong to payroll. FirstHR is an onboarding and HR platform, not a payroll provider.

What Breaks a Commission Tracker

Four failures account for most commission disputes at small-business scale, and none of them is a formula error. Each one is a column that was never added or a habit that was never set.

The pattern behind all four is the same. A large company runs commission through a compensation team and a system that keeps its own history. A small business runs it through one file and one person, usually the owner, usually while doing four other things, and the exposure is identical. Keeping the payroll records that sit behind each payout is what turns a disputed number into a five-minute conversation.

That gets harder with every hire. The month a second rep starts is the month split credit, ramp draws, and two sets of statements arrive at once, and the plan someone signed on day one becomes the document everything is measured against. FirstHR keeps that side in order: the signed plan captured with e-signature, onboarding tasks for the first month, and the employee record holding both. Applicant tracking is coming soon to FirstHR.

Log, Reconcile, and File

A downloaded workbook is the starting point, and these work on their own. The strain shows up in the upkeep: deals logged at month end from memory, adjustments made by overwriting, statements never sent, and no dated copy of the period anyone could produce a year later.

To run the people side of that without a second spreadsheet, FirstHR stores the signed commission plan against the employee profile, keeps every superseded version next to the current one, and puts the onboarding paperwork for each new sales hire on a checklist with dates rather than in an inbox. FirstHR is an onboarding and HR platform, not a payroll provider, a commission engine, or a law firm: it does not calculate attainment, run payroll, or decide what your plan should say, so pair it with your payroll provider and a qualified attorney for those calls. Applicant tracking is coming soon to FirstHR.

Frequently Asked Questions