Comment créer un simulateur d'amortissement d’emprunt bancaire sur Excel
Aujourd’hui, je vous propose de découvrir comment réaliser un tableau de remboursement d’emprunt, afin de pouvoir simplement simuler un emprunt ou un prêt (prêt immobilier, crédit à la consommation, …) à l’aide d’Excel. Tout au long de ce tutoriel, nous allons voir comment déterminer le montant des mensualités de remboursement et comment celle-ci est décomposée (remboursement du capital, intérêts, frais, …). Pour les plus pressé, vous trouverez également le simulateur de prêt à télécharger. Le tableau que nous allons mettre en place va être très simple d’utilisation, il suffit en effet de remplir les champs prévus à cet effet, et les calculs vont se mettre à jour automatiquement.
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier disponible ci-dessous :
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Les caractéristiques de l’emprunt
Avant de pouvoir calculer le tableau d’amortissement de l’emprunt, nous allons devoir demander à l’utilisateur de saisir les différentes caractéristiques de ce dernier (montant emprunté, taux d’intérêt, durée de l’emprunt,…).
Pour cela, nous allons créer un petit tableau en haut de la page afin de recueillir les informations suivantes :
- Date de début,
- Montant emprunté (en €),
- Taux d’intérêt annuel,
- Durée de l’emprunt (en années),
- Frais réguliers (en €)
Puis nous allons mettre ce tableau en forme (pour en savoir plus sur la mise en forme des cellules, je vous invite à suivre le cours dédié sur Excelformation.fr : Apprendre à utiliser Excel pour les débutants - La mise en forme de cellules) :
Vous noterez également que pour simplifier les choses, nous avons mis un fond jaune pour les cellules qui devront être remplies par l’utilisateur.
Pour tester au fur et à mesure, nous allons remplir le tableau avec des données fictives :
Nous allons donc créer une simulation pour un emprunt de 350000€ remboursé sur 25 ans au taux annuel de 1,50%, auquel vient s’ajouter une assurance mensuelle de 100€.
2. La synthèse de l’emprunt
À présent, nous allons insérer un second encart, juste à, droite de la table des caractéristiques de l’emprunt afin de déterminer les éléments de calcul de l’emprunt :
Nombre de mensualités : il s’agit simplement de multiplier le nombre d’années saisi par l’utilisateur par douze. Nous supposons que l’emprunt est remboursé chaque mois, mais il suffit d’adapter le simulateur en fonction des paramètres réels de l’emprunt
B7*12
Montant de chaque mensualité : pour déterminer le montant de chacune des mensualités, nous allons utiliser la formule financière intégrée à Excel VPM(), que nous allons détailler juste après :
- La formule VPM() permet de calculer le montant de chacun des remboursements à effectuer pour un emprunt constitué de remboursements constants, avec un taux d’intérêt constant également,
- Le premier paramètre de la formule VPM() correspond au taux d’intérêt du prêt pour chaque période. S’agissant d’un taux constant, nous allons donc utiliser le taux annuel saisi par l’utilisateur (dans la cellule B6), que nous divisons par 100 (pour le transformer en pourcentage) et le diviser par douze pour obtenir l’équivalent du taux mensuel,
- Le second paramètre correspond au nombre de remboursements, que nous venons de calculer dans la cellule F4,
- Le dernier paramètres que nous allons utiliser pour calculer le montant de chaque mensualité correspond à la valeur actuelle de l’emprunt, c’est-à-dire au montant emprunté,
- Et enfin, nous venons ajouter le montant des frais réguliers pour connaître le coût total de chaque mensualité :
=-VPM(B6/100/12;F4;B5)+B8
Montant des versements : il s’agit de multiplier le montant de chacune des mensualités par le nombre de mensualités,
= F5*F4
Le coût total de l’emprunt : nous calculons alors la différence entre le montant total de l’emprunt, et le montant initialement emprunté,
F6-B5
Le montant des intérêts correspond au coût total de l’emprunt auquel nous retirons le montant des frais réguliers (que nous allons calculer juste après),
F7-F9
En enfin le montant des frais réguliers correspond simplement au montant mensuel de ces derniers que l’on multiplie par le nombre de mensualités
= B8*F4
3. Mise en place du plan de remboursement
Nous connaissons maintenant chaque paramètre de l’emprunt, nous allons pouvoir simplement mettre en place le tableau de remboursement, que l’on appel également le tableau d’amortissement de l’emprunt.
Pour commencer, nous allons placer l’encart de récapitulation en dessous des caractéristiques pour simplifier la mise en page.
- Pour cela, nous le sélectionnons simplement,
- Puis nous plaçons le curseur de la souris sur l’une des bordure de la sélection (le curseur prend alors la forme d’une croix fléchée)
- Et enfin, en maintenant le bouton droit de la souris, nous faisons glisser la sélection en dessous du premier tableau :
De cette manière, nous allons pouvoir construire notre plan d’amortissement sur la partie droite de la feuille de calcul :
Le tableau de remboursement est constitué de huit colonnes :
- Dans la première colonne (qui ici ne porte pas de nom, mais que l’on peut appel Échéance par exemple), nous allons insérer le numéro de la ligne,
- Dans la colonne date, nous retrouverons la date du remboursement,
- Puis le capital restant à rembourser en début de période,
- Le montant de la mensualité,
- Le montant des intérêts,
- Le montant des frais,
- Le montant du remboursement,
- Et enfin le montant du capital restant à rembourser après prise en compte de la mensualité
Pour remplir ce tableau, nous allons procéder en trois étapes :
- Tout d’abord nous allons remplir la première ligne,
- Puis la seconde,
- Et enfin nous allons étendre cette seconde ligne pour l’ensemble des autres lignes du tableau
3.1. Remplir la première ligne
Commençons par remplir la première de notre tableau de remboursements.
S’agissant de la première ligne, nous allons simplement saisir le chiffre 1 dans la première colonne :
Pour déterminer la date de notre premier remboursement, nous allons utiliser la formule DATE() à l’aide de nombre de périodes écoulées (soit ici une seule période, donc un mois de plus) :
=DATE(ANNEE($B$4);MOIS($B$4)+E6;1)-1
Pour calculer cette date, nous partons de la date de début, saisie en cellule B4, que nous appelons en utilisant sa référence absolue (les symboles $), afin que lorsque nous allons dupliquer cette formule, le point de départ reste toujours inchangé.
En revanche, le nombre de période saisi en cellule E6 est appelé avec la référence relative de cette cellule, si bien que lorsque nous allons copier la cellule sur la ligne suivante, nous puissions récupérer un nombre de période saisi sur la même ligne.
Notez également que nous finissons notre formule en retirant un jour (-1), afin de récupérer le dernier jour du mois précédent (pour un paiement de l’échéance en fin de période)
Le capital restant à rembourser en début de période correspond au montant total emprunté, saisi par l’utilisateur dans la cellule B5 :
=B5
Le montant de chaque mensualité est déjà calculé dans la cellule B12 (attention au symboles dollars pour utiliser la référence absolue à cette cellule) :
=$B$12
Le montant des intérêts se calcul en appliquant le taux mensuel (taux annuel que l’on diviser par 100 puis par 12) sur le capital restant à rembourser
=G6*$B$6/100/12
Les frais mensuels sont saisis dans la cellule B8 :
=$B$12
Le remboursement de la part capital correspond à la différence entre la mensualité totale la somme des intérêts et des frais :
=H6-I6-J6
Et enfin le capital restant à rembourser en fin de période est égal à celui déterminé au début, auquel nous retirons le montant du remboursement :
=G6-K6
3.2. Remplir la première ligne
Maintenant que la première de notre tableau de remboursements est correctement remplie, nous pouvons passer à la seconde.
Le numéro de la ligne correspond à celui de la ligne précédente que nous incrémentons simplement d’une ligne afin de mettre en place un compteur de ligne :
=E6+1
La date de remboursement de la seconde mensualité peut être déterminée à l’aide de la même formule que pour la première ligne. Pour récupérer la formule de la ligne située juste au-dessus, il suffit de placer la sélection sur la cellule F7, puis d’utiliser le raccourci clavier [Ctrl]+[b] afin d’étendre la formule située juste au-dessus :
Nous retrouvons ici bien l’intérêt d’avoir utilisé une référence absolue pour la date de début, qui reste ainsi toujours sur la cellule B4, tandis que la cellule E6 est maintenant devenu E7 dans cette nouvelle formule.
Le capital restant à rembourser au début de cette nouvelle période correspond au capital à rembourser en fin de période précédente.
Nous pouvons également utiliser la formule ARRONDI() pour récupérer la valeur de ce capital arrondi au centime (avec deux décimal) :
Les colonnes restantes à remplir pour cette seconde ligne sont les mêmes que pour la première. Nous allons donc pouvoir étendre simplement les formules du dessus, en les sélectionnant, puis en utilisant le raccourci clavier [Ctrl]+[b] :
3.3. Remplir les lignes suivantes
Pour terminer la construction de notre tableau des remboursements d’emprunt, nous allons simplement dupliquer cette seconde ligne, sur le nombre de lignes nécessaires.
Pour cela, nous commençons par la sélectionner :
Puis nous plaçons le curseur de la souris sur la poignée située en bas à droite de la dernière cellule (le curseur prend alors la forme d’un signe plus) :
Puis nous faisons glisser cette poignée en maintenant le bouton de la souris enfoncé :
Et voilà, notre tableau de remboursement d’emprunt est maintenant terminé :
