Thread: real estate commission schedule

OK i am needing some help with formulas for figuring out my commission earnings on real estate sales. I have attached a sheet with necessary info.

I receive a certain percentage of my broker's commission based on what type of house sale occurs. When one of my listings sell I receive the commission in A2:A7. When I sell a house to Company A I receive the commisions from B2:B7, company B C2:C7, and company C D2:D7.

My own personal commission percentages increase based on the income schedule E2:F7. For example, once I have earned $8137, my percentages for sales all jump to Row 3.

I have set up a chart below the commission schedule for each individual sale to calculate the commission for each type of sale. Each "x" represent a sale for each category (LISTING, COMP A, COMP B, COMP C). The broker's commission is always 3.5% of the total sales price. My commission will be a certain percentage of the broker's commission based on the scale above.

Can anyone help with some formulas that would change the commission percentage earned based on total previous commissions earned?

Thanks for the help!!!

For example, once I have earned $8137, my percentages for sales all jump to Row 3.

I think given your setup you would need to make certain assumptions - ie commission rate is based on prior sales (current sale is excluded) thus you don't have split tier deals...

IMO you would need to calculate each commission value in an adjacent column to your table rather than simply trying to total in the bottom row - it is my opinion that in this instance you can't really calculate in that manner given you need to know cumulative commission prior to any given deal when calculating output and for that you need to store the prior payments.

Based on the above perhaps then:

Code:

I16: 
=$D16*INDEX($A$2:$D$7,MATCH(SUM($I$15:$I15),$E$2:$E$7),MATCH("x",$E16:$H16,0))
copied down to I25

The above will give you commission per deal - you can then use SUMIF to generate the subtotals in row 26 such that

Code:

E26: =SUMIF(E$16:E$25,"x",$I$16:$I$25)
copied across to H26

In all honesty given the setup and rules I think that would prove relatively complex and would warrant a few extra calcs.

Tiered-commission payments can often be calculated relatively easily given they are normally based on gross sales rather than on historic commission paid.... in this instance you would (as I see it) need to establish at commencement of deal calculation:

-- Commission Paid up to and excl. Current Deal

-- Current Tier Upper Commission Payment Threshold

-- Commission Yet to be Paid out of Current Tier

-- How much of your % of the brokers commission of current deal is required at the current tier rate to use up the remainder of said tier

-- Take any remainder of your % of the brokers % (if exists) and apply it at the next tier rate.

Of course things would get even more complex should a deal be so gargantuan that the commission to be paid would consume multiple tiers ...
(I've seen it happen... unfortunately I wasn't the salesperson)

So in essence I'd say it all boils down to whether or not it's a show stopper requirement - if it is then I'd probably consider using a User Defined Function (VBA) myself. I'm not saying you can't do it using native functions but that VBA would be my preference.