Tableau d'amortissement sur Excel : formules et méthode pas à pas
Construire un tableau d'amortissement sur Excel (ou Google Sheets) tient en trois fonctions financières et une poignée de formules recopiées vers le bas. Ce guide donne la méthode exacte, les formules à saisir et un exemple vérifié sur 200 000 € — puis l'alternative qui évite toute erreur de saisie.
Pas envie de manipuler des formules ? Notre générateur de tableau d'amortissement produit l'échéancier complet (mensuel + annuel) avec export PDF, gratuitement et sans inscription.
Les 3 fonctions financières indispensables
Excel et Google Sheets partagent trois fonctions (noms français / anglais) :
| Fonction (FR) | Équivalent (EN) | Ce qu'elle calcule |
|---|---|---|
| VPM | PMT | La mensualité fixe (capital + intérêts) |
| INTPER | IPMT | La part d'intérêts d'une échéance donnée |
| PRINCPER | PPMT | La part de capital d'une échéance donnée |
Toutes attendent un taux mensuel (taux annuel ÷ 12) et un nombre de mensualités (durée en années × 12).
Étape 1 — Calculer la mensualité avec VPM
Pour un prêt de 200 000 € sur 25 ans à 3,40 % :
=VPM(3,4%/12 ; 25*12 ; 200000)
Le résultat est -990,55 €. Excel affiche un montant négatif car il s'agit d'une sortie d'argent : ajoutez un signe moins devant la formule (=-VPM(...)) pour l'afficher en positif. C'est la mensualité hors assurance.
Étape 2 — Poser les colonnes du tableau
Créez cinq colonnes. On suppose la mensualité (990,55 €) en cellule $C$1 et le taux annuel (3,40 %) en $C$2 :
| Colonne | En-tête | Formule (ligne 1 du tableau, ex. ligne 5) |
|---|---|---|
| A | N° d'échéance | 1, puis =A5+1 recopié vers le bas |
| B | Capital restant dû | 200000 en 1ʳᵉ ligne, puis =E5 (CRD fin précédent) |
| C | Intérêts du mois | =B5*$C$2/12 |
| D | Capital amorti | =$C$1-C5 |
| E | CRD fin de mois | =B5-D5 |
Recopiez les lignes 5 vers le bas sur 300 lignes (25 × 12). La logique est celle de tout échéancier de prêt : les intérêts se calculent sur le capital restant dû, et le reste de la mensualité rembourse le capital.
Astuce : les fonctions
=INTPER(3,4%/12 ; A5 ; 300 ; 200000)et=PRINCPER(3,4%/12 ; A5 ; 300 ; 200000)donnent directement les colonnes intérêts et capital sans passer par le CRD — pratique pour vérifier votre tableau. Comme VPM, elles renvoient des montants négatifs : ajoutez un signe moins (=-INTPER(...),=-PRINCPER(...)) pour retrouver les valeurs positives des colonnes de l'étape 2.
Étape 3 — Vérifier les premières lignes
Votre tableau doit reproduire exactement ces valeurs :
| Mois | Capital restant dû | Intérêts | Capital amorti | CRD fin de mois |
|---|---|---|---|---|
| 1 | 200 000,00 € | 566,67 € | 423,88 € | 199 576,12 € |
| 2 | 199 576,12 € | 565,47 € | 425,08 € | 199 151,04 € |
| 3 | 199 151,04 € | 564,26 € | 426,29 € | 198 724,75 € |
Contrôle du mois 1 : intérêts = 200 000 × 3,40 % ÷ 12 = 566,67 € ; capital amorti = 990,55 − 566,67 = 423,88 € ; nouveau CRD = 200 000 − 423,88 = 199 576,12 €. À la 300ᵉ ligne, le capital restant dû doit tomber à 0 € : c'est le test de cohérence du tableau. Sur toute la durée, ce prêt coûte 97 166 € d'intérêts.
Ajouter la colonne assurance
L'assurance se calcule à part, sans passer par VPM. Sur un contrat au capital initial à 0,34 % :
Assurance mensuelle = 200000 * 0,34% / 12 = 56,67 €
Ajoutez une colonne F avec cette valeur constante, puis une colonne « mensualité totale » = =$C$1+56,67, soit 1 047,22 €. Pour un contrat calculé sur le capital restant dû, remplacez la constante par =B5*0,34%/12 : la cotisation devient dégressive. Le détail des deux modes est expliqué dans notre article sur l'assurance dans le tableau d'amortissement.
Les 4 erreurs classiques sur Excel
| Erreur | Conséquence | Correction |
|---|---|---|
| Utiliser le taux annuel dans VPM | Mensualité 12× trop élevée | Diviser le taux par 12 |
| Oublier le signe négatif de VPM | Capital amorti négatif | Saisir =-VPM(...) ou raisonner en valeur absolue |
| Inclure l'assurance dans VPM | Amortissement faussé | Calculer l'assurance dans une colonne à part |
Ne pas figer les références ($) | Formules décalées à la recopie | Verrouiller mensualité et taux avec $C$1, $C$2 |
L'alternative sans formule : le générateur
Un tableur reste manuel : 300 lignes à recopier, des références à figer, aucun export propre. Pour un résultat instantané et sans risque d'erreur :
Générez votre tableau d'amortissement complet avec notre générateur gratuit : saisissez montant, taux, durée et assurance, obtenez l'échéancier mois par mois et année par année, avec export PDF. Pour comparer plusieurs durées ou taux en direct, le simulateur de prêt affiche mensualité et coût total côte à côte.
FAQ
Quelle formule Excel pour calculer un tableau d'amortissement ?
Trois fonctions : VPM (mensualité), INTPER (part d'intérêts d'une échéance) et PRINCPER (part de capital). En anglais : PMT, IPMT et PPMT. Toutes utilisent le taux mensuel (taux annuel ÷ 12) et le nombre total de mensualités (années × 12).
Comment calculer la mensualité d'un prêt sur Excel ?
Avec =VPM(taux annuel/12 ; durée en années*12 ; montant). Pour 200 000 € sur 25 ans à 3,40 % : =VPM(3,4%/12 ; 300 ; 200000) renvoie -990,55 € (hors assurance). Le résultat est négatif car Excel le traite comme une dépense.
Pourquoi mon capital restant dû ne tombe pas à zéro à la fin ?
Presque toujours à cause du taux : VPM attend un taux mensuel. Si vous saisissez le taux annuel sans le diviser par 12, ou si vous arrondissez la mensualité trop tôt, le tableau dérive. Vérifiez que la dernière ligne (mois 300) affiche bien 0 €.
Faut-il inclure l'assurance dans la formule VPM ?
Non. VPM ne calcule que le couple capital + intérêts. L'assurance se calcule séparément (capital × taux d'assurance ÷ 12) dans sa propre colonne, puis s'ajoute pour obtenir la mensualité totale.