Real Estate Commission Calculator (Tiered)

duteberta

Board Regular
Joined
Jun 14, 2009
Messages
92
Office Version
  1. 365
Platform
  1. MacOS
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.

spreadsheetPIC.jpg
 
have you got a new spreadsheet at all ?
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I am a new team lead and this spreadsheet is awesome for tracking however not so much for business planning. I'm killing myself trying to work out a code that works and am stuck on how to put this in terms of number of transactions and average sales price. Anyone who can help with this? I have the base of the sheet built but get stuck on when I hit the broker and franchise caps and try to roll them into the tiered structure. Please help! ... Tim
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top