Excel : créer un plan d’amortissement d’emprunt
Apprenez à créer dans Excel un plan d’amortissement avec VPM, IPMT et PPMT, puis un tableau d’échéances clair et vérifiable.
- Difficulté
- Intermédiaire
Construire un plan d’amortissement dans Excel permet de visualiser chaque échéance d’un emprunt, la part d’intérêts payée et la part de capital remboursée. C’est utile pour contrôler une offre de prêt, préparer une simulation ou expliquer clairement l’évolution d’une dette.
Dans ce tutoriel, vous allez créer une feuille structurée, calculer l’échéance avec VPM, ventiler chaque paiement avec IPMT et PPMT, puis terminer par un tableau d’échéances détaillé. L’objectif est d’obtenir un fichier lisible, recopiable et facile à vérifier.
Prérequis
Vous avez besoin d’Excel avec les fonctions financières classiques disponibles. Il faut aussi connaître les bases suivantes : saisir une formule, utiliser la recopie vers le bas et appliquer un format nombre ou pourcentage.
Le tutoriel reste valable si votre Excel utilise des séparateurs différents selon la langue ou les paramètres régionaux. Selon votre version, les arguments des fonctions peuvent être séparés par des points-virgules au lieu de virgules.
Matériel nécessaire
- Un classeur Excel vide
- Les données du prêt : capital, taux, durée, nombre d’échéances par an
- Les fonctions Excel VPM, IPMT et PPMT
Étapes
Préparer la zone de paramètres du prêt
Ouvrez une feuille Excel vide et réservez le haut de la feuille aux paramètres. Saisissez des libellés clairs dans une première colonne, par exemple : Capital emprunté, Taux annuel, Durée en années et Échéances par an.
Dans la colonne voisine, entrez les valeurs du prêt. Ajoutez ensuite deux cellules de calcul pour le taux par période et le nombre total d’échéances.
Calculez le taux par période en divisant le taux annuel par le nombre d’échéances par an. Calculez ensuite le nombre total d’échéances en multipliant la durée en années par le nombre d’échéances par an.
Calculer l’échéance avec VPM
Ajoutez une ligne nommée Échéance ou Mensualité selon votre cas. Dans la cellule de résultat, saisissez une formule VPM basée sur le taux par période, le nombre total d’échéances et le capital emprunté.
Pour obtenir un montant positif à l’affichage, placez en général le capital avec un signe négatif dans la formule, ou inversement selon votre convention. L’essentiel est de garder la même logique de signe dans tout le tableau.
Vérifiez que le montant renvoyé correspond bien à une échéance constante. Si vous modifiez le taux, la durée ou le capital, la cellule doit se mettre à jour immédiatement.
Créer l’en-tête du tableau d’échéances
Laissez une ou deux lignes vides, puis créez l’en-tête du tableau. Utilisez des colonnes comme Période, Échéance, Intérêts, Principal remboursé et Capital restant dû.
Si vous souhaitez suivre le calendrier réel, ajoutez aussi une colonne Date. Vous pourrez y faire progresser les échéances à partir d’une date de départ, mais ce suivi reste facultatif pour un plan d’amortissement purement financier.
Appliquez un style de tableau simple ou un fond distinct à l’en-tête. La lisibilité est essentielle quand le tableau comporte de nombreuses lignes.
Renseigner la première période
Dans la première ligne du tableau, entrez le numéro de période 1. Dans la colonne Échéance, référencez la cellule contenant le résultat de VPM.
Dans la colonne Intérêts, utilisez la fonction IPMT avec le taux par période, le numéro de période, le nombre total d’échéances et le capital. Dans la colonne Principal remboursé, utilisez PPMT avec les mêmes paramètres de base.
Dans la colonne Capital restant dû, soustrayez le principal remboursé du capital initial. Vous obtenez ainsi le solde après la première échéance.
Recopier les formules sur toutes les échéances
Dans la ligne suivante, saisissez la période 2, puis recopiez les formules de la première ligne vers le bas jusqu’au nombre total d’échéances. La colonne Période doit progresser de 1 en 1.
Pour le capital restant dû, faites en sorte que chaque ligne reparte du solde de la ligne précédente. Vous pouvez soit retrancher le principal remboursé au solde précédent, soit reconstruire le calcul depuis le capital initial si votre logique reste cohérente.
Contrôlez quelques lignes au milieu du tableau. La part d’intérêts doit généralement diminuer au fil des périodes, tandis que la part de principal remboursé augmente dans un prêt à échéance constante.
Ajouter une date d’échéance si nécessaire
Si vous voulez suivre le calendrier du prêt, ajoutez une date de départ au-dessus du tableau. Dans la première ligne, renseignez la première date d’échéance, puis faites progresser les suivantes selon la périodicité du prêt.
Pour un prêt mensuel, incrémentez d’un mois à chaque ligne avec une formule adaptée à votre version d’Excel. Vérifiez visuellement que les dates restent cohérentes, surtout autour des fins de mois.
Placez cette colonne avant le numéro de période si vous voulez un tableau plus proche d’un relevé bancaire. Sinon, gardez-la après pour privilégier la lecture financière.
Formater les montants pour faciliter la lecture
Sélectionnez les colonnes de montants et appliquez un format monétaire ou comptable. Utilisez aussi un nombre raisonnable de décimales pour éviter un tableau trop chargé.
Mettez en évidence la cellule d’échéance, les totaux éventuels et le dernier capital restant dû. Une couleur discrète ou une bordure plus marquée suffit souvent à guider l’œil.
Si votre tableau est long, activez le figement des volets pour garder l’en-tête visible. Cette simple action améliore fortement le confort de lecture.
Vérifier la cohérence des calculs
Contrôlez d’abord que l’échéance est identique sur toutes les lignes, sauf cas particulier prévu par votre modèle. Vérifiez ensuite que, pour chaque période, la somme Intérêts + Principal remboursé correspond à l’échéance.
Descendez à la dernière ligne et observez le capital restant dû. Il doit arriver à zéro ou à une valeur très proche de zéro selon les arrondis affichés.
Ajoutez enfin une ligne de total sous le tableau si vous souhaitez consolider les intérêts payés et le principal remboursé. Ce contrôle final aide à repérer une formule décalée ou une référence mal figée.
Transformer la plage en modèle réutilisable
Quand le tableau fonctionne, conservez la feuille comme modèle. Renommez clairement les zones, ajoutez un titre et laissez la zone de paramètres bien séparée du tableau d’échéances.
Vous pouvez aussi dupliquer la feuille pour comparer plusieurs scénarios : durée plus courte, capital différent ou périodicité modifiée. Le plus important est de ne changer que les cellules de saisie, pas les formules internes.
Si vous travaillez souvent sur ce type de fichier, gardez une version propre sans valeurs sensibles. Vous repartirez plus vite pour une nouvelle simulation.
Erreurs fréquentes & dépannage
L’erreur la plus fréquente consiste à utiliser le taux annuel directement dans VPM, IPMT ou PPMT alors que les paiements sont mensuels. Il faut toujours aligner le taux et le nombre de périodes sur la même fréquence.
Autre piège courant : oublier de figer les références de paramètres avant la recopie. Le tableau semble correct au début, puis dérive à partir de la deuxième ou troisième ligne.
Il arrive aussi que les signes soient inversés entre capital, échéance et ventilation intérêts/principal. Choisissez une convention simple, puis gardez-la partout pour éviter les confusions.
Enfin, ne vous fiez pas seulement à l’apparence. Un tableau bien formaté peut contenir une formule décalée ; vérifiez toujours le dernier solde et la somme des composants de l’échéance.
Astuces & pour aller plus loin
Commencez par un modèle minimal avec cinq colonnes et peu de mise en forme. Une fois les calculs validés, ajoutez le confort visuel, les totaux et la colonne de date.
Si vous devez présenter le fichier à d’autres personnes, ajoutez une petite légende sur la feuille pour rappeler le rôle de VPM, IPMT et PPMT. Cela facilite la relecture sans alourdir le tableau principal.
Pour comparer plusieurs hypothèses, dupliquez la feuille au lieu d’écraser les valeurs d’origine. Vous conserverez un historique simple de vos simulations.
Questions fréquentes
À quoi sert la fonction VPM dans ce plan d’amortissement ?
VPM sert à calculer le montant de l’échéance constante à partir du taux par période, du nombre total d’échéances et du capital emprunté. Elle fournit la base du tableau d’amortissement.
Quelle différence entre IPMT et PPMT ?
IPMT renvoie la part d’intérêts d’une échéance pour une période donnée. PPMT renvoie la part de principal remboursé sur cette même échéance.
Pourquoi mon capital restant dû n’arrive-t-il pas exactement à zéro ?
Un léger écart peut venir des arrondis d’affichage. Vérifiez d’abord les décimales réelles et la cohérence des formules avant de modifier manuellement la dernière ligne.
Dois-je obligatoirement ajouter une colonne de dates ?
Non. La colonne de dates est utile pour le suivi calendaire, mais le calcul financier du plan d’amortissement fonctionne sans elle.
Puis-je réutiliser ce modèle pour des échéances autres que mensuelles ?
Oui, à condition d’adapter le nombre d’échéances par an et de calculer un taux par période cohérent. Le principe du tableau reste le même.
Conclusion
Avec une zone de paramètres claire, une échéance calculée par VPM et une ventilation par IPMT et PPMT, vous obtenez dans Excel un plan d’amortissement solide et lisible. Ce modèle vous permet de suivre chaque échéance, de contrôler le capital restant dû et de réutiliser facilement la feuille pour d’autres simulations.
Prenez surtout le temps de vérifier la cohérence des périodes, des signes et des références avant de diffuser le fichier. Une structure simple et bien contrôlée vaut mieux qu’un tableau complexe difficile à relire.