How to Use n8n with ._Template 81 Commission Calculation Monthly

Sales commission calculation is one of those recurring operational tasks that quietly eats hours every month. Someone exports deal data from the CRM, drops it into a spreadsheet, applies tiered rules, cross-checks against quotas, and then emails each rep a statement. Multiply that across a growing team and you have a process that is both error-prone and impossible to audit. Template 81 — Monthly Commission Calculation — replaces that ritual with an n8n workflow that runs on a schedule, pulls the numbers, applies your rules deterministically, and produces per-rep statements without anyone touching a formula.

The Problem: Manual Commission Runs Don't Scale

Commission logic looks simple until you write it down. A typical plan has a base rate, accelerators once a rep passes quota, clawbacks for refunded deals, splits on team deals, and caps on certain product lines. In a spreadsheet, each of those rules lives in a nested IF statement that only one person understands. When that person is on vacation, the run stalls. When a rate changes mid-quarter, someone updates one tab and forgets another.

The failure modes are predictable and expensive: reps get paid the wrong amount, disputes consume manager time, and finance has no clean trail showing how a number was derived. The root cause is that the calculation lives in a document instead of in a system. Every month you re-run a fragile manual process and hope nobody fat-fingered a cell reference.

The Solution: A Scheduled n8n Workflow as the Source of Truth

Template 81 moves the entire commission run into n8n, where the logic is versioned, the inputs are pulled automatically, and the output is reproducible. Instead of a spreadsheet that a human edits, you get a workflow that on the first of every month reads closed-won deals for the prior period, applies the plan rules in code, and writes a statement per rep. The rules live in one place. The run is idempotent — re-running it with the same inputs produces the same output — so you can always defend a number.

The core shape of the workflow is: a trigger fires on schedule, data nodes fetch deals and rep quotas, a Code node applies the commission logic, and output nodes distribute the results and archive them. Because it is n8n, every step is inspectable — you can open any execution and see exactly what data flowed through each node.

Step-by-Step Setup in n8n

Here is how the template is wired. Adapt the data-source nodes to your stack, but keep the calculation and distribution structure intact.

1. Schedule Trigger. Start with a Schedule Trigger node set to run monthly — cron expression 0 6 1 * * runs at 06:00 on the first day of each month. This gives finance the statements before the workday starts. For testing, swap in a Manual Trigger so you can fire runs on demand.

2. Compute the period window. Add a Code node right after the trigger to calculate the previous month's start and end dates. Output two fields, periodStart and periodEnd, in ISO format. Every downstream query references these instead of hardcoded dates, so the workflow is self-dating.

3. Fetch closed deals. Use an HTTP Request node (or a native CRM node like HubSpot / Pipedrive) to pull deals with status = won and close_date between periodStart and periodEnd. Enable pagination in the HTTP Request node — set Pagination to "Response Contains Next URL" or use a limit/offset loop so you never truncate a large result set.

4. Fetch rep quotas and rates. A second data node reads the commission plan — base rate, quota, accelerator threshold, and cap per rep. Keep this in a Google Sheets, Airtable, or database node so plan changes never require editing the workflow itself.

5. Group deals by rep. Add an Item Lists node (operation: Aggregate Items) or a Code node that groups deals by owner_id and sums deal value per rep. This produces one item per rep carrying their total attributed revenue.

6. Apply the commission logic. This is the heart of the template — a Code node running JavaScript. For each rep, merge their revenue total with their plan row, then compute the payout:

const attained = rep.revenue; const q = rep.quota;
let payout = attained * rep.baseRate;
if (attained > q) { payout += (attained - q) * rep.acceleratorRate; }
payout = Math.min(payout, rep.cap ?? Infinity);
return { repId: rep.id, attained, quota: q, payout: Math.round(payout * 100) / 100 };

Keeping the math in code rather than chained IF nodes means the entire plan is readable in one screen and reviewable in version control.

7. Handle clawbacks. If you process refunds, add a parallel branch that queries refunded deals in the period and subtracts their commission before the payout is finalized. Merge the two branches with a Merge node (mode: Combine by repId) so each rep's net figure is complete.

8. Distribute statements. Split into two outputs. Route the full dataset to a Google Sheets or database node that appends a timestamped commission ledger for finance. Then use a Loop Over Items node feeding a Gmail / Send Email node to send each rep an individual statement showing attainment, quota, and payout. Reference fields directly in the email body with expressions like {{ $json.payout }}.

9. Notify on completion. End with a Slack or WhatsApp node posting a summary to your ops channel: number of reps processed and total payout. This is your signal that the run succeeded without opening n8n.

Benefits: Time Back and a Clean Audit Trail

The most immediate win is time. A run that took a person half a day now completes in seconds and needs zero manual input. But the durable benefit is trust. Because the workflow is deterministic and every execution is logged, you can open any past run and show precisely which deals fed a payout and which rule applied. Disputes get resolved by pulling up an execution instead of reconstructing a spreadsheet from memory.

You also decouple plan changes from process risk. When a rate changes, you edit one row in the plan sheet — the logic and distribution stay untouched. New reps appear automatically because the workflow reads whoever owns deals in the period, rather than relying on a hardcoded list. And the archived ledger gives finance a continuous, timestamped record they can reconcile against payroll.

Common Pitfalls to Avoid

Floating-point rounding. JavaScript math on currency drifts by fractions of a cent. Always round to two decimals at the end (Math.round(x * 100) / 100) and never before intermediate steps, or your totals won't reconcile.

Timezone edges. A deal closed at 11pm on the last day of the month can land in the wrong period if your date filter uses UTC while your CRM records local time. Normalize both the period window and the deal dates to the same timezone in the Code node.

Pagination truncation. The single most common cause of wrong numbers is an HTTP Request node that silently returns only the first page of deals. Confirm pagination is configured and test against a period you know has more records than the page limit.

No idempotency guard. If the workflow errors halfway and you re-run it, you don't want duplicate statements or a doubled ledger entry. Write to the ledger with an upsert keyed on repId + period, and gate the email branch so it only sends once the ledger write succeeds.

Silent failures. Wrap the run so a failed data fetch alerts you instead of quietly producing zero-commission statements. Use n8n's Error Trigger workflow or set the Slack node to fire on the error path — a missing payout is far worse than a delayed one.

Set the schedule, wire your data sources, drop in your plan rules, and let the workflow own the monthly commission run — so your team reviews numbers instead of building them.