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

 

[agentbarker]

Re: Real Estate Commission with Cap Formula Help

Bump? Not sure if that's a thing on these boards....

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

Sorry this is late but I have been away this weekend.

I've revised the formula to allow for a more flexible input, both the % split (D2) and cap (E2) can be adjust dynamically. Note that both B2, C2 & D2 are formatted as %.

Excel 2013/2016
	A	B	C	D	E	F
1	$ 3,877,000.00	$ 116,310.00	$ 100,310.00		$ 16,000.00
2	SELL PRICE	COMM 3%	80%	20%	$ 15,000	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	$ 70,000.00	$ 2,100.00	$ 2,000.00	$ 100.00	$ 15,900.00
30	$ 5,000.00	$ 150.00	$ 50.00	$ 100.00	$ 16,000.00
JAN

Cell Formulas
Range	Formula
C2	=1-D2
C3	=IF(ISBLANK(A3),"",IF(AND(SUM($B2:B$3)<$E$2/$D$2,SUM($B$3:B3)>=$E$2/$D$2),B3-D3,IF(SUM($B2:B$3)*$D$2<$E$2,$C$2*B3,B3-100)))
D3	=IF(ISBLANK(A3),"",IF($E$2=0,B3*$D$2,IF(AND(SUM($B2:B$3)<$E$2/$D$2,SUM($B$3:B3)>=$E$2/$D$2),$E$2-E2,IF(AND(E2<$E$2,E3>=$E$2),$E$2-E2,IF(OR(E2<$E$2,E3<$E$2),IF(E2=$E$2,E3,E3-E2),100)))))
E3	=IF(OR($E$2=0,ISBLANK(A3)),"",SUM($B$3:B3)-SUM($C$3:C3))

Excel 2013/2016
	A	B	C	D	E	F
1	$ 3,877,000.00	$ 116,310.00	$ 113,510.00		$ -
2	SELL PRICE	COMM 3%	80%	20%	$ -	Cap
3	$ 100,000.00	$ 3,000.00	$ 2,900.00	$ 600.00
4	$ 230,000.00	$ 6,900.00	$ 6,800.00	$ 1,380.00
5	$ 95,000.00	$ 2,850.00	$ 2,750.00	$ 570.00
6	$ 120,000.00	$ 3,600.00	$ 3,500.00	$ 720.00
7	$ 150,000.00	$ 4,500.00	$ 4,400.00	$ 900.00
8	$ 220,000.00	$ 6,600.00	$ 6,500.00	$ 1,320.00
9	$ 115,000.00	$ 3,450.00	$ 3,350.00	$ 690.00
10	$ 56,000.00	$ 1,680.00	$ 1,580.00	$ 336.00
11	$ 250,000.00	$ 7,500.00	$ 7,400.00	$ 1,500.00
12	$ 130,000.00	$ 3,900.00	$ 3,800.00	$ 780.00
13	$ 95,000.00	$ 2,850.00	$ 2,750.00	$ 570.00
14	$ 165,000.00	$ 4,950.00	$ 4,850.00	$ 990.00
15	$ 145,000.00	$ 4,350.00	$ 4,250.00	$ 870.00
16	$ 195,000.00	$ 5,850.00	$ 5,750.00	$ 1,170.00
17	$ 100,000.00	$ 3,000.00	$ 2,900.00	$ 600.00
18	$ 120,000.00	$ 3,600.00	$ 3,500.00	$ 720.00
19	$ 140,000.00	$ 4,200.00	$ 4,100.00	$ 840.00
20	$ 250,000.00	$ 7,500.00	$ 7,400.00	$ 1,500.00
21	$ 192,000.00	$ 5,760.00	$ 5,660.00	$ 1,152.00
22	$ 135,000.00	$ 4,050.00	$ 3,950.00	$ 810.00
23	$ 99,000.00	$ 2,970.00	$ 2,870.00	$ 594.00
24	$ 85,000.00	$ 2,550.00	$ 2,450.00	$ 510.00
25	$ 130,000.00	$ 3,900.00	$ 3,800.00	$ 780.00
26	$ 150,000.00	$ 4,500.00	$ 4,400.00	$ 900.00
27	$ 200,000.00	$ 6,000.00	$ 5,900.00	$ 1,200.00
28	$ 135,000.00	$ 4,050.00	$ 3,950.00	$ 810.00
29	$ 70,000.00	$ 2,100.00	$ 2,000.00	$ 420.00
30	$ 5,000.00	$ 150.00	$ 50.00	$ 30.00
31
JAN

