Commission Tracking

[POSProphet]

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!

Thanks!