Forum Discussion
Tiered Commission Rate After Cumulative Sales Volume is Reached
I have a spreadsheet that details the transaction amount, gross commission, and then calculates agent commission based on the individual rate the agent and brokerage has set. Every agent gets a 5% bump in commission once the total sales reaches $1,500,000. However the formula i used will calculate the agent commission on every dollar of a transaction that would cause the agent to reach the tier
For instance: If agent A has sold $1,300,000 they will have 75% agent commission on every transaction thus far. If agent A has transaction for $250,000 then they hit the threshold but the formula pays out 80% on the whole $250k rather than 75% on 200k and 80% on the 50k.
Formula
=IFS(AND(B5="John Doe",SUMIF($B$5:B5,"John Doe",$I$5:I5)>=1500000),(J5-H5)*0.85,AND(B5="John Doe",SUMIF($B$5:B5,"John Doe",$I$5:I5)<=1500000),(J5-H5)*0.8)
(J5-H5) etc. represents paying out referral fees where applicable
5 Replies
Since you've gone to the trouble of creating a spreadsheet with dummy names in order to post an image of it here, would you be so kind as to put a copy of that spreadsheet on OneDrive or GoogleDrive and then post a link to it here, Otherwise, in order for us to write a formula and test it in your context, you're asking that we create the whole spreadsheet.......which you already have done. Help us help you, by posting a copy as noted.
I notice that you have a table on the second sheet, a table for different classifications of "Agent" vs "Associate," with the former receiving slightly higher (5%) percentages for the same cutoffs.
It brought to mind our Federal Income Tax Tables. And I wonder if you could use a variation on the approach I've created here in the attached for calculating taxes based on different filing statuses.
In the meantime, while you play with that, although your example is valid for a simple formula for "John Doe" -- I wonder if you wouldn't be better served if you could give us a bit more guidance on how you plan to use this for the entire agency. Do you have a single sheet that records all sales and whether the person involved is a broker or associate? Do you intend to do this calculation based on each sale, paying the commission due then and there, or is it more of a monthly thing, where multiple sales during a month get accounted for in total? Or quarterly?
Would it help, in short, to create a "dashboard" sheet that pulled data for a given agent for a given period (as well as all periods that preceded it in the year) and gave a single "commission earned for the month" or "...quarter" or, possibly, "...for the year?
I ask because there are so many different ways to look at this, and your sample sheet carries the heading "Closing Record 2022" implying that it's meant to be a year-end calculation. In which case, one could do one calculation very easily on ALL the sales due to John Doe, rather than a line by line calculation as you're currently trying to do.
(These are all questions I'd be asking if we were sitting face-to-face, working to design a spreadsheet that not only gives you the correct calculations but also does so in ways that comport with the actual way you'll be using it.)
