Commission rounding: find the cent instead of hiding it
Reproduce a one-cent commission difference, compare rounding per line with rounding a total, and apply one signed rounding policy consistently.
Two worksheets can use the same sales and percentage yet differ by a cent. The missing detail is often when rounding happens. A commission close needs both a precision rule and a calculation order, especially when refunds or many small lines are involved.
The useful part
State whether commission is rounded on each item or only after adding unrounded results. Trimsum rounds each line to cents, with exact half-cent results rounded away from zero, and then adds the line results.
Separate calculation precision from display precision
Displaying two decimal places does not necessarily mean a spreadsheet has rounded its stored value. A cell may show $5.01 while retaining $5.005 for later arithmetic. Adding those hidden values can produce a different total from adding the visible cents.
Decide whether the record is built from rounded item commissions or from a single calculation on the combined basis. Neither formula should be substituted silently for the other. The method must match the intended calculation and be applied consistently across the close.
Reproduce a one-cent difference with three lines
In this fictional AUD example, three accepted lines each have a supplied basis of $10.01 and a 50% rate. Each exact commission is $5.005. Rounding each positive half-cent away from zero produces $5.01.
| Line | Basis | Exact commission | Rounded line |
|---|---|---|---|
| A | $10.01 | $5.005 | $5.01 |
| B | $10.01 | $5.005 | $5.01 |
| C | $10.01 | $5.005 | $5.01 |
| Sum of rounded lines | $30.03 | — | $15.03 |
| Round combined basis × 50% | $30.03 | $15.015 | $15.02 |
The difference is explained
$15.03 − $15.02 = $0.01. No source row is missing; the two totals use different rounding orders.
Apply the signed rule to refunds too
A reversal of one example line has a basis of −$10.01. At 50%, the exact result is −$5.005. Under half-away-from-zero rounding, the rounded result is −$5.01, which cancels the original rounded $5.01 exactly.
Do not rely on a programming language’s default rounding function without testing negative half values. Some methods treat positive and negative ties differently. In a reconciliation record, a full signed reversal should follow the documented rule rather than acquire a different treatment because its sign changed.
Check whether the basis was rounded earlier
Commission rounding is not the only possible rounding step. If an exclusive basis is derived from an inclusive amount using an assumed tax rate, that basis may first be rounded to cents. Commission is then calculated from the rounded basis. An explicit source tax amount can produce a different basis from an inferred amount.
Trimsum stores monetary values as cents. With an exclusive basis and missing tax, it derives and rounds the basis using the configured assumed rate, then rounds the commission for each line. Preserve that order when reproducing the calculation in a spreadsheet. A long decimal carried through every stage is a different method.
Use a small fixture to isolate the cause
If the difference remains larger than the rounding bridge explains, return to category, basis, date and inclusion checks. Calling a difference “rounding” is only useful when you can reproduce the exact cents.
- Take the exact source rows from the period, without changing amounts or rates.
- Write the selected basis for each line and show more than two decimal places in the raw commission calculation.
- Calculate a sum of rounded line commissions.
- Separately calculate the rounded sum of unrounded commissions.
- Compare the two results and identify which lines contain fractional cents.
- Repeat with a negative line so the refund rule is visible.
Record the policy rather than inserting a balancing row
Keep the rounding method with the rates and calculation basis. A reviewer should be able to reproduce the total from the displayed line results and understand why another report might use a different order.
Avoid creating a fictional sale or changing a genuine item by one cent just to match a headline figure. Record the explained rounding variance instead and use the appropriate agreed treatment in the wider pay process. The comparison tool below shows both orders on the same supplied data.
Put it to work.
Find which lines create the difference and inspect both totals.
Compare rounding methods