Tiered Commission Rate After Cumulative Sales Volume is Reached

[Nervarine]

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

[#2]

@Nervarine 

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.

[#3]

@mathetes 

[#4]

@Nervarine 

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.)

[#5]

Our broker receives a 5% higher commission split than other agents. I do have dashboards with sumif formulas to show how much volume and commission is earned each month. The sheet will show every closing from every agent throughout the entire year. The sheet says 2022 but it will be used as a template for 2023

[#6]

If you already do a monthly summary, presumably you also have a monthly detailed transactional record...each sale, date, agent, classification of agent, amount, fees, commissions, etc. I would hope you would be producing this annual report from that same transactional database. If that's not what you're doing, it should be, because otherwise you're possibly doing double entry of transactions (this annual report plus whatever you use for the monthly, for starters). There's far more data integrity in using a single record of the transactions, and then multiple reports that draw on that single database.

Assuming that is the case, would you be willing to post a sample of that database (with names changed to fictitious names) and even your monthly dashboard. I'm thinking this annual report could just draw from that same model you already have.

By the way, what version of Excel do you have? If you have Excel 2021 or newer (or a 365 subscription) then functions like FILTER can be used to great advantage.

(I'm at a conference, away from home, with a laptop but not much time to spend on this. I'm back over the weekend with more time.)

What I'm looking for is a more complete description, not only of the formula you want, but what the bigger picture is here....like how many agents are there in total? how many sales on average will each have? what kind of annual summary would make the most sense? And if you really want a detailed list with every agent, every sale, then the aforementioned database, or at least  a more representative dummy sheet, so the formula can take that more comprehensive representation into account.