Build a Sales Commission Report with Python and Excel

Commission is the report where every rep checks the arithmetic. It has to apply the plan exactly as written — which tier rates apply, whether they are marginal or apply to the whole amount, what happens to cancelled deals — and it has to show the working, because a statement that just says "commission: 9,700" generates questions. The inputs are simple: a month of deals from the CRM and a plan table. This page, part of Building Finance and Operations Reports, calculates commissions for both common tier styles, applies a clawback, flags reps close to the next tier and writes a payroll summary plus one statement per rep.

Prerequisites

pip install pandas numpy openpyxl xlsxwriter

deals.xlsx has the September deals for three reps, one of them still pending, plus an August deal that has since been cancelled. commission_plan.xlsx holds the tier table — 5% up to 50,000, 8% from 50,000 to 100,000, 10% above — and a record of the commission already paid on the cancelled deal.

Step 1 — Total the commissionable sales

Only deals closed in the month count. Pending deals wait for next month; cancelled ones are handled as clawbacks, not as negative sales:

import pandas as pd

month_start, month_end = pd.Timestamp("2026-09-01"), pd.Timestamp("2026-09-30")
deals = pd.read_excel("deals.xlsx", parse_dates=["Close Date"])
in_month = deals[deals["Close Date"].between(month_start, month_end)]
closed = in_month[in_month["Status"] == "closed"]
sales = closed.groupby("Rep")["Amount"].sum()
print(sales.to_dict())
print("not paid this month:", in_month.loc[in_month["Status"] != "closed", ["Deal", "Status"]].values.tolist())

Ana has sold 132,000, Ben 48,500 and Cai 96,000; Cai's pending deal D-508 is listed so the rep can see why it is missing. Status values are a frequent source of silent errors — "Closed Won" versus "closed" — so check the distinct values of the column each month, or map them through a table.

Step 2 — Apply marginal tiers

With marginal tiers, each rate applies only to the sales inside its band, like income tax bands. For each band, the amount inside it is the rep's total clipped to the band's limits, minus the band's lower limit. numpy's clip computes that for every rep and band at once:

import numpy as np

tiers = pd.read_excel("commission_plan.xlsx", sheet_name="Tiers")
lower = tiers["From"].to_numpy()
upper = tiers["To"].fillna(np.inf).to_numpy()
rate = tiers["Rate"].to_numpy()

in_band = np.clip(sales.to_numpy()[:, None], lower, upper) - lower
band_commission = in_band * rate
marginal = pd.Series(band_commission.sum(axis=1), index=sales.index)
working = pd.DataFrame(band_commission, index=sales.index, columns=[f"{r:.0%} band" for r in rate])
print(working.assign(Total=marginal).round(2))

sales.to_numpy()[:, None] makes a column of totals, which broadcasts against the row of band limits to give one row per rep and one column per band. Ana's 132,000 is 50,000 at 5%, 50,000 at 8% and 32,000 at 10%: 2,500 + 4,000 + 3,200 = 9,700. The per-band columns are exactly what the statement needs to show the working.

Step 3 — Or apply whole-amount tiers

Some plans pay the highest tier reached on all sales: sell over 100,000 and everything earns 10%. That is a lookup rather than a sum — find the band the total falls in and apply its rate to the whole amount. pd.cut does the lookup:

edges = list(lower) + [np.inf]
whole_rate = pd.cut(sales, bins=edges, right=False, labels=rate).astype(float)
whole = sales * whole_rate
print(pd.DataFrame({"sales": sales, "rate": whole_rate, "whole-amount": whole, "marginal": marginal.round(2)}))

right=False makes each band include its lower limit, so exactly 50,000 earns 8%, matching "from 50,000". Ana earns 13,200 under this plan against 9,700 marginally. Whole-amount tiers create a cliff: Ben, at 48,500, earns 2,425; one more 1,500 sale would take Ben to 50,000 and 4,000 — 1,575 more commission for 1,500 of sales. That incentive is sometimes intended and sometimes a surprise, which is why the report should use the plan document's exact wording, and why the next step flags reps near a threshold.

Step 4 — Apply clawbacks and flag reps near a tier

Deal D-471 closed in August, earned commission, and has now been cancelled. The plan claws back what was paid on it. Take the amount from the record of the August payment, not by recalculating: the rep's August tier determined what they were paid, and recalculating with September's totals would give a different figure:

basis = pd.read_excel("commission_plan.xlsx", sheet_name="Clawback basis").set_index("Rep")["Paid on D-471"]
clawback = -basis.reindex(sales.index, fill_value=0)

summary = pd.DataFrame({"Sales": sales, "Commission": marginal.round(2), "Clawback": clawback})
summary["Payable"] = summary["Commission"] + summary["Clawback"]

next_limit = pd.Series([lower[lower > s].min() if (lower > s).any() else np.nan for s in sales], index=sales.index)
summary["To next tier"] = next_limit - sales
summary["Near tier"] = summary["To next tier"] <= 5000
print(summary)
print("payroll total:", summary["Payable"].sum())

Ben's September commission of 2,425 less the 1,600 clawback leaves 825 payable. Ben is 1,500 below the 50,000 threshold, and Cai 4,000 below 100,000, so both are flagged — useful for the sales manager, and a prompt to double-check any deal that just missed the month end. Ana is past the top threshold, so there is no next tier to reach.

Step 5 — Write the payroll summary and statements

