Tableau d’amortissement sur tableur: méthode et formules
Construire son propre tableau d’amortissement dans un tableur comme Excel ou Google Sheets permet de comprendre, ligne par ligne, comment une mensualité de prêt se décompose entre part d’intérêts et part de capital, et comment le capital restant dû diminue au fil du temps. Cet exercice, accessible à toute personne à l’aise avec les bases d’un tableur, repose sur quelques fonctions financières natives et sur une logique de calcul récurrente qu’il suffit de bien assimiler pour l’appliquer à n’importe quel scénario de financement.
Ce guide détaille la structure d’un tableau d’amortissement, les formules à utiliser pour le tableau d’amortissement tableur (formules VPM, INTPER, PRINCPER), les erreurs les plus courantes lors de sa construction et les vérifications à effectuer pour s’assurer de sa cohérence, que ce soit pour un crédit immobilier, un prêt personnel ou tout autre financement à mensualités constantes.
Il est utile de rappeler d’emblée qu’un tableau construit soi-même dans un tableur reste un exercice de simulation personnelle, indicatif et pédagogique. Il ne remplace jamais l’échéancier contractuel officiel remis par l’établissement prêteur, seul document faisant foi une fois un financement effectivement accordé.
Comprendre le sujet
Un tableau d’amortissement est un échéancier qui détaille, période par période, la composition de chaque remboursement d’un prêt : le montant total de la mensualité, la part qui correspond aux intérêts, la part qui correspond au remboursement du capital emprunté, et le capital restant dû une fois cette échéance payée. Dans un prêt classique à mensualités constantes, le montant remboursé chaque mois reste identique du premier au dernier versement, mais sa composition interne évolue : au début du prêt, les intérêts représentent une part importante de la mensualité, puis cette part diminue progressivement au profit du remboursement du capital, à mesure que le capital restant dû baisse.
De nombreux simulateurs en ligne permettent d’obtenir rapidement ce type de tableau en saisissant simplement un montant, un taux et une durée. Construire le sien dans un tableur présente cependant un intérêt différent : cela permet de comprendre concrètement la mécanique du calcul, de personnaliser librement les hypothèses de départ, de faire varier un paramètre à la fois pour observer son effet, et de comparer plusieurs scénarios côte à côte sans dépendre d’un outil figé. C’est aussi un exercice pédagogique qui aide à mieux lire et interpréter un échéancier reçu d’un établissement prêteur.
Il est essentiel de garder à l’esprit que ce tableau fait maison reste un outil de travail personnel. Il aide à se projeter, à tester des hypothèses et à préparer une demande de financement en connaissance de cause, mais il ne se substitue en aucun cas à l’échéancier officiel qu’un établissement prêteur transmet une fois un dossier étudié et un crédit effectivement mis en place, ce document intégrant des éléments contractuels précis que le tableur ne peut reproduire à l’identique.
Comment cela fonctionne
La structure d’un tableau d’amortissement repose sur un nombre limité de colonnes que l’on retrouve dans la quasi-totalité des modèles : le numéro de la période (souvent le mois), la mensualité totale, la part d’intérêts de la période, la part de capital amorti et le capital restant dû en fin de période. Chaque ligne correspond à une échéance, et l’ensemble des lignes couvre toute la durée du prêt, du premier au dernier remboursement.
La logique de calcul est récurrente : les intérêts d’une période donnée se calculent toujours sur le capital restant dû à l’issue de la période précédente, en appliquant le taux d’intérêt périodique. La part de capital amorti se déduit ensuite par simple soustraction, puisque la mensualité totale est fixe : part de capital égale mensualité moins intérêts de la période. Le nouveau capital restant dû s’obtient enfin en retranchant cette part de capital du capital restant dû précédent. Cette chaîne de calcul, ligne après ligne, aboutit logiquement à un capital restant dû qui atteint zéro à la dernière échéance.
Les tableurs modernes intègrent des fonctions financières natives qui évitent de recalculer chaque étape manuellement. La fonction VPM, appelée PMT dans les versions anglophones, calcule directement le montant de la mensualité constante à partir du taux périodique, du nombre total de périodes et du montant emprunté. La fonction INTPER, ou IPMT en anglais, calcule la part d’intérêts d’une période précise, tandis que la fonction PRINCPER, ou PPMT, calcule la part de capital de cette même période. La somme de la part d’intérêts et de la part de capital d’une période donnée correspond toujours au montant renvoyé par la fonction VPM, ce qui constitue d’ailleurs un premier point de contrôle utile.
Un point mérite une attention particulière : ces fonctions attendent un taux périodique, et non un taux annuel brut. Pour un remboursement mensuel, le taux annuel nominal doit être divisé par douze avant d’être utilisé dans la formule, et la durée doit être exprimée en nombre total de mensualités plutôt qu’en années. Un prêt sur vingt ans remboursé mensuellement correspond ainsi à deux cent quarante périodes. Omettre cette conversion est l’une des sources d’erreur les plus fréquentes lors de la construction d’un tableau d’amortissement dans un tableur.
Les critères généralement analysés
Avant de saisir la moindre formule, quelques données de départ doivent être clairement identifiées, car elles conditionnent l’ensemble du tableau. Le montant emprunté constitue le point de départ du capital restant dû à la première ligne. Le taux nominal annuel retenu sert de base au calcul du taux périodique utilisé dans les fonctions financières. La durée totale du prêt, combinée à la périodicité choisie, détermine le nombre exact de lignes que comportera le tableau.
La périodicité des échéances mérite d’être fixée dès le départ et respectée tout au long du tableau : la très grande majorité des prêts personnels et immobiliers fonctionnent sur une base mensuelle, mais certains financements peuvent prévoir des échéances trimestrielles ou annuelles, ce qui modifie en conséquence le taux périodique et le nombre total de périodes à retenir.
La date de la première échéance est un détail parfois négligé, alors qu’elle influence le calendrier complet du tableau, en particulier lorsque le prêt comporte un différé ou lorsque la première mensualité intervient plusieurs semaines après le déblocage des fonds. Il est également important de distinguer le taux nominal, celui qui sert directement dans les formules VPM, INTPER et PRINCPER, du taux annuel effectif global, ou TAEG, qui intègre en plus des intérêts un ensemble de frais annexes comme les frais de dossier ou l’assurance emprunteur. Un tableau construit uniquement à partir du taux nominal donne une image du coût des intérêts, mais pas du coût complet du crédit tel qu’il est mesuré par le TAEG, indicateur de référence pour comparer des offres entre elles.
Sur le plan réglementaire, il est utile de garder à l’esprit que tout taux proposé par un établissement doit rester en deçà du taux d’usure applicable à la catégorie de prêt concernée, plafond révisé périodiquement par la Banque de France. Ce repère n’entre pas directement dans les formules d’un tableau d’amortissement construit dans un tableur, mais il rappelle qu’un taux nominal doit toujours être resitué par rapport au TAEG affiché et au cadre légal en vigueur au moment de la simulation.
Les erreurs fréquentes à éviter
La première erreur, et sans doute la plus répandue, consiste à oublier de convertir le taux annuel en taux périodique avant de l’utiliser dans les formules. Utiliser directement un taux annuel de quatre pour cent dans une fonction VPM configurée pour un calcul mensuel fausse entièrement le résultat, produisant une mensualité très éloignée de la réalité et, par ricochet, un tableau entier incohérent.
Une deuxième erreur classique touche à la gestion des références de cellules lors de la recopie des formules vers le bas du tableau. Certaines cellules, comme celles contenant le taux périodique ou le nombre total de périodes, doivent rester fixes quelle que soit la ligne, ce qui impose de les verrouiller en références absolues. D’autres cellules, comme celle du capital restant dû de la période précédente, doivent au contraire évoluer d’une ligne à l’autre en références relatives. Un mauvais verrouillage provoque des décalages qui ne sautent pas toujours aux yeux immédiatement, mais qui rendent le tableau incohérent sur plusieurs lignes.
Une troisième source d’erreur consiste à confondre taux nominal et TAEG dans une comparaison, ou à omettre l’assurance emprunteur et les frais annexes lorsqu’on cherche à évaluer le coût réel d’un financement. Un tableau construit uniquement avec un taux nominal donne une vision partielle du coût total, qui peut être significativement sous-évalué si l’assurance et les frais ne sont pas intégrés séparément à l’analyse.
Enfin, il n’est pas rare de voir apparaître de petits écarts d’arrondi qui s’accumulent ligne après ligne et empêchent le capital restant dû de revenir exactement à zéro à la dernière échéance. Ce phénomène, généralement lié à des arrondis appliqués trop tôt dans la chaîne de calcul, se corrige en général en laissant les formules calculer avec la précision maximale du tableur et en n’arrondissant qu’à l’affichage, ou en ajustant légèrement la dernière mensualité pour absorber le résidu.
Comment comparer plusieurs options
L’un des principaux intérêts d’un tableau construit soi-même est de pouvoir tester facilement plusieurs hypothèses. La méthode la plus simple consiste à dupliquer la feuille ou la structure du tableau pour chaque scénario à comparer, en ne modifiant que les paramètres qui diffèrent, comme le taux, la durée ou le montant emprunté, tout en conservant la même logique de calcul pour chaque version.
Comparer uniquement le montant de la mensualité entre deux scénarios donne une vision incomplète : un allongement de la durée réduit mécaniquement la mensualité, mais augmente généralement le coût total des intérêts payés sur l’ensemble du prêt. Il est donc préférable d’ajouter, pour chaque scénario, une ligne de synthèse qui totalise la somme des intérêts versés, la somme du capital remboursé et le coût global du crédit, afin de visualiser d’un coup d’œil les écarts réels entre les options envisagées.
Cette approche est particulièrement utile pour évaluer l’effet d’un allongement ou d’un raccourcissement de la durée, ou pour comparer plusieurs propositions reçues de différents établissements. Il faut cependant rappeler qu’une comparaison rigoureuse entre offres suppose de raisonner en TAEG et non uniquement en taux nominal affiché, dans la mesure où deux offres avec un taux nominal identique peuvent avoir un coût réel différent selon les frais de dossier, l’assurance emprunteur ou les garanties associées.
Conseils pratiques avant de faire une simulation
Avant d’ouvrir un tableur, il est utile de rassembler en amont toutes les données nécessaires à la construction du tableau : le montant à emprunter, le taux nominal annuel envisagé, la durée souhaitée, la périodicité des échéances et, le cas échéant, une estimation des frais de dossier ou du coût de l’assurance emprunteur si l’on souhaite intégrer une vision plus complète du coût total.
Le choix de la périodicité doit rester cohérent avec le type de prêt envisagé et constant tout au long du tableau : mélanger une base mensuelle pour certaines lignes et une base annuelle pour d’autres invalide immédiatement l’ensemble du calcul. Il est également recommandé de tester d’abord le tableau sur un cas simple, avec des chiffres ronds et faciles à vérifier mentalement, avant de le complexifier avec des hypothèses plus fines comme un différé de remboursement ou un remboursement anticipé partiel.
Une fois la structure validée sur un cas simple, il devient plus facile de l’adapter à un scénario réel. Il reste toutefois essentiel de garder à l’esprit que ce tableau demeure un outil de simulation personnelle, construit à partir d’hypothèses saisies par l’utilisateur lui-même. Ses résultats doivent systématiquement être confrontés à une offre réelle transmise par un établissement prêteur avant toute décision de financement, ce document contractuel intégrant des éléments que le tableau maison ne peut à lui seul anticiper avec exactitude.
Dans quels cas cette solution peut être pertinente
Construire son propre tableau d’amortissement dans un tableur est particulièrement utile pour une personne qui souhaite tester l’effet d’un remboursement anticipé partiel sur la durée restante ou sur le coût total d’un prêt, en modifiant simplement le capital restant dû à une période donnée et en observant comment le reste du tableau se recalcule.
Cet exercice est également pertinent pour comparer, de façon autonome et détaillée, plusieurs propositions de financement reçues de différents établissements, en les structurant dans un format homogène qui facilite la lecture croisée des mensualités, des intérêts cumulés et du coût global de chaque option.
Au-delà de l’usage pratique, la construction d’un tableau d’amortissement a une vraie valeur pédagogique : elle aide à mieux comprendre, dans le temps, la mécanique de répartition entre intérêts et capital, et à mieux lire, par la suite, n’importe quel échéancier reçu d’un établissement prêteur.
Enfin, certains professionnels du financement, comme des courtiers ou des agents immobiliers, peuvent utiliser ce type de tableau pour illustrer un scénario de financement auprès d’un client, à condition de toujours préciser qu’il s’agit d’une simulation indicative et qu’il ne remplace en rien l’échéancier officiel qu’établira le prêteur une fois le dossier étudié.
FAQ
Quelle est la différence entre les fonctions VPM et PMT dans un tableur ?
Il n’y a pas de différence de calcul entre les deux : VPM est le nom de la fonction dans la version française d’Excel et de Google Sheets, tandis que PMT est le nom utilisé dans les versions anglophones de ces mêmes tableurs. Les deux fonctions demandent les mêmes arguments, à savoir le taux périodique, le nombre total de périodes et le montant emprunté, et renvoient toutes deux le montant de la mensualité constante correspondante.
Comment vérifier que mon tableau d’amortissement construit dans un tableur est cohérent ?
Plusieurs contrôles simples permettent de valider un tableau. Il faut d’abord vérifier que la somme de la part d’intérêts et de la part de capital de chaque ligne correspond bien à la mensualité calculée par VPM. Il faut ensuite s’assurer que le capital restant dû diminue de façon régulière et atteint zéro, ou une valeur très proche de zéro, à la toute dernière ligne du tableau. Enfin, la somme de toutes les parts de capital sur l’ensemble des lignes doit être égale au montant initialement emprunté.
Un tableau d’amortissement fait maison est-il aussi fiable que celui fourni par un établissement prêteur ?
Un tableau construit dans un tableur peut être mathématiquement exact si les formules sont correctement appliquées, mais il reste un exercice de simulation personnelle fondé sur des hypothèses saisies par l’utilisateur. Il ne prend pas nécessairement en compte tous les frais, garanties ou conditions particulières propres à une offre réelle. Il ne peut donc pas être considéré comme équivalent à l’échéancier contractuel officiel remis par un établissement prêteur, qui intègre l’ensemble des éléments définitifs du financement accordé.
Comment intégrer un remboursement anticipé dans mon tableau d’amortissement ?
La méthode la plus courante consiste à insérer, à la période concernée, un montant de capital supplémentaire remboursé en plus de la mensualité habituelle, puis à recalculer le capital restant dû à partir de ce nouveau montant réduit pour toutes les lignes suivantes. Selon l’hypothèse retenue, ce remboursement anticipé peut soit raccourcir la durée restante en conservant la même mensualité, soit réduire le montant des mensualités futures en conservant la durée initiale, deux logiques de recalcul distinctes qu’il convient de choisir clairement avant d’ajuster le tableau.
Faut-il inclure l’assurance emprunteur dans les formules VPM, INTPER et PRINCPER ?
Les fonctions VPM, INTPER et PRINCPER calculent uniquement la mensualité liée au remboursement du capital et des intérêts, sans intégrer l’assurance emprunteur. Pour obtenir une vision plus complète du coût mensuel réel, il est recommandé d’ajouter une colonne distincte dédiée au coût de l’assurance, puis de cumuler ce montant à la mensualité calculée par les formules financières afin d’estimer la charge totale à comparer avec le TAEG affiché par l’établissement prêteur.
Conclusion
Construire un tableau d’amortissement dans un tableur repose sur une logique accessible dès lors que l’on comprend la mécanique récurrente du calcul : les intérêts d’une période se calculent sur le capital restant dû précédent, la part de capital se déduit de la mensualité constante, et le nouveau capital restant dû s’obtient par simple soustraction. Les fonctions financières natives, VPM, INTPER et PRINCPER, automatisent cette logique à condition d’utiliser un taux périodique correctement converti et un nombre de périodes cohérent avec la périodicité retenue.
Le résultat obtenu reste toutefois indicatif et dépend entièrement des hypothèses saisies au départ, qu’il s’agisse du montant emprunté, du taux nominal ou de la durée. Ce type de tableau constitue un excellent outil pédagogique pour comprendre la structure d’un crédit, tester différents scénarios ou préparer une demande de financement, mais il ne saurait remplacer une simulation réalisée auprès d’un établissement prêteur ni l’échéancier contractuel officiel qu’il transmet une fois un dossier étudié.
Les conditions réelles d’un financement peuvent varier selon le profil de l’emprunteur, les revenus, la stabilité financière, l’établissement financier sollicité, le montant demandé, la durée de remboursement, l’analyse du dossier, les justificatifs fournis et la réglementation applicable au moment de la demande. Pour toute décision de financement, il reste recommandé de compléter cet exercice personnel par une simulation actualisée auprès d’un établissement prêteur ou d’un professionnel du crédit, seul en mesure de fournir des conditions précises et adaptées à une situation individuelle.