Dans ce tutoriel, je vais vous montrer comment créer dans Excel un simulateur de crédit capable de calculer automatiquement chaque mensualité, les intérêts payés, le capital réellement remboursé et le montant qu’il nous reste à payer.
Lorsque nous versons 450 € à la banque, notre dette ne diminue pas forcément de 450 €.
Une partie de cette somme correspond aux intérêts, et c’est justement ce que notre tableau va nous permettre de voir mois après mois.
À la fin, nous pourrons modifier le montant emprunté, le taux ou la durée et laisser Excel recalculer tout le crédit.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons imaginer que nous souhaitons financer une voiture en empruntant 28 000 € sur 6 ans avec un taux annuel fixe de 5,2 %.
Pour commencer, nous allons souhaiter déterminer le montant de chaque mensualité correspondante à ce crédit.
Nous nous plaçons dans la cellule B10 afin d’y saisir la formule suivante :
=VPM(B8/12;B9*12;B7)
La fonction VPM() calcule un paiement constant à partir d’un taux, d’un nombre de périodes et d’un montant emprunté.
Notre taux de 5,2 % est annuel alors que nous allons effectuer un paiement chaque mois. Nous divisons donc B8 par 12.
Même chose pour la durée : 6 années correspondent à 72 mensualités, d’où le calcul B9*12.
Ici, nous pouvons voir que le montant retourné est négatif, car lorsque nous utilisons des fonctions financières comme VPM(), Excel considère l’argent emprunté et l’argent remboursé comme des flux financiers de sens opposés.
Nous pouvons donc simplement inverser ce résultat en ajoutant le signe « - » devant VPM(), pour afficher la mensualité sous forme positive :
=-VPM(B8/12;B9*12;B7)
Avec notre exemple, Excel nous retourne une mensualité d’environ 453 €.
Pour gagner du temps lors de la création d’une formule plus complexe, ou si vous ne connaissez pas forcément toutes les fonctions d’Excel, il est également possible d’utiliser « IA Excelformation.fr > Créer une formule » et décrire simplement le calcul souhaité en français.
Ici, nous sélectionnons la cellule B10, et nous demandons à l’IA : « Détermine le montant de chaque mensualité correspondante à cet emprunt » :
Le résultat est alors exactement identique :
Retrouvez plus d’informations sur la puissance de « IA Excelformation.fr », en cliquant ici.
Notre zone de paramètres est terminée.
L’intérêt est que toutes les formules du tableau vont maintenant dépendre de ces quelques cellules : si nous remplaçons 28 000 € par 35 000 €, tout sera recalculé automatiquement.
2. Construire le tableau d’amortissement
Nous allons maintenant créer le véritable tableau de calcul à partir de la cellule A13.
La colonne D en jaune est particulière : elle nous permettra de saisir manuellement un remboursement supplémentaire lorsque nous le souhaitons.
Pour le premier mois, nous allons commencer par récupérer le solde de départ.
Au premier mois, nous devons encore rembourser la totalité des 28 000 €.
Dans B14, nous saisissons :
=$B$7
Les signes « $ » verrouillent la référence à B7. Pour les ajouter rapidement, nous pouvons sélectionner B7 dans la barre de formule puis appuyer sur [F4].
Dans C14, nous récupérons la mensualité calculée précédemment :
=$B$10
Nous arrivons maintenant à la colonne « Intérêts ».
Le taux étant annuel, nous calculons les intérêts d’un mois en prenant le solde de départ multiplié par le taux annuel, puis en divisant par 12.
Dans E14 :
=B14*$B$8/12
Nous obtenons environ 121,33 €.
C’est ici que notre tableau devient intéressant. Sur les quelque 453 € que nous allons verser lors du premier mois, 121,33 € correspondent uniquement aux intérêts.
Seul ce qui reste va réellement réduire notre dette.
3. Calculer le capital remboursé et le solde restant
Dans F14, nous allons déterminer le capital réellement remboursé.
Nous prenons notre mensualité, nous retirons les intérêts et nous ajoutons éventuellement le remboursement supplémentaire présent en colonne D.
=C14-E14+D14
Comme D14 contient zéro, seuls la mensualité et les intérêts interviennent.
Nous constatons alors qu’une mensualité d’environ 453 € ne réduit pas notre crédit du même montant.
Au premier mois, environ 121 € couvrent les intérêts et environ 332 € réduisent réellement le capital.
Dans G14, nous pouvons maintenant calculer ce qu’il reste à rembourser :
=MAX(0;B14-F14)
La fonction MAX() nous évite d’obtenir un solde négatif à la toute fin du crédit. Si notre formule devait théoriquement retourner -12 €, Excel conserverait zéro.
Passons au deuxième mois.
Son solde de départ correspond simplement au solde restant du mois précédent. Nous saisissons donc dans B15 :
=G14
Dans C15, nous reprenons la même formule que sur la ligne précédente, c’est-à-dire la valeur de la cellule B10. Pour gagner du temps, nous pouvons utiliser le raccourcis [Ctrl]+[B] pour dupliquer la formule :
=$B$10
Et nous allons faire de même pour les cellule E15 à G15 pour récupérer les formules :
=B15*$B$8/12
=C15-E15+D15
=MAX(0;B15-F15)
Nous pouvons maintenant sélectionner A15:G15 et recopier cette ligne vers le bas sur plusieurs dizaines de lignes, en utilisant pour cela la poignée de recopie :
L’idée étant que le nombre de ligne corresponde au moins au nombre de mensualités et que la valeur de la colonne « Solde restant » soit égale à 0 :
Excel adapte les références relatives automatiquement, tandis que les cellules précédées de « $ », comme $B$8 et $B$10, restent verrouillées.
4. Remboursements anticipés
Maintenant, regardons ce qui se passe si nous mettons en place des remboursements anticipés.
Pour cela, revenons au mois 3 et saisissons 500 € dans D16.
Notre mensualité normale ne change pas, mais ces 500 € viennent s’ajouter au capital remboursé.
Le solde restant diminue donc nettement plus vite.
Et le bénéfice ne s’arrête pas là.
Le mois suivant, les intérêts sont calculés sur un solde désormais plus faible.
Nous allons donc également payer moins d’intérêts sur les échéances suivantes.
Nous pouvons tester la même chose avec les 1 000 € du mois 6 ou les 750 € du mois 10 : dès que nous modifions une valeur de la colonne D, tous les calculs situés en dessous se mettent à jour.
Si nous regardons en bas, nous voyons que désormais le remboursement interviendra directement à compter du 66ème mois !
Attention toutefois : notre tableau présente une simulation mathématique et les règles sont grandement simplifiées.
Dans un vrai crédit, les possibilités et les éventuels frais liés aux remboursements anticipés dépendent du contrat et peuvent notamment inclure des pénalités en cas de remboursement anticipé.
5. Arrêter proprement le tableau et analyser le crédit
Notre tableau fonctionne, mais si nous regardons attentivement les dernières lignes, nous pouvons constater une petite anomalie de présentation.
En effet, sur la dernière échéance, nous voyons que celle-ci est toujours de 453.54€, alors qu’il ne reste que 224.76€ à régler.
Nous allons donc modifier la formule de la colonne C, pour plafonner le montant de la mensualité au capital restant à rembourser majoré des intérêts correspondants.
Dans C14, nous remplaçons la formule précédente par :
=MIN($B$10;B14+E14)
Le montant de la mensualité correspond donc au montant calculé dans la cellule B10, sans toutefois dépasser ce qu’il reste à rembourser véritablement.
Excel choisit donc la plus petite valeur entre la mensualité habituelle et le montant nécessaire pour solder le crédit.
Nous recopions cette formule sur toute la colonne en double cliquant sur la poignée de recopie :
Tout est maintenant propre au niveau de la présentation.
Nous pouvons terminer par quelques indicateurs sous le tableau.
Nous pouvons terminer par quelques indicateurs sous le tableau.
Le premier va nous permettre de connaître le montant total réellement remboursé, en additionnant à la fois les mensualités classiques de la colonne C et les remboursements supplémentaires saisis dans la colonne D.
Nous saisissons donc :
=SOMME(C14:D120)
La fonction SOMME() additionne ici l'ensemble de la plage comprise entre C14 et D120. Comme nous sélectionnons deux colonnes, Excel additionne aussi bien les mensualités normales que les éventuels remboursements anticipés.
Juste en dessous, nous allons isoler la partie correspondant uniquement aux intérêts.
=SOMME(E14:E120)
Cette fois-ci, nous additionnons uniquement la colonne E, dans laquelle nous avons calculé mois après mois les intérêts du crédit.
Cette valeur est particulièrement intéressante, car elle représente finalement le coût du crédit lié aux intérêts, en dehors bien sûr des éventuels frais de dossier, assurances ou autres frais bancaires que nous n'avons pas intégrés dans notre simulation.
Nous pouvons d'ailleurs vérifier la logique de notre calcul assez facilement : le montant total remboursé doit correspondre approximativement au capital emprunté auquel nous ajoutons le total des intérêts.
Il nous reste maintenant à déterminer la durée réelle du crédit.
Pourquoi parler de durée « réelle » ? Tout simplement parce que les remboursements supplémentaires peuvent raccourcir la durée initialement prévue.
Dans notre exemple, le crédit est prévu sur 6 ans, soit 72 mois. Mais si nous ajoutons plusieurs remboursements anticipés dans la colonne D, le solde peut atteindre zéro avant la 72e mensualité.
Pour compter le nombre de mois effectivement utilisés dans notre tableau, nous saisissons :
=NB.SI(B14:B120;">0")
La fonction NB.SI() va parcourir les cellules de B14 à B120 et compter uniquement celles dont le solde de départ est supérieur à zéro.
Autrement dit, chaque ligne correspondant à un mois où nous avons encore quelque chose à rembourser est comptabilisée, tandis que les lignes situées après le remboursement complet sont ignorées.
Nous obtenons ainsi directement le nombre réel de mensualités nécessaires.
Un résultat obtenu est correct, mais ce n'est pas forcément la présentation la plus parlante.
Nous pouvons donc convertir ce nombre de mois en années et en mois, en saisissant à côté de la cellule E9 :
="Soit "&ENT(E9/12)&" ans et "&MOD(E9;12)&" mois"
Décortiquons rapidement cette formule.
La fonction ENT() récupère la partie entière du résultat de E9 divisé par 12. Si notre crédit dure 66 mois, nous avons :
=ENT(66/12)
Excel retourne 5, soit 5 années complètes.
Nous utilisons ensuite MOD() :
=MOD(66;12)
MOD() retourne le reste d'une division. Après avoir constitué 5 années complètes, soit 60 mois, il reste ici 6 mois.
Notre cellule affiche donc :
Soit 5 ans et 6 mois
Et nous pouvons maintenant mesurer très facilement l'impact de nos remboursements supplémentaires.
Il suffit par exemple de remplacer temporairement les montants de la colonne D par zéro.
Excel recalcule immédiatement le total remboursé, le montant des intérêts ainsi que la durée réelle du crédit.
Nous pouvons ensuite remettre nos remboursements de 500 €, 1 000 € ou 750 € et observer la différence.
Nous voyons ainsi concrètement combien de mois nous pouvons gagner et surtout combien d'intérêts nous pouvons éviter en réduisant plus rapidement le capital restant dû.