Excel 2013/2016
	A	B	C	D	E	F
1	$ 3,877,000.00	$ 116,310.00	$ 102,910.00		$ 13,400.00
2	SELL PRICE	COMM 3%	85%	15%	$ 12,500	Cap
3	$ 100,000.00	$ 3,000.00	$ 2,550.00	$ 450.00	$ 450.00
4	$ 230,000.00	$ 6,900.00	$ 5,865.00	$ 1,035.00	$ 1,485.00
5	$ 95,000.00	$ 2,850.00	$ 2,422.50	$ 427.50	$ 1,912.50
6	$ 120,000.00	$ 3,600.00	$ 3,060.00	$ 540.00	$ 2,452.50
7	$ 150,000.00	$ 4,500.00	$ 3,825.00	$ 675.00	$ 3,127.50
8	$ 220,000.00	$ 6,600.00	$ 5,610.00	$ 990.00	$ 4,117.50
9	$ 115,000.00	$ 3,450.00	$ 2,932.50	$ 517.50	$ 4,635.00
10	$ 56,000.00	$ 1,680.00	$ 1,428.00	$ 252.00	$ 4,887.00
11	$ 250,000.00	$ 7,500.00	$ 6,375.00	$ 1,125.00	$ 6,012.00
12	$ 130,000.00	$ 3,900.00	$ 3,315.00	$ 585.00	$ 6,597.00
13	$ 95,000.00	$ 2,850.00	$ 2,422.50	$ 427.50	$ 7,024.50
14	$ 165,000.00	$ 4,950.00	$ 4,207.50	$ 742.50	$ 7,767.00
15	$ 145,000.00	$ 4,350.00	$ 3,697.50	$ 652.50	$ 8,419.50
16	$ 195,000.00	$ 5,850.00	$ 4,972.50	$ 877.50	$ 9,297.00
17	$ 100,000.00	$ 3,000.00	$ 2,550.00	$ 450.00	$ 9,747.00
18	$ 120,000.00	$ 3,600.00	$ 3,060.00	$ 540.00	$ 10,287.00
19	$ 140,000.00	$ 4,200.00	$ 3,570.00	$ 630.00	$ 10,917.00
20	$ 250,000.00	$ 7,500.00	$ 6,375.00	$ 1,125.00	$ 12,042.00
21	$ 192,000.00	$ 5,760.00	$ 5,302.00	$ 458.00	$ 12,500.00
22	$ 135,000.00	$ 4,050.00	$ 3,950.00	$ 100.00	$ 12,600.00
23	$ 99,000.00	$ 2,970.00	$ 2,870.00	$ 100.00	$ 12,700.00
24	$ 85,000.00	$ 2,550.00	$ 2,450.00	$ 100.00	$ 12,800.00
25	$ 130,000.00	$ 3,900.00	$ 3,800.00	$ 100.00	$ 12,900.00
26	$ 150,000.00	$ 4,500.00	$ 4,400.00	$ 100.00	$ 13,000.00
27	$ 200,000.00	$ 6,000.00	$ 5,900.00	$ 100.00	$ 13,100.00
28	$ 135,000.00	$ 4,050.00	$ 3,950.00	$ 100.00	$ 13,200.00
29	$ 70,000.00	$ 2,100.00	$ 2,000.00	$ 100.00	$ 13,300.00
30	$ 5,000.00	$ 150.00	$ 50.00	$ 100.00	$ 13,400.00
JAN

 

[AlanY]

Re: Real Estate Commission with Cap Formula Help

Formula in C3 should read

<tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">A3</font>),"",IF(<font color="Red">$E$2=0,B3*$C$2,IF(<font color="Green">AND(<font color="Purple">SUM(<font color="Teal">$B2:B$3</font>)<$E$2/$D$2,SUM(<font color="Teal">$B$3:B3</font>)>=$E$2/$D$2</font>),B3-D3,IF(<font color="Purple">SUM(<font color="Teal">$B2:B$3</font>)*$D$2<$E$2,$C$2*B3,B3-100</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />