Forum Discussion
Calculating Commissions with escalator clause
Hi, I am trying to calculate commissions with an escalator clause and break it out by month. In column A I have the date the deal closed. In column E I have the amount of the deal, with E29 totaling the deals amount. Rows 29-41 are the months of the year. My commission structure is all deals are at .04 until I reach $100K, then it changes to .06. For instance, if I sold $50K in December (commissions are paid out following month of close) I would expect $2K to be paid in January. If I sold $75K in January, I would expect to paid out at .04 for the remaining $50K and then .08 at the $25K over the total of $100K. Is there a way to calculate this? I have tried numerous SumIfs formulas &date but cant get anything to work.
See the attached workbook.
6 Replies
- Not sure if the above is clear - my goal is to have each month show my expected commission keeping in mind the commission structure
Does the escalator start over each month? Or only at the beginning of the year?
- Hi,
the escalator starts over at the start of the new Fiscal year, which is April. Capturing that piece of that isnt essential - I can always make another box of months to capture if needed. Thank you,
