Real Estate Commission Calculator

[duteberta]

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 split is based on that transaction.

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Range of Cumu Sales[/TD]
[TD][/TD]
[TD]BUY[/TD]
[TD]LIST[/TD]
[/TR]
[TR]
[TD]$1[/TD]
[TD]$4,999,999[/TD]
[TD]90%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]$5,000,000[/TD]
[TD]$9,999,999[/TD]
[TD]91%[/TD]
[TD]82%[/TD]
[/TR]
[TR]
[TD]$10,000,000[/TD]
[TD]$14,999,999[/TD]
[TD]92%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]$15,000,000[/TD]
[TD]$19,999,999[/TD]
[TD]95%[/TD]
[TD]90%[/TD]
[/TR]
[TR]
[TD]$20,000,000[/TD]
[TD]$999,999,999[/TD]
[TD]97%[/TD]
[TD]95%[/TD]
[/TR]
</tbody>[/TABLE]

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?

So here is the spreadsheet as it stands now, what functions/formulas do I use for cells I11:I34?

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

https://www.dropbox.com/s/3manz85z5krvmb9/compPLAN.xlsx?dl=0


Thanks

 

[L. Howard]

If these value are correct for cells I11, 12, 13, 14 then this formula in I11 and pull down...???

=IF(C11="BUY",VLOOKUP(D11,$A$3:$C$7,3,1),VLOOKUP(D11,$A$3:$D$7,4,1))*D11

[TABLE="width: 161"]
<tbody>[TR]
[TD] 256,122.00
[/TD]
[/TR]
[TR]
[TD] 148,000.00
[/TD]
[/TR]
[TR]
[TD] 44,800.00
[/TD]
[/TR]
[TR]
[TD] 322,200.00
[/TD]
[/TR]
</tbody>[/TABLE]

Howard

Or should the lookup cell be E11?

H

 

[duteberta]

Yes- I think the lookup is E11. 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. Tammy is the LIST agent. 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.

 

[L. Howard]

Okay, I see it is a bit more complicated than my simplistic approach. Perhaps it's called a Tiered Commission?

Have a look here, where the formula uses SUMPRODUCT.

Howard

https://www.excelcampus.com/modeling/calculate-commissions-with-tiered-rate-structure/

 

[bsuperior]

I believe you wouldn’t be able to do this by just using formula in Excel. Tiered commission calculation is based on different factors.For example, real estate companies usually only consider the annual sale and drop the tier of the agent at the end of the year. Also, not all of property transaction will be closed. How would you consider them in your calculation? Or every agent have their own tier table. There are some accounting software out there like https://bsuperiorsystem.com/product/commission_management/ which is perimary focsuing on real estate.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
1		Prior	June	July		Bracket/ Hurdle	Rate	Rate Differential
2	Input amounts: Sales	3,850,000.00	500,000.00	1,000,000.00		0	80%	80%
3						5,000,000	82%	2%
4	Sales allocation	3,080,000.00	400,000.00	807,000.00		10,000,000	85%	3%
5						15,000,000	90%	5%
6	Total	5,350,000.00	4,287,000.00			20,000,000	95%	5%
7
9a

Cell Formulas
Range	Formula
H2	=G2-N(G1)
B4	=SUMPRODUCT(--(SUM($B$2:B2)>rB),SUM($B$2:B2)-rB,rDiff)-SUM($A$4:A4)
B6	=SUM(B2:E2)
C4	=SUMPRODUCT(--(SUM($B$2:C2)>rB),SUM($B$2:C2)-rB,rDiff)-SUM($A$4:B4)
C6	=SUMPRODUCT(--(B6>rB),B6-rB,rDiff)
D4	=SUMPRODUCT(--(SUM($B$2:D2)>rB),SUM($B$2:D2)-rB,rDiff)-SUM($A$4:C4)

Named Ranges
Name	Refers To	Cells
rB	='9a'!$F$2:$F$6
rDiff	='9a'!$H$2:$H$6