Le remboursement d’un prêt est l’acte de rembourser l’argent précédemment emprunté à un prêteur, généralement par une série de versements périodiques comprenant le principal plus les intérêts. Saviez-vous que vous pouvez utiliser le logiciel Excel pour calculer les remboursements de votre prêt ?
Cet article est un guide étape par étape pour la mise en place du calcul des prêts.
Contenu
Points clés à retenir
- Utilisez Excel pour vous faire une idée de votre prêt hypothécaire en déterminant votre mensualité, votre taux d’intérêt et votre échéancier.
- Vous pouvez examiner plus en détail la répartition d’un prêt avec excel et créer un calendrier de remboursement qui vous convient.
- Pour chaque étape, il existe des calculs que vous pouvez modifier pour répondre à vos besoins spécifiques.
- En décomposant et en examinant votre prêt étape par étape, le processus de remboursement peut sembler moins accablant et plus facile à gérer.
Comprendre votre hypothèque
Grâce à Excel, vous pouvez mieux comprendre votre prêt hypothécaire en trois étapes simples. La première étape détermine le montant du versement mensuel. La deuxième étape calcule le taux d’intérêt et la troisième détermine le calendrier du prêt.
Vous pouvez construire un tableau en 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 le paiement mensuel.
Calculer le paiement mensuel
Tout d’abord, voici comment calculer le montant des mensualités d’un prêt hypothécaire. En utilisant le taux d’intérêt annuel, le capital et la durée, nous pouvons déterminer le montant à rembourser mensuellement.
La formule, telle que montrée dans la capture d’écran ci-dessus, s’écrit comme suit :
=-PMT(taux;longueur;valeur_actuelle ;[future_value];[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 optionnels, la valeur résiduelle est par défaut égale à zéro ; payable à l’avance (pour un) ou à la fin (pour zéro), est également optionnel.
La formule Excel utilisée pour calculer le paiement mensuel du prêt est la suivante :
=-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 capital emprunté. Notre paiement mensuel sera de 1 161,88 $ sur 10 ans.
Calculer le taux d’intérêt annuel
Nous avons vu comment mettre en place le calcul d’une mensualité pour un prêt hypothécaire. Mais nous pouvons vouloir fixer une mensualité maximale que nous pouvons nous permettre et qui indique également le nombre d’années pendant lesquelles nous devrions rembourser le prêt. C’est pourquoi 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 le TAUX, comme le montre la capture d’écran ci-dessus. Elle s’écrit comme suit :
=RATE(Nper;pmt;present_value ;[future_value];[type])
Les trois premiers arguments sont la durée du prêt (nombre de périodes), le paiement mensuel pour rembourser le prêt et le capital emprunté. Les trois derniers arguments sont facultatifs, et la valeur résiduelle est par défaut égale à zéro ; l’argument de la durée pour gérer l’échéance à l’avance (pour un) ou à la fin (pour zéro) est également facultatif. Enfin, l’argument de l’estimation est facultatif mais peut donner une première estimation du taux.
La formule Excel utilisée pour calculer le taux de prêt est la suivante
= 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 de taux 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 dollars sur 13 ans pour payer 960 dollars par mois, nous devons négocier un prêt à un taux annuel maximum de 3,58 %.
L’utilisation d’Excel est un excellent moyen de suivre ce que vous devez et d’établir un calendrier de remboursement qui minimise les frais que vous pourriez devoir.
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 capital emprunté et la mensualité à rembourser. En d’autres termes, combien de temps faudra-t-il pour rembourser un prêt hypothécaire de 120 000 dollars à un taux de 3,10 % et avec un versement mensuel de 1 100 dollars ?
La formule que nous utiliserons est le NPER, comme le montre la capture d’écran ci-dessus, et elle s’écrit comme suit :
=NPER(rate;pmt;present_value ;[future_value];[type])
Les trois premiers arguments sont le taux annuel du prêt, le paiement mensuel nécessaire pour rembourser le prêt et le capital emprunté. Les deux derniers arguments sont facultatifs, la valeur résiduelle est par défaut égale à zéro. L’argument du terme payable à l’avance (pour un) ou à la fin (pour zéro) est également optionnel.
=NPER((1+B2)^(1/12)-1;-B4;B3) = NPER((1+3,10%)^(1/12)-1;-1100;120000)
Remarque : 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 nécessaires au remboursement du prêt. En d’autres termes, pour emprunter 120 000 dollars, avec un taux annuel de 3,10 % et pour payer 1 100 dollars par mois, nous devrions rembourser des échéances de 128 mois ou de 10 ans et huit mois.
Décomposition du prêt
Le paiement d’un prêt est composé du principal et des intérêts. Les intérêts sont calculés pour chaque période – par exemple, les remboursements mensuels sur 10 ans nous donneront 120 périodes.
Le tableau ci-dessus montre la répartition d’un prêt (une période totale égale à 120) en utilisant les formules PPMT et IPMT. Les arguments des deux formules sont les mêmes et se répartissent comme suit :
=-PPMT(taux;num_période;durée;principal ;[residual];[term])
Les arguments sont les mêmes que pour la formule PMT déjà vue, à l’exception de « num_period », qui est ajouté pour montrer la période sur laquelle ventiler le prêt étant donné le principal et les 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 indiqué dans la capture d’écran ci-dessus « Décomposition des prêts » sur la période analysée, qui est de « un », c’est-à-dire la première période ou le premier mois. Nous payons 1 161,88 $ répartis en 856,20 $ de principal et 305,68 $ d’intérêts.
Calcul des prêts en Excel
Il est également possible de calculer le remboursement du capital et des intérêts pour plusieurs périodes, comme les 12 premiers mois ou les 15 premiers mois.
=-CUMPRINC(taux;longueur;principal;date_début;date_fin;type)
Nous retrouvons les arguments, taux, durée, 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 « date_début » et « date_fin ». 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 montré dans la capture d’écran « Cumul 1ère année », donc les périodes analysées vont de une à douze de la première période (premier mois) à la douzième (12ème mois). Sur une année, 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 calendrier période par période, de savoir combien nous allons payer mensuellement en principal et en intérêts, et de connaître le montant restant à payer.
Création d’un calendrier de prêts
Pour créer un calendrier de prêts, nous utiliserons les différentes formules évoquées ci-dessus et les étendrons sur le nombre de périodes.
Dans la colonne de la première 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 paiement de prêt sur 10 ans multiplié par 12 mois est égal à 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 ce montant, il faut insérer la formule suivante dans la cellule de notre première période :
=-PMT(TP-1;B4*12;B3) =-PMT((1+3,10%)^(1/12)-1;10*12;120000)
La troisième colonne est le capital qui sera remboursé mensuellement. Par exemple, pour la 40e période, nous rembourserons 945,51 $ en principal sur notre montant total mensuel de 1 161,88 $.
Pour calculer le montant du principal 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 celle des intérêts, pour laquelle nous utilisons la formule de calcul du capital remboursé sur notre montant mensuel pour 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 du 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é.
https://www.investopedia.com/articles/personal-finance/082415/schedule-loan-repayments-excel-formulas.asp
