Graduating Commission Calculator

Hello-

I am starting a new real estate brokerage and will be hiring real estate agents. I wish to pay the agents on a graduating commission split (the more the agent sells, the greater the % split). This plan is for any calendar year- then starts anew on January 1. For the purpose of this sheet, I only am concerned about the sales in 1 calendar year.

I would like to have a spreadsheet table that show one transaction (side) per row. Then shows the agent what their commission is based the chart below.

The splits would have two criteria: amount of total CUMULATIVE sales and whether the transaction was a BUY or LIST. It would be as follows:

Range of Cumu Sales BUY LIST
$1 $4,999,999 90% 80%
$5,000,000 $9,999,999 91% 82%
$10,000,000 $14,999,999 92% 85%
$15,000,000 $19,999,999 95% 90%
$20,000,000 $999,999,999 97% 95%

The challenge for me is: what happens when a transaction crosses the threshold of one tier to the next. How does the agent get accurately compensated at the percentage below the threshold and increased percentage ABOVE the threshold?

Ultimately, I need to calculate the commission (not to total sales).

But the complexity comes when my agent crosses from one bracket to a higher bracket in one transaction.

So for instance:

Tammy is sitting at $4,850,000 in total sales. She then sells a $350,000 house.

So of that $350,000 - the first $150,000 is credited to her at 80% (getting her to the top of that 80% bracket). Then the remaining $200,000 would be credited to Tammy at the next bracket level of 82%. I know there is an Excel function that can handle this, but I don't know what it is.

Here is a link to spreadsheet- hope you can see it.

https://www.dropbox.com/s/3manz85z5k...PLAN.xlsx?dl=0


Thanks