How to Calculate Commission in Excel and Google Sheets

Choose the compensation model before the formula

A flat commission worksheet multiplies eligible sales by a rate: =B2*C2 when B2 contains eligible sales and C2 contains a numerical percentage. Base pay is then added separately for the same period. A tiered plan needs more information: does each rate apply only to its band, or does reaching a threshold apply one rate to the whole eligible amount? Those are different formulas even if the tier table looks identical.

This guide implements hypothetical structures supported by the Commission Calculator. It does not infer eligibility, payment timing, caps, draws or chargebacks from a sales number. Keep the written plan and the worksheet's interpretation together. Formula examples use English-style function names and commas; adapt separators to the spreadsheet locale as needed. Monetary outputs remain gross educational calculations, not a payroll determination or a prediction of take-home deposits.

Create and verify a flat-rate row

Label A1 Period, B1 Eligible sales, C1 Commission rate, D1 Commission, E1 Base pay, and F1 Total gross. Enter sales 18,400 in B2, rate 4.5% in C2 and base 900 in E2. Formula =B2*C2 in D2 gives 828. Formula =D2+E2 in F2 gives 1,728. Separating the two earnings components makes it clear that the sales rate does not apply to the fixed base.

Type 4.5% explicitly into the percentage cell, whose numerical value is 0.045. If you choose to store a plain 4.5 instead, use =B2*C2/100 and label that convention. The website's rate field takes 4.5, not 0.045. A shared spreadsheet rate in H1 can be anchored with =B2*$H$1, while a per-row rate should stay relative. Check copied rows with different sales amounts before using a whole column.

Implement marginal tiers one band at a time

Consider an illustrative marginal schedule: 4% on the first 15,000 of eligible sales, 6% on the portion above 15,000 through 40,000, and 9% on the portion above 40,000. For nonnegative sales in B2, use =MIN(B2,15000)*4%+MAX(0,MIN(B2,40000)-15000)*6%+MAX(0,B2-40000)*9%. Each MAX prevents a negative contribution from a band not yet reached. The MIN terms limit the lower bands to their defined widths.

At sales 48,000, the three contributions are 15,000 × 4% = 600, 25,000 × 6% = 1,500, and 8,000 × 9% = 720. Commission totals 2,820. With base 900 for that same period, total gross is 3,720. Keep a separate calculation column for each band if a reviewer needs to audit the amounts; the compact formula is equivalent, but the expanded layout often makes a wrong threshold easier to notice.

Use an explicitly different whole-sales formula

Under a whole-sales schedule with inclusive lower thresholds 0, 15,000 and 40,000, the applicable rate at 48,000 is 9% on all sales. Commission is 4,320 and total with base 900 is 5,220. An explicit formula is =B2*IF(B2>=40000,9%,IF(B2>=15000,6%,4%)). This formula intentionally jumps at thresholds. It must not be substituted for the marginal formula simply because it is shorter.

For a maintainable whole-sales lookup, place thresholds 0, 15000 and 40000 in H2:H4 in ascending order and rates 4%, 6% and 9% in I2:I4. Formula =B2*VLOOKUP(B2,$H$2:$I$4,2,TRUE) uses an approximate lookup to select the applicable lower threshold. Google documents the sorting requirement for this mode. Include zero and validate nonnegative sales; an unsorted or incomplete table can return a plausible but incorrect rate.

Test boundaries against the actual threshold convention

At exactly 15,000, the illustrative marginal formula still earns 600, because the higher band contains no sales yet. The whole-sales formula selects 6% at that inclusive threshold and earns 900. At exactly 40,000, marginal commission is 2,100, whereas whole-sales commission is 3,600. Test just below, exactly at and just above each threshold. These comparisons reveal whether a worksheet has implemented the intended rule rather than only matching a convenient large-sales example.

If the actual agreement uses a strict greater-than condition, its boundary differs from the inclusive-lower schedule used here. Do not change > to >= casually while translating a document. Also distinguish rate lookup from commission accumulation: looking up the highest attained rate and multiplying all sales models whole-sales commission, not marginal bands. Preserve the threshold convention in the sheet header or notes so someone maintaining it can understand why a boundary test has its stated result.

Whole-sales thresholds are inclusive lower bounds at 0, 15,000 and 40,000. Base pay is excluded from this table.

Validate required data before computing earnings

For a flat row under this nonnegative model, require numerical sales and base pay at least zero, with a numerical rate from 0% to 100%. For example, =IF(AND(ISNUMBER(B2),ISNUMBER(C2),ISNUMBER(E2),B2>=0,C2>=0,C2<=1,E2>=0),B2*C2+E2,NA()) flags invalid input instead of silently generating a total. Keep a separately checked commission column if component reporting is required. A blank rate is not a verified zero rate.

For tier tables, require strictly increasing thresholds, an initial zero threshold and valid rates. Reject duplicate threshold rows instead of depending on whichever row a lookup happens to use. Negative adjustments need an explicitly modelled treatment outside these nonnegative examples. Do not wrap all formula errors in IFERROR with a zero result, because a broken reference or missing rate would then look like a valid no-commission period.

State rounding and aggregation policies

If the plan requires a commission amount rounded to two decimals per transaction, use ROUND at that point and sum those rounded outputs. If it requires a period-level calculation, sum the relevant eligible sales first and apply the period rule. These operations can differ, especially for tiers, because splitting a period into separate calculations may reset its bands. Decide from the actual plan rather than selecting whichever approach produces the preferred amount.

Currency formatting alone changes display, not necessarily the stored value. Check that imported sales and rates are numeric, and reconcile required row counts because SUM can omit referenced text. Keep base-pay periods aligned with the commission period. A monthly base should not be added unchanged to each weekly row unless that is genuinely the stated amount. The sheet does not automatically resolve exchange rates, deductions, payroll taxes or sales-return timing.

Cross-check the worksheet against supported website modes

Use flat or base-plus mode to verify sales 18,400 at 4.5%, commission 828 and base-plus total 1,728. Then enter tiers starting at 0 with 4%, 15,000 with 6%, and 40,000 with 9%. At sales 48,000 and base 900, marginal mode should return commission 2,820 and total 3,720; whole-sales mode should return 4,320 and 5,220. The same inputs deliberately produce different outputs because the methods differ.

Keep these examples and boundary checks in a small audit range so future formula edits can be tested. Recalculate the check range after changing a threshold, rate or copied formula, and investigate a mismatch before using the sheet for another period. Save the selected method, rate storage convention, period, eligibility basis and rounding point with each output. That record makes the worksheet reproducible while leaving actual compensation terms to the agreement that defines them.