Comprendre le tableau d'amortissement de prêt immobilier avec Excel
Un tableau d'amortissement est un document qui détaille le remboursement d'un prêt sur toute sa durée. Il indique, pour chaque échéance, la part du remboursement qui correspond au capital et celle qui correspond aux intérêts. Il permet de visualiser l'évolution du capital restant dû et d'anticiper les coûts totaux du prêt. L'utilisation d'Excel facilite la création et la manipulation de ce tableau, permettant des simulations rapides et précises.
Pourquoi utiliser Excel pour son tableau d'amortissement ?
Excel offre plusieurs avantages pour la création d'un tableau d'amortissement :
- Personnalisation: Vous pouvez adapter le tableau à vos besoins spécifiques, en ajoutant des colonnes pour les assurances, les frais de dossier, etc.
- Simulations: Vous pouvez facilement modifier les paramètres du prêt (taux d'intérêt, durée, montant) pour observer leur impact sur les mensualités et le coût total.
- Visualisation: Excel permet de créer des graphiques pour visualiser l'évolution du capital restant dû et la répartition entre capital et intérêts.
- Automatisation: Une fois le tableau configuré, les calculs se font automatiquement, ce qui vous fait gagner du temps et réduit le risque d'erreurs.
Étape par étape : Création du tableau d'amortissement sur Excel
Voici les étapes à suivre pour créer un tableau d'amortissement de prêt immobilier sur Excel. Nous allons utiliser des formules Excel courantes pour automatiser les calculs.
1. Préparation des données initiales
Avant de commencer, vous devez rassembler les informations suivantes sur votre prêt :
- Montant du prêt (capital initial) : Le montant total emprunté.
- Taux d'intérêt annuel : Le taux d'intérêt nominal annuel.
- Durée du prêt (en années ou en mois) : La période totale de remboursement.
- Nombre de paiements par an : Généralement 12 (mensuel).
Dans votre feuille Excel, créez les cellules suivantes et saisissez les valeurs correspondantes :
| Cellule | Libellé | Exemple de valeur |
|---|---|---|
| B1 | Montant du prêt | 200000 |
| B2 | Taux d'intérêt annuel | 0.035 |
| B3 | Durée du prêt (en années) | 20 |
| B4 | Paiements par an | 12 |
2. Calcul des paramètres dérivés
À partir des données initiales, calculez les paramètres suivants :
- Taux d'intérêt périodique : Taux d'intérêt annuel divisé par le nombre de paiements par an.
- Nombre total de paiements : Durée du prêt en années multipliée par le nombre de paiements par an.
Dans Excel, ajoutez les cellules suivantes et entrez les formules correspondantes :
| Cellule | Libellé | Formule Excel | Exemple de valeur |
|---|---|---|---|
| B5 | Taux d'intérêt périodique | =B2/B4 |
0.002916667 |
| B6 | Nombre total de paiements | =B3*B4 |
240 |
3. Création de l'en-tête du tableau
Créez l'en-tête du tableau d'amortissement avec les colonnes suivantes :
| Colonne | Libellé |
|---|---|
| A | Période |
| B | Paiement |
| C | Intérêts |
| D | Capital remboursé |
| E | Capital restant dû |
Commencez à la ligne 8 de votre feuille Excel.
4. Calcul du paiement périodique (mensualité)
Utilisez la fonction PMT (ou VPM en français) d'Excel pour calculer le montant du paiement périodique. La syntaxe de la fonction est la suivante :
=PMT(taux; npm; va; [vf]; [type])
taux: Taux d'intérêt périodique (B5).npm: Nombre total de paiements (B6).va: Valeur actuelle (montant du prêt) (B1).vf: Valeur future (0 pour un prêt entièrement amorti). Facultatif.type: Indique si le paiement est effectué en début (1) ou en fin (0) de période. Facultatif. Par défaut, il est considéré comme 0.
Dans la cellule B9 (première ligne de données), entrez la formule suivante :
=-PMT(B5;B6;B1)
Le signe négatif devant la fonction PMT est utilisé pour afficher un résultat positif.
5. Remplissage des lignes du tableau
Maintenant, remplissez les lignes du tableau avec les formules suivantes :
- Période (Colonne A) : Commencez à 1 et incrémentez de 1 à chaque ligne. Dans A9, entrez
1. Dans A10, entrez=A9+1et copiez cette formule vers le bas jusqu'à la ligne correspondant au nombre total de paiements. - Intérêts (Colonne C) : Calculez les intérêts payés pour chaque période en multipliant le capital restant dû de la période précédente par le taux d'intérêt périodique. Dans C9, entrez
=E8*B5. Remarque : E8 est le capital restant dû initial (B1). - Capital remboursé (Colonne D) : Calculez la part du paiement qui correspond au capital remboursé en soustrayant les intérêts du paiement total. Dans D9, entrez
=B9-C9. - Capital restant dû (Colonne E) : Calculez le capital restant dû après chaque paiement en soustrayant le capital remboursé du capital restant dû de la période précédente. Dans E9, entrez
=E8-D9. Remarque : E8 est le capital initial (B1).
Maintenant, sélectionnez les cellules C9, D9 et E9, et copiez-les vers le bas jusqu'à la ligne correspondant au nombre total de paiements. La colonne B (Paiement) doit être la même valeur pour chaque ligne (B9). Vous pouvez donc copier la valeur de B9 dans toutes les cellules de la colonne B, ou utiliser une référence absolue (=-PMT($B$5;$B$6;$B$1)) dans B9 et copier cette formule vers le bas.
6. Vérification du tableau
Vérifiez que le capital restant dû à la dernière période est proche de zéro. Il peut y avoir une légère différence due aux arrondis. Si le capital restant dû est significativement différent de zéro, vérifiez vos formules.
Exemple concret
Supposons que vous ayez un prêt de 200 000 € avec un taux d'intérêt annuel de 3,5 % sur 20 ans. Votre tableau d'amortissement ressemblera à ceci (les premières lignes) :
| Période | Paiement (€) | Intérêts (€) | Capital remboursé (€) | Capital restant dû (€) |
|---|---|---|---|---|
| 200000 | ||||
| 1 | 1159.95 | 583.33 | 576.62 | 199423.38 |
| 2 | 1159.95 | 581.65 | 578.30 | 198845.08 |
| 3 | 1159.95 | 579.96 | 579.99 | 198265.09 |
| 4 | 1159.95 | 578.26 | 581.69 | 197683.40 |
Vous pouvez constater que la part des intérêts diminue au fil du temps, tandis que la part du capital remboursé augmente.
Conseils et astuces pour optimiser votre tableau d'amortissement
- Ajouter des colonnes pour les assurances et les frais : Incluez les coûts supplémentaires liés à votre prêt pour avoir une vision complète de vos dépenses.
- Utiliser des mises en forme conditionnelles : Mettez en évidence les périodes où la part des intérêts est la plus importante.
- Créer des scénarios : Simulez l'impact d'un remboursement anticipé ou d'une renégociation de taux.
- Utiliser la fonction
CUMIPMT: Cette fonction permet de calculer le cumul des intérêts payés sur une période donnée. - Utiliser la fonction
CUMPRINC: Cette fonction permet de calculer le cumul du capital remboursé sur une période donnée.
La fonction CUMIPMT pour le cumul des intérêts
La fonction CUMIPMT permet de calculer le cumul des intérêts payés entre deux périodes. Sa syntaxe est :
=CUMIPMT(taux, npm, va, période_début, période_fin, type)
taux: Taux d'intérêt périodique.npm: Nombre total de paiements.va: Valeur actuelle (montant du prêt).période_début: Numéro de la première période à inclure dans le calcul.période_fin: Numéro de la dernière période à inclure dans le calcul.type: Indique si le paiement est effectué en début (1) ou en fin (0) de période.
Par exemple, pour calculer le cumul des intérêts payés pendant les 5 premières années (60 premiers mois) d'un prêt, vous pouvez utiliser la formule suivante :
=CUMIPMT(B5,B6,B1,1,60,0)
La fonction CUMPRINC pour le cumul du capital remboursé
La fonction CUMPRINC permet de calculer le cumul du capital remboursé entre deux périodes. Sa syntaxe est :
=CUMPRINC(taux, npm, va, période_début, période_fin, type)
Les arguments sont les mêmes que pour la fonction CUMIPMT.
Par exemple, pour calculer le cumul du capital remboursé pendant les 5 premières années (60 premiers mois) d'un prêt, vous pouvez utiliser la formule suivante :
=CUMPRINC(B5,B6,B1,1,60,0)
Erreurs à éviter lors de la création de votre tableau
- Oublier de diviser le taux d'intérêt annuel par le nombre de paiements par an : Cela conduira à un calcul incorrect du paiement périodique.
- Ne pas utiliser les références absolues ($) correctement : Cela peut entraîner des erreurs lors de la copie des formules.
- Saisir des données incorrectes : Vérifiez attentivement les informations sur votre prêt avant de les saisir dans Excel.
- Ne pas vérifier le résultat final : Assurez-vous que le capital restant dû à la dernière période est proche de zéro.
Conclusion
Créer un tableau d'amortissement de prêt immobilier sur Excel est un excellent moyen de comprendre le fonctionnement de votre prêt et de planifier vos finances. En suivant les étapes décrites dans cet article et en utilisant les formules Excel appropriées, vous pouvez créer un tableau précis et personnalisé qui vous aidera à prendre des décisions éclairées. N'hésitez pas à expérimenter avec différents scénarios pour optimiser votre remboursement et minimiser le coût total de votre prêt. Avec un peu de pratique, vous deviendrez un expert de l'amortissement de prêt sur Excel !