I'm looking to model on a quarterly basis the revenue related to a client.

Basically, if 1 client comes in Jan '14, I want to model Revenue with 5% growth indefinitely on a quarterly basis. That's the relatively easy part, separating new revenue from recurring.

The hard part for me that I can't do the corresponding commissions, which change from year to year. Yr 1=25%, Yr 2=15%, Yr 3=10%, Yr 4=5%, Yr5 on=2.5%. That one client that comes on 1/1/14, generates a quarterly commission based on the quarterly value of the assets at the end of the quarter. Each quarter a commission is paid.

I need to show that 1 client (and ultimately be able to layer multiple clients into the model over periods of time) generating revenue and a corresponding commission based on the schedule above.

Example -
assets = 100,000
fee = 2.0% annually, .5% / qtr
asset grows at 5% annually
generates revenue of X for each quarter

Then I need to calculate the revenue each quarter, where 1st year revenue from that client is calculated at 25%, 2nd year revenue from that client is at 15%, etc. based on the schedule above

If it were 1 client, it would be easy - the problem I'm having is then having multiple new clients added to the model, each layering on year 1, 2, 3, etc., revenues and corresponding commissions.

As an example:
use above information
Q1 - 1 client, Q2-1 client, Q3 - 2 clients
I need to show the quarterly new revenue, recurring revenue, cumulative revenue, then the commissions total for each quarter
client 1 would have 4 quarters of Yr 1 Commission, 4Q of Year 2 commission, etc.
client 2 would have the same, but start in Q2
client 3, etc.

The model needs to also accommodate growth of the assets quarterly, which would increase the revenue and corresponding commissions. I feel like it's almost an array where 1 event (client onboarding) creates an array of revenue at different growth rates, that then also creates a corresponding commission with commission rates that change every year for 5 years, then remains constant.

Sorry for the details, but hoping it helps.

Thanks!