5 Commission Calculation Errors That Cost UK Businesses Thousands

A sales leader we spoke with recently shared a story that still makes him wince. A decimal point in his commission spreadsheet had shifted one column to the right. The result: a rep received a commission payment ten times larger than they'd earned. The rep, to their credit, flagged it immediately and returned the money. But the error had already been processed through payroll, reported to HMRC, and recorded in the company's accounts. Unwinding it took weeks.

This isn't rare. Commission calculation errors happen constantly in UK businesses, and they fall into predictable patterns. Some cost money directly. Others cost something harder to recover: your team's trust in the numbers.

Here are the five errors we see most often.

1. The VLOOKUP That Pulls From the Wrong Column

This is the classic. A VLOOKUP formula references column F when it should reference column G. Or someone inserts a new column into the source data, and every lookup shifts without anyone noticing.

The damage compounds because these errors are silent. The formula returns a value — just the wrong one. There's no error message, no red flag. The spreadsheet looks fine. The rep gets paid. It's only when someone manually checks the calculation three months later that the discrepancy surfaces.

According to research published in the Journal of End User Computing, approximately 88% of spreadsheets contain at least one error. In commission spreadsheets — where formulas often span multiple sheets and reference dozens of data points — the probability approaches certainty.

What to watch for: Any time your source data structure changes, audit every formula that references it. Better yet, build your lookups to fail visibly when something unexpected happens rather than returning plausible-looking wrong numbers.

2. Deals Counted Twice (Or Not at All)

CRM data is messier than it looks. A deal gets closed, then reopened for an amendment, then closed again. A rep transfers a deal to a colleague mid-cycle. Someone marks an opportunity as "Closed Won" in December but the invoice doesn't go out until January.

Each of these scenarios creates opportunities for double-counting or missed deals. In one company we heard from, a quarterly commission run included £340,000 worth of deals that had been counted in the previous quarter's calculations as well. The CFO caught it during reconciliation. Had it gone through, the overpayment would have exceeded £25,000.

The inverse is equally common: deals that fall through the cracks entirely. A rep closes business that never appears in their commission statement. They raise it, but now you're in dispute resolution mode — and even if you fix it promptly, trust has eroded.

What to watch for: Build explicit rules for when a deal becomes commissionable. Is it the close date in the CRM? The invoice date? The date payment is received? Document this clearly, and ensure your calculation process matches the policy exactly.

3. Applying the Wrong Commission Rate

Commission structures aren't static. Rates change when reps hit accelerators. They vary by product line, deal size, or customer type. A rep might be on a different plan than their colleague, or on a transitional arrangement after a role change.

Keeping track of which rate applies to which rep on which deal is genuinely difficult. And when the wrong rate gets applied, the error often goes unnoticed — because the calculation still produces a reasonable-looking number.

One finance director described discovering that a senior rep had been paid at their previous (lower) rate for four months after a promotion. The underpayment totalled over £8,000. The rep had assumed the numbers were right and hadn't checked. When the error surfaced, the rep was furious — not about the money, which was corrected, but about the four months they'd spent believing the company valued them less than it actually did.

Under HMRC guidance, commission payments are treated as earnings and must be included in PAYE calculations at the point they're paid. Corrections require adjustments to tax records, which creates administrative burden beyond just the pound value of the error.

What to watch for: Maintain a clear, version-controlled record of each rep's commission plan, including effective dates for any changes. Cross-reference this against your calculation logic every pay period.

4. Currency and Exchange Rate Miscalculations

For UK companies with international deals, currency creates another failure point. A deal closes in euros or dollars, but commission is calculated in sterling. Which exchange rate applies? The rate on the day the deal closed? The day the invoice was raised? The day the payment came in? The rate used in the CRM versus the rate used by finance?

We've seen cases where the CRM stored a deal value in the original currency but the commission spreadsheet treated it as GBP, effectively paying commission on a number 15-20% higher than the actual sterling value.

What to watch for: Establish a single, documented rule for exchange rate conversions and apply it consistently. Ensure your CRM data clearly indicates currency, and verify that downstream calculations handle conversion correctly.

5. Manual Adjustments That Don't Get Documented

Commission calculations rarely survive contact with reality unchanged. There's always something: a clawback for a churned customer, a SPIF payment, a one-time adjustment for a deal that straddled two periods, a correction for last month's error.

The problem isn't the adjustments themselves — they're often necessary. The problem is when they're made directly in the spreadsheet without documentation. Someone adds £500 to a rep's total with a note like "adjustment per Tom." Three months later, nobody remembers what the adjustment was for. Was it a one-time payment? Should it have been recurring? Did it actually get paid, or was it just logged for future payment?

These undocumented adjustments accumulate. They make the spreadsheet impossible to audit. And they create the conditions for disputes, because nobody can reconstruct why the numbers are what they are.

What to watch for: Every adjustment needs three things: what was changed, why it was changed, and who authorised it. If your current system doesn't capture this, it's not a system — it's a liability.

The Compounding Cost of Errors

Individual errors cost money. But the real damage is cumulative. Each error that slips through — even if it's eventually caught and corrected — teaches your team something: the numbers might be wrong.

Once that belief takes hold, your best reps start shadow-accounting. They build their own spreadsheets to track what they think they're owed. They scrutinise every statement. They raise queries on deals that were actually calculated correctly, because they've lost faith in the process.

Research from Harvard Business School found that sales compensation design directly affects both motivation and behaviour. What the research doesn't always capture is that compensation administration matters just as much. The most elegant commission plan in the world fails if reps don't trust the execution.

This is the hidden cost of commission errors: not just the pounds paid incorrectly, but the time spent resolving disputes, the attention diverted from selling, and the trust that takes months to rebuild.

What Good Looks Like

Error-free commission calculation isn't about working harder at spreadsheets. It's about building systems where errors either can't happen or can't hide.

That means automated data flows rather than manual exports. Calculation logic that's visible and auditable rather than buried in nested formulas. Real-time visibility so discrepancies surface immediately rather than festering until month-end.

It means treating commission accuracy as infrastructure, not a recurring task.

The companies that get this right don't just save money on corrections. They free their sales leaders to focus on strategy instead of spreadsheet forensics. They give their reps confidence that the numbers are right. And they build the kind of operational trust that lets everyone focus on what actually matters: closing business.