Commission Tracking


New Member
Jul 14, 2010
I need help tracking commission on a recurring basis, but also on a sliding scale based on the month.

I've been struggling with this for two weeks now, and have only come up with a temporary "just to get by" spreadsheet. I'm at my wit's end and now only dream in formulas, tables and variables. I'm hoping someone with a bit more knowledge and experience can help me get to a positive place with this.

Let me explain the commission structure.
A salesman sells item to customer at a monthly fee. Salesman gets 50% of the monthly fee for the first 12 months. Salesman gets 25% of the monthly fee for the next 24 months.

What I need the spreadsheet to do:
- Track all recurring commission amounts by month.
- Spreadsheets should be divided up by year (2009, 2010, 2011, etc) with tabs along the bottom for each month.

Issues I’m experiencing:
- How to know when commission amount changes after 12 and 36 months without making a note or setting up the commission spreadsheets 3 years in advance.
- Some monthly fees are only paid by the customer for 8 or 6 months, but commission needs to be paid every month.
- If a customer does not pay or cancels the monthly fee, commission paid out must end.

If it will help, I would be happy to send anyone the spreadsheet I have now and maybe it can just be improved??

Any help or guidance would be appreciated more than you know!


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...