Real Estate Commission Calculator (Tiered)

[duteberta]

I am struggling to make an accurate calculator for real estate commissions. I wish to see a RUNNING TOTAL of YTD commissions as they occur. This would also serve to let me view sales activity.

Here is the commission policy:

Annual Gross Commissionable Sales (GCS) $0 - $1,999,999
the agent/broker split is 60/40 (60% to agent and 40% to broker)

Annual Gross Commissionable Sales (GCS) $2,000,000 - $4,999,999
the agent/broker split is 70/30 (70% to agent and 30% to broker)

Annual Gross Commissionable Sales (GCS) $5,000,000 - $9,999,999
the agent/broker split is 80/20 (80% to agent and 20% to broker)

Annual Gross Commissionable Sales (GCS) $10,000,000 to infinity
the agent/broker split is 90/10 (90% to agent and 10% to broker)​

In addition here are two caveats:

They agent must pay 6% out of the agent's split until the annual $3,000 cap is reached, then the agent keeps 100% of their split for the rest of the year.

The brokers split has a cap of $22,000 per year. Once the brokers side reaches a total of $22,000, the agent then keeps 100% of commissions (the split ends).
​

My struggle is where a sale or commission crosses the "criteria line" and part of the value should adhere to one criteria and the other part of the value should adhere to another criteria.

Here is a Dropbox link to my spreadsheet. I highlighted in RED where I'm struggling, because my formulas are not sufficient.

 

[etaf]

can you post a link to the actual spreadsheet please

 

[duteberta]

can you post a link to the actual spreadsheet please

The link is in my post. I hope it works.

 

[etaf]

I can only see an image of the spreadsheet

 

[duteberta]

I can only see an image of the spreadsheet

did you click on "Here is Dropbox link...
"?

 

[shg]

See the last tab in the workbook at https://app.box.com/s/etysu13dpy3pl5i7f986

 

[etaf]

did you click on "Here is Dropbox link...
"?

DAH!!!! sorry missed

if you PUT this in K6 - does it work for you ?
=IF(SUM($J$6:J6)*$K$5>3000,IF(SUM($J$5:J5)*$K$5<3000,(3000-(SUM($J$5:J5)*$K$5)),0),J6*$K$5)

 

[duteberta]

Yes- it works!!!!! Thank you!

 

[etaf]

your welcome

so that covers the

They agent must pay 6% out of the agent's split until the annual $3,000 cap is reached, then the agent keeps 100% of their split for the rest of the year.

Now does something need to be added for these sections

The brokers split has a cap of $22,000 per year. Once the brokers side reaches a total of $22,000, the agent then keeps 100% of commissions (the split ends).

Also the commission structure

Here is the commission policy:Annual Gross Commissionable Sales (GCS) $0 - $1,999,999
the agent/broker split is 60/40 (60% to agent and 40% to broker)

Annual Gross Commissionable Sales (GCS) $2,000,000 - $4,999,999
the agent/broker split is 70/30 (70% to agent and 30% to broker)

Annual Gross Commissionable Sales (GCS) $5,000,000 - $9,999,999
the agent/broker split is 80/20 (80% to agent and 20% to broker)

Annual Gross Commissionable Sales (GCS) $10,000,000 to infinity

the agent/broker split is 90/10 (90% to agent and 10% to broker)

 

[duteberta]

There is one error in here and it was because I did a poor job of explaining one thing. If anyone can help with this I would be very appreciative.
The spreadsheet works perfectly except for - here is how I should have explained it. I did a STRIKETHROUGH on my bad explanation:

Can someone help me fix this calculation?
****
[TABLE="width: 538"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Agent pays 6% out <strike>of their split</strike> THE GROSS SPLIT (example: total commission is 7%, the GROSS SPLIT would be 3.5%) until the annual $3,000 cap is reached, then the agent keeps 100% of their split for the rest of the year.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]The broker’s split has a cap of $22,000 per year. Once the broker’s side reaches a total of $22,000, the agent then keeps 100% of commissions (no more split).[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]This calculator takes TIERS into consideration. This means that if a sale occurs over a tier/rate change, the lower portion is calculated for below the tier and the upper portion is calculated for above the tier.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Example:[/TD]
[/TR]
[TR]
[TD]The agent split changes at $2 Million GCS. Therefore if an agent was sitting at $1,900,000 in GCS then sold a $300,000 house they would be at $2,200,000 GCS. [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]The portion below the $2,000,000 tier ($100,000) would be at the 60/40 split and the portion above the tier ($200,000) would be at the 70/30 split.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]The same applies to office CAPS. Once a CAP is reached, the portion over the cap is retained by agent.[/TD]
[/TR]
</tbody>[/TABLE]