L’essentiel
- Formule Excel pour la mensualité d’un prêt :
=PMT(taux/12; durée*12; -capital)— fonction native VPM en français. - Capital remboursé à l’échéance n :
=PPMT(taux/12; n; durée*12; -capital)(PRINCPER en français). - Intérêts payés à l’échéance n :
=IPMT(taux/12; n; durée*12; -capital)(INTPER en français). - Cas pratique : 200 000 € sur 25 ans à 3,80 % → mensualité 1 035 € hors assurance.
- Assurance emprunteur : à ajouter dans une colonne dédiée, au taux fixe sur le capital initial (groupe) ou décroissant sur le capital restant dû (déléguée).
- Excel ou calculateur en ligne ? Excel pour la flexibilité et l’archivage, un calculateur en ligne pour la rapidité et la fiabilité.
Vocabulaire des fonctions financières d’Excel
Excel et LibreOffice Calc utilisent un vocabulaire spécifique pour les fonctions de calcul de prêt. Voici les termes que vous croiserez dans toute formule d’amortissement.
- PMT (anglais) / VPM (français) — fonction qui calcule la mensualité totale (capital + intérêts) à partir du taux périodique, du nombre d’échéances et du capital. Standard pour les prêts à mensualités constantes.
- IPMT (anglais) / INTPER (français) — fonction qui calcule la part d’intérêts d’une échéance donnée. Prend un argument supplémentaire : le numéro de la période.
- PPMT (anglais) / PRINCPER (français) — fonction qui calcule la part de capital remboursé à une échéance donnée. Complémentaire d’IPMT : PPMT + IPMT = PMT.
- NPER (anglais) / NPM (français) — fonction qui calcule le nombre de périodes nécessaires pour rembourser un prêt à une mensualité fixe.
- RATE (anglais) / TAUX (français) — fonction qui calcule le taux d’intérêt périodique d’un prêt à partir des autres paramètres.
- PV (anglais) / VA (français) — Present Value / Valeur Actuelle, le capital initial dans la nomenclature financière Excel.
- FV (anglais) / VC (français) — Future Value / Valeur Capitalisée, le capital final (généralement zéro pour un prêt amortissable).
- Taux périodique — taux d’intérêt sur la période de remboursement (mois, trimestre). Pour un prêt mensuel à 3,80 % annuel : taux périodique = 3,80 % / 12.
- Convention de signe — Excel inverse les flux : un capital reçu est positif, une mensualité versée est négative. D’où le signe moins (
-capital) dans la formule pour obtenir un résultat positif. - Référence absolue — utilisation des dollars (
$B$1) pour figer une cellule lors du tirage d’une formule. Indispensable pour les paramètres fixes du prêt. - Cumul amortissement — somme des capitaux amortis depuis le début, calculée via
SOMME($D$8:D8)dans une colonne dédiée. - Restant dû (CRD) — capital restant à rembourser, calculé en cascade :
=$B$1 - SOMME($D$8:D8). Doit tomber à zéro à la dernière échéance.
À quoi sert un tableau d’amortissement de prêt sur Excel
Un tableau d’amortissement de prêt détaille, mois après mois, comment se rembourse un crédit : quelle part de la mensualité va au remboursement du capital, quelle part part en intérêts, et combien il reste à rembourser à chaque échéance.
Excel est l’outil de référence pour générer ce tableau parce qu’il permet d’ajuster instantanément durée, taux ou capital et de visualiser l’impact ligne par ligne. Cet article donne les formules exactes et un modèle Excel prêt à l’emploi pour le tableau d’amortissement d’un prêt immobilier ou consommation.
Si vous voulez le résultat sans recopier de formule, le calculateur Amortia sort le tableau complet, l’assurance et le coût total en moins d’une seconde.
Les trois fonctions Excel à connaître
Excel propose trois fonctions natives pour amortir un prêt à mensualités constantes (cas classique des prêts immobiliers en France) :
| Fonction | Nom français | Rôle |
|---|---|---|
PMT |
VPM |
Calcule la mensualité totale (capital + intérêts) |
IPMT |
INTPER |
Calcule la part d’intérêts d’une échéance donnée |
PPMT |
PRINCPER |
Calcule la part de capital remboursé d’une échéance donnée |
La somme PPMT(n) + IPMT(n) doit toujours être strictement égale à la mensualité PMT pour la même période. Cette identité est utile comme contrôle de cohérence des formules quand vous bâtissez le tableau.
La formule de base
Pour un prêt de 200 000 € sur 25 ans à 3,80 % :
| Cellule | Contenu | Valeur |
|---|---|---|
B1 |
Capital | 200000 |
B2 |
Taux annuel | 3,80% |
B3 |
Durée (années) | 25 |
B4 |
Mensualité | =PMT(B2/12; B3*12; -B1) → 1 035 € |
Le signe moins devant B1 permet d’obtenir un résultat positif (Excel inverse la convention de signe pour les flux financiers : un capital reçu est positif, une mensualité versée est négative — on inverse pour afficher le résultat tel qu’on l’attend).
Le tableau d’amortissement complet
À partir de la cellule A7, créez les colonnes : N°, Date, Mensualité, Capital, Intérêts, Restant dû. Pour la première ligne (n = 1, soit la cellule A8) :
| Colonne | Formule |
|---|---|
| Mensualité | =$B$4 |
| Capital remboursé | =PPMT($B$2/12; A8; $B$3*12; -$B$1) |
| Intérêts payés | =IPMT($B$2/12; A8; $B$3*12; -$B$1) |
| Restant dû | =$B$1 - SOMME($D$8:D8) |
Tirez les formules vers le bas sur 300 lignes (25 × 12 = 300 mensualités). Le restant dû à la dernière ligne doit être proche de zéro (à l’arrondi près — voir la section pièges).
Ajouter l’assurance emprunteur
Pour intégrer l’assurance « groupe » calculée sur le capital initial, ajoutez une colonne :
Prime mensuelle =
=$B$1 * taux_assurance / 12
Pour 200 000 € à 0,34 % d’assurance, ça donne 57 €/mois. La mensualité totale (avec assurance) = mensualité PMT + prime. Sur 25 ans, ça fait 17 000 € d’assurance en plus du capital et des intérêts.
En assurance déléguée (assurance externe à la banque, autorisée par les lois Lagarde puis Lemoine), la prime peut se calculer sur le capital restant dû et donc décroître au fil du remboursement. La formule devient :
Prime mensuelle (déléguée) =
=F8 * taux_assurance / 12
où F8 est la cellule du capital restant dû à l’échéance n. Le coût réel est alors typiquement 30 à 50 % moins cher pour les bons profils — un levier d’économie qu’il est utile de savoir simuler.
Pour un calcul exact incluant assurance et tableau complet sans formule à recopier, le calculateur Amortia vous donne le résultat en moins d’une seconde.
Modèle Excel structuré : la feuille type
Voici la structure d’une feuille de travail propre, copier-collable :
| Cellule | Libellé | Valeur ou formule |
|---|---|---|
B1 |
Capital emprunté | 200 000 |
B2 |
Taux nominal annuel | 3,80% |
B3 |
Durée en années | 25 |
B4 |
Taux d’assurance (capital initial) | 0,34% |
B5 |
Mensualité hors assurance | =PMT(B2/12; B3*12; -B1) |
B6 |
Prime mensuelle assurance | =B1*B4/12 |
B7 |
Mensualité totale | =B5+B6 |
B8 |
Total remboursé hors assurance | =B5*B3*12 |
B9 |
Coût total des intérêts | =B8-B1 |
B10 |
Coût total assurance (sur durée) | =B6*B3*12 |
B11 |
Coût total du crédit | =B9+B10 |
Sous cette zone de paramètres, tirez le tableau ligne à ligne avec les formules PPMT, IPMT et le restant dû calculé en cascade. Le bloc B5–B11 vous donne en un coup d’œil tout ce qui compte économiquement.
Les pièges fréquents avec Excel
1. Oublier le signe négatif devant le capital dans PMT. Si vous écrivez =PMT(B2/12; B3*12; B1) au lieu de =PMT(B2/12; B3*12; -B1), Excel renvoie un résultat négatif. Ce n’est pas une erreur de calcul mais de convention de signe.
2. Confondre taux annuel et taux mensuel. PMT, IPMT et PPMT veulent le taux périodique, pas annuel. Pour un prêt mensuel à 3,80 % annuel, il faut écrire B2/12 partout, sinon les valeurs sont absurdes (Excel interprète 3,80 % comme un taux mensuel et calcule une mensualité monstrueuse).
3. Oublier le caractère absolu des références. Si vos formules sont en =PPMT(B2/12; A8; B3*12; -B1) (sans $), tirer la formule vers le bas décale aussi les références aux paramètres. Utilisez systématiquement $B$2, $B$3, $B$1 pour les paramètres fixes.
4. Arrondir les colonnes intermédiaires. Si vous arrondissez à l’euro la mensualité puis recalculez l’intérêt à partir de cette valeur arrondie, vous accumulez des erreurs sur 300 lignes. Gardez les valeurs exactes (centimes) dans les colonnes intermédiaires, arrondissez seulement à l’affichage.
5. Calculer l’assurance sur le capital final au lieu du capital initial (assurance groupe). En assurance « groupe », la prime se calcule sur le capital initial pendant toute la durée — vous payez 57 €/mois même quand il ne reste plus que 50 000 € à rembourser. En assurance déléguée, c’est l’inverse, la prime décroît avec le capital restant dû. Mélanger les deux logiques fausse le calcul.
6. Oublier les frais annexes pour le coût réel. Frais de dossier (500 à 1 500 €), garantie (1 à 2 % du capital pour la caution Crédit Logement), frais de notaire si financés. Ces postes ne rentrent pas dans PMT mais doivent être ajoutés au coût total pour obtenir le vrai TAEG comparable entre deux offres.
Excel ou calculateur en ligne ?
Excel a trois forces : flexibilité totale (vous pouvez ajouter une colonne pour des frais ponctuels, simuler un remboursement anticipé, brancher des graphiques), intégration dans une feuille de pilotage budgétaire personnel, et archivage dans le cloud OneDrive ou Google Drive partageable avec votre courtier.
Un calculateur en ligne a aussi trois forces : rapidité (résultat en moins d’une seconde, sans recopier de formules), fiabilité (zéro risque d’erreur de formule ou de référence), et affichage immédiat (vous voyez le coût total et le tableau dès la saisie).
Notre recommandation : calculateur pour la simulation, Excel pour l’archivage.
- Pour un calcul rapide d’un prêt immobilier : calculateur Amortia — mensualité + tableau complet en moins d’une seconde.
- Pour un dossier comparatif de plusieurs offres bancaires : Excel avec les formules ci-dessus, une feuille par offre, une feuille « synthèse ».
Questions fréquentes
Quelle formule Excel pour calculer la mensualité d’un prêt ?
La fonction native est =PMT(taux/12 ; durée*12 ; -capital) en anglais ou =VPM(taux/12 ; durée*12 ; -capital) en français. Le signe moins devant le capital permet d’obtenir un résultat positif. Pour 200 000 € sur 25 ans à 3,80 %, la formule est =VPM(3,80%/12 ; 300 ; -200000) et donne 1 035 €.
Comment séparer capital et intérêts dans Excel ?
Avec IPMT (INTPER) pour les intérêts d’une échéance donnée et PPMT (PRINCPER) pour le capital remboursé. Les deux fonctions partagent la même syntaxe : taux périodique, numéro d’échéance, nombre total d’échéances, capital. La somme PPMT + IPMT pour une période donnée doit être égale à PMT.
Le tableau Excel gère-t-il l’assurance emprunteur ?
Pas nativement — il faut ajouter une colonne avec la prime mensuelle, calculée comme =capital_initial * taux_assurance / 12 pour une assurance « groupe » ou =capital_restant_dû * taux_assurance / 12 pour une assurance « déléguée » (cellule à mettre à jour ligne par ligne en référençant la colonne du restant dû).
Pourquoi mon tableau d’amortissement ne tombe pas exactement à zéro à la fin ?
À cause des arrondis cumulés. Sur 300 mensualités, un arrondi de 0,01 € à chaque ligne peut donner un écart de 1 à 3 € sur le restant dû final. La banque ajuste la dernière mensualité pour que le solde soit exactement zéro. Dans Excel, faites pareil : =$B$1 - SOMME($D$8:D306) sur la dernière ligne pour solder.
Comment simuler un remboursement anticipé dans Excel ?
Ajoutez une colonne « Remboursement anticipé » que vous remplissez à la ligne souhaitée. Modifiez la formule du restant dû pour qu’elle soustraie aussi cette colonne. À la ligne suivante, recalculez PPMT et IPMT sur le nouveau capital restant comme s’il s’agissait d’un prêt qui démarrait à zéro pour la durée restante. Plus simple : utiliser un calculateur dédié.
Excel propose-t-il une fonction unique pour générer tout le tableau d’amortissement ?
Non. Il faut composer le tableau ligne par ligne avec PPMT, IPMT et une formule de restant dû. Les fonctions financières d’Excel calculent une seule échéance à la fois — pas de sortie en bloc comme dans un logiciel dédié. C’est pour ça qu’un calculateur en ligne va plus vite si vous n’avez pas déjà le modèle.
Existe-t-il un modèle Excel téléchargeable Amortia ?
Un modèle Excel prêt à l’emploi pour le prêt immobilier sera proposé en téléchargement gratuit dans une prochaine mise à jour de cet article. En attendant, les formules ci-dessus sont copier-collables directement dans une feuille vierge.
Quelle est la différence entre PMT et VPM dans Excel ?
Aucune — ce sont les mêmes fonctions. PMT est le nom anglais (Payment), VPM le nom français (Valeur de Paiement Mensuel). Excel les reconnaît selon la langue de l’interface. Les arguments sont identiques : taux périodique, nombre de périodes, capital. Idem pour IPMT/INTPER (intérêts) et PPMT/PRINCPER (principal).
Comment calculer la mensualité d’un prêt sur Excel sans formule ?
Si vous ne voulez pas écrire la formule mathématique des annuités constantes K × [t / (1 − (1 + t)⁻ⁿ)] à la main, utilisez la fonction native =PMT(taux/12; durée*12; -capital). Excel applique automatiquement la formule des annuités constantes en interne. Pour 200 000 € sur 25 ans à 3,80 % : =PMT(3,80%/12; 300; -200000) retourne 1 035 €.
Pourquoi mon résultat PMT est négatif ?
C’est la convention de signe d’Excel qui veut que les flux entrants (capital reçu de la banque) soient positifs et les flux sortants (mensualités versées) négatifs. Pour obtenir un résultat positif, mettez le capital en négatif dans la formule : =PMT(taux/12; durée*12; -B1) au lieu de =PMT(taux/12; durée*12; B1). C’est le piège n°1 des débutants.
Comment ajouter l’assurance emprunteur dans le tableau Excel ?
Excel ne gère pas l’assurance nativement. Ajoutez une colonne dédiée avec la formule =$B$1 * taux_assurance / 12 pour une assurance « groupe » (calculée sur le capital initial constant), ou =F8 * taux_assurance / 12 pour une assurance « déléguée » (calculée sur le capital restant dû qui décroît). La mensualité totale = mensualité PMT + prime d’assurance.
Combien de lignes pour un tableau d’amortissement de prêt immobilier ?
Une ligne par mensualité. Pour un prêt de 25 ans, comptez 300 lignes (25 × 12). Pour 20 ans, 240 lignes. Pour 15 ans, 180 lignes. Tirez les formules PPMT et IPMT vers le bas en référençant le numéro d’échéance dans la colonne A. Le capital restant dû à la dernière ligne doit être proche de zéro à l’arrondi près (1-3 € d’écart accumulé sur 300 mensualités, c’est normal).
Comment simuler un remboursement anticipé partiel dans Excel ?
Ajoutez une colonne « Remboursement anticipé » que vous laissez vide sauf à la ligne de l’échéance concernée. Modifiez la formule du capital restant dû pour soustraire aussi cette colonne. À partir de l’échéance suivante, recalculez PPMT et IPMT en référençant le nouveau capital restant comme s’il s’agissait d’un prêt qui démarrait à zéro pour la durée restante. Plus simple : utiliser le calculateur Amortia.
Quelle différence entre VPM et VA dans Excel ?
Deux fonctions financières différentes. VPM (Valeur de Paiement Mensuel, anglais PMT) calcule la mensualité d’un prêt. VA (Valeur Actuelle, anglais PV) calcule le capital initial qu’on peut emprunter à partir d’une mensualité, d’un taux et d’une durée. Utile pour répondre à la question « combien je peux emprunter avec 1 200 € de mensualité ? » : =VA(taux/12; durée*12; -mensualité).
Excel a-t-il une fonction qui génère tout le tableau d’un coup ?
Non, pas nativement. Vous devez composer le tableau ligne par ligne avec PPMT (capital), IPMT (intérêts) et une formule de cascade pour le restant dû. Excel calcule une échéance à la fois. Pour générer un tableau complet en un clic, utilisez un calculateur en ligne dédié comme Amortia, ou écrivez une macro VBA.
Pourquoi mon tableau Excel ne tombe pas à zéro à la dernière ligne ?
Arrondis cumulés. Sur 300 échéances, un arrondi de 0,01 € à chaque ligne accumule un écart de 1 à 3 € sur le capital restant dû final. Solution : sur la dernière ligne, utilisez =$B$1 - SOMME($D$8:D306) pour solder exactement le capital. La banque fait pareil dans la pratique : la dernière mensualité est ajustée de quelques centimes.
Comment calculer la capacité d’emprunt avec Excel ?
Inversez la formule : utilisez VA (Valeur Actuelle) à partir de la mensualité maximale autorisée par votre taux d’endettement (35 % de revenus nets). Pour 4 000 € de revenus nets sur 25 ans à 3,80 % : =VA(3,80%/12; 300; -1400) retourne environ 270 000 € de capital empruntable. Réduisez de l’assurance et des frais pour avoir le capital net.
Aller plus loin
Pour comprendre les concepts derrière les formules :
- Calculer un prêt immobilier : mensualité, intérêts, coût total — guide complet de la formule des annuités constantes appliquée au crédit immobilier, avec cas pratique 200 000 € sur 25 ans.
Pour faire le calcul sans Excel :
- Calculateur de prêt immobilier Amortia — mensualité + tableau complet + assurance en moins d’une seconde, sans inscription.