How to Build a Commission Calculator in Excel (Free Template)

Why 74% of Businesses Still Calculate Commissions in Excel

Excel is the best calculation engine ever built.

That's not hyperbole. I've been in corporate finance for over 20 years, and three of those years I was the CFO. In 15 of those, I spent building commission models.

I've calculated that I probably made 43 of them in eight different companies over the years. Tiered rates, rep splits, overlay commissions, manager overrides — all in a spreadsheet.

And it worked. The math was solid. The logic was sound. We got numbers out every month.

But here's what nobody tells you: there's a 90/10 rule in commission management.

Excel is brilliant at the 90% — the calculation, the logic, getting a number.

It's the other 10% that will wreck you.

In this guide, I'm going to show you:

How to build a commission calculator that handles the 90% beautifully
The Excel techniques that make it maintainable and scalable
When you'll know you've outgrown it (and what to do next)

Download the free template here — This template is hosted on Siplify, our sister site and sales commission platform built by founder Matt Brattin. We want to be transparent: clicking this link will take you to Siplify's resource page where you can download the calculator for free.

The 7-Tab Architecture: How This Calculator Works

Most commission models fail because they're built like a game of Jenga. One wrong move and the whole thing collapses.

This calculator uses a 7-tab structure that separates inputs, processing, and outputs:

1. Instructions Tab

Step-by-step walkthrough with timestamps linked to the video. Every tab has a "click here for walkthrough" link that takes you to the relevant moment in the tutorial.

2. Inputs Tab

Raw data from your CRM lands here. This is a data table called "table_deals" with required fields:

Opportunity ID
Customer name
Sales rep
Support rep (optional)
Product name
Amount
Closed date (or whatever date commissions are earned)
Contract term (defaults to 12 months if blank)

Rule: No calculations here. Just raw CRM data.

3. Helpers Tab

This is the mini workhorse behind the scenes. It's transparent by design — I could have hidden this, but I wanted you to see how things accumulate.

What it does:

Grabs all unique sales reps from your data
Grabs all unique products
Creates every possible combination of person × product
Generates "commissionable events" — more rows than your input data because it creates separate events for primary reps, support reps, and managers

This is where the event generator lives — the engine that turns 25 deals into 38 commissionable events.

4. People Tab

Your sales team roster. Simple inputs:

