---
source: https://carbone.io/examples/sales-commission-statement/
title: "Commission Statement for Sales Commission Software | Carbone"
description: "Generate monthly sales commission statements from your CRM or sales commission software: tiered rates, accelerators, clawbacks and payout as live formulas."
generated_at: "2026-11-17"
---

# Sales commission statement

Tiered commission, accelerators and clawbacks for each sales rep

[![Sales commission statement: ODS generated from an ODS template and JSON data](/examples/sales-commission-statement/thumb-result.webp)](/examples/sales-commission-statement/result.ods "Download the generated document")

[Download template](/examples/sales-commission-statement/template.ods)

[Sample data](/examples/sales-commission-statement/data.json)

[Download the generated ODS](/examples/sales-commission-statement/result.ods)

- **Template:** ODS
- **Export as:** ODS, XLSX, PDF, CSV, HTML [+19 more](/documentation/developer/http-api/generate-reports.md#output-file-type)
- **Carbone:** 5.18+

Features

-   [Loop](/documentation/design/repetitions/with-arrays.md)
-   [Aggregation](/documentation/design/computation/aggregation.md)
-   [Formula](/documentation/design/template-formats/xlsx.md)
-   [Conditional formatting](/documentation/design/template-formats/xlsx.md)
-   [Data bars](/documentation/design/template-formats/xlsx.md)
-   [Number format](/documentation/design/formatters/number.md)
-   [Date](/documentation/design/formatters/date.md)

## Generate commission statements from your sales commission software

Every month, sales operations sends each rep a commission statement before payroll: the deals credited in the period, the quota attainment, the commission earned in each tier of the plan, the adjustments to earlier statements and the amount paid. The deals come from the CRM, the plan rules and splits from the sales commission software, and the rep checks every line against the written plan. For employees working in California, that plan must state in writing how commissions are computed and paid ([California Labor Code § 2751](https://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=LAB&sectionNum=2751)), and the statement applies those rules line by line.

In this example, Merrovia, a fictional B2B software company, pays Jordan Ellery, a mid-market account executive, for October 2026 under a plan with a monthly quota, a base rate up to 100% of quota and two accelerators above it. You will:

-   Fill the header and a KPI block with attainment, credited ACV, commission and payout
-   List the credited deals with a loop and write amounts as native numbers
-   Compute the credited amount, the attainment, the tier and the commission of each deal with formulas
-   Sum the SPIFs, adjustments and clawbacks, then compute the payout and the year to date
-   Flag the tiers with conditional formatting and show the attainment with data bars

## How to build a commission statement spreadsheet

### 1\. Fill the header and the KPI block

The header prints the rep, the plan and the pay date: `{d.rep.name}`, `{d.plan.name}` and `{d.statement.payDate:formatD('MMM D, YYYY')}` with [:formatD](/documentation/design/formatters/date.md#formatd-patternout-patternin). A cell becomes a number when it holds a single tag ending with [:formatN](/documentation/design/template-formats/xlsx.md#numbers-and-dates), and the cell format displays it:

```text
{d.plan.monthlyQuota:formatN}        cell format: "of --TMPL-5-MARK--quot;#,##0" monthly quota"
{d.deals[].id:aggCount:formatN}      cell format: 0" deals credited"
```

[:aggCount](/documentation/design/computation/aggregation.md#aggcount-partitionby) counts the deals. The KPI cells reuse these numbers: the credited ACV is `=SUM(OFFSET($G$19,0,0,$D$8,1))`, the sum of as many deal rows as the count in D8, and the attainment is `=$D$7/$A$8`.

### 2\. List the credited deals

One row repeats for each deal, and the end tag `{d.deals[i+1]}` sits alone on the next row:

```text
{d.deals[i].closeDate:formatD('MMM D')}  {d.deals[i].id}  {d.deals[i].account}  {d.deals[i].type}
{d.deals[i].acv:formatN}  {d.deals[i].splitPct:formatN}
```

The ACV booked and the split are numbers in the cell formats `"--TMPL-5-MARK--quot;#,##0` and `0"%"`. Every other value of the row is a formula.

### 3\. Compute the tiers with formulas

In an ODS template, a formula keeps its cell addresses when Carbone repeats the row. Point at the current row with `INDEX(column, ROW())`, and keep every cell that formulas reference above the loop: the tier table sits above the deals, with its thresholds and rates.

```text
Credited ACV      =INDEX(E:E,ROW())*INDEX(F:F,ROW())/100
Attainment after  =SUM($G$19:INDEX(G:G,ROW()))/$A$8
Tier reached      =IF(INDEX(H:H,ROW())<=$D$12,$A$12,IF(INDEX(H:H,ROW())<=$D$13,$A$13,$A$14))
```

The commission of a deal is the tiered commission after the deal minus the tiered commission before it, so a deal that crosses a threshold gets a blended rate, and the deal commissions add up to the tier table total. The tier table computes the credited amount of each band with `=MAX(0,MIN($D$7,D12*$A$8)-E12)` and its commission with `=G12*J12`. The total rows sum the generated rows with `=SUM($K$19:INDEX(K:K,ROW()-1))`. Learn more about [formulas in spreadsheets](/documentation/design/template-formats/xlsx.md#formulas).

### 4\. Sum the adjustments and compute the payout

A second loop lists the SPIFs, adjustments and clawbacks with `{d.adjustments[i].type}` and `{d.adjustments[i].amount:formatN}`. The payout calculation sums each type of the rows above it:

```text
=SUMIF($I$19:INDEX(I:I,ROW()-1),"Clawback",$K$19:INDEX(K:K,ROW()-1))
```

The year-to-date block adds the period to the prior months sent in `{d.ytdPrior.credited:formatN}` and `{d.ytdPrior.earned:formatN}`, and recomputes the annual attainment.

### 5\. Flag the tiers and show the attainment

[Conditional formatting](/documentation/design/template-formats/xlsx.md#conditional-formatting) set on the range of the loop applies to every generated row. A formula rule such as `I19=$A$14` highlights the deals in the top accelerator, and text rules flag the clawbacks in red and the adjustments in amber. Data bars scaled from 0% to 200% show the attainment after each deal and in the KPI block.

## Related Examples

Check out these similar templates to learn more:

- [Excel payroll register](/examples/payroll-register/index.md): XLSX template to XLSX.
- [Budget vs actual report](/examples/budget-vs-actual-report/index.md): XLSX template to XLSX.
- [Accounts receivable aging report](/examples/accounts-receivable-aging-report/index.md): XLSX template to XLSX.

## Related topics

- [Accounts receivable aging report](/examples/accounts-receivable-aging-report/index.md)
- [Bank reconciliation report](/examples/bank-reconciliation-report/index.md)
- [Budget vs actual report](/examples/budget-vs-actual-report/index.md)
- [Excel transaction report](/examples/transaction-report/index.md)
- [13-week cash flow forecast](/examples/cash-flow-forecast-13-week/index.md)
- [cXML purchase order](/examples/cxml-purchase-order/index.md)
