Formule ? ou Code VBA ?

Bonjour,

J’ai créé une feuille de calcul que je ne contrôle bientôt plus

L’idée est qu’une personne qui doit rembourser un montant (en C1) paie tous les mois des acomptes identiques (en C2), sauf bien entendu le dernier acompte pour le solde de la dette. Ce dernier acompte doit cependant obligatoirement être plus bas que les autres acomptes.

Certaines fois, les intérêts ne sont dus qu’après un certain nombre de mois (au maximum 12 mois sans intérêt, voir B4 et de G47 à G58).

Mon problème vient que nous devons saisir le remboursement échelonné du montant principal dû et des intérêts séparément (dans un programme de caisse de pension, sous la forme de retenue de rente) mais que la personne concernée ne doit pas nécessairement connaitre cette séparation interne.

Comme les intérêts sont immédiatement déduits sous la forme de retenue de rente, le créancier paie parfois des intérêts qui ne sont pas encore dus puis c’est compensé vers la fin de la période de remboursement.

En l’état actuel des choses, j’ai calculé sans trop de problème la partie que nous communiquons au créancier (Base pour le plan de remboursement, de B45 à J288, plan de remboursement prévu sur un maximum de 20 ans. Voir aussi la feuille « Français » communiquée au créancier).

Le problème se corse lorsque j’essaie de trouver en K47 une formule qui permette de déterminer quelle est la hauteur du montant de la retenue « montant principal » à inscrire dans notre programme séparé afin que : premièrement le dernier acompte calculé ensuite automatiquement vers le bas (dans mon exemple en K77) soit plus bas que les acomptes précédents et deuxièmement afin que les déductions de l’intérêt (colonne L) suivent la même logique, c'est-à-dire : x acomptes de y francs puis un dernier acompte plus bas que les précédents mais bien entendu pas négatif.

Dans mon exemple, j’ai déterminé par tâtonnement que ce montant en K47 devrait être exactement entre 493.45 et 493.60 (arrondis suisses à 5 centimes). Pensez-vous qu’il existe une formule qui permettre de déterminer automatiquement ce montant ? Faut-il tenir compte de la dette principale uniquement ou aussi du total des intérêts dus (H17) ? Faut-il tenir compte de la durée en mois pendant lesquels un intérêt est dû ? De la durée en mois pendant lesquels la dette est remboursée ? De la durée en mois pendant laquelle l’intérêt n’est pas encore dû ? Faut-il tenir compte du taux d’intérêt ?

J’ai tourné toutes les données ci-dessus dans tous les sens, mais je n’arrive pas à un résultat satisfaisant.

Pourriez-vous m’aider ?

Avec mes cordiales salutations.

Re-bonjour,

Comme je ne reçois pas de réponse à ma première question, je me demande si finalement la solution ne viendrait pas d’un code VBA.

Voici – formulé d’une autre manière, avec un autre exemple à la clef et avec des conditions légèrement modifiées afin que ce soit, je l’espère, plus simple – mon problème :

Selon des données introduites de C1 à C5, un plan de remboursement est préparé en B45:J288 et peut être communiqué au créancier par la feuille « Français ».

En interne, on doit cependant séparé l’intérêt du « montant principal » dû. Je recherche donc un chiffre en K47 qui permet de séparer ces deux données avec les conditions suivantes :

- le dernier acompte « montant principal » ainsi calculé dans la colonne K (ce dernier acompte est repérable si nécessaire par la colonne C, dans laquelle apparaît un 2 ou un 3 lorsqu’il s’agit du dernier acompte, dans mon nouvel exemple en K79) doit être positif mais inférieur aux autres acomptes.

- CONDITION MODIFIEE : le dernier acompte d’intérêt, calculé dans la colonne L, au même niveau que le dernier acompte « montant principal », ne devrait pas être négatif ni dépasser de 3 ou 4 fois les premiers acomptes d’intérêt (Dans mon nouvel exemple, le dernier acompte d’intérêt est de 62.50 pour des acomptes normaux de 15.00, ce serait acceptable. Si on indique 1084.00 en K47, le dernier acompte d’intérêt serait de 30.50, se serait mieux).

