Build commission spreadsheets

The baseline model. One rate applied to all revenue. Good for teams selling one product line where the comp plan is straight percentage.

Setup

1. Create a Deals sheet with columns A: Rep, B: Revenue, C: Rate, D: Commission.
2. In C2, enter your rate as a decimal - 0.10 for 10%. (Or leave C blank and use a named cell CommRate in the formula.)
3. In D2, enter the formula below, then copy down the column.
4. At the bottom: =SUM(D:D) for the period total.

Formula - all platforms

=B2*C2

Pro tip. Put the rate in a single named cell (e.g., CommRate) and reference it with =B2*CommRate. When the rate changes, you edit one cell, not the column.

A percentage that says how far each rep is toward quota - and a text status label you can filter or conditional-format on.

Setup

1. On a Summary sheet: A: Rep, B: Annual Quota, C: YTD Revenue, D: Attainment, E: Status.
2. Format column D as percentage (Home → Percent in Excel, Format → Number → Percent in Sheets).
3. Apply conditional formatting to column D: green ≥ 100%, amber 80-99%, red < 80%.

Formulas - all platforms

D2:  =C2/B2
E2:  =IFS(D2>=1.25, "Above 125%",
          D2>=1,    "At quota",
          D2>=0.8,  "At risk",
          TRUE,     "Below 80%")

IFS requires Excel 2019+, Excel 365, Google Sheets, or LibreOffice 6.2+. On older Excel, use nested IF: =IF(D2>=1.25,"Above 125%",IF(D2>=1,"At quota",IF(D2>=0.8,"At risk","Below 80%"))).

Pro tip. Use IFERROR(D2/B2, 0) to avoid the #DIV/0! noise for reps with no quota entered yet.

Each tier rate applies only to the slice of revenue that falls inside that tier's band. The total is the sum of all slices. Also called "slice" or "marginal" tiering.

Setup

