Tableau d’amortissement emprunt Excel : le construire, le lire et le vérifier

par Antoine Pelletier

Pour obtenir un tableau d’amortissement d’emprunt dans Excel, il suffit de saisir trois données (montant emprunté, taux annuel, durée en mois), de calculer la mensualité avec la fonction VPM, puis de dérouler chaque ligne :

  • intérêts du mois = capital restant dû × taux annuel ÷ 12
  • capital remboursé = mensualité − intérêts
  • nouveau capital restant dû = ancien capital − capital remboursé

Le tableau doit se terminer à zéro à la dernière échéance.

Un tableau d’amortissement détaille, mois après mois, la part d’intérêts et la part de capital contenues dans chaque mensualité. Sous Excel, il se construit en une dizaine de minutes, sans macro ni extension. Il vous permet de simuler un prêt immobilier, de vérifier une offre de prêt reçue d’une banque ou de mesurer l’effet d’un remboursement anticipé. Voici la méthode, les formules exactes, un exemple chiffré et les pièges qui faussent le plus souvent les résultats.

Construire un tableau d’amortissement emprunt Excel en cinq étapes

Ouvrez un classeur vierge et suivez cet ordre. Il évite de réécrire les formules à chaque simulation.

  1. Réservez les cellules B1 à B3 aux paramètres : montant emprunté, taux annuel nominal (hors assurance), durée en années.
  2. En B4, calculez le nombre d’échéances : =B3*12.
  3. En B5, calculez la mensualité hors assurance : =-VPM(B2/12;B4;B1).
  4. À partir de la ligne 8, créez les colonnes : numéro d’échéance, mensualité, intérêts, capital amorti, capital restant dû.
  5. Recopiez la première ligne vers le bas jusqu’au dernier mois, puis vérifiez que le capital restant dû finit à zéro.

Le signe moins devant VPM est volontaire : Excel considère le prêt reçu comme un flux positif et les remboursements comme des flux négatifs. Sans ce signe, vous obtiendrez une mensualité négative, ce qui n’est pas une erreur de calcul mais une convention de signe.

Les formules d’un tableau d’amortissement sous Excel, colonne par colonne

Voici les formules de la première ligne d’échéance, en supposant que le tableau commence en ligne 9 et que le capital initial figure en E8.

Colonne Formule (ligne 9) Rôle
A : échéance =A8+1 Numéro du mois
B : mensualité =$B$5 Montant constant hors assurance
C : intérêts =E8*$B$2/12 Intérêts sur le capital restant dû
D : capital amorti =B9-C9 Part qui réduit la dette
E : capital restant dû =E8-D9 Solde après l’échéance

Si vous préférez les fonctions financières natives, =-INTPER($B$2/12;A9;$B$4;$B$1) donne les intérêts du mois et =-PRINCPER($B$2/12;A9;$B$4;$B$1) la part de capital. Les deux approches doivent aboutir au même résultat au centime près, ce qui constitue un bon test de cohérence. Pour comprendre d’où vient la mensualité, l’article sur Quelle formule mathématique permet de calculer le taux d’intérêt annuel détaille la logique mathématique qu’Excel applique en coulisses.

Exemple chiffré : 200 000 € sur 20 ans à 3,5 %

Prenons un prêt immobilier de 200 000 € à 3,5 % sur 240 mois, assurance exclue. La mensualité s’établit autour de 1 159,92 €. Voici les premières lignes que doit afficher votre calculateur de prêt maison.

Échéance Mensualité Intérêts Capital amorti Capital restant dû
1 1 159,92 € 583,33 € 576,59 € 199 423,41 €
2 1 159,92 € 581,65 € 578,27 € 198 845,14 €
3 1 159,92 € 579,96 € 579,96 € 198 265,18 €

Le coût total des intérêts approche 78 400 € sur la durée. On observe aussi le mécanisme central de l’amortissement : en début de prêt, les intérêts pèsent presque la moitié de la mensualité, puis la part de capital grossit lentement. C’est la raison pour laquelle un remboursement anticipé réalisé tôt coûte moins cher en intérêts qu’un remboursement tardif.

Pour vous entraîner sur un cas plus petit avant de passer à l’immobilier, vous pouvez reproduire les chiffres de la page Prêt 10000 euros sur 5 ans et comparer votre tableau à ces résultats.

Modèle Excel à télécharger ou fichier maison : que choisir ?

Les modèles gratuits proposés par les courtiers, les éditeurs de logiciels ou Microsoft offrent un gain de temps réel : mise en forme, graphiques, parfois gestion des mensualités variables. Leur limite tient à la lisibilité des formules. Un fichier téléchargé dont vous ne comprenez pas les cellules ne permet pas de repérer une erreur, et certains modèles anciens ne gèrent ni les taux révisables ni les dates réelles.

Un fichier construit par vos soins est plus modeste, mais vous maîtrisez chaque ligne. Une solution raisonnable consiste à bâtir votre propre version, puis à la confronter à un modèle téléchargé : si les deux affichent le même coût total, vous pouvez avoir confiance. Le fichier fonctionne aussi dans LibreOffice et Google Sheets, car VPM, INTPER et PRINCPER y existent sous des noms proches (PMT, IPMT et PPMT en version anglaise).

Cas particuliers d’un prêt immobilier : différé, taux variable, remboursement anticipé

La grille standard suppose un taux fixe et une mensualité constante. Plusieurs situations demandent de l’adapter.

  • Différé partiel : pendant la période de différé, seuls les intérêts sont payés. Le capital restant dû ne bouge pas, puis la mensualité est recalculée sur la durée restante.
  • Différé total : les intérêts s’ajoutent au capital. Ajoutez une colonne d’intérêts capitalisés pour garder une trace fidèle.
  • Taux variable ou révisable : placez le taux dans une colonne dédiée et recalculez la mensualité à chaque révision, avec le capital restant dû et le nombre de mois restants.
  • Remboursement anticipé : soustrayez le montant versé du capital restant dû à la date voulue, puis choisissez entre réduire la durée ou la mensualité.

Pour confronter votre simulation à une proposition bancaire, la page sur les Taux de crédit immobilier actuels explique comment lire une offre de prêt et distinguer taux nominal, TAEG et frais annexes. Si l’emprunt concerne un véhicule, le raisonnement reste le même, avec un niveau de taux pret auto généralement plus élevé et une durée plus courte.

Intégrer l’assurance emprunteur dans le tableau

Le tableau construit plus haut ne représente que le remboursement du crédit. Or l’assurance emprunteur s’ajoute à la mensualité réelle, et elle pèse sur le coût total. Deux méthodes existent selon le contrat.

Si la cotisation est calculée sur le capital initial, elle est constante : ajoutez une colonne « assurance » égale au capital emprunté × taux d’assurance ÷ 12. Si elle porte sur le capital restant dû, elle diminue au fil du temps : la formule devient capital restant dû de la ligne précédente × taux ÷ 12. Dans les deux cas, additionnez ensuite mensualité et assurance pour obtenir la sortie d’argent mensuelle réelle. Cette colonne permet aussi de comparer un contrat de groupe à une délégation d’assurance, et de chiffrer l’économie possible sur toute la durée du financement.

Les intérêts figurent dans votre tableau, mais leur traitement fiscal dépend de votre situation. Pour savoir si c’est envisageable dans votre cas, consultez l’article Peut-on déduire les intérêts d’emprunt pour une résidence principale, avant de présumer d’un avantage.

Erreurs fréquentes dans un tableau d’amortissement Excel

  • Taux annuel non converti : oublier de diviser par 12 donne une mensualité absurde. Le taux mensuel proportionnel suffit pour un prêt classique, mais confrontez-le à l’offre de prêt.
  • Durée saisie en années dans la fonction VPM : la fonction attend un nombre de périodes, donc des mois si le taux est mensuel.
  • Références non figées : sans le signe $ devant les cellules de paramètres, la recopie vers le bas décale les formules.
  • Solde final non nul : un reste de quelques centimes provient d’arrondis. Un écart de plusieurs euros signale une erreur de formule.
  • Assurance et frais oubliés : comparer votre tableau à la mensualité annoncée par la banque sans ajouter l’assurance crée un faux écart.
  • Date de première échéance ignorée : si le déblocage des fonds est échelonné, les intérêts intercalaires des premiers mois ne figurent pas dans une grille standard.

Une dernière habitude utile : verrouillez les cellules de formules et ne modifiez que les trois paramètres. Vous évitez ainsi d’écraser une formule par une valeur saisie à la main, source classique d’écarts invisibles. Gardez enfin le tableau officiel de la banque comme référence : le vôtre sert à comprendre et à simuler, le sien fait foi pour le contrat.

Quelle fonction Excel calcule la mensualité d’un emprunt ?

La fonction VPM calcule la mensualité à partir du taux périodique, du nombre d’échéances et du montant emprunté. Placez un signe moins devant pour obtenir un résultat positif, par exemple =-VPM(3,5%/12;240;200000).

Peut-on faire un tableau d’amortissement sans macro ?

Oui. Quelques formules recopiées vers le bas suffisent : intérêts, capital amorti et capital restant dû. Aucune macro n’est nécessaire, et le fichier reste compatible avec LibreOffice et Google Sheets.

Pourquoi mon tableau ne retombe-t-il pas exactement sur zéro ?

Des écarts de quelques centimes proviennent des arrondis, surtout si vous arrondissez chaque ligne. Si l’écart est plus important, vérifiez la conversion du taux en mensuel, le nombre d’échéances et les références figées par le signe $.

Comment simuler un remboursement anticipé dans Excel ?

Ajoutez une colonne de versement exceptionnel et déduisez-la du capital restant dû à la date choisie. Ensuite, recalculez la mensualité sur la durée restante pour réduire la mensualité, ou conservez-la pour raccourcir la durée du prêt.

Le tableau Excel remplace-t-il celui de la banque ?

Non. Il sert à simuler, comparer et contrôler. Le tableau remis avec l’offre de prêt reste la référence contractuelle, car il intègre les frais, les dates réelles et les éventuelles particularités du contrat.

Vous aimerez aussi