calcul d'une date et d'une mensualité

[christophe697569]

Bonjour à tous et merci de m'aider.

Je souhiate en fait créer un contrat d'apurement sur une feuille Excel. Beaucoup de blabla sur les premières lignes puis ajouter dans ce genre de contrat un tableau de 5 colonnes et 2 lignes.


1ere colonne : somme due que j'indique (ex. 530 euros)
2eme colonne : mensualité que j'indique (ex. 50)
3eme colonne : date de la 1ere échéance que j'indique (ex. 01/01/06).

Ensuite, je souhaite que le logiciel calcule de lui même :
4eme colonne : montant du dernier versement (soit 30 dans mon exemple)
5eme colonne : date de dernier versement (soit le 01/11/06 dans m'exemple).

En supposant que les titres des colonnes sont sur A1 à E1 et les valeurs entrées sont sur A2 à C2, j'ai mis en D2 la formule suivante :

=SI(MOD(A2;B2)=0;B2;MOD(A2;B2))

Cela semble fonctionner mais il y a sûrement plus simple. Si vous pouviez améliorer cette formule, je suis preneur.

Par contre je suis bloqué pour la formule dans E2.
Il faut que j'ajoute le nombre de mois qui correspond au nombre de mensualités mais si la date de la dernière mensualité tombe sur l'année suivante il faut que çà marche. De plus si la dernière mensualité est inférieure aux autres çà me pose un problème.

J'espère avoir été clair. Pour améliorer la compréhension, je vous donne quelques exemples auxquels j'aimerais arriver.

500 100 01/11/06 100 01/03/07

300 70 01/12/06 20 01/04/07

120 18 01/08/06 12 01/02/07

Merci pour votre aide !

@+
Christophe

 

[Monique]

Re : calcul d'une date et d'une mensualité

Bonjour,

Un essai, avec :
nb de mensualités
puis la date de la dernière échéance
puis les 2 formules ensemble

Nb de mensualités en E2
=ENT(A2/B2)+(MOD(A2;B2)>0)

Date de la dernière échéance en F2
=DATE(ANNEE(C2);MOIS(C2)+E2-1;1)

Les deux ensemble :
=DATE(ANNEE(C2);MOIS(C2)+ENT(A2/B2)+(MOD(A2;B2)>0);1)

 

[christophe697569]

Re : calcul d'une date et d'une mensualité

Bonjour

Tout d'abord merci car je pense être prêt de mon objectif grâce à vous.
Vous l'avez bien compris, je veux une formule qui regroupe tout dans E2.

J'ai pris votre formule mais je n'obtiens pas toujours le bon résultat.

Par exemple, si j'ai :

400 90 01/11/06 je devrais avoir 40 01/03/07

5 mensualités dont les 4 premières font 90 et la dernière 40
1ère mensualité le 01/11/06
2ème 01/12/06
3ème 01/01/07
4ème 01/02/07
5ème 01/03/07

Avec votre formule j'obtiens en E2 la date 01/04/07

Il y a un mois de décalage.

J'ai essayé avec :

400 100 01/04/06 je devrais avoir 100 et 01/07/06

Avec votre formule j'obtiens en E2 la date 01/08/06

Il y a toujours un mois de décalage.

Le problème se situe en fait au niveau quotient à savoir s'il est entier ou pas et aussi du fait que la première mensualité se fait tout de suite pas un mois plus tard.

Merci de continuer à m'aider ! Je cherche de mon côté et je vous tiens au courant si je trouve.

@+
Christophe

 

[Monique]

Re : calcul d'une date et d'une mensualité

Re,

Il y a un -1 qui s'est perdu en cours de route quand j'ai mis les deux formules ensemble :
=DATE(ANNEE(C2);MOIS(C2)+ENT(A2/B2)+(MOD(A2;B2)>0)-1;1)

 

[christophe697569]

Re : calcul d'une date et d'une mensualité

Bonjour Monique

En attendant votre réponse et son -1 magique j'avais bidouillé cette formule :

=SI(MOD(A2;B2)>0;DATE(ANNEE(C2);MOIS(C2)+ENT(A2/B2);JOUR(C2));DATE(ANNEE(C2);MOIS(C2)+ENT(A2/B2)-1;JOUR(C2)))

Elle semble fonctionner.

Votre dernière formule fonctionne aussi tout en étant plus simple.

Pouvez-vous me dire si ma formule est correcte ?

J'ai remplacé votre 1 pour le jour par jour(c2) car j'ai dû mal m'exprimer, le jour n'est pas systématiquement le 1er du mois. Avec mes exemples au 1er je vous ai trompée.

De toutes façons, merci 1000 fois !

@+
Christophe

 

[jafa]

Re : calcul d'une date et d'une mensualité

bonjour le forum

Juste une petite intervention dans un domaine qui est mien.

la première date d'échéance est comptée doublement puisqu'elle porte le N° 1 si on lui ajoute le nombre des échéances on a focement une en plus.

La sollution serait donc de réduire dans la formule (Mois(xx)-1)+ ent(mod ect..

j'ai procédé à plusieurs essais et c'est bon..

avec mes salutations

jafa qui vous dit à +

 

[Monique]

Re : calcul d'une date et d'une mensualité

Re,

La condition n'est pas utile,
elle est déjà ici :
+(MOD(A2;B2)>0)
Cette partie renvoie VRAI ou FAUX, donc + 1 ou 0
Elle donne 1 si (MOD(A2;B2)>0) et 0 sinon

Celle-ci doit aller :
=DATE(ANNEE(C2);MOIS(C2)+ENT(A2/B2)+(MOD(A2;B2)>0)-1;JOUR(C2))

 

[christophe697569]

Re : calcul d'une date et d'une mensualité

Bonjour

C'est nickel !

Merci beaucoup à tous et à toutes et bonne continuation !

@+
Christophe