"VPM", the famous one

Nous voilà donc embarqués dans diverses simulations de crédit bancaire.

Hummm !!!

Je commence par indiquer les différents critères du modèle :

– le montant souhaité,

– le taux proposé par la banque,

– le nombre de mois, ainsi que l’équivalent en nombre d’années.

Il faudra donc demande à Excel de calculer :

– les mensualités

– le coût global du crédit.

Allons-y :

Je me place sur la cellule qui doit indiquer le montant des mensualités. Puis je clique sur le bouton Insérer une fonction (fx).

De là, je choisis d’abord la catégorie Finances puis ensuite, tout en bas de la liste, la fonction VPM.

L’assistant fonction s’affiche ensuite, ce qui me permet d’indiquer un certain nombre de parmètres (voir la vue, ci-dessous).

En fait, pour utiliser cette fonction, je dois renseigner, au moins, les trois premiers champs :

– Taux

– Npm

– Va

Il s’agit de fonctions qui font elles-mêmes référence à d’autres fonctions. J’ai essayé de comprendre la logique de toute cette affaire-là. Je vous épargne le compte rendu des impressions laissées par cette aventure extravagante et hallucinatoire. Vous n’aurez donc droit, ci-dessous, qu’aux définitions générales de ces trois fonctions. L’essentiel, à mon avis, étant d’en comprendre le sens dans ce contexte particulier de crédit bancaire.

Toutefois, si vous voulez en savoir plus sur les fonctions financières (et vous amuser comme des petits fous), je vous conseille de vous reporter directement aux explications données par Microsoft.

Je commence par le premier : Taux. En cliquant sur le champ, j’obtiens, dans l’assistant, un commentaire sur le sens de cette fonction (voir ci-dessous) :

Taux est le taux d’intérêt du prêt par période. Par exemple, utilisez 6%/4 pour des paiements trimestriels à 6% APR

J’en déduis que pour des mensualités, il faudra donc diviser le taux d’intérêt par douze…

Je clique sur la cellule concernée C3, je tape l’opérateur de division / et 12 (voir ci-dessous).

Je passe au champ suivant : Npm. Il ne s’agit pas d’un parti politique mais, comme l’indique le commentaire, du nombre total de versements pour rembourser le prêt.

Alors je clique sur la cellule qui indique le nombre de mois :

Et enfin : Va. De quoi s’agit-il, s’il vous plaît ?

Lisons le commentaire : Va est la valeur actuelle, c’est-à-dire la valeur présente du total des remboursement futurs.

Concrètement, il faut indiquer le montant de l’emprunt en valeur négative (avec le signe moins), afin que ce montant soit affiché en positif.

Je tape donc le signe moins ( – ) et je clique sur la cellule C2.

Je valide par OK…

… l’affaire est jouée.

Voilà donc notre VPM calculée en trois clics :

Il me faut maintenant le coût total de l’opération. Je me place en C7 et je tape la formule pour multiplier le nombre de mois par le nombre de mensualités.

Regardez ci-dessous le résultat de l’opération (bancaire) ; pour obtenir 50K€ il me faudra débourser près de 76K€ pour un taux d’intérêt à 6%… Aïe, Aïe, Aïe !!!

Je continue (pourtant) la simulation. Désormais, à partir de ce modèle, je veux évaluer la variation sur le taux d’intérêt.

Je tape un autre tableau, en dessous, avec divers taux.

Il faut, ensuite, raccorder ce nouveau tableau avec le précédent :

– En B10, je tape = (signe égal) puis, je clique sur la cellule qui indique le taux de crédit (C3).

– En C10, je procède de la même façon en cliquant sur le montant correspondant aux mensualités (C6).

Je continue avec le coût total :

– En D10, doit s’afficher une valeur qui sera calculée à partir de la fonction stockée en (C7).

Tout cela paraît assez compliqué et terriblement abstrait lorsqu’on le dit ou lorsqu’on le lit, mais regardez bien ci-dessous, normalement vous devriez vous y retrouver assez vite :

Je vais, maintenant, demander à Excel de calculer automatiquement les mensualités et les coûts globaux, à l’aide de la table de données.

1) Je sélectionne le nouveau tableau.

2) Je clique sur le Menu Données, puis sur Table…