- NOUVELLE CONDITION A CAUSE DE LA CONDITION MODIFIEE CI-DESSUS : le total dans la colonne M du dernier acompte « montant principal » et du dernier acompte d’intérêt ne doit pas dépasser le total des acomptes précédents.

- NOUVELLE CONDITION PUISQUE CONDITION MODIFIEE CI-DESSUS : si le chiffre trouvé en K47 pouvait être un chiffre rond (sans centime, sinon arrondi à 0.05) ce serait mieux.

Pour l’instant je procède par tâtonnement afin de trouver les bons chiffres à mettre en K47. Je commence avec un montant légèrement plus bas que celui prévu en C2 et j’inscris des chiffres de plus en plus bas. Est-ce de cette manière que devrait procéder un code VBA ?

https://www.excel-pratique.com/~files/doc/Plan_de_remboursement.zip

Si vous pouviez m’aider, je vous garanti que vous me sortiriez une sacré épine du pied !

Bon week-end.

Bonjour,

Je pense que l'absence de réponse est, pour une large part, due à la longueur de ton exposé qui effraie quelque peu.

J'ai regardé tes deux posts, tes fichiers, mais j'ai renoncé.

Le plus efficace serait de décomposer ton problème en sous-problèmes et de nous les exposer un par un, après avoir eu une solution pour chacun.

Donc, avec une question à la fois, on devrait finir par tout résoudre.

A te (re)lire

@+

Salut Thibo,

Merci d'avoir pris la peine de m'écrire.

En fait je ne peux pas décomposer mes problèmes, car il n'y en a qu'un seul. Mais comme il est placé assez loin dans mon travail et que la première partie est absolument nécessaire, je ne peux pas isoler la partie concernée.

J'esprère que quelqu'un - toi peut-être - essaie malgré tout d'aller voir cette fameuse cellule K47 ; je pense alors que le problème deviendrait beaucoup plus clair.

Tout mon bla-bla n'est là que pour préciser la situation.

Encore un grand merci pour ton intervention.

Bonnes salutations.

re,

Sans avoir compris grand chose, un essai avec cette formule en K47 :

=ARRONDI(E47-SOMME($G$46:$G$288)/NB.SI($F$47:$F$288;">"&0);0)

Je te laisse tester

@+

Salut Thibo

Merci d'avoir cherché une solution.

Malheureusement, comme pour plusieurs de mes essais indiqués dans mon premier message, tu t'approches de la solution mais ça ne joue pas absolument.

La dernière retenue d'intérêt est effectivement négative (-1.50 en L79).

Voici le fichier avec ta formule :

https://www.excel-pratique.com/~files/doc/Plan_de_remboursement_formule_Thibo.zip

Si je modifie le montant de la dette (par exemple 35'000.00 en C1) ce dernier acompte négatif augmente encore.

Peut-être une précision : En tâtonnant, je trouve toujours une solution acceptable. Il doit donc bien y avoir une solution pour automatiser cette recherche du montant correct en K47.

Merci encore pour ta peine et bonnes salutations.

re,

Dans ce cas, une toute petite correction :

=ARRONDI.SUP(E47-SOMME($G$46:$G$288)/NB.SI($F$47:$F$288;">"&0);0)

Dans cette formule, j'arrondis à zéro décimale.

Si tu mets 1 ou 2 décimales, le résultat de la dernière ligne sera plus proche de zéro.

Je te laisse tester

@+

Thibo,

Ta formule fonctionne à merveille. Merci infiniment pour ton aide.

J'ai par contre découvert, lors de mes essais, que parfois il ne faudrait pas seulement arrondir à deux chiffres après la virgule ! Dans l'exemple ci-joint, j'ai dû arrondir à 3 chiffres après la virgule afin que les derniers acomptes ne soient pas négatifs.

https://www.excel-pratique.com/~files/doc/A2u64Plan_de_remboursement.zip

Un tel arrondi n'est bien entendu pas possible dans la pratique, mais ce n'est qu'un petit problème. Les cas qui arriveront seront probablement extrêmement rares et je vais simplement placer un contrôle afin que, dans un tel cas, on choisisse simplement un acompte légèrement différent.

Tu m'as permis de faire un pas de géant en avant (à condition que je ne découvre pas de nouveaux problèmes à l'utilisation) et je t'en suis extrêmement reconnaissant.

