Planifier les remboursements de prêt avec des formules Excel
Table des matières
Développer
- Comprendre votre prêt hypothécaire
- Calculez le paiement mensuel
- Calculer le taux d’intérêt annuel
- Déterminer la durée d’un prêt
- Décomposition du prêt
- Calcul de prêt dans Excel
- Amortissement du prêt
- Créer un calendrier de prêt
Le remboursement d’un prêt est l’acte de rembourser de l’argent déjà emprunté à un prêteur, généralement au moyen d’une série de paiements périodiques comprenant le principal et les intérêts. Saviez-vous que vous pouvez utiliser le logiciel Excel pour calculer vos remboursements de prêts?
Cet article est un guide étape par étape pour configurer les calculs de prêt.
Points clés à retenir:
- Utilisez Excel pour avoir une idée de votre prêt hypothécaire en déterminant votre paiement mensuel, votre taux d’intérêt et votre calendrier de prêt.
- Vous pouvez examiner de manière plus approfondie la ventilation d’un prêt avec Excel et créer un calendrier de remboursement qui vous convient.
- Il existe des calculs disponibles pour chaque étape que vous pouvez modifier pour répondre à vos besoins spécifiques.
- Décomposer et examiner votre prêt étape par étape peut rendre le processus de remboursement moins fastidieux et plus gérable.
Comprendre votre prêt hypothécaire
En utilisant 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 et la troisième étape détermine le calendrier de prêt.
Vous pouvez créer un tableau dans Excel qui vous indiquera le taux d’intérêt, le calcul du prêt pour la durée du prêt, la décomposition du prêt, l’amortissement et la mensualité.
Calculez le paiement mensuel
Tout d’abord, voici comment calculer la mensualité d’un prêt hypothécaire. En utilisant le taux d’intérêt annuel, le principal et la durée, nous pouvons déterminer le montant à rembourser mensuellement.
La formule, comme indiqué dans la capture d’écran ci-dessus, est écrite comme suit:
= -PMT (taux; longueur; valeur_actuelle; [valeur_avenir]; [type])
Le signe moins devant PMT est nécessaire car la formule renvoie un nombre négatif. Les trois premiers arguments sont le taux du prêt, la durée du prêt (nombre de périodes) et le principal emprunté. Les deux derniers arguments sont facultatifs, la valeur résiduelle par défaut est zéro; payable à l’avance (pour un) ou à la fin (pour zéro) 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)
Explication: Pour le taux, nous utilisons le taux mensuel (période de taux), puis nous calculons le nombre de périodes (120 pour 10 ans multiplié par 12 mois) et, enfin, nous indiquons le principal emprunté. Notre paiement mensuel sera de 1 161,88 $ sur 10 ans.
Calculez le taux d’intérêt annuel
Nous avons vu comment mettre en place le calcul d’une mensualité pour une hypothèque. Mais nous voudrons peut-être fixer un paiement mensuel maximal que nous pouvons nous permettre, qui indique également le nombre d’années pendant lesquelles nous aurions à rembourser le prêt. Pour cette raison, nous aimerions 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 de la période (mensuel, dans notre cas), puis le taux annuel. La formule utilisée sera RATE, comme indiqué dans la capture d’écran ci-dessus. Il s’écrit comme suit:
= TAUX (Nper; pmt; valeur_actuelle; [valeur_avenir]; [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 principal emprunté. Les trois derniers arguments sont facultatifs et la valeur résiduelle par défaut est zéro; le terme argument pour gérer la maturité à l’avance (pour un) ou à la fin (pour zéro) est également facultatif. Enfin, l’argument estimation est optionnel mais peut donner une première estimation du taux.
La formule Excel utilisée pour calculer le taux de prêt est:
= TAUX (12 * B4; -B2; B3) = TAUX (12 * 13; -960; 120000)
Remarque: les données correspondantes dans le paiement mensuel doivent être signalées par un signe négatif. C’est pourquoi il y a un signe moins avant la formule. La période tarifaire est de 0,294%.
Nous utilisons la formule = (1 + B5) est 12-1 ^ = (1 + 0,294%) ^ 12-1 pour obtenir le taux annuel de notre prêt, qui est de 3,58%. En d’autres termes, pour emprunter 120 000 $ sur 13 ans pour payer 960 $ par mois, il faudrait négocier un prêt à un taux annuel maximal de 3,58%.
L’utilisation d’Excel est un excellent moyen de garder une trace de ce que vous devez et de proposer un calendrier de remboursement qui minimise les frais que vous pourriez devoir payer.
Déterminer la durée d’un prêt
Nous allons maintenant voir comment déterminer la durée d’un prêt lorsque vous connaissez le taux annuel, le principal emprunté et la mensualité à rembourser. En d’autres termes, combien de temps aurons-nous besoin pour rembourser une hypothèque de 120 000 $ avec un taux de 3,10% et un paiement mensuel de 1 100 $?
La formule que nous utiliserons est NPER, comme indiqué dans la capture d’écran ci-dessus, et elle s’écrit comme suit:
= NPER (taux; pmt; valeur_actuelle; [valeur_avenir]; [type])
Les trois premiers arguments sont le taux annuel du prêt, la mensualité nécessaire pour rembourser le prêt et le principal emprunté. Les deux derniers arguments sont facultatifs, la valeur résiduelle est nulle par défaut. Le terme argument payable d’avance (pour un) ou à la fin (pour zéro) est également facultatif.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3,10%) ^ (1/12) -1; -1100; 120000)
Signe moins avant la formule
Les données correspondantes dans le paiement mensuel doivent être signalées par un signe négatif. C’est pourquoi nous avons un signe moins avant la formule. La durée de remboursement est de 127,97 périodes (mois dans notre cas).
Nous utiliserons la formule = B5 / 12 = 127,97 / 12 pour le nombre d’années pour compléter le remboursement du prêt. Autrement dit, pour emprunter 120 000 $, avec un taux annuel de 3,10% et pour payer 1 100 $ par mois, il faudrait rembourser les échéances sur 128 mois ou 10 ans et huit mois.
Décomposition du prêt
Un paiement de prêt est composé du principal et des intérêts. L’intérêt est calculé pour chaque période – par exemple, les remboursements mensuels sur 10 ans nous donneront 120 périodes.
Le tableau ci-dessus montre la ventilation d’un prêt (une période totale égale à 120) à l’aide des formules PPMT et IPMT. Les arguments des deux formules sont les mêmes et se décomposent comme suit:
= -PPMT (taux; période_nombre; longueur; principal; [résiduel]; [terme])
Les arguments sont les mêmes que pour la formule PMT déjà vue, à l’exception de « num_period », qui est ajouté pour indiquer la période sur laquelle décomposer le prêt compte tenu du principal et des 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 est montré dans la capture d’écran ci-dessus « Décomposition du prêt » sur la période analysée, qui est « un »; c’est-à-dire la première période ou le premier mois. Nous payons 1 161,88 $ répartis en un capital de 856,20 $ et un intérêt de 305,68 $.
Calcul de prêt dans Excel
Il est également possible de calculer le remboursement 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, taux, longueur, principal et terme (qui sont obligatoires) que nous avons déjà vus dans la première partie avec la formule PMT. Mais ici, nous avons également besoin des arguments « start_date » et « end_date ». Le « start_date » indique le début de la période à analyser, et le « end_date » indique la fin de la période à analyser.
Voici un exemple:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Le résultat est affiché dans la capture d’écran «Cumul 1 année», les périodes analysées vont donc de un à 12 de la première période (premier mois) au douzième (12e mois). Sur un an, nous paierions 10 419,55 $ en principal et 3 522,99 $ en intérêts.
Amortissement du prêt
Les formules précédentes nous permettent de créer notre horaire période par période, de savoir combien nous paierons mensuellement en principal et intérêts, et de savoir combien il reste à payer.
Créer un calendrier de prêt
Pour créer un échéancier de prêt, nous utiliserons les différentes formules évoquées ci-dessus et les étendrons sur le 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 puisqu’un remboursement de prêt de 10 ans multiplié par 12 mois équivaut à 120.
La deuxième colonne est le montant mensuel que nous devons payer chaque mois, qui est constant sur l’ensemble du calendrier de prêt. Pour calculer le montant, insérez la formule suivante dans la cellule de notre 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 principal qui sera remboursé mensuellement. Par exemple, pour la 40e période, nous rembourserons 945,51 $ en principal sur notre montant mensuel total de 1 161,88 $.
Pour calculer le montant du capital remboursé, 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, pour lesquels nous utilisons la formule pour calculer le principal remboursé sur notre montant mensuel afin de découvrir 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 une combinaison de principal sous une période antérieure à la cellule contenant le principal emprunté. Cette période commence à changer lorsque nous copions et faisons glisser la cellule vers le bas. Le tableau ci-dessous montre qu’au bout de 120 périodes, notre prêt est remboursé.