Build a tiered commission calculator

Northwind pays its salespeople on a progressive scale: the first slice of sales earns one rate, the next slice a higher one, and the top slice higher still. That is the same shape as an income-tax band, and it trips people up the same way — it is tempting to apply the top rate to the whole figure, which overpays everyone. Doing it by hand in a spreadsheet means a tangle of nested IFs that nobody trusts.

This is a custom function. Drop =COMMISSION() next to the sales column and each salesperson’s payout is worked out tier by tier: 5% on sales up to £10k, 8% on the part between £10k and £50k, and 12% on anything above. Change the rates in one place and every cell updates.

What you’ll need

• A sheet with a sales figure per salesperson — say their total in column B, with a header in row 1.
• A spare column for the commission result.
• Nothing else. A custom function lives in the bound script and runs as a formula; there is no trigger and no API to set up.

The script

// The commission bands, lowest first. `upTo` is the top of each
// band; `rate` is the rate that applies to the slice inside it.
// Edit these to change the scheme — every cell recalculates.
const TIERS = [
  { upTo: 10_000, rate: 0.05 },   // 5% on the first £10,000
  { upTo: 50_000, rate: 0.08 },   // 8% on £10,000–£50,000
  { upTo: Infinity, rate: 0.12 }, // 12% on everything above £50,000
];

/**
 * Calculates progressive commission on a sales amount. Each band's
 * rate applies only to the slice of sales that falls inside it.
 *
 * @param {number} amount The total sales figure.
 * @return {number} The commission earned.
 * @customfunction
 */
function COMMISSION(amount) {
  // Guard: no sales, no commission.
  if (!amount || amount <= 0) return 0;

  let remaining = amount; // sales not yet allocated to a band
  let prev = 0;           // the bottom of the current band
  let total = 0;          // commission accumulated so far

  // Walk the bands lowest to highest, taking a slice from each.
  for (const tier of TIERS) {
    // The slice of sales that sits inside this band.
    const slice = Math.max(0, Math.min(tier.upTo - prev, remaining));

    // That slice earns this band's rate.
    total += slice * tier.rate;

    // Move on: the slice is now allocated, the next band starts here.
    remaining -= slice;
    prev = tier.upTo;

    // Stop once all the sales have been allocated.
    if (remaining <= 0) break;
  }

  return total;
}

How it works

1. TIERS describes the scheme as plain data: each band has an upTo ceiling and the rate that applies to the money inside it. The last band uses Infinity so it catches everything above £50k.
2. COMMISSION bails out at zero for blank or negative sales, so an empty cell returns 0 rather than an error.
3. It walks the bands from the bottom. For each one it works out the slice — the part of the remaining sales that fits between the previous ceiling and this one — with Math.min capping it at the band width and Math.max keeping it non-negative.
4. That slice is multiplied by the band’s rate and added to the running total.
5. The allocated slice is subtracted from remaining, prev moves up to this band’s ceiling, and the loop continues.
6. As soon as every pound of sales has been allocated, remaining hits zero and the loop stops early — higher bands are never touched.

Example run

On £62,000 of sales the function allocates the figure across all three bands:

Band	Slice	Rate	Commission
First £10,000	£10,000	5%	£500
£10,000–£50,000	£40,000	8%	£3,200
Above £50,000	£12,000	12%	£1,440
Total			£5,140

So =COMMISSION(62000) returns 5140. Note that applying the top 12% to the whole £62,000 would give £7,440 — the tiering is the difference.

Use it

Type the formula into the first commission cell, pointing at the sales cell:

=COMMISSION(B2)

Then drag it down the column. Each row recalculates instantly, and if you adjust a rate in TIERS the whole column updates on the next edit.

Watch out for

• Custom functions cannot read Script Properties or make external calls — that is fine here, but it means the TIERS rates live in the code. Changing the scheme is a code edit, not a spreadsheet edit.
• The bands must be listed lowest first and must not overlap. An out-of-order or overlapping TIERS array will allocate slices wrongly.
• COMMISSION takes a single number. Pass it a range, like =COMMISSION(B2:B9), and it will see an array and return 0. Apply it one row at a time.
• The result is an unrounded number. If payroll wants pennies exact, wrap the cell with =ROUND(COMMISSION(B2), 2).
• Recalculation only fires when an input cell changes or the sheet reopens. Editing TIERS alone will not refresh existing cells until they are touched.