Fonction excel pour calcul de commissions

Bonjour à tous,


Je suis en train d'essayer de faire un tableau de ventilation excel pour un prévisionnel financier qui me torture pas mal les méninges :


J'ai un premier tableau qui simule un client de référence sur lequel est indiqué un montant de commission qui varie à chaque mois sur 3 ans :


Exemple : un client typique fait 1000€ de CA le 1er mois, j'applique un taux fixe de 10% de commission, j'ai donc un montant de commission de 100€. Le 2e mois, il fait 1200€ de CA toujours avec le même taux de commission, donc 120€ de commission, le 3e mois il fait 1500€ donc 150€ de commission et ainsi de suite sur 3 ans.


Dans le deuxième tableau que je souhaite faire, j'ai un prévisionnel du nombre de nouveaux clients à chaque mois.


Exemple : Janvier 2015 : 3 nouveaux clients, Février 2015 : 2 nouveaux clients, Mars 2015 : 4 nouveaux clients, etc... et ainsi de suite sur 3 ans.


Jusque là tout va bien. C'est ensuite que ça se corse :

Je souhaiterais pouvoir faire une formule dynamique pour évaluer le montant total des commissions à chaque mois, sachant que le montant des commissions varie pour chaque client en fonction du nombre de mois depuis lequel il est client.


Exemple :
En janvier 2015 : 3 nouveaux clients : montant des commissions : 3 x 100 = 300 €
En février 2015 : 2 nouveaux clients + 3 anciens : montant des commissions : 2 x 100 + 3 x 120 = 560 €
En mars 2015 : 4 nouveaux clients + 2 anciens (1mois) + 3 anciens (2mois) : montant des commissions : 4 x 100 + 2 x 120 + 3 x 150 = 1090 €
Etc...


Ma question est donc : est-il possible de trouver une formule qui automatise le calcul mois par mois sur 3 ans en tenant compte de l'ancienneté de chaque client (et donc du montant de commission correspondant) afin d'éviter d'avoir à taper manuellement chaque formule ?



Question bonus : L'idéal serait de rajouter en plus une fonction qui considère qu'un certain pourcentage de clients partent au bout de 12 mois (30% mettons).



Merci d'avance pour votre aide !



Pierre