Tableau d’amortissement d’un emprunt sur Excel : mensualité, assurance, taux d’endettement et remboursement anticipé
Construire un tableau d'amortissement d'emprunt sur Excel : mensualité avec VPM, part d'intérêts et de capital, assurance emprunteur sur capital initial ou restant dû, coût total du crédit, taux d'endettement et simulation d'un remboursement anticipé avec indemnités, à partir d'un prêt immobilier de 200 000 €.
Réponse rapide : un tableau d’amortissement détaille chaque échéance d’un prêt : intérêts, capital remboursé, assurance et capital restant dû. Sur Excel, la mensualité hors assurance d’un prêt à taux fixe se calcule avec :
=VPM(Taux/12;DuréeMois;-Capital)
Chaque ligne applique ensuite : intérêts = capital restant dû × taux/12 ; capital amorti = mensualité − intérêts ; nouveau capital restant dû = ancien − capital amorti. Pour un prêt immobilier de 200 000 € à 3,40 % sur 20 ans, la mensualité hors assurance est de 1 149,67 € et le coût total des intérêts de 75 920,47 €. Ces chiffres sont un exemple pédagogique, pas une offre de prêt.
Étape 1 : les paramètres
| Cellule | Nom | Exemple |
|---|---|---|
| B2 | Capital | 200 000 € |
| B3 | Taux (nominal annuel) | 3,40 % |
| B4 | DuréeMois | 240 |
| B5 | TauxAssurance (annuel) | 0,30 % |
| B6 | BaseAssurance | « initial » ou « restant dû » |
| B7 | PremièreÉchéance | Date |
| B9 | Mensualité | =VPM(Taux/12;DuréeMois;-Capital) |
Le taux à utiliser est le taux nominal du prêt (taux débiteur). Le TAEG, lui, intègre les frais de dossier, de garantie et l’assurance : il sert à comparer les offres, pas à calculer les intérêts de chaque échéance.
Étape 2 : les lignes du tableau
| Colonne | Formule (ligne 12) |
|---|---|
| A N° | 1 puis =SI(H12>0,005;A12+1;"") |
| B Date | =PremièreÉchéance puis =SI(A13="";"";MOIS.DECALER(B12;1)) |
| C Capital restant dû début | =Capital puis =H12 |
| D Intérêts | =ARRONDI(C12*Taux/12;2) |
| E Capital amorti | =MIN(Mensualité-D12;C12) |
| F Assurance | =SI(BaseAssurance="initial";Capital;C12)*TauxAssurance/12 |
| G Échéance totale | =D12+E12+F12 |
| H Capital restant dû fin | =C12-E12 |
Recopiez sur 240 lignes (ou davantage si vous simulez des modifications) en encadrant les formules par SI($A13="";"";…) pour que le tableau s’arrête de lui-même.
Exemple : 200 000 € à 3,40 % sur 20 ans, assurance 0,30 % du capital initial
| N° | Capital restant dû | Intérêts | Capital amorti | Assurance | Échéance |
|---|---|---|---|---|---|
| 1 | 200 000,00 | 566,67 | 583,00 | 50,00 | 1 199,67 |
| … | … | … | … | … | … |
| 120 | — | — | — | 50,00 | 1 199,67 |
| 240 | — | ≈ 3,25 | ≈ 1 146,42 | 50,00 | ≈ 1 199,67 |
- Après la 1re échéance, le capital restant dû est de 199 417,00 €.
- Au bout de 10 ans (120 échéances), il reste encore 116 814,75 € à rembourser, et 54 774,98 € d’intérêts ont déjà été payés.
- Coût total : intérêts 75 920,47 € + assurance 50 € × 240 = 12 000 € = 87 920,47 €, hors frais de dossier et de garantie.
Avec une assurance calculée sur le capital restant dû, la cotisation baisse au fil du temps et son coût total est plus faible, mais le taux appliqué est souvent plus élevé : comparez les deux sur le tableau en changeant la cellule BaseAssurance. Depuis la loi Lemoine, l’assurance emprunteur peut en principe être changée à tout moment ; vérifiez les conditions d’équivalence de garanties avec votre banque.
Étape 3 : taux d’endettement
Les recommandations du Haut Conseil de stabilité financière, appliquées par les banques, plafonnent en règle générale le taux d’endettement à 35 % des revenus nets, assurance comprise, avec une durée de crédit immobilier de 25 ans maximum (des marges de flexibilité existent). Dans Excel :
=(Mensualité+Assurance+AutresCrédits)/RevenusNetsMensuels
Dans l’exemple, une échéance de 1 199,67 € suppose au moins 1 199,67 ÷ 0,35 ≈ 3 428 € de revenus nets mensuels sans autre crédit. Pour vérifier que l’échéance tient dans votre budget réel, reportez-la dans votre budget mensuel sur Excel.
Étape 4 : simuler un remboursement anticipé
Ajoutez une colonne « Remboursement anticipé » et modifiez le capital restant dû fin : =C12-E12-I12. Deux options après un remboursement partiel :
- Réduire la durée en gardant la même mensualité : nombre d’échéances restantes
=NPM(Taux/12;-Mensualité;NouveauCapital). - Réduire la mensualité en gardant la durée :
=VPM(Taux/12;ÉchéancesRestantes;-NouveauCapital).
Indemnités de remboursement anticipé (IRA) : pour un crédit immobilier, la loi les plafonne à six mois d’intérêts sur le capital remboursé au taux moyen du prêt, sans dépasser 3 % du capital restant dû avant le remboursement ; le contrat peut prévoir moins, voire rien. Exemple : rembourser 50 000 € après 10 ans → six mois d’intérêts = 50 000 × 3,40 % ÷ 2 = 850 € ; 3 % du capital restant dû = 3 504,44 €. Le plafond applicable est donc de 850 €. Formule : =MIN(Montant*Taux/2;3%*CapitalRestantDû). Vérifiez toujours votre offre de prêt.
Fonctions utiles pour aller plus vite
- Intérêts d’une échéance donnée :
=INTPER(Taux/12;12;DuréeMois;-Capital) - Capital amorti d’une échéance :
=PRINCPER(Taux/12;12;DuréeMois;-Capital) - Intérêts cumulés d’une période :
=-CUMUL.INTER(Taux/12;DuréeMois;Capital;1;12;0) - Capacité d’emprunt pour une mensualité donnée :
=VA(Taux/12;DuréeMois;-MensualitéMax)
Ces fonctions supposent un échéancier régulier ; dès qu’il y a un différé, un palier ou un remboursement anticipé, le tableau ligne par ligne reste la référence.
Erreurs fréquentes
- Calculer avec le TAEG au lieu du taux nominal.
- Oublier l’assurance dans l’échéance et dans le taux d’endettement.
- Confondre années et mois dans VPM.
- Ignorer les frais (dossier, garantie, courtage) dans le coût total.
- Ne pas tenir compte des IRA avant de décider un remboursement anticipé.
Questions fréquentes
Pourquoi mon tableau diffère-t-il légèrement de celui de la banque ?
Arrondis, date de la première échéance (échéance brisée), calcul des intérêts intercalaires ou de l’assurance : de petits écarts sont normaux. Des écarts importants signalent un paramètre différent.
Comment simuler un prêt avec différé ?
Pendant le différé partiel, l’échéance ne comprend que les intérêts (capital amorti = 0) ; ensuite, recalculez la mensualité avec VPM sur la durée restante.
Cela fonctionne-t-il dans Google Sheets ?
Oui : VPM, INTPER, PRINCPER, NPM, VA et MOIS.DECALER existent en français dans Google Sheets.
Retrouvez nos autres guides dans Dettes et épargne.