Bonjour,

J’avais mis un peu vite ce fil sur « Résolu ».

Thibo m’avais proposé une formule en K45 pour résoudre mon problème. Malheureusement cette formule ne fonctionne pas toujours d’une manière optimale. Dans l’exemple fourni, il faudrait utiliser cette formule avec un arrondi au franc afin d’obtenir un résultat utilisable, alors que dans la plupart des cas il faut l’utiliser avec un arrondi à 5 centimes, voire à 10 ou 50 centimes (un arrondi au centime n’étant pas utilisable dans la pratique).

https://www.excel-pratique.com/~files/doc/3lDNuPlan_de_remboursement.zip

C’est pourquoi je pense qu’une solution avec un code VBA - qui fonctionnerait pratiquement comme mes tâtonnements manuels - fournirait plus de résultats utilisables et présenterait d’autres avantages.

Je m’explique : Comme des résultats arrondis au franc seraient préférables, il faudrait tout d’abord qu’un code VBA cherche si un tel résultat arrondi au franc était utilisable.

Ensuite, si aucun résultat arrondi au franc n’est utilisable, il faudrait chercher un résultat arrondi à 5 centimes.

Est-ce que la recherche d’un résultat arrondi à 10 ou 50 centimes serait aussi nécessaire afin d’augmenter les chances d’obtenir un résultat utilisable ?

Enfin si aucun résultat n’est utilisable, ce code pourrait quand même inscrire l’un des résultats cherchés – mais malheureusement non utilisables - et un message d’impossibilité apparaît en D2 : « Choisir un autre acompte ».

Il est indiqué en D34, D35 et D36 si les résultats sont utilisables ou non ; cellules dont dépend le message en D2 indiqué ci-dessus.

Pour démarrer la recherche par tâtonnement, j’utilise un montant légèrement inférieur au premier acompte choisi en C1, mais le résultat ne peut jamais être très inférieur à ce montant. Je dirais alors qu’en recherchant par une macro toutes les possibilités entre 100 % et 75 % de cette valeur, on doit trouver un résultat sinon c’est qu’il n’en existe pas.

Encore une précision : j'avais indiqué une fois plus vite qu'il y avait toujours un résultat utilisable pour n'importe quelle combinaison en C1-C5, mais ce n'est effectivement pas vrai.

J’espère de tout cœur que l’un d’entre vous puisse m’aider encore.

Bonne journée à tous.

Bonjour à tous,

Ne peux-tu pas faire un petit fichier avec seulement quelques cellules et formules

qui te posent problème ?

Quelques annotations devraient suffire à expliquer.

Amicalement

Claude.

Salut Dubois,

Merci pour ton conseil. J'ai essayé de modifier mon fichier afin qu'il soit plus facile à consulter. J'y ai également inscrit directement quelques informations.

J'espère que ce soit mieux ainsi.

https://www.excel-pratique.com/~files/doc/gayuGPlan_de_remboursement.zip

Avec mes cordiales salutations.

Bonsoir à tous,

C'est un peu bricolé ! regarde

https://www.excel-pratique.com/~files/doc/Plan_de_remboursement.xlsm

Je ne suis pas encore équipé pour ziper au format XLS

Sub Tatonne()
'par Claude Dubois le 01/03/09
    Application.ScreenUpdating = False
    '********** 1er tour *******
    Range("k45") = Range("L37")
        Do While Range("k45") - Range("r36") > 0
            Range("k45") = Range("k45") - 10
        Loop
    Do While Range("k45") - Range("r36") < 0
        Range("k45") = Range("k45") + 1
    Loop
    '********** 2ème tour *******
        Do While Range("k45") - Range("r36") > 0
            Range("k45") = Range("k45") - 0.5
        Loop
    Do While Range("k45") - Range("r36") < 0
        Range("k45") = Range("k45") + 0.05
    Loop
End Sub

amicalement

Claude.

Dubois,

