Simulation de remboursement de prêt assurance comprise

[JPPei]

Bonjour,
Je cherche à affiner un tableau de simulation de remboursement de prêt que j’utilise chaque fois qu’un membre de ma famille (ou moi-même) bâtit un projet de prêt avec un banquier.
J’en ai un qui prévoit beaucoup de cas de figure (taux variable, assurance fixe ou proportionnelle, mensualités différentes en cours de période de remboursement…).
Et il en existe des très bons sur le net.
Mais je bute sur une offre un peu spéciale (de HSBC) où l’assurance est de 0.033 % du capital restant dû.
Ce qui est facile si cette assurance s’ajoute à la mensualité de remboursement du prêt (laquelle baisse ainsi chaque mois).
Mais le banquier propose des mensualités identiques pendant toute la durée du prêt, assurance comprise.

Ainsi, chaque mois, le remboursement (toujours identique) comprend :
- le paiement des intérêts (INTPER) ;
- le paiement de l’assurance (qui passe de 130 € dans mon cas à 0 le dernier mois) ;
- le remboursement d’une part de capital.

J’ai essayé d’ajouter l’assurance au capital restant dû, ça ne fonctionne pas.
D’après vous, existe-il un moyen de faire la simulation dans un tableau Excel ?

Vous remerciant par avance pour votre aide
J2P

 

[job75]

Bonjour JPPei, le forum,

Voyez le fichier joint et le tableau d'amortissement.

Les formules sont très simples mais pour qu'elles fonctionnent il faut activer le calcul itératif.

Sinon il y aurait des références circulaires.

Pour le calcul des intérêts mensuels j'ai appliqué le taux annuel divisé par 12.

Bonne journée.

 

[JPPei]

Bonjour job75,
Merci pour ce tableau.
Sauf erreur de compréhension de ma part, dans votre tableau, le montant de la mensualité détermine le nombre de mois de remboursement.
Existe-t-il la possibilité de fixer le nombre de mensualités de remboursement (généralement déterminant le taux du prêt consenti) et qu’Excel renvoie le montant des mensualités pour arriver à 0 à la fin des x mois ?
Car les tableaux proposés par mon banquier ont ces données de départ :
- montant du prêt ;
- taux du prêt annuel ;
- pourcentage du capital restant dû pour l’assurance ;
- nombre de mensualités.
Et ils restituent le montant des mensualités fixes sur toute la période.
Peut-être faut-il utiliser le calcul itératif pour déterminer le montant de la mensualité ?
Mais je n’ai jamais utilisé cette particularité d’Excel.
J2P

 

[job75]

Re,

Je ne vois pas ce que vient faire ici le "pourcentage du capital restant dû pour l’assurance".

Normalement la banque doit vous fournir un tableau d'amortissement, sinon le lui demander.

A+

 

[JPPei]

Re-bonjour et merci pour votre réponse.

Bien entendu toutes les banques fournissent les tableaux et font les simulations. Mais il m’est arrivé très souvent de partir de leur mode de calcul (que, dans le cas présent, je n’arrive pas à simuler sur Excel) afin de leur proposer un montage un peu différent.
C’est ainsi. Je me considère mieux armé si je peux explorer moi-même des alternatives à ce que mon banquier offre.
Dans le cas présent, il s’agit d’un prêt sollicité par ma fille qui doit avoir deux périodes de remboursement : pendant les 7 à 12 premiers mois, avoir des mensualités plus faibles. Puis basculer sur les mensualités restantes pour terminer le remboursement du prêt. Ce sont ces dernières mensualités que je ne sais pas calculer.
Je sais parfaitement réaliser cette simulation lorsque l’assurance est fixe ou qu’elle est variable mais non décomptée de la mensualité. Je suis moins à l’aise pour le faire avec leur mode de calcul.
Aussi, si un participant de ce forum connaissait la réponse, je pourrais aller au bout de ma réflexion.
Sinon, j’appliquerai ce que vous conseillez : ma fille demandera au banquier de procéder à quelques simulations complémentaires.

 

[job75]

Re,

Maintenant pour déterminer les mensualités voyez ce fichier (2) :

- construire le tableau avec le nombre de lignes adéquat et les bonnes formules en dernière ligne

- fixer le nombre maximum d'itérations à 10000

- entrer en C6 (Mensualité) la formule ;

=C6+(DECALER(C10;C5;)>C6)

A+

 

[JPPei]

Re bonjour et surtout merci de vos réponses rapides et bien ciblées.

Je pense que ce que vous préconisez (calcul itératif) est la bonne solution.
J’ai appliqué votre formule (partie gauche de l’onglet « Feuil1 ») mais je dois avoir loupé une marche. C’est ma première utilisation de cette fonction.
En fait, il faut probablement inscrire quelque part que l’on veut que la somme de la colonne « Capital » soit égale au montant du prêt (ou que le chiffre de la colonne « Prêt restant » soit à 0) pour que le calcul itératif connaisse l’objectif à atteindre (voir simulation du banquier à droite).

J2P

NB : dans le cas présent, les références circulaires (onglet « Origine ») n’ont pas lieu d’être car en fait l’assurance est calculée en fonction du capital restant dû du mois précédent. Donc dans l’onglet « Feuil1 », j’ai changé ce lien.

 

[Usine à gaz]

Bonjour à toutes et à tous,
Bonjour JPPei,
Bonjour Job75,

Sans prétention, un fichier en pièce jointe que j'utilisais il y a déjà longtemps LOL
Je l'ai protégé sans mot de passe.
Certainement à améliorer.
Amicalement,
Lionel,

 

[job75]

Re, salut Lionel,

Il faudrait quand même connaître la fonction DECALER, c'est vraiment élémentaire !

Avec des petits montants on peut faire croître la mensualité par pas de 0,01 €, formule en C5 :

=C5+(DECALER(C9;MAX(A:A);)>C5)/100

Par sécurité j'ai fixé le nombre max d'itérations à 32 767 (le maximum autorisé).

PS : vous ne semblez pas avoir compris que le critère qui stoppe les itérations est :

la dernière mensualité ne doit pas être supérieure à la mensualité courante en C5.

Et vous n'avez pas compris non plus que les formules sur la dernière ligne sont différentes !!!

Votre fichier en retour.

A+

 

[JPPei]

Re-bonjour job75,

Brillantissime en effet.

Je vais étudier la fonction decaler. Entre-temps, j’ai trouvé une autre solution via « Valeur cible » qui impose une macro si on veut confier le simulateur à une personne moins branchée.

Mais ça marche bien.

Merci pour votre aide et le tuyau de la fonction « decaler »
J2P

 

[job75]

Re,

Entre-temps, j’ai trouvé une autre solution via « Valeur cible » qui impose une macro si on veut confier le simulateur à une personne moins branchée.

Oui mais c'est nettement plus compliqué qu'un simple calcul itératif.

A+