Rep names (copied from Helpers tab)
Job titles (AE, SDR, Manager, etc.)
Manager names (only if they're commission-eligible)
Manager flag (Y/N)

5. Plans Tab

This is your rules engine — the most powerful tab in the document.

For every rep × product combination, you configure:

Quota amount
Does it contribute to quota? (Y/N)
Is it commissionable? (Y/N)
Commission type (Percentage, Flat, None)
Base rate (e.g., 15%)
Tier 2 threshold (e.g., 80% of quota)
Tier 2 rate (e.g., 20%)
Tier 3 threshold and rate (optional)
Effective start date
Effective end date

Why this matters: If you have a plan change at the end of Q1, you can set an effective end date of 3/31 and create a new row with a start date of 4/1. The calculator picks up the right rules based on the closed date.

6. Commission Ledger Tab

The calculation engine. You paste the event generator data from Helpers, and it:

Looks up plan rules for each event
Determines tier placement
Calculates commission amounts
Tracks quota attainment
Provides full auditability for every commissionable event

Every row shows: This is what you got, this is where it landed in the threshold, this is how you're adding up, this was the effective tier that was in place.

7. Statement Template Tab

The final output. Select a rep name, set your date range, and it generates:

Period commission total
Year-to-date commission
Quota attainment (with visual gauge)
Monthly trend chart
Deal-by-deal breakdown (expandable/collapsible)

Change the name, change the date range, export as PDF, send it out. Done.

Why this structure works:

Raw data stays in Inputs
Processing happens in Helpers and Ledger
Configuration lives in People and Plans
Output is clean and rep-ready

The Formula Architecture: Modern Excel Functions

Here's where most commission models break: dragged formulas.

You write a formula. You drag it down 1,000 rows. Somewhere around row 347, you accidentally overwrite one. Your model is broken. And you'll never find it because it still looks fine.

This is a ghost error.

This calculator uses modern Excel functions to eliminate that risk. Microsoft has thrown so many amazing new functions at us that have made a lot of things very, very easy and exciting.

But you can get carried away. My intent here was to make this as user-friendly as possible.

The LET Function: Declaring Variables

The most complex thing I'm doing in this calculator is taking advantage of LET functions. They might look intimidating at first, but I can assure you they're not that bad once you get into it. In fact, they're pretty great.

Here's how LET works: You declare variables that you're going to be using, and then you do something with your variables.

Example from the Helpers tab (generating the rep list):

=LET(
    _salesReps, tbl_Deals[Sales_Rep],
    _supportReps, FILTER(tbl_Deals[Support_Rep], tbl_Deals[Support_Rep]<>"", ""),
    _managers, FILTER(tbl_People[Manager_Name], tbl_People[Manager_Name]<>"", ""),

    _combined, VSTACK(
        _salesReps,
        IF(_supportReps="", "", _supportReps),
        IF(_managers="", "", _managers)
    ),

    _cleaned, FILTER(_combined, _combined<>"","No Reps Found"),

    SORT(UNIQUE(_cleaned))
)

What this does:

Grabs sales reps from the deals table
Grabs support reps (filtering out blanks)
Grabs managers from the people table (filtering out blanks)
Combines them into a vertical stack (VSTACK)
Filters out any remaining blanks
Returns a sorted, unique list

One formula. No helper columns. No dragging. Just clean, maintainable logic.

Creating Rep × Product Combinations

This one probably looks more complicated than it is. Here's what it does:

The goal: Create every possible combination of rep and product.

If you have 4 reps and 3 products, that's 12 combinations. Alex × Product A, Alex × Product B, Alex × Product C, then Frank × all three, etc.

The formula:

Count the reps (4)
Count the products (3)
Multiply them together (12 total rows needed)
Create a "rep index" that goes: 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4 (each rep repeated for each product)
Create a "product index" that goes: 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3 (cycling through products)
Use HSTACK to combine them

=LET(
    _reps, B7#,
    _products, D7#,
    _repCount, ROWS(_reps),
    _prodCount, ROWS(_products),
    _totalRows, _repCount * _prodCount,
    _repIndex, INT((SEQUENCE(_totalRows)-1) / _prodCount) + 1,
    _prodIndex, MOD(SEQUENCE(_totalRows)-1, _prodCount) + 1,
    HSTACK(INDEX(_reps, _repIndex), INDEX(_products, _prodIndex))
)

It's creating combinations using SEQUENCE (which generates arrays of numbers) and index matching.

Hopefully that wasn't just more confusing than it needed to be. The way you can play with this, since you have access to the file, is just isolate these specific things and make those your output. Change the formula to just say, "I just want to look at the index and see what the output is," and you'll see.

The Event Generator

This is the heart of the calculator. It takes your deals and creates separate commissionable events for:

Primary reps (the AE who closed the deal)
Support reps (overlay commissions for SEs, CSMs, etc.)
Managers (overrides based on team performance)

What it does:

Declares all the base data (Opportunity ID, customer name, closed date, sales rep, product, contract term, ACV)
Creates three separate event types (Primary, Support, Manager)
Uses VSTACK to combine them into one unified commissionable event log
Filters to make sure everything is valid

This is what turns 25 deals into 38 commissionable events.

The Commission Ledger Logic

Once you paste the event data into the Commission Ledger, here's what happens:

For each event, the calculator looks up:

Does it contribute to quota? (from Plans tab, matching rep + product + effective date)
Is it commissionable? (same lookup)
What's the base rate? (Tier 1 rate from Plans)
What are the tier thresholds? (80% for Tier 2, etc.)
What are the tier rates? (20% for Tier 2, etc.)

Then it calculates:

ACV for the period (based on closed date and term)
Quota attainment (cumulative for the rep)
Which tier the rep landed in (based on attainment vs. thresholds)
Effective commission rate (could be Tier 1, Tier 2, or flat)
Final commission amount

Every formula uses this pattern:

=FILTER(
  PlanRules[Column],
  (PlanRules[Payee] = ThisEvent[Payee]) *
  (PlanRules[Product] = ThisEvent[Product]) *
  (PlanRules[Start Date] <= ThisEvent[Closed Date]) *
  (PlanRules[End Date] >= ThisEvent[Closed Date])
)

It's saying: Give me the right rule for this unique combination of person, product, and date.

That's how you can layer in multiple plan designs in the same table and have the calculator pick the right one based on effective dates.

When to Use This Model (And When to Stop)

This model will get you 90% of the way there. It's better than what most people are using.

Use this if:

You have fewer than 50 reps
Your commission plans are straightforward (2-3 tiers, basic splits)
You're comfortable with Excel and can maintain it
Nobody's threatening lawsuits or audits

You've outgrown it when:

You're spending 5+ hours every month answering "where's my commission?"
Your reps don't trust the numbers and keep shadow spreadsheets
Auditors or legal teams start asking about controls and documentation
You're manually copying and pasting data every month (or struggling with Power Query)
There's no audit trail — if you fat-finger something, there's nothing protecting against little tweaks and changes
There's no self-service portal — reps only get access when they get access, which drives them not to trust the information
This thing is not going to scale very comfortably past 10 to 15 reps

That's the 10% Excel doesn't solve.

No approval workflow. No audit trail. No self-service portal. No live updates. That's what produces shadow ledgers. That's what drives salespeople to not trust information.

And when there are errors — which there often are — it's going to be a problem.

Excel wasn't built for any of that. And that's okay — it doesn't have to be.

Excel is a calculator. When you need insurance, you need something else.

Download the Template

Download the free Excel template here

Note: This template is hosted on Siplify, our sister site and sales commission platform built by founder Matt Brattin. Clicking this link will take you to Siplify's resource page where you can download the calculator for free — no credit card required.

Inside the template you'll find:

The complete 7-tab structure
Sample commission plans (tiered, flat rate, overlay)
Documented formulas with explanations
Instructions for customizing to your plans

What's Next?

If you want to level up your Excel skills for real-world business impact, check out Excel 4 Academy where the focus is on:

Power Query for automated, refreshable reporting
Real-world practical examples across Finance, Sales, Marketing, HR, and Operations
Excel analytics scenarios that mirror actual business challenges
Outcomes and impact — not just formulas, but how to create work that gets noticed and trusted

It's designed to make you faster, more accurate, and more valuable so you earn trust, create time, and move up faster.

Related Resources:

Matt Brattin is a former CFO and the founder of TMB Analytics and Siplify. He's spent 20+ years building commission systems, forecasting models, and financial processes — and teaching others to do the same.