Payroll needs one number per rep. Each rep needs their own sheet showing their deals, the band working and the adjustments — without seeing anyone else's figures:

out = "commission_2026-09.xlsx"
with pd.ExcelWriter(out, engine="xlsxwriter") as writer:
    wb = writer.book
    money = wb.add_format({"num_format": "#,##0.00;(#,##0.00);-"})
    bold = wb.add_format({"bold": True})
    summary.reset_index().to_excel(writer, sheet_name="Payroll", index=False)
    writer.sheets["Payroll"].set_column("A:A", 10)
    writer.sheets["Payroll"].set_column("B:F", 13, money)

    for rep in sales.index:
        ws = wb.add_worksheet(rep)
        ws.write("A1", f"Commission statement — {rep} — September 2026", wb.add_format({"bold": True, "font_size": 13}))
        rep_deals = closed[closed["Rep"] == rep][["Deal", "Close Date", "Amount"]]
        ws.write_row(2, 0, ["Deal", "Close date", "Amount"], bold)
        for i, (deal, closed_on, amount) in enumerate(rep_deals.itertuples(index=False, name=None), start=3):
            ws.write(i, 0, deal)
            ws.write_datetime(i, 1, closed_on.to_pydatetime(), wb.add_format({"num_format": "yyyy-mm-dd"}))
            ws.write_number(i, 2, amount, money)
        r = 3 + len(rep_deals)
        ws.write(r, 0, "Total sales", bold)
        ws.write_number(r, 2, sales[rep], money)
        r += 2
        ws.write_row(r, 0, ["Band", "Sales in band", "Rate", "Commission"], bold)
        for j, (lo, hi, rt) in enumerate(zip(lower, upper, rate), start=1):
            label = f"{lo:,.0f} – {hi:,.0f}" if np.isfinite(hi) else f"over {lo:,.0f}"
            ws.write_row(r + j, 0, [label])
            ws.write_number(r + j, 1, in_band[sales.index.get_loc(rep), j - 1], money)
            ws.write_number(r + j, 2, rt, wb.add_format({"num_format": "0%"}))
            ws.write_number(r + j, 3, band_commission[sales.index.get_loc(rep), j - 1], money)
        r += len(rate) + 1
        for label, value in [("Commission", summary.at[rep, "Commission"]), ("Clawback (D-471)", summary.at[rep, "Clawback"]),
                             ("Payable", summary.at[rep, "Payable"])]:
            ws.write(r, 0, label, bold if label == "Payable" else None)
            ws.write_number(r, 3, value, money)
            r += 1
        ws.set_column("A:A", 20)
        ws.set_column("B:D", 14)

print(pd.ExcelFile(out).sheet_names)
print(pd.read_excel(out, sheet_name="Ben", header=None).dropna(how="all").fillna("").to_string(index=False, header=False))

The statement lists Ben's two deals, the band working — all of Ben's sales fall in the first band — and the clawback line, ending at 825 payable. When statements are emailed, write one file per rep instead of one sheet each; Send Each Recipient Their Own Excel Report shows the distribution step. Before sending, protect the sheets or convert them to PDF so the figures are not edited in transit.

Common pitfalls

• Misreading the plan. Marginal and whole-amount tiers give very different pay; implement the plan's wording.
• Boundary rules. Decide whether exactly 50,000 is in the lower or upper band and set right to match.
• Recalculated clawbacks. Claw back what was actually paid, from the payment record.
• Status values. "Closed Won" and "closed" differ; check distinct values each month.
• Floating-point totals. Round commission to cents once, before summing payroll.
• One file for everyone. Reps should not see each other's statements.

Performance notes

The tier calculation is vectorised, so thousands of reps cost nothing. Writing one sheet per rep is fine for dozens; for hundreds, write one file per rep, or a single statement sheet filtered by rep, to keep the workbook manageable.

Conclusion

Filter deals to those closed in the month and total them per rep. Apply marginal tiers with numpy's clip against the band limits, or whole-amount tiers with a pd.cut lookup, matching the plan's wording and boundary rules. Claw back the commission actually paid on cancelled deals, flag reps within reach of the next tier, and write a payroll summary with one statement per rep that shows every step of the working.

Frequently asked questions

How do I calculate tiered commission in pandas? For marginal tiers, work out how much of each rep's sales falls inside each band — the total clipped to the band's lower and upper limits, minus the lower limit — multiply by the band's rate and add the bands up. numpy's clip does this for all reps at once.

What is the difference between marginal and whole-amount tiers? Marginal tiers pay each rate only on the sales inside its band, like income tax. Whole-amount (retroactive) tiers pay the highest rate reached on all sales. The second creates a cliff: one more sale that crosses a threshold can increase commission by far more than the sale itself.

How do I handle a cancelled deal that was already paid commission? Claw back the commission that was actually paid on it, taken from the record of the earlier payment rather than recalculated, and show it as a separate negative line on the rep's statement so the adjustment is visible.

Should each rep get their own commission statement? Yes. A statement listing their deals, the tier working and any adjustments lets a rep check their pay without seeing colleagues' figures. Write one sheet per rep, or one file per rep when statements are emailed individually.

Related

• Up one level: Building Finance and Operations Reports — the pipeline behind these reports.
• Approximate-Match VLOOKUP in pandas — rate tables looked up by value.
• Bin Values into Ranges with pandas cut — band boundaries in depth.
• Build a Budget vs Actual Variance Report — the month-end companion.