# Calculating Sales Commissions in Excel

In a previous article I explained how sales commissions with tiered rates and product groups can be calculated using the table-valued functions of our .net library Finaquant Calcs.

In this article, I will explain how sales commissions can be calculated in Excel. The commission scenario is the same as explained in the previous article:

## Commission Scenario

• Dealers receive sales commissions proportional to their sales totals for each product group in a quarter or month (commission or payment period).
• Product groups (or pools) can be defined by product category or individual products for each dealer.
• The commission scale with tiered rates (class or level scale logic) for each product group (or pool) determines the amount of commissions to be paid at the end of each commission period.

## Steps for Commission Calculation

Following 2.5-minute video shows all the steps explained below:

2) Run Create Test Tables to generate all the four input tables required for commission calculation:

Input tables:
`SalesTable` Sale transactions per each dealer
`ComScaleTable` Commission scale table with tiered commission rates
`ProdPoolTable` Data table which defines product groups by assigning categories or individual products to a group ID.
`ScalePoolTable` Data table which assigns a commission scale to each product group.

3) Run Calculate Sales Commissions after selecting and entering proper parameters into the parameter form in Excel:

## Results

Two output tables will be generated:

1) Sales Commissions per Product Pool and Dealer

2) Sales Commissions per Dealer

## Conclusions

This little demonstration shows you that you don’t need an expensive software, or elaborate database programming to implement a rule-based calculation like the commission example here. You can simply do it in Excel, using table-valued functions.

## Developer Notes

Commission Calculation was added to the Excel Add-in (Finaquant in Excel) as an example for a user-defined table function. Excel or .NET developers may see the related article: How to Add a User-Defined Table Function to Excel