J'ai essayé d'ouvrir ton fichier joint, mais je n'obtiens que des codes bizarres de ce genre :

  <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
- <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="bin" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings" /> 

J'ai aussi essayé d'introduire ton code dans un module de ma feuille mais, lorsque je lance la macro par F5, la seule chose qui se passe est que la cellule K45 est vidée.

Que fais-je de faux ??

Bonne soirée.

re,

Non c'est moi qui n'est pas encore familiarisé avec mon nouveau matos !

essaye celui-là

https://www.excel-pratique.com/~files/doc/cJRstPlan_de_remboursement.xlsm

de toute façon, çà ne va pas, j'ai essayé avec un acompte de 2000, çà met à 27 mensualités et le résultat n'est pas bon !

il faut approfondir et je dois dire que je n'ai pas trop la tête à çà pour le moment.

Mais le principe de tatonnage est là !

Je regarde et dis moi pour cette histoire de fichier, format ???

Claude.

Re-salut Claude,

Excuse-moi, je viens de découvrir que tu as aussi un prénom

Je n'arrive toujours pas à ouvrir ton fichier. Losque je clique sur le lien, un autre onglet Explorer s'ouvre et je dois choisir entre plusieurs fichiers, mais aucun ne m'ouvre une feuille de calcul Excel, bien que j'ai également Excel 2007.

Avec la formule de Thibo, le dernier acompte d'intérêt était toujours plus bas que les précédents. Je n'ai donc pas contrôlé cette condition dans mon précédent fichier. Je joins alors encore un nouveau fichier où cette condition complémentaire à été rajoutée en Q37, de même que ton code ci-dessus.

https://www.excel-pratique.com/~files/doc/QP3FTPlan_de_remboursement.zip

Merci encore pour ta peine et bonne soirée.

re,

essai au format sans macro.

Ajoute un module et copie le code,

https://www.excel-pratique.com/~files/doc/snoeSPlan_de_remboursement2.xlsx

je ne m'occupe pas de ton dernier fichier. (pour l'instant)

Claude.

Salut Claude,

Merci de te donner la peine de t'occuper de ma demande.

Je n'arrive malheureusement toujours pas à ouvrir ton fichier. J'ai toujours les mêmes codes bizarres mais pas de fichier Excel.

Comme tu m'indiques que tu ne t'occupes plus de ce problème pour l'instant, j'espère de tout coeur que d'autres visiteurs s'intéssent à trouver une solution à mon soucis

Bonne soirée à vous tous.

Bonsoir,

La macro fonctionne en relation avec la formule suivante à mettre en R36

=RECHERCHEV(Q40;J46:K286;2;FAUX)

çà pas être facile sur le forum ! si on ne pas ouvrir mes fichiers.

En fait : cette macro fait comme tu le demandais, elle augmente progressivement K45 ,

Claude.

Salut Claude,

Selon tes informations, j'ai essayé d'introduire ta formule en R36 et j'ai placé un bouton juste en dessous afin de déclancher ta macro (alors qu'il faudrait plutôt qu'elle se déclanche automatiquement lorsque l'on modifie l'une des cellules de L36 à L40, mais ça je ne sais pas le faire non plus).

Malheureusement ça ne fonctionne pas vraiment. Le dernier acompte d'intérêt est souvent négatif, l'une des conditions en O36:Q38 n'est ainsi pratiquement jamais remplie.

https://www.excel-pratique.com/~files/doc/ByitRPlan_de_remboursement.zip

Peux-tu encore m'aider, j'aurais tellement besoin de ce code ?

Bonne soirée.

Re-bonsoir Claude, bonsoir tout le monde,

En fait je viens de me rendre compte, selon l'extrait ci-dessous, que tu m'as peut-être mal compris.

dubois a écrit :

En fait : cette macro fait comme tu le demandais, elle augmente progressivement K45 ,

Claude.

J'avais indiqué dans mes messages précédents que je recherchais manuellement la bonne valeur par tâtonnement en diminuant progressivement les valeurs recherchées à partir d'une certaine valeur de base, non pas en les augmentant. Est-ce pour cela que ça ne fonctionne pas ?