Calendrier d'échéances

[Mikayel]

Bonjour à tous,

Je réalise actuellement un tableau de suivi des crédits baux sur des sociétés. Le suivi de ces crédits représente un atout pour le suivi de la trésorerie. J'ai réalisé un tableau qui comporte d'ores et déjà la date de début, la date de fin, le loyer TTC et la périodicité. Par exemple: le crédit bail numéro 1 a commencé le 14 janvier 2006 et se terminera le 14 mai 2011, son loyer TTC est de 10 000 euros et sa périodicité de un mois. Donc tous les mois du 14 janvier 2006 jusqu'au 14 mai 2011, il sera prelevé sur un compte 10 000 euros.

ORGANISME | NUM FOURNISSEUR | NUM CONTRAT | COMPTE D'IMPUTATION | DATE DEBUT (JJ/MM/AA) | DATE FIN (JJ/MM/AA) | LOYER TTC | BANQUE PRELEVEE | PERIODICITE (M/T/A)

J'ai réussi à calculer la charge annuelle par crédit bail, par banque, ainsi que sur plusieurs autres critères. Je bute sur un problème, j'aimerais aller encore plus dans le détail et prévoir un calendrier des prélèvements. Dire donc, au 1 janvier 2011, il sera débité tant, le 2 tant, le 3 tant. Je ne sais pas trop comment m'y prendre.

Il faut que je demande à excel de calculer que le crédit bail qui a commencé le 1 janvier est mensuel et donc doit me renvoyer la même somme sur le 1 février, le 1 mars, jusqu'à la fin...

Si vous avez compris et que vous savez comment m'aider, merci d'avance!

 

[Mikayel]

Re : Calendrier d'échéances

Rebonjour

Donc voici mon fichier!

 

[hoerwind]

Re : Calendrier d'échéances

Bonjour,

Le fichier joint a été supprimé ???

Quoiqu'en soit la raison, essaie la formule suivante en 'Tableau désiré'!B5 :
=SOMMEPROD((Suivi!$F$4:$F$56<=$A5)*(Suivi!$G$4:$G$56>=$A5)*(Suivi!$I$4:$I$56=B$4)*Suivi!$H$4:$H$56)
formule à copier vers le bas et vers la droite.

Edition :
Un nouveau fichier est arrivé !
La formule proposée ne tient pas compte de Suivi!J:J (période)
Je suppose que dans cette colonne M vaut mois et T trimestre, et que les valeurs en colonne colonne H (loyer) sont données par M et par T.
Je revois la formule en conséquence.

 

[hoerwind]

Re : Calendrier d'échéances

Re,

C'est corrigé sous la pièce jointe.
Vois si cela te convient.

 

[Mikayel]

Re : Calendrier d'échéances

Impressionant!

Merci infiniment!

 

[Mikayel]

Re : Calendrier d'échéances

En fait, il y a encore un petit problème

Toutes les échéances (trimestrielles) sont callées sur des trimestres civils (par le MOD(MOIS(jan09);3)) cependant, l'échéancier doit renvoyer le vrai découpage des trimestres.

Par exemple un crédit qui commence le 5 février n'a pas un découpage civil de ses trimestres, il aura le découpage:

5 février, 5 mai, 5 août, 5 novembre

Je ne vois pas trop comment modifier SOMMEPROD((Suivi!$F$4:$F$100<=$A5)*(Suivi!$G$4:$G$100>=$A5)*(Suivi!$I$4:$I$100=B$4)*(Suivi!$J$4:$J$100="T")*Suivi!$H$4:$H$100) pour lui dire en français "Si le premier mois du trimestre est février alors tu dois renvoyer la somme de février mais pas sur mars ni sur avril et reprendre la somme en mai et faire ce calcul jusqu'à la dernière échéance trimestrielle".

Merci...

 

[hoerwind]

Re : Calendrier d'échéances

Bonjour,

Pour le lui en français, il aurait fallu préciser que par "trimestre" il ne fallait pas entendre "échéance trimestrielle", mais bien "échéance tri-mensuelle".

Mais cela pose questions !
Si pour les échéances mensuelles le jour de la date de début est toujours égal au jour de la date de fin, ce n'est pas le cas pour les échéances tri-mensuelles.
Que faut-il faire, calculer le montant à la proportionnelle ?
De plus, pour les délais d'échéances tri-mensuelles, le nombre de mois entre les dates de début et de fin n'est pas toujours égal à un multiple de 3.

Par exemple Suivi!F6:G6 : 21/10/05 et 31/01/08
D'après ce que j'ai compris la 1ère échéance tombe le 31/12/05, (01/10/2005 + 3 mois - 1 jour);la suivante le 31/03/06, sauf si ce sont des échéances anticipatives.
Alors ce serait le 01/10/2005, mais comment peut-on payer avant le début du contrat ?
Il faudrait donc imputer le montant au mois d'octobre 2005, la suivante au mois de janvier 2005, ..., à moins que ce soit au mois de décembre 2005 ?
Mais quel montant faut-il imputer au mois de janvier 2008, la dernière tranche ne comportant qu'un mois ?

A te lire avec un complément d'explication.

 

[Mikayel]

Re : Calendrier d'échéances

Je ne connaissais même pas ce mot!

En fait dans ton exemple, le premier prélèvement de loyer sera le 21 octobre, le second le 21 janvier, le troisième le 21 avril, etc.

La date de fin doit être fausse ou si ça ne correspond pas, il ne faut pas le prendre en compte et donc imputer au dernier mois le loyer (normalement c'est très cadré: investissement global / nombre échéances = loyers)

Merci beaucoup

 

[hoerwind]

Re : Calendrier d'échéances

Re,

La nouvelle question ne pose pas grand problème, pour autant que les données fournies puissent être exploitées logiquement.

Exemple :
Pour les dates de fin de contrat il y a deux versions différentes, soit c'est le même jour que la date de début (ligne 4, toutes deux le 1), soit c'est le dernier jour du mois précédent (ligne 11, 1 et 31).

Ajouter à cela des périodes T qui ne sont pas divisibles par 3, et dont, si j'ai compris, il ne faut pas tenir compte du reliquat de la division, ne simplifie pas le calcul.

Deux solutions :
Ou bien les dates sont corrigées avant d'être transmises,
Ou bien on crée deux colonnes supplémentaires dans lesquelles les dates seront corrigées suivant des conditions qui restent à établir.

Si tu pouvais donner, dans une colonne supplémentaire et ce ligne par ligne, le nombre de périodes calculées manuellement, ce serait plus facile pour établir les contraintes des calculs.