3) Je place le point d’insertion dans le champ « Cellule d’entrée en colonne » puis je clique sur la cellule correspondant à ma variable : le taux d’intérêt (C3).

En fait, j’aurais tout aussi bien pu choisir de renseigner le champ « cellule d’entrée en ligne ». Dans ce cas – table de données à une variable – l’un ou l’autre de ces deux choix (colonne ou ligne) est indifférent ; ce qui n’est pas le cas, comme nous le verrons, si l’on doit faire intervenir deux variables.

Je clique ensuite sur OK.

C’est calculé. Il me reste à modifier le format de cellules (monétaire ou comptabilité).

Je souhaite, à présent, faire une simulation sur différents montants. Je crée un troisième tableau, pour faire apparaître le montant de l’emprunt, les mensualités, et le coût total.

Ma variable sera donc, cette fois-ci, la cellule C2 le montant de l’emprunt.

Toujours dans la première ligne, j’indique, pour la colonne « Mensualité », le montant correspondant du premier tableau (C6).

Je procède à l’identique pour le coût total (C7) :

En première colonne, sous le montant de référence, je tape divers montants à partir desquels sera faite la simulation.

Je sélectionne ce nouveau tableau…

…puis je retourne dans le Menu Données / Table…

Cette fois-ci, comme il s’agit d’évaluer la variation à partir des montants, je clique sur la cellule du premier tableau correspondant à cette variable (C2).

Je valide en cliquant sur OK.

Là encore, par soucis de clarté, je désélectionne la table, puis je sélectionne les données calculées (C17:D19) afin de passer ce groupe de cellules en format monétaire.

Autre hypothèse : je souhaite désormais faire une simulation faisant intervenir deux variables : la durée du crédit et le taux. Je veux connaître, dans un premier temps, le montant des mensualités.

Je crée encore un autre tableau, en indiquant en première cellule - angle supérieur gauche - les coordonnées de la cellule à partir de laquelle je veux obtenir les différents résultats avec cette simulation ; il s’agit donc des mensualités (C6).

Dessous, en colonne, je tape les différentes hypothèses de taux d’intérêt.

Puis, en ligne, je saisis les durées, indiquées en mois.

Par soucis de lisibilité, j’établis une correspondance entre mois et années. Pour cela, en bas du tableau, sous la cellule comportant la dernière valeur de taux, je tape « Années », puis sur cette ligne, dans la cellule suivante j’indique la formule suivante : =B23/12

Après l’avoir validée (CTRL+Entrée) j’étire cette cellule pour obtenir les correspondances en années, pour toutes les durées.

Je sélectionne ce tableau (sans prendre la ligne des années).

Je retourne encore dans le Menu Données / Table…

Cette fois-ci, je dois indiquer les deux variables :

– en ligne, comme nous l’avons vu, il faut indiquer le nombre de mois (C4)

– en colonne, il s’agit du taux d’intérêt (C3).

Si vous avez un doute, regardez bien le tableau sélectionné sur l’image : nous sommes bien d’accord qu’en ligne figurent les mois et que les taux sont disposés en colonne.

Je valide par OK.

Ce tableau indique donc différentes hypothèses faisant intervenir des variations de durée et de taux de crédit sur le même montant. En fait, il est impossible, avec la table de données, de faire intervenir plus deux variables (une pour ligne, une pour cellule). Voilà pourquoi avec cet outil là, je dois décomposer ma simulation en de multiples tableaux ; ce qui n’est pas forcément plus mal, d’ailleurs, pour faciliter la lisibilité.

Je m’efforce, justement, de mettre en forme ce tableau pour le rendre le plus compréhensible possible. J’ajoute, au-dessus de la ligne comportant les différentes durées, un groupe de cellules fusionnées dans lequel je tape « Nombre mois ». Je place également des bordures afin de bien indiquer que le la première ligne « Nombre mois » doit être interprétée comme étant une indication générique s’appliquant à la ligne suivante.

Pour autant, un problème demeure : en cellule A23, première cellule de mon tableau, s’affiche la valeur 421,93 €.

Nous avons vu qu’il est indispensable d’indiquer à cet endroit-là le montant à partir duquel Excel calcule les résultats de la table. Néanmoins, lorsqu’on regarde ce tableau on ne comprend pas, du point de vue de la logique, ce que chiffre vient faire ici.

On s’attendrait, plutôt, qu’à cet emplacement s’affiche une information générique qui fasse référence aux données stockées en-dessous ; par exemple « Taux d’intérêt ». Or si l’on remplace « 421,93€ » par cette mention, c’est toute notre table qui s’effondre…

Il va falloir bidouiller.

En premier lieu, je passe le contenu de la cellule A23 en blanc ; ce n’est pas bien terrible, mais je n’ai rien trouvé de mieux.

Ensuite, je vais activer, depuis la barre d’outils Dessins, une zone de texte, afin d’y taper « Intérêts » et placer ce cache-misère sur la cellule en essayant de retrouver le même fond de couleur que celui du champ « Nombre de mois ».

Le tableau est déjà plus compréhensible.

Dernière simulation : j’aimerais, cette fois-ci, connaître le coût global pour les mêmes variables.

Je crée encore un tableau, pour lequel j’indique, donc, placé en origine les coordonnées de la cellule du modèle qui devra être recalculée : C7.

Laquelle correspond sur le premier tableau, en toute logique, au coût total (voir ci-dessous).

La seule différence avec la précédente simulation ne repose que sur cette cellule : à présent on indique la référence du coût global, alors que tout à l’heure c’était les mensualités.

La valeur correspondante s’affiche donc comme point de référence dans mon dernier tableau.

Je recommence, ensuite, exactement la même procédure que précédemment :

1) Saisie des différentes valeurs pour « taux » et « nombre de mois »

2) Sélection du tableau

3) Menu Données / Table…

4) J’indique les coordonnées des variables en cliquant sur les cellules du premier tableau : en ligne la mensualité (C4) ; en colonne, le taux (C3).

Je valide par OK.

Voilà le tableau complet :

Fignolages

Je voudrais, pour terminer cette exploration dans le monde captivant de la simulation, en profiter pour mettre en pratique une fonctionnalité d’Excel que nous n’avons pas encore vue : la mise en forme conditionnelle.

Mon objectif consiste à mettre en valeur, dans chacune de mes simulations, les données figurant déjà dans le modèle de base (le premier tableau). On suppose que cela permettra d’évaluer plus facilement les comparaisons des différentes hypothèses à partir d’une proposition bancaire « de référence ».

Je commence par sélectionner le deuxième tableau (variations sur les taux). Puis je clique sur le Menu Format / Mise en forme conditionnelle…

Une boîte de dialogue (ou assistant) s’affiche alors à l’écran. Cela va me permettre d’indiquer chacune des conditions permettant à Excel de modifier automatiquement l’apparence de certaines cellules.

Je laisse le premier champ, puis je clique sur le deuxième champ afin de sélectionner « égale à » :

Je clique dans le troisième champ afin de faire apparaître le point d’insertion, puis je clique sur la cellule indiquant le taux d’intérêt (C3) :

La première condition est définie : « si la table comporte une donnée égale à la valeur stockée en cellule C3 ».

Il faut, maintenant indiquer ce qu’Excel doit faire si « la condition est remplie », sachant qu’il ne s’agit ici, que de modifier l’apparence de la cellule : je clique sur le bouton « Format » :

Une autre boîte dialogue s’affiche à l’écran. Je choisis la couleur « Pervenche ».

Puis je demande de passer le contenu en gras italique.

Je valide par OK sur cette boîte de dialogue « Format ».

Ce n’est pas fini. Sachant que la table fait intervenir le taux, la mensualité et le coût total, je dois recommencer pour que la mise en forme conditionnelle s’applique également à ces deux derniers paramètres. Il faut donc cliquer sur le bouton « Ajouter ».

Je définis ainsi, les mêmes conditions de mis en forme pour les mensualités (C6) et le coût total (C7).

Ci-dessous, le modèle de référence est mis en valeur automatiquement dans la table. Il en sera de même y compris si je viens à en changer les données.

Attention, pour supprimer la mise en forme conditionnelle, il faut retourner par le « Menu Format/ Mise en forme conditionnelle… » puis cliquer sur le bouton « Supprimer… ».

Quelques petits ajustement d’espace peuvent être apportés entre chaque tableau afin d’améliorer encore quelque peu la lisibilité.

Ci-dessous une capture de l’aperçu avant d’impression.

N’hésitez pas à récupérer ci-dessous les version PDF et XLS de cet exercice…