Tiered Commission

I am creating a tiered commission spreadsheet where brokers are paid based on sales they make. I have a formula that does the tiered schedule, but it just gives me a total for all sales, and I need to see individual amounts for each sale. Here is an example: A broker gets a check for 10,000. Of that 10,000, they get 60%. Then they receive a check for 20,000. Of that 20,000, they receive 15,000 at the 60% level, and 5,000 at the 65% level (25,000 is the threshold). Each check is entered individually for the brokers, so I want to be able to see that from the 10,000 they received 6000 and from the 20000 they received 12,250. Right now my formula will only tell me that they received a total of 18,250, but I don't know how much of that came from which clients.... Does that make sense?

Here is my schedule:
0-25,000 60%
25001-50000 65%
50001-75000 70%
75001-100000 75%
100001 + 80%

So, for example, this is what I would like to see happen:
10000 6,000
20000 12,250
etc.


This is what is happening for me:
10,000 18,250
20,000


Can anyone help?!

Great! It works! Thank you so much
Now I have another level to add. I'm thinking I might know how to do this, but I'm sure you can tell me for sure. There are different payout percentages dependent on what year the client is. So the schedule I gave you was for a first year client. However, a second year client's schedule has the same amount levels, but the percentages are 65, 70, 70, 75, 80. And for a third year client, a broker will receive 70, 70, 75, 75, 80 for the levels. Can I just create two more tables like the one above and do an if/then statement on the commission? ex: If(B10=1, SUMPRODUCT((SUM(A$9:A10) > $A$3:$A$7) * (SUM(A$9:A10) - $A$3:$A$7) * ($B$3:$B$7-$B$2:$B$6)) - SUM(B$9:B9), if(B10=2, Sumproduct((the next table which I haven't created yet))........ would that work??

Well, I think it is going to work, but again I ran into a snag. I calculate this monthly, and each month has its own sheet. So I have a spot on the bottom of each broker's page that keeps a running total of their year to date 1st year clients, year to date 2nd year clients, and year to date 3rd year clients... I think these are the cells I need in the formula, so when I enter a new check, it is added to the year to date total, and then from there it figures out what tier it falls into.. So which cells would I modify in the formula to reference the year to date cells, rather than just the total of the checks I enter this month?? Would it be on the commission side or the check side??

example2.png

Here is a picture of what I'm working with. I realize the last post was a bit messy. I want to enter the clients name, advisory year, and gross advisory. I want the formula to then tell calculate the net to advisor based on what this new entry does to the YTD total. So for example, if in January a broker has 15,000 in first year advisory, then in february they get a check for 20,000 from a first year, their first year total is now 35,000. so the 15000 in january would be 60%, and the first 10,000 from february will be 60%, but the other 10,000 in february will be 65%. So I need the formula to look at the YTD total, not just the gross advisory column total... Does that clear things up a bit?