CalculatriceCliquez pour ouvrir
Le remboursement est l’acte de rembourser de l’argent précédemment emprunté à un prêteur, généralement par le biais d’une série de paiements périodiques comprenant le principal et les intérêts. Vous pouvez utiliser le logiciel Excel pour calculer les montants de remboursement des dettes et les adapter à vos besoins spécifiques pour une meilleure gestion financière.
Conclusion :
- Excel peut aider à calculer les détails du prêt tels que les paiements mensuels et les taux d’intérêt pour une meilleure planification financière.
- La fonction PMT d’Excel calcule le remboursement mensuel du prêt en fonction du taux d’intérêt et de la durée du prêt.
- La compréhension des détails du prêt, tels que les détails des intérêts et du principal, est simplifiée à l’aide de formules Excel.
- La création d’un échéancier de prêt dans Excel garantit un suivi précis des paiements au fil du temps.
- Le plan d’amortissement Excel offre une vision claire des soldes des prêts et de l’avancement du remboursement.
Comment analyser votre prêt hypothécaire à l’aide d’Excel
Grâce à Excel, vous pouvez mieux comprendre votre prêt hypothécaire en trois étapes simples. La première étape détermine le paiement mensuel. La deuxième étape calcule le taux d’intérêt, la troisième étape détermine l’avancement du prêt.
Vous pouvez créer un tableau dans Excel qui vous indique le taux d’intérêt, le calcul du prêt sur la durée du prêt, la répartition du prêt, l’amortissement et les mensualités.
Étape 1 : Utilisez Excel pour calculer le remboursement mensuel de votre prêt
Tout d’abord, voici comment calculer votre versement hypothécaire mensuel. En utilisant le taux d’intérêt annuel, le capital et la durée, nous pouvons déterminer le paiement mensuel.
La formule, telle qu’elle apparaît dans la capture d’écran ci-dessus, s’écrit comme suit :
=-PMT(taux;longueur;valeur actuelle;(valeur future);(type))
Le signe moins devant PMT est nécessaire car la formule renvoie un nombre négatif. Les trois premiers arguments sont le taux d’intérêt du prêt, la durée du prêt (nombre de périodes) et le montant principal emprunté. Les deux derniers arguments sont facultatifs ; la valeur résiduelle par défaut est 0, et le paiement anticipé (pour un) ou final (pour 0) est également facultatif.
La formule Excel utilisée pour calculer la mensualité du prêt est :
= PMT((1+B2)^(1/12)-1;B4*12;B3)=PMT((1+3,10%)^(1/12)-1;10*12;120000)
Pour le taux d’intérêt, on utilise le taux d’intérêt mensuel (période d’intérêt), puis on calcule le nombre de périodes (120 en 10 ans multiplié par 12 mois) et, enfin, on indique le montant principal emprunté à 120 000 USD. Notre paiement mensuel serait de 1 161,88 $ sur 10 ans.
Étape 2 : Déterminez le taux d’intérêt annuel à l’aide d’Excel
Nous avons vu comment configurer le calcul de vos mensualités hypothécaires. Cependant, nous souhaiterons peut-être fixer le paiement mensuel maximum que nous pouvons nous permettre, tout en indiquant le nombre d’années qu’il nous faudra pour rembourser le prêt. C’est pour cette raison que nous souhaitons connaître le taux d’intérêt annuel correspondant.
Comme le montre la capture d’écran ci-dessus, nous calculons d’abord le taux d’intérêt périodique (mensuel, dans notre cas) puis le taux d’intérêt annuel. La formule utilisée sera RATE, comme indiqué dans la capture d’écran ci-dessus. Il est écrit ainsi :
=RATE(Nper;pmt;current_value;(future_value);(type))
Les trois premiers arguments sont la durée du prêt (nombre de périodes), la mensualité pour rembourser le prêt et le montant du principal emprunté. Les trois derniers arguments sont facultatifs et la valeur résiduelle est par défaut 0 ; L’argument term pour gérer le terme avant (pour un) ou à la fin (pour zéro) est également facultatif. Enfin, l’argument estimation est facultatif mais peut fournir une première estimation du taux.
La formule Excel utilisée pour calculer les taux d’intérêt des prêts est la suivante :
=TAUX(12*B4;-B2;B3) = TAUX(12*13;-960;120000)
A noter que les données correspondantes dans la mensualité doivent avoir un signe négatif. C’est pourquoi il y a un signe moins devant la formule. Le taux d’intérêt à terme est de 0,294 %.
Nous utilisons la formule = (1 + B5) vaut 12-1 ^ = (1 + 0,294 %) ^ 12-1 pour obtenir le taux d’intérêt annuel de notre prêt, qui est de 3,58 %. Autrement dit, pour emprunter 120 000 $ sur 13 ans et payer 960 $ mensuellement, il faudrait négocier le prêt à un taux d’intérêt maximum de 3,58 % annuel.
Informations rapides
Utiliser Excel est un excellent moyen de suivre ce que vous devez et d’établir un calendrier de remboursement afin de minimiser les frais que vous pourriez devoir payer.
Étape 3 : Comment utiliser Excel pour connaître la durée de votre prêt
Nous allons maintenant voir comment déterminer la durée du prêt lorsque l’on connaît le taux d’intérêt annuel, le montant du principal emprunté et la mensualité qui sera due. Autrement dit, combien de temps aurons-nous pour rembourser une hypothèque de 120 000 $ avec un taux d’intérêt de 3,10 % et une mensualité de 1 100 $ ?
La formule que nous utiliserons est NPER, comme indiqué dans la capture d’écran ci-dessus, et s’écrit comme suit :
=NPER(taux;pmt;valeur actuelle;(valeur future);(type))
Les trois premiers arguments sont le taux d’intérêt annuel du prêt, la mensualité requise pour rembourser le prêt et le montant du principal emprunté. Les deux derniers arguments sont facultatifs, la valeur résiduelle par défaut est 0. Le terme argument doit venir en premier (pour un) ou à la fin (pour zéro) également facultatif.
=NPER((1+B2)^(1/12)-1;-B4;B3) = NPER((1+3,10%)^(1/12)-1;-1100;120000)
Les données correspondantes dans le paiement mensuel doivent avoir un signe négatif. C’est pourquoi nous avons un signe moins devant la formule. La période de récupération est de 127,97 périodes (dans notre cas des mois).
Nous utiliserons la formule = B5/12 = 127,97/12 pour le nombre d’années pour finaliser le remboursement. Autrement dit, pour emprunter 120 000 $, avec un taux d’intérêt annuel de 3,10 % et des mensualités de 1 100 $, il faudrait rembourser les versements en 128 mois, soit 10 ans et 8 mois.
Étape 4 : Décomposez les remboursements du prêt à l’aide d’Excel
Les remboursements du prêt comprennent le principal et les intérêts. Les intérêts sont calculés pour chaque période. Par exemple, un remboursement mensuel sur 10 ans nous donnerait 120 périodes.
Le tableau ci-dessus présente le détail du prêt (durée totale égale à 120) en utilisant les formules PPMT et IPMT. Les arguments des deux formules sont les mêmes et se décomposent comme suit :
=-PPMT(taux;num_time;longueur;principal;(résiduel);(période))
Les arguments sont les mêmes que pour la formule PMT vue, sauf “num_time”, qui est ajouté pour indiquer la période sur laquelle diviser le prêt en principal et en intérêts. Voici un exemple :
=-PPMT((1+B2)^(1/12)-1;1;B4*12;B3) = PPMT((1+3,10%)^(1/12)-1;1;10*12;120000)
Le résultat affiché dans la capture d’écran ci-dessus, « Répartition du prêt » pour la période analysée, est « un » ; c’est-à-dire la première période ou le premier mois. Nous payons 1 161,88 $, divisé en 856,20 $ de capital et 305,68 $ d’intérêts.
Étape 5 : Calculer le prêt global sur Excel
Il est également possible de calculer les paiements du principal et des intérêts sur plusieurs périodes, comme les 12 premiers mois ou les 15 premiers mois.
=-CUMPRINC(taux;longueur;principal;date_début;date_fin;type)
On retrouve les arguments, l’échelle, la longueur, le principal et le terme (obligatoire) que nous avons vu dans la première partie avec la formule PMT. Mais ici, nous avons aussi besoin des arguments “start_date” et “end_date”. “Start_date” indique la date de début de la période analysée, et “end_date” indique la date de fin de la période analysée.
Voici un exemple :
=-CUMPRINC((1+B2)^(1/12)-1;B4*12;B3;1;12;0)
Les résultats sont présentés dans la capture d’écran ci-dessus, « Accumulé première année », les périodes analysées vont donc de 1 à 12 de la première période (premier mois) à la 12ème période (12 mois). Dans un an, nous paierons 10 419,55 $ de capital et 3 522,99 $ d’intérêts.
Étape 6 : Créer un plan d’amortissement dans Excel
Les formules précédentes nous permettent de créer un planning période par période, de savoir combien de capital et d’intérêts nous allons payer chaque mois, et de savoir combien il reste à payer.
Étape 7 : Construisez votre échéancier de remboursement de prêt à l’aide d’Excel
Pour créer un échéancier de prêt, nous utiliserons les différentes formules évoquées ci-dessus et les échelonnerons en nombre de périodes.
Dans la première colonne de période, entrez « 1 » comme première période, puis faites glisser la cellule vers le bas. Dans notre cas, nous avons besoin de 120 périodes car un remboursement de prêt sur 10 ans multiplié par 12 mois équivaut à 120.
La deuxième colonne indique le montant mensuel que nous devons payer chaque mois. Ce montant reste le même pendant toute la durée du prêt. Pour calculer le montant, insérez la formule suivante dans la cellule de la première période :
=-PMT(TP;B4*12;B3) =-PMT((1+3,10%)^(1/12)-1;10*12;120000)
La troisième colonne est le montant principal qui sera remboursé mensuellement. Par exemple, au cours de la 40e période, nous rembourserions 945,51 $ de capital pour un paiement mensuel total de 1 161,88 $.
Pour calculer le montant en principal à rembourser, nous utilisons la formule suivante :
=-PPMT(TP;A18;$B$4*12;$B$3) =-PPMT((1+3,10%)^(1/12);1;10*12;120000)
La quatrième colonne est les intérêts, on utilise la formule pour calculer le remboursement mensuel du principal pour connaître le montant des intérêts à payer :
=-INTPER(TP;A18;$B$4*12;$B$3) =-INTPER((1+3,10%)^(1/12);1;10*12;120000)
La cinquième colonne contient le montant restant à payer. Par exemple, après le 40e paiement, nous devrons payer 83 994,69 $ sur 120 000 $.
La formule est la suivante :
=$B$3+CUMPRINC(TP;$B$4*12;$B$3;1;A18;0)
La formule utilise la combinaison du montant principal selon le point avant la cellule contenant le montant principal emprunté. Cet intervalle commence à changer à mesure que nous copions et faisons glisser la cellule vers le bas. Le tableau ci-dessous montre qu’après 120 périodes notre prêt sera remboursé.
Comment créer un échéancier de remboursement de prêt dans Excel ?
Pour créer un tableau d’amortissement ou un calendrier de remboursement dans Excel, vous devez créer un tableau avec la durée totale du prêt dans la première colonne, les mensualités dans la deuxième colonne, le principal mensuel dans la troisième colonne, les intérêts mensuels dans la quatrième colonne et le montant restant dans la cinquième colonne. Chaque colonne utilisera une formule différente pour calculer le montant approprié divisé par le nombre de périodes de remboursement.
Quelle est la formule de paiement mensuel dans Excel ?
Utilisez la fonction PMT dans Excel pour créer la formule : PMT(rate, nper, pv, (fv), (type)). Cette formule vous permet de calculer vos mensualités en divisant le taux d’intérêt annuel par 12 pour le nombre de mois dans une année.
Comment créer une formule PMT dans Excel ?
Lorsque vous créez une formule PMT, telle que PMT(rate, nper, pv, (fv), (type)), vous avez besoin de certains points de données. Dans cette formule, le taux d’intérêt est le taux d’intérêt du prêt, nper est le nombre total de paiements, pv est la valeur actuelle ou le principal du prêt, fv est la valeur future après le remboursement du prêt et type est le moment où le paiement est dû. Comme indiqué entre parenthèses, fv et type sont des arguments facultatifs.
Conclusion
Rembourser ses dettes peut être difficile, mais des outils organisationnels comme Excel peuvent simplifier le processus de remboursement des dettes et accroître la responsabilisation. Des fonctions telles que PMT, RATE, NPER aident à créer des feuilles de calcul permettant de suivre les taux d’intérêt, les conditions de prêt, les calendriers d’amortissement, etc. Dans le cadre d’une stratégie organisationnelle plus large, cela peut rendre le processus de remboursement du prêt plus réalisable.