1. Create a Tiers sheet with columns A: Floor, B: Ceiling, C: Rate. One row per band.
2. Rows (for a $1M quota): 0 | 800000 | 6%, 800000 | 1000000 | 10%, 1000000 | 1250000 | 15%, 1250000 | 1E9 | 20%. Use 1E9 (one billion) as the top tier ceiling - effectively infinity.
3. On the Deals sheet, make sure column B is revenue for that deal (or YTD revenue, if you're computing cumulative attainment across deals).
4. Drop the SUMPRODUCT formula into the commission column. It reads all four bands at once.

Formula - all platforms

=SUMPRODUCT(
    (MIN(B2, Tiers!$B$2:$B$5) - MIN(B2, Tiers!$A$2:$A$5))
    * Tiers!$C$2:$C$5
)

How it works: for each band, MIN(revenue, ceiling) - MIN(revenue, floor) is the width of revenue that fell inside that band. Multiply the widths by the rate vector and sum. Works regardless of how many tiers you have - just extend the ranges.

Pro tip. Keep floor/ceiling values as absolute dollars, not percentages of quota. Mixing percentage bands with dollar revenue is the number-one source of tier bugs. If plans are expressed as % of quota, multiply once in the Tiers sheet (e.g., =$E$1*0.8).

The highest tier the rep attained pays its rate on all revenue, retroactively. Lower-tier rates are ignored. Also called "retroactive" or "flat-rate-by-tier" tiering.

Setup

1. Reuse the Tiers sheet from Recipe 3 - but the floors and ceilings are expressed as attainment percentages (0, 0.8, 1.0, 1.25).
2. On the Deals/Summary sheet, you need E: Quota and B: Revenue.
3. Calculate attainment with =B2/E2, then pick the rate with IFS, then multiply by revenue.

Formula - all platforms

=IFS(
    B2/E2 >= 1.25, Tiers!$C$5,
    B2/E2 >= 1,    Tiers!$C$4,
    B2/E2 >= 0.8,  Tiers!$C$3,
    TRUE,          Tiers!$C$2
) * B2

Alternative: approximate-match VLOOKUP against a sorted attainment-thresholds table. =VLOOKUP(B2/E2, AttBands, 2, TRUE) * B2 where AttBands is a two-column range {0, rate1; 0.8, rate2; 1, rate3; 1.25, rate4} sorted ascending. The TRUE fourth argument tells VLOOKUP to find the highest threshold less than or equal to the attainment.

Pro tip. Split the rate lookup into its own column (e.g., F: Active Tier Rate) and then compute =B2*F2. Reps will ask "what rate did you apply?" - having the answer in a cell saves you an email.

Different products pay different rates. Put the mapping in a lookup table and reference it from the deals sheet. This is the core skill for multi-product plans.

Setup

1. Create a Rates sheet: A: Product, B: Rate. Enter one row per product with its decimal rate.
2. Convert the range to a table (Insert → Table in Excel, Format as Table in LibreOffice, or use a named range in Sheets) named RateTable. Tables auto-extend as you add products.
3. On the Deals sheet, add column C: Product. Use data validation to constrain the product column to values from the Rates sheet.
4. Commission formula multiplies the looked-up rate by revenue.

Formula - VLOOKUP (universal)

=IFERROR(VLOOKUP(C2, Rates!$A:$B, 2, FALSE), 0) * B2

Works in every version of Excel, Google Sheets, and LibreOffice Calc. FALSE forces exact match - critical for product names. IFERROR(..., 0) catches missing products (set to 0, or a default rate).

Formula - XLOOKUP (Excel 365 / Excel 2021+)

Formula - XLOOKUP (Google Sheets)

=XLOOKUP(C2, Rates!$A:$A, Rates!$B:$B, 0) * B2

XLOOKUP is cleaner: it doesn't care about column order, has a built-in "if not found" argument (the last 0), and is easier to audit. If you can use it, use it.

Formula - LibreOffice Calc 7.3+

LibreOffice 7.3 added XLOOKUP. For older versions or maximum compatibility, stick with the VLOOKUP variant above.

Pro tip. Product names drift. "Pro", "Pro Plan", "PRO" are three different strings to VLOOKUP. Enforce a canonical list with data validation (Data → Data Validation → list from range pointing at Rates!A:A) so reps can only pick valid products.

Pay the base rate on everything up to quota, and a multiplied rate on everything over quota. The most common "kicker" structure in SaaS.

Setup

1. Define two named cells: BaseRate (e.g., 0.10) and AccelMult (e.g., 2.0 for 2x).
2. Use B: Revenue and E: Quota on the Deals or Summary sheet.
3. Drop the formula below into the commission column. It computes under-quota and over-quota portions in one shot.

Formula - all platforms

=MIN(B2, E2) * BaseRate
 + MAX(0, B2 - E2) * BaseRate * AccelMult

Written on two lines for readability - it's a single formula. MIN(revenue, quota) = under-quota portion. MAX(0, revenue - quota) = over-quota portion. The second term gets the multiplier.

Pro tip. Split into two columns if auditors want to see the split - G: Under quota = =MIN(B2,E2)*BaseRate, H: Over quota = =MAX(0,B2-E2)*BaseRate*AccelMult, I: Total = =G2+H2. More cells, way easier to debug.

Limit total commissions for the year at a ceiling. The formula uses remaining headroom (cap minus YTD already paid) so caps hold even across multiple payout periods.

Setup

1. Define a named cell CapAmount (or per-rep cap on the Summary sheet).
2. On each payout row: D: Uncapped commission, F: YTD paid in prior periods.
3. Compute the capped payout with MIN against remaining headroom.

Formula - all platforms

Payout this period:
    =MIN(D2, MAX(0, CapAmount - F2))

Forfeited to cap:
    =MAX(0, D2 - (CapAmount - F2))

Remaining headroom after payout:
    =MAX(0, CapAmount - F2 - G2)   [G2 = payout this period]

MAX(0, CapAmount - F2) is the remaining headroom before the cap. MIN(earned, headroom) is what's actually paid. Any difference is forfeited.

Pro tip. Track YTD paid with a running SUMIF on processed payout rows, not a static cell. That way if you re-open a prior period and adjust, the cap math stays consistent. F2 = SUMIFS(PaidCol, PeriodCol, "<"&currentPeriod, RepCol, A2).

Allocate deal credit to two or more reps by percentage. Keep splits in a separate sheet so one deal can feed many rows.

Setup

1. Deals sheet: A: Deal ID, B: Revenue, C: Rate, D: Total Commission (= B2*C2).
2. Splits sheet: A: Deal ID, B: Rep, C: Split %, D: Credited Commission. One row per rep per deal.
3. Constrain column C to 0-100% with data validation. Validate that splits sum to 100% per deal (formula below).

Formulas - all platforms

Credited commission (Splits!D2):
    =VLOOKUP(A2, Deals!$A:$D, 4, FALSE) * C2

Validation check - should equal 1 (100%):
    =SUMIF(Splits!$A:$A, A2, Splits!$C:$C)

Flag rows where split sum ≠ 100%:
    =IF(ROUND(SUMIF($A:$A, A2, $C:$C), 4)<>1, "⚠  Check", "OK")

Pro tip. Round the SUMIF to 4 decimal places before comparing to 1 - otherwise floating-point drift will trip your validation on splits like 33.33% + 33.33% + 33.34%.

Recover part of a commission when a customer cancels inside the clawback window. Standard SaaS mechanic for annual contracts paid up-front on close.

Setup

1. Create a Clawback sheet: A: Commission paid, B: Close date, C: Churn date (or =TODAY()), D: Window (months), E: Elapsed months, F: Clawback amount, G: Status.
2. Default column D to a named cell ClawbackWindow (e.g., 12). Override per row for exceptions.
3. Compute elapsed months, then the pro-rated clawback. If the deal is past its window, the clawback is zero.

Formulas - all platforms

Elapsed months (E2):
    =MAX(0, (YEAR(C2)-YEAR(B2))*12 + MONTH(C2)-MONTH(B2))

Clawback amount (F2):
    =IF(E2 >= D2, 0, (D2 - E2) / D2 * A2)

Status (G2):
    =IF(E2 >= D2, "Safe", "Clawback risk")

DATEDIF(B2, C2, "M") is an alternative to the YEAR/MONTH subtraction - it works in all three platforms but behaves subtly differently near month boundaries.

Pro tip. Set column C to =TODAY() by default for active deals and only overwrite with a hard-coded date when a customer actually churns. That way your "at-risk" report is live every time you open the file.

Aggregate deal-level commission to a per-rep summary. The workhorse function here is SUMIF (or SUMIFS for multi-criteria).

Setup

1. Deals sheet: A: Rep, D: Commission (from Recipe 1-6).
2. Summary sheet: A: Rep, B: YTD Commission, plus whatever else (quota, attainment).
3. Drop the SUMIF below into the summary's Commission column. Optional: add SUMIFS with a period filter for monthly or quarterly rollups.

Formulas - all platforms

YTD total for this rep:
    =SUMIF(Deals!$A:$A, A2, Deals!$D:$D)

Period total (e.g., Q2 2026):
    =SUMIFS(Deals!$D:$D,
            Deals!$A:$A, A2,
            Deals!$B:$B, ">="&DATE(2026,4,1),
            Deals!$B:$B, "<" &DATE(2026,7,1))

Deals closed (count):
    =COUNTIF(Deals!$A:$A, A2)

Pro tip. For dashboards with multiple periods and filters, build a PivotTable on top of the Deals sheet instead (Insert → PivotTable). Drag Rep to Rows, Commission to Values (Sum), and add a Close Date slicer. Refresh is one click - no formulas to break.