Real Estate Commission with Cap Formula

[agentbarker]

Hello All!

Via some google searches, I found a few similar questions answered by you fine people but didn't see my exact question and couldn't tweak on my own. Here is the scenario:

Agent has an 80/20 split with the brokerage. If they sell 100000.00 house, they get 3% commission (3000) and give the broker 20% (600). They would then keep the 2400. This will continue throughout the calendar year. Once they've earned enough commission to pay the broker 15000.00, they no longer pay the broker 20%. They then only pay 100.00 per transaction. So once they earn 75000 in gross commissions, they only pay 100.00 after that.

I need a formula that adds until it hits 15k and then adds 100.00 for each additional row?? (I think)
Let me know what you guys think. Thanks SO much in advance!

Here's a link to the google sheet:
https://docs.google.com/spreadsheets/d/1pw4Ys3nqW5LoAYO1HJvenULv4pWN1WGfHoRf5u8omFY/edit?usp=sharing

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

didn't quite get that.

100 is taken out after the 15000 cap in Col E, i.e. from Row 21 onward.

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

so, after the 15000 cap is met, it is no longer an 80/20 split. The agent keeps 100% of the commission and is only responsible for paying the broker 100 per transaction. So, after 15K, each transaction will be 15100, 15200, 15300, etc. and then the 80/20 goes away. so is there a way to have it recalculate commissions after the 15K is hit?

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

ok, leave C3 & D3 unchanged

change C4 to

=IF(E3<15000,B4*80%,B4)

and D4

=IF(E3<15000,B4*20%,100)

copy down

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

getting warmer...could you add an ifblank to remove the auto population of the 100?

and for the question i've seen in the past here:
Around the threshold of the 15000K cap, can you have the formula cap exactly at 15k and give the difference to the agent? if the agents 20 due to broker would put the agent over 15k, can you cap it at exactly 15k and give the difference to the agent, then the next transaction would be 100.00?

forgive me for being a noob pest. You've been outstanding.

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

Ok, so the E column needs to also read:

='after 15k' then take 'b' minus 100.00.

e20 and e21 should be something close to
e20 - 16056
e21 - e20+b21-100.00

does that make sense?

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

=IF(ISBLANK(A21),"",IF(SUM($D$3:D20)>=15000,E20+B21-100,SUM($D$3:D21)))

I think this worked...the only thing now is right at that sweet spot of 15000. Need the formula to true up to 15K and then give excess to agent, but only at that one tipping point..

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

I think this worked...the only thing now is right at that sweet spot of 15000. Need the formula to true up to 15K and then give excess to agent, but only at that one tipping point..

https://www.mrexcel.com/forum/excel...iered-2.html?highlight=real+estate+commission

This post has the same issue I do, but I didn't see an answer.

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="class: cms_table, width: 538"]
<tbody>[TR]
[TD]Agent pays 6% out <strike>of their split</strike> THE GROSS SPLIT (example: total commissionis 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]

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

have a look if this work for you, copy C3:E3 down

Excel 2013/2016
	A	B	C	D	E
1	$ 3,925,000.00	$ 117,750.00	$ 101,850.00		$ 15,900.00
2	SELL PRICE	COMM 3%	80 SPLIT	20 SPLIT	15K CAP
3	$ 100,000.00	$ 3,000.00	$ 2,400.00	$ 600.00	$ 600.00
4	$ 230,000.00	$ 6,900.00	$ 5,520.00	$ 1,380.00	$ 1,980.00
5	$ 95,000.00	$ 2,850.00	$ 2,280.00	$ 570.00	$ 2,550.00
6	$ 120,000.00	$ 3,600.00	$ 2,880.00	$ 720.00	$ 3,270.00
7	$ 150,000.00	$ 4,500.00	$ 3,600.00	$ 900.00	$ 4,170.00
8	$ 220,000.00	$ 6,600.00	$ 5,280.00	$ 1,320.00	$ 5,490.00
9	$ 115,000.00	$ 3,450.00	$ 2,760.00	$ 690.00	$ 6,180.00
10	$ 56,000.00	$ 1,680.00	$ 1,344.00	$ 336.00	$ 6,516.00
11	$ 250,000.00	$ 7,500.00	$ 6,000.00	$ 1,500.00	$ 8,016.00
12	$ 130,000.00	$ 3,900.00	$ 3,120.00	$ 780.00	$ 8,796.00
13	$ 95,000.00	$ 2,850.00	$ 2,280.00	$ 570.00	$ 9,366.00
14	$ 165,000.00	$ 4,950.00	$ 3,960.00	$ 990.00	$ 10,356.00
15	$ 145,000.00	$ 4,350.00	$ 3,480.00	$ 870.00	$ 11,226.00
16	$ 195,000.00	$ 5,850.00	$ 4,680.00	$ 1,170.00	$ 12,396.00
17	$ 100,000.00	$ 3,000.00	$ 2,400.00	$ 600.00	$ 12,996.00
18	$ 120,000.00	$ 3,600.00	$ 2,880.00	$ 720.00	$ 13,716.00
19	$ 140,000.00	$ 4,200.00	$ 3,360.00	$ 840.00	$ 14,556.00
20	$ 250,000.00	$ 7,500.00	$ 7,056.00	$ 444.00	$ 15,000.00
21	$ 192,000.00	$ 5,760.00	$ 5,660.00	$ 100.00	$ 15,100.00
22	$ 135,000.00	$ 4,050.00	$ 3,950.00	$ 100.00	$ 15,200.00
23	$ 99,000.00	$ 2,970.00	$ 2,870.00	$ 100.00	$ 15,300.00
24	$ 85,000.00	$ 2,550.00	$ 2,450.00	$ 100.00	$ 15,400.00
25	$ 130,000.00	$ 3,900.00	$ 3,800.00	$ 100.00	$ 15,500.00
26	$ 150,000.00	$ 4,500.00	$ 4,400.00	$ 100.00	$ 15,600.00
27	$ 200,000.00	$ 6,000.00	$ 5,900.00	$ 100.00	$ 15,700.00
28	$ 135,000.00	$ 4,050.00	$ 3,950.00	$ 100.00	$ 15,800.00
29	$ 123,000.00	$ 3,690.00	$ 3,590.00	$ 100.00	$ 15,900.00
30		$ -
31		$ -
32		$ -
33		$ -
34		$ -
35		$ -
36		$ -
37		$ -
38		$ -
39		$ -
40		$ -
41		$ -
42		$ -
43		$ -
44		$ -
45		$ -
46		$ -
47		$ -
48		$ -
49		$ -
50		$ -
JAN

Cell Formulas
Range	Formula
C3	=IF(ISBLANK(A3),"",IF(AND(SUM($B2:B$3)<75000,SUM($B$3:B3)>=75000),B3-D3,IF(SUM($B2:B$3)*20%<15000,80%*B3,B3-100)))
D3	=IF(ISBLANK(A3),"",IF(ISNONTEXT(E2),IF(AND(SUM($B2:B$3)<75000,SUM($B$3:B3)>=75000),15000-E2,IF(AND(E2<15000,E3>=15000),15000-E2,IF(OR(E2<15000,E3<15000),E3-E2,100))),B3-C3))
E3	=IF(ISBLANK(A3),"",SUM($B$3:B3)-SUM($C$3:C3))

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

have a look if this work for you, copy C3:E3 down

AMAZING. Absolutely outstanding. Thank you so much. I won't say closed, but I think this thread may go away...LOL.

Truly,
Shane

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help



Can I ask one more favor?

I've copied the columns to show the same scenario where there isnt a cap and the 80/20 split goes forever. should be MUCH easier than the cap version. Could you take one more look and construct a formula for I3:k50?
https://docs.google.com/spreadsheets/d/1pw4Ys3nqW5LoAYO1HJvenULv4pWN1WGfHoRf5u8omFY/edit?usp=sharing

Truly,
Shane