Simulation de crédits avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Emprunts et investissements avec Excel

Dans cette mise en pratique Excel, nous revenons sur les fonctions financières et notamment la fonction VPM qui permet de calculer le montant des remboursements périodiques d'un prêt. La société CAP'ECO souhaite réaliser un investissement important. Pour cela, elle a besoin d'un financement et fait appel à plusieurs banques afin de comparer les offres. L'objectif à l'issue est de faire ressortir de façon évidente, la société bancaire la plus favorable pour financer cet investissement. Ces conclusions dépendront du montant des mensualités à rembourser en tenant compte, d'une période fixe et de taux d'intérêts différents selon les organismes bancaires.



Comme l'illustre la capture ci-dessus, les conditions de quatre banques sont comparées dans un tableau de bord. De ces conditions se déduisent des calculs de coûts et notamment de mensualités fixes. De ces calculs se déduisent des conclusions qui permettront d'orienter la société vers l'organisme bancaire à privilégier.

• Télécharger le classeur Excel gestion-prets-investissements.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Le tableau est décomposé en deux sections. La première section concerne les lignes 6 à 10. La seconde section concerne les lignes 11 à 15. Dans la première section sont listées les conditions des banques et de l'emprunt comme le montant, le taux d'intérêt et la durée du remboursement. La seconde section doit accueillir les calculs. Elle doit donc être utilisée notamment pour calculer le montant des mensualités et du remboursement total afin de pouvoir comparer les banques entre elles.

Calcul du montant net emprunté
Le montant net emprunté est la différence entre le montant principal de l'emprunt et les frais de dossier. Le calcul consiste donc en une simple soustraction entre les deux valeurs.

• Sélectionner les cellules des montants nets à calculer, soit la plage C11:F11,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la première cellule du montant principal, soit C6,
• Taper le symbole moins (-) du pavé numérique, pour la soustraction,
• Sélectionner la première cellule des frais de dossier, soit C10,
• Valider le calcul par CTRL + Entrée afin de le répliquer sur les autres cellules,

Cette combinaison de touches sur une plage de cellules, permet en effet de répliquer la formule sur l'ensemble des cellules présélectionnées. Cette technique professionnelle permet d'éviter de tirer la poignée et de gagner du temps.

La ligne suivante propose de faire ressortir l'indication sur la valeur mensuelle de l'assurance, en fonction du nombre de mois défini par la période de remboursement. Il s'agit donc de diviser le coût total de l'assurance par le nombre de mensualités. Nous allons exploiter de nouveau la technique la plus efficace pour répliquer les calculs de formules Excel.

• Sélectionner toutes les cellules du coût mensuel de l'assurance, soit la plage C12:F12,
• Taper le symbole = du pavé numérique pour débuter le calcul,
• Sélectionner le montant total de l'assurance pour la première banque, soit la cellule C9,
• Enfoncer le slash (/) du pavé numérique pour la division,
• Sélectionner la durée de remboursement pour la première banque, soit la cellule C7,
• Valider le calcul par CTRL + Entrée pour le répliquer sur la plage présélectionnée,

Les références des cellules impliquées dans le calcul, se déplacent fort logiquement en suivant le mouvement de la formule répliquée.



Nous constatons que les résultats varient assez sensiblement d'une banque à une autre. C'est la combinaison de toutes les conditions et de tous les calculs intermédiaires qui permettront, une fois consolidés, de livrer les conclusions pertinentes sur le choix de la banque à privilégier.

Mensualité constante de remboursement d'un emprunt
Le calcul des mensualités qui permettent de rembourser le montant d'un crédit en fonction d'un taux d'intérêt et d'une période de remboursement, peut se faire simplement dans Excel grâce à la fonction financière VPM. Sa syntaxe est la suivante : =-VPM(taux_mensuel ; nombre_mensualites ;montant_emprunte).

Comme la fonction Excel VPM considère qu'elle part du montant total emprunté à rembourser jusqu'à honorer la totalité, elle renvoie une valeur négative. C'est pourquoi nous la préfixons du symbole moins (-) afin de transformer la valeur négative en valeur positive. Nous pourrions aussi l'imbriquer dans la fonction ABS qui retourne la valeur absolue d'un nombre. Le taux mensuel correspond au taux d'intérêt négocié avec la banque et ramené au mois. En général, on divise ce nombre par 12. Ici, le pourcentage est déjà indiqué en taux d'intérêt mensuel. Le nombre de mensualités correspond à la période totale du remboursement définie en nombre de mois. Et enfin, le montant emprunté correspond à la valeur du crédit demandé.

• Sélectionner toutes les cellules des mensualités constantes à calculer, soit la plage C13:F13,
• Taper le symbole = pour débuter le calcul suivi du symbole moins (-),
• Saisir le nom de la fonction financière suivi d'une parenthèse ouvrante, soit VPM(,
• Sélectionner le taux d'intérêt mensuel de la première banque, soit la cellule C8,
• Taper un point-virgule pour passer à l'argument du nombre de remboursements,
• Sélectionner la période définie pour la première banque, soit la cellule C7,
• Taper un point-virgule pour passer à l'argument du montant total emprunté,
• Sélectionner le montant principal de l'emprunt pour la banque 1, soit la cellule C6,
• Fermer la parenthèse de la fonction VPM,
• Puis, valider la formule par CTRL + Entrée pour la répliquer sur toutes les cellules,

Les mensualités respectives aux banques s'affichent dans les colonnes correspondantes. Nous notons à ce stade que la banque 2 semble être la plus favorable, avec un montant mensuel de remboursement inférieur à celui des autres. Mais ce calcul ne considère pas toutes les données, comme les frais de dossier ou le montant de l'assurance, qui peuvent varier sensiblement d'un organisme bancaire à un autre.

La mensualité totale consiste à ajouter le coût mensuel de l'assurance à la mensualité constante de remboursement du crédit. Il s'agit donc d'une simple addition.

• Sélectionner toutes les cellules du calcul de la mensualité totale, soit la plage C14:F14,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la mensualité constante pour la première banque, soit la cellule C13,
• Taper le symbole + du pavé numérique pour l'addition,
• Sélectionner le coût mensuel de l'assurance pour la banque 1, soit la cellule C12,
• Valider le calcul par CTRL + Entrée pour le répliquer sur toutes les banques,

Calcul du coût total d'un crédit
Les résultats précédents ont démontré que la banque 2 restait toujours la plus compétitive sur la mensualité totale de remboursement du prêt. Cependant, en intégrant les frais mensuels relatifs à l'assurance, l'écart s'est réduit avec les autres organismes bancaires. Pour réaliser une étude comparative pertinente des conditions accordées par les banques, il est important de livrer le résultat final du coût total du crédit. Ce dernier englobe en effet les frais de dossier qui peuvent fortement varier d'une banque à une autre.

Le calcul consiste donc à multiplier la mensualité totale par le nombre de versements à honorer puis à y ajouter, le montant des frais de dossier. Pour obtenir un résultat propre, nous choisissons d'employer la fonction Excel arrondi qui permettra de limiter le résultat à deux chiffres après la virgule. Sa syntaxe est la suivante : =Arrondi(nombre_a_arrondir ;nombre_decimales).

• Sélectionner les cellules du coût total du crédit, soit la plage C15:F15,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction pour limiter les décimales suivi d'une parenthèse ouvrante, soit Arrondi(,
• Sélectionner la mensualité totale pour la première banque, soit la cellule C14,
• Taper l'étoile du pavé numérique (*) pour la multiplication,
• Sélectionner la durée du remboursement pour la banque 1, soit la cellule C7,
• Taper le symbole + du pavé numérique pour l'addition,
• Sélectionner les frais de dossier pour la première banque, soit la cellule C10,
• Taper un point-virgule pour passer à l'argument du nombre de décimales,
• Saisir le chiffre 2 et fermer la parenthèse de la fonction Arrondi,
• Valider la formule par CTRL + Entrée pour la répliquer sur toute la ligne,

A la lecture des résultats, la banque 2 n'est pas la plus favorable. Les frais de dossier ont changé la donne. Tous les paramètres sont donc importants à considérer pour réaliser des simulations d'emprunts et monter des études comparatives avant de prendre une décision. Justement, pour que ce tableau soit totalement automatisé et facilement exploitable pour d'autres simulations de crédits, nous allons livrer une conclusion textuelle sans équivoque dans la dernière ligne. L'objectif est de faire ressortir l'organisme bancaire à privilégier sans devoir étudier tous les résultats de calculs intermédiaires.

Nous allons donc exploiter la fonction Excel conditionnelle Si afin de poser un raisonnement. Si le coût total du crédit est le plus petit des quatre, alors c'est la banque correspondante qui doit être élue. La fonction statistique Min d'Excel permet justement d'extraire la plus petite des valeurs d'une plage de cellules. Il suffit de comparer ce résultat avec chacun des coûts pour chacune des banques.

• Sélectionner la plage de cellules C16 à F16,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit Si(,
• Sélectionner le premier coût total à comparer aux autres, soit la cellule C15,
• Taper le symbole = pour la comparaison du critère de la fonction Si,
• Saisir le nom de la fonction pour extraire le plus petit, suivi d'une parenthèse ouvrante, soit Min(,
• Sélectionner tous les coûts totaux, soit la plage de cellules C15:F15,
• Enfoncer la touche F4 du clavier pour figer cette plage,

En effet, lorsque nous répliquerons ce calcul, les références de la cellule du coût total se déplaceront avec la formule répliquée pour comparer chacune des banques. Mais ces montants respectifs, doivent toujours être comparés aux valeurs de la plage de cellules des coûts totaux qui eux, ne doivent donc pas bouger. C'est ainsi que la touche F4 du clavier en encadrant les références des cellules de dollars empêche ces dernières de se déplacer. La plage est figée.

• Fermer la parenthèse de la fonction Min,
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Saisir le texte A privilégier entre guillemets (Touche 3 en haut du clavier),
• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets pour une cellule vide le cas échéant, soit '',
• Fermer la parenthèse de la fonction Si,
• Enfin, répliquer le calcul par le raccourci CTRL + Entrée,

La formule complète est la suivante :

=SI(C15=MIN($C$15:$F$15); 'A privilégier'; '')



Le résultat est différent de celui que nous pressentions jusqu'alors. Les frais de dossier inclus dans le dernier calcul ont suffi à faire pencher la balance.

Comme tous les calculs sont bâtis sur des variables, l'ensemble des conclusions du tableau se met instantanément à jour, pour livrer une étude comparative des banques sans équivoque.

Si par exemple vous modifiez le taux d'intérêt de la banque 3 à 0.35%, vous constateriez que la conclusion livrée par notre formule conditionnelle, tournerait aussitôt en sa faveur.