Construire un tableau d’amortissement d’emprunt dans Excel

Quand vous contractez un emprunt, la banque vous remet un tableau d’amortissement. Mais savez-vous le reconstruire vous-même ? C’est essentiel pour comprendre ce que vous payez réellement, comparer deux offres, ou simuler un projet avant de signer. Le tableau d’amortissement décompose chaque mensualité en deux parts : les intérêts et le capital remboursé. Excel le construit parfaitement grâce à quelques fonctions financières. Suivez le guide.

Que vous financiez un véhicule utilitaire, du matériel professionnel ou un local, l’enjeu est toujours le même : savoir, échéance après échéance, combien part en intérêts et combien réduit réellement votre dette. Un prêt de 30 000 € à 4,5 % sur 5 ans ne coûte pas seulement « 4,5 % » : il coûte plusieurs centaines d’euros d’intérêts chaque trimestre au début, puis de moins en moins. Reconstruire le tableau dans Excel vous donne ce niveau de lecture, vous permet de tester l’impact d’un apport supplémentaire, d’un allongement de durée ou d’un remboursement anticipé, et de présenter à votre banquier des hypothèses chiffrées plutôt que des impressions. Dans ce guide, je travaille avec des dates au format jj/mm/aaaa et des montants en euros, comme dans vos écritures comptables.

Le principe d’un emprunt amortissable

Dans un prêt classique à mensualités constantes, vous payez chaque mois la même somme. Mais sa composition évolue : au début, la part d’intérêts est forte et la part de capital faible ; au fil du temps, le rapport s’inverse. À la fin, le capital restant dû est nul. Le tableau d’amortissement retrace mois par mois cette mécanique.

Concrètement, chaque échéance applique le taux mensuel au capital encore dû. Comme ce capital diminue à chaque versement, les intérêts diminuent eux aussi, mécaniquement, mois après mois. La mensualité restant fixe, c’est la part de capital remboursé qui grossit d’autant. Sur notre exemple de 30 000 € à 4,5 %, la toute première échéance ne contient que 112,50 € d’intérêts (soit 30 000 € × 0,375 % de taux mensuel) ; douze mois plus tard, ce montant a déjà nettement baissé. Comprendre ce glissement est la clé pour juger l’intérêt d’un remboursement anticipé : plus vous remboursez tôt, plus vous économisez d’intérêts, car vous attaquez la dette quand elle est encore élevée.

Étape 1 : poser les paramètres de l’emprunt

Pour des mensualités, on travaille avec le taux mensuel (=B2/12) et le nombre de mensualités (=B3*12), soit 60 échéances ici.

Étape 2 : calculer la mensualité avec PMT

La fonction reine du calcul d’emprunt est =PMT(...). Elle renvoie la mensualité constante :

=PMT(B2/12, B3*12, B1)

Avec nos paramètres, elle renvoie environ -559,33 €. Le signe négatif indique une sortie d’argent ; pour l’afficher en positif, encadrez par =-PMT(B2/12, B3*12, B1). Notez que =PMT(...) attend les arguments dans l’ordre taux, nombre de périodes, valeur actuelle ; les deux derniers arguments (valeur future et type) sont facultatifs et valent zéro par défaut, ce qui convient à un prêt amortissable classique soldé à zéro en fin de durée.

Si vous souhaitez raisonner en mensualité « tout compris » assurance incluse, ajoutez la cotisation d’assurance emprunteur à part : pour un taux d’assurance de 0,30 % par an calculé sur le capital initial, comptez =B1*0,30%/12, soit 7,50 € par mois ici. La mensualité réelle prélevée passe alors à environ 566,83 €. C’est ce montant assurance comprise qu’il faut rapprocher de votre capacité de remboursement dans le plan de trésorerie.

Étape 3 : décomposer intérêts et capital

Excel offre deux fonctions dédiées pour chaque échéance. La part d’intérêts de la période n se calcule avec =IPMT(...) et la part de capital avec =PPMT(...). Pour la première mensualité (période 1 en A6) :

=-IPMT($B$2/12, A6, $B$3*12, $B$1)

=-PPMT($B$2/12, A6, $B$3*12, $B$1)

En recopiant ces formules sur 60 lignes (avec les numéros de période de 1 à 60), vous obtenez la ventilation complète. La somme des deux parts égale toujours la mensualité.

Étape 4 : suivre le capital restant dû

Le capital restant dû après chaque échéance est le précédent moins le capital remboursé du mois. Pour la première ligne :

=B1-PPMTligne

Puis pour les lignes suivantes : =CapitalRestantPrecedent-CapitalRembourse. À la dernière échéance, ce solde doit tomber à zéro (à quelques centimes d’arrondi près).

Exemple : premières lignes du tableau

On voit nettement la part d’intérêts décroître (de 112,50 € à 105,76 € en cinq mois) pendant que la part de capital augmente d’autant. Sur la première année, vous aurez remboursé un peu plus de 5 500 € de capital, et le solde restant dû tournera autour de 24 400 €.

Étape 5 : calculer le coût total du crédit

Le coût total des intérêts est la somme de la colonne intérêts, ou plus simplement la somme des mensualités moins le capital emprunté :

=(-PMT(B2/12, B3*12, B1)*B3*12)-B1

Ici, environ 3 559 € d’intérêts sur 30 000 € empruntés. C’est ce chiffre qu’il faut comparer entre deux offres, et non le seul taux affiché. Ajoutez-y les frais de dossier et le coût total de l’assurance (0,30 % sur 5 ans représente ici 450 €) pour obtenir le coût réel du financement, celui qui se rapproche du TAEG communiqué par la banque.

Cas pratique : comparer deux offres de financement

Imaginons que vous deviez financer un véhicule utilitaire à 30 000 €. La banque A propose 4,5 % sur 5 ans, la banque B propose 4,1 % mais sur 6 ans avec 300 € de frais de dossier. Le taux de B est plus bas, mais sa durée est plus longue : laquelle coûte le moins cher au total ? Excel tranche en une formule.

Pour chaque offre, calculez la mensualité avec =-PMT(Taux/12, Duree*12, Capital), puis le coût total des intérêts avec =(-PMT(Taux/12, Duree*12, Capital)*Duree*12)-Capital. Ajoutez les frais de dossier au résultat de B. Vous pouvez automatiser le verdict avec une condition lisible :

=IF(CoutTotalA < CoutTotalB, "Offre A moins chère", "Offre B moins chère")

Le résultat surprend souvent : malgré son taux affiché plus élevé, l’offre A sur 5 ans coûte moins d’intérêts au total que l’offre B sur 6 ans, car la durée pèse davantage que le dixième de point de taux. La mensualité de A est en revanche plus élevée (environ 559 € contre 470 €), ce qui se gère dans le plan de trésorerie. Tout l’intérêt du tableau Excel est là : il sépare le coût total du crédit, qui dépend de la durée, et la pression mensuelle sur la trésorerie, qui dépend de la mensualité.

Automatiser et simuler avec une zone de paramètres

Pour transformer votre tableau en véritable simulateur, isolez les paramètres (capital, taux, durée, assurance) dans des cellules nommées en haut de la feuille, et référencez-les partout en absolu avec $B$1, $B$2, $B$3. Vous changez une valeur, et tout le tableau se recalcule instantanément, mensualité, ventilation et coût total compris.

Sécurisez aussi le capital restant dû avec =IFERROR(...) pour éviter qu’une cellule vide ou un paramètre incohérent n’affiche une erreur disgracieuse, et arrondissez les colonnes monétaires au centime avec =ROUND(montant, 2) afin que la somme des arrondis colle au total. Pour modéliser un remboursement anticipé au 24e mois, par exemple un versement exceptionnel de 5 000 € au 15/03/2027, réduisez d’autant le capital restant dû sur la ligne concernée, puis recalculez la mensualité des échéances suivantes avec =-PMT($B$2/12, PeriodesRestantes, NouveauCapitalRestant) si vous gardez la durée, ou laissez la mensualité inchangée et raccourcissez simplement la durée si vous préférez payer plus vite. Comparez les deux scénarios : conserver la mensualité et réduire la durée maximise toujours l’économie d’intérêts.

Erreurs courantes

• Oublier de diviser le taux par 12. Utiliser le taux annuel pour des mensualités multiplie les intérêts par douze. Toujours =Taux/12 pour un calcul mensuel.
• Mélanger durée en années et en mois. Le nombre de périodes de =PMT(...) doit être en mois si le taux est mensuel. Restez cohérent.
• Ignorer l’assurance emprunteur. Le tableau bancaire intègre souvent une cotisation d’assurance qui alourdit la mensualité réelle. Ajoutez-la pour comparer ce qui est comparable.
• Comparer deux prêts sur le seul taux. Frais de dossier, assurance et durée changent le coût total. Comparez le coût total du crédit, pas seulement le taux nominal.
• Recopier les formules sans figer les références. Les paramètres (capital, taux, durée) doivent être en référence absolue ($B$1) pour que la recopie sur 60 lignes ne décale pas les cellules. Sinon, le tableau se décompose à mi-parcours.

Synthèse comparative des deux offres

Conclusion : l’offre A, pourtant affichée à un taux plus élevé, revient moins chère de près de 600 € au total. Seule la mensualité, plus lourde de 89 € par mois, doit être validée au regard de votre trésorerie.

Pour aller plus loin

Pour replacer la mensualité dans votre pilotage global, reportez-la dans votre plan de trésorerie mensuel et vérifiez sa cohérence avec votre budget prévisionnel annuel.

Questions fréquentes

Quelle différence entre le TAEG et le taux nominal ?

Le taux nominal sert au calcul des intérêts purs, c’est lui que vous saisissez dans =PMT(...). Le TAEG (taux annuel effectif global) intègre en plus les frais de dossier, l’assurance emprunteur et les garanties : c’est l’indicateur légal pour comparer le coût réel de deux crédits. Dans notre cas pratique, l’offre B avait le meilleur taux nominal mais pas forcément le meilleur TAEG, une fois ses 300 € de frais et sa durée plus longue intégrés. Fiez-vous toujours au TAEG et au coût total en euros, jamais au seul taux nominal mis en avant dans la publicité.

Peut-on modéliser un remboursement anticipé dans Excel ?

Oui. Il suffit de réduire le capital restant dû à la date du remboursement partiel, puis de recalculer la mensualité avec =PMT(...) sur le nouveau capital et la durée restante. Pensez aux éventuelles indemnités de remboursement anticipé.

La fonction PMT fonctionne-t-elle pour un prêt in fine ?

Pour un prêt in fine, le capital n’est remboursé qu’à l’échéance : seules les intérêts sont payés chaque période. On utilise alors directement =Capital*TauxMensuel pour la mensualité d’intérêts, et le capital sort en une fois à la fin.

Pourquoi mon capital restant dû ne tombe pas exactement à zéro ?

C’est un effet d’arrondi sur les centimes. La dernière échéance est généralement ajustée de quelques centimes par la banque pour solder exactement le prêt. Un écart inférieur à un euro est normal. Si vous voulez forcer un solde exact à zéro dans Excel, calculez la dernière mensualité comme le capital restant dû de l’avant-dernière ligne augmenté de ses intérêts, plutôt que de recopier =PMT(...), et arrondissez chaque ligne avec =ROUND(montant, 2) pour que les centimes ne s’accumulent pas.