Calculer sa mensualité d'emprunt avec Excel : La formule PMT expliquée
Excel est un outil puissant pour gérer vos finances personnelles, et le calcul des mensualités d'emprunt ne fait pas exception. La formule PMT (ou paiement) est spécialement conçue pour cela. Elle vous permet de déterminer le montant fixe que vous devrez rembourser périodiquement, en tenant compte du taux d'intérêt, de la durée de l'emprunt et du montant emprunté.
Comprendre la formule PMT
La syntaxe de la formule PMT est la suivante :
=PMT(taux; npm; va; [vf]; [type])
Décortiquons chaque argument :
- taux : Le taux d'intérêt par période. Si votre taux annuel est de 5%, et que vous effectuez des paiements mensuels, vous devrez diviser ce taux par 12 (5%/12).
- npm : Le nombre total de périodes de paiement. Si vous remboursez un emprunt sur 5 ans avec des mensualités, npm sera de 5 * 12 = 60.
- va : La valeur actuelle, c'est-à-dire le montant de l'emprunt que vous contractez aujourd'hui.
- [vf] : (Facultatif) La valeur future, c'est-à-dire la valeur que vous souhaitez obtenir après le dernier paiement. Si vous remboursez intégralement votre emprunt, cette valeur sera de 0. Si omis, Excel considère que vf = 0.
- [type] : (Facultatif) Indique si les paiements sont effectués en début (1) ou en fin (0) de période. Si omis, Excel considère que type = 0 (paiement en fin de période).
Exemple concret : Calcul d'une mensualité d'emprunt immobilier
Imaginons que vous empruntiez 200 000 € pour l'achat d'un bien immobilier, avec un taux d'intérêt annuel de 3,5% sur une durée de 20 ans. Voici comment appliquer la formule PMT dans Excel :
- Taux mensuel : 3,5% / 12 = 0,002916667 (environ)
- Nombre de mensualités : 20 ans * 12 mois/an = 240
- Montant de l'emprunt : 200 000 €
Dans une cellule Excel, entrez la formule suivante :
=PMT(0,002916667; 240; 200000)
Excel affichera le résultat : -1159,92 €. Le signe négatif indique qu'il s'agit d'une sortie d'argent (un paiement).
Capture d'écran descriptive : Une capture d'écran d'une feuille Excel montrant les valeurs entrées (taux mensuel, nombre de mensualités, montant emprunté) dans des cellules distinctes, et la formule PMT utilisant ces références de cellules. La cellule contenant la formule PMT affiche le résultat du calcul (-1159,92 €).
Utiliser les références de cellules pour plus de flexibilité
Au lieu d'entrer les valeurs directement dans la formule, il est préférable de les stocker dans des cellules et d'utiliser les références de ces cellules dans la formule PMT. Cela vous permettra de modifier facilement les paramètres de l'emprunt et de voir l'impact sur la mensualité.
Par exemple :
- Cellule A1 : Taux annuel (3,5%)
- Cellule A2 : Durée de l'emprunt (20 ans)
- Cellule A3 : Montant de l'emprunt (200 000 €)
La formule deviendrait alors :
=PMT(A1/12; A2*12; A3)
Capture d'écran descriptive : Une capture d'écran d'une feuille Excel montrant les valeurs entrées (taux annuel, durée de l'emprunt, montant emprunté) dans les cellules A1, A2 et A3 respectivement. La cellule contenant la formule PMT utilise les références A1, A2 et A3 pour effectuer le calcul.
Calculer le coût total de l'emprunt
Une fois que vous avez calculé la mensualité, vous pouvez facilement déterminer le coût total de l'emprunt en multipliant la mensualité par le nombre de mensualités.
Dans notre exemple :
Coût total = 1159,92 € * 240 = 278 380,80 €
Le coût total de l'emprunt est donc de 278 380,80 €, ce qui signifie que vous paierez 78 380,80 € d'intérêts sur la durée de l'emprunt.
Comparer différents scénarios d'emprunt
L'un des avantages d'utiliser Excel pour calculer les mensualités d'emprunt est la possibilité de comparer facilement différents scénarios. Vous pouvez modifier le taux d'intérêt, la durée de l'emprunt ou le montant emprunté et observer l'impact sur la mensualité et le coût total de l'emprunt.
Par exemple, vous pouvez créer un tableau comparatif avec différentes durées d'emprunt (15 ans, 20 ans, 25 ans) et calculer la mensualité et le coût total pour chaque durée. Cela vous permettra de choisir l'option la plus adaptée à votre budget et à vos objectifs financiers.
Capture d'écran descriptive : Une capture d'écran d'une feuille Excel montrant un tableau comparatif avec différentes durées d'emprunt (15, 20 et 25 ans) et les mensualités et coûts totaux correspondants, calculés à l'aide de la formule PMT.
Autres fonctions Excel utiles pour la gestion de vos emprunts
Outre la fonction PMT, Excel propose d'autres fonctions utiles pour analyser et gérer vos emprunts.
Fonction IPMT (INT.PER.MAJ)
La fonction IPMT (Interest Payment) permet de calculer la part d'intérêts incluse dans un paiement donné. Sa syntaxe est la suivante :
=IPMT(taux; période; npm; va; [vf]; [type])
- période : La période pour laquelle vous souhaitez calculer la part d'intérêts (par exemple, le 1er mois, le 10ème mois, etc.).
Par exemple, pour calculer la part d'intérêts du premier paiement dans notre exemple précédent, vous pouvez utiliser la formule suivante :
=IPMT(0,002916667; 1; 240; 200000)
Fonction PPMT (PRINCPER)
La fonction PPMT (Principal Payment) permet de calculer la part de capital remboursée dans un paiement donné. Sa syntaxe est la suivante :
=PPMT(taux; période; npm; va; [vf]; [type])
Par exemple, pour calculer la part de capital remboursée du premier paiement dans notre exemple précédent, vous pouvez utiliser la formule suivante :
=PPMT(0,002916667; 1; 240; 200000)
Créer un tableau d'amortissement avec Excel
Un tableau d'amortissement est un tableau qui détaille, pour chaque période de paiement, la part d'intérêts, la part de capital et le solde restant dû. Vous pouvez facilement créer un tableau d'amortissement avec Excel en utilisant les fonctions PMT, IPMT et PPMT.
Étapes pour créer un tableau d'amortissement :
- Créez un tableau avec les colonnes suivantes : Période, Mensualité, Intérêts, Capital, Solde restant dû.
- Dans la colonne Période, entrez les numéros de 1 à npm (nombre total de périodes).
- Dans la colonne Mensualité, entrez la formule PMT (qui sera la même pour toutes les périodes).
- Dans la colonne Intérêts, entrez la formule IPMT.
- Dans la colonne Capital, entrez la formule PPMT.
- Dans la colonne Solde restant dû, entrez la formule qui calcule le solde restant après chaque paiement (Solde précédent - Capital).
- Recopiez les formules vers le bas pour toutes les périodes.
Capture d'écran descriptive : Une capture d'écran d'une feuille Excel montrant un tableau d'amortissement complet avec les colonnes Période, Mensualité, Intérêts, Capital et Solde restant dû. Les formules PMT, IPMT et PPMT sont utilisées pour calculer les valeurs dans les colonnes correspondantes.
Erreurs courantes à éviter lors du calcul des mensualités d'emprunt avec Excel
- Ne pas diviser le taux annuel par 12 : Si vous effectuez des paiements mensuels, assurez-vous de diviser le taux d'intérêt annuel par 12 pour obtenir le taux mensuel.
- Ne pas multiplier la durée de l'emprunt par 12 : Si la durée de l'emprunt est exprimée en années, multipliez-la par 12 pour obtenir le nombre total de mensualités.
- Oublier le signe négatif : La formule PMT renvoie un résultat négatif, car il s'agit d'une sortie d'argent. Si vous souhaitez afficher le résultat en positif, vous pouvez multiplier la formule par -1.
- Utiliser des formats de cellules incorrects : Assurez-vous d'utiliser le format de cellule approprié (Nombre, Monétaire, Pourcentage) pour afficher correctement les valeurs.
Conclusion
Calculer sa formule mensualité emprunt excel est à la portée de tous grâce à la fonction PMT. En comprenant les arguments de la formule et en utilisant les références de cellules, vous pouvez facilement simuler différents scénarios d'emprunt et prendre des décisions financières éclairées. N'hésitez pas à explorer les autres fonctions Excel mentionnées dans cet article pour approfondir votre analyse et mieux gérer vos emprunts. Excel est un outil précieux pour maîtriser vos finances personnelles, alors profitez-en pleinement !