Planifier les remboursements de prêt avec des formules Excel
Le remboursement d'un prêt consiste à 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. Saviez-vous que vous pouvez utiliser le logiciel Excel pour calculer le remboursement de vos emprunts?
Cet article est un guide étape par étape pour la configuration des calculs de prêt.
Points clés à retenir
- Utilisez Excel pour contrôler votre prêt hypothécaire en déterminant votre versement mensuel, votre taux d’intérêt et votre échéancier de prêt.
- 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.
- Il existe des calculs disponibles pour chaque étape que vous pouvez adapter à vos besoins spécifiques.
- En décomposant et en examinant votre prêt étape par étape, le processus de remboursement peut sembler moins fastidieux et plus facile à gérer.
Comprendre votre hypothèque
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 indique 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 versement mensuel.
Calculer le paiement mensuel
Premièrement, voici comment calculer le versement mensuel d’une hypothèque. En utilisant le taux d'intérêt annuel, le capital et la durée, nous pouvons déterminer le montant à rembourser mensuellement.
Comme le montre la capture d'écran ci-dessus, la formule est écrite comme suit:
= -PMT (taux; longueur; valeur_présente; [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 étant zéro; payable d'avance (pour un) ou à la fin (pour zéro), est également facultatif.
La formule Excel utilisée pour calculer le versement 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ées par 12 mois) et, enfin, nous indiquons le principal emprunté. Notre versement mensuel sera de 1 161, 88 $ sur 10 ans.
Calculer le taux d'intérêt annuel
Nous avons vu comment configurer le calcul d’un paiement mensuel pour une hypothèque. Mais nous pouvons vouloir fixer un paiement mensuel maximum que nous pouvons nous permettre, qui indique également le nombre d'années sur lesquelles nous aurions à rembourser le prêt. Pour cette raison, nous aimerions connaître le taux d’intérêt annuel correspondant.
Comme indiqué dans 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 est écrit comme suit:
= RATE (Nper; pmt; valeur_présente; [valeur_avenir]; [type])
Les trois premiers arguments sont la durée du prêt (nombre de périodes), le paiement mensuel à effectuer 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; l'argument terme pour gérer l'échéance à l'avance (pour un) ou à la fin (pour zéro) est également facultatif. Enfin, l'argument d'estimation est facultatif mais peut donner une estimation initiale du taux.
La formule Excel utilisée pour calculer le taux débiteur est la suivante:
= TAUX (12 * B4; -B2; B3) = TAUX (12 * 13; -960; 120000)
Remarque: les données correspondantes dans le paiement mensuel doivent avoir 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) soit 12-1 ^ = (1 + 0, 294%) ^ 12-1 pour obtenir le taux annuel de notre emprunt, qui est de 3, 58%. En d’autres termes, pour emprunter 120 000 $ sur 13 ans pour payer 960 $ par mois, nous devrions négocier un prêt à un taux annuel maximum de 3, 58%.
Utiliser Excel est un excellent moyen de garder une trace de ce que vous devez et de proposer un échéancier de remboursement qui minimise les frais que vous pourriez éventuellement devoir payer.
Déterminer la durée d'un prêt
Nous allons maintenant voir comment déterminer la durée d’un emprunt lorsque vous connaissez le taux annuel, le principal emprunté et le versement mensuel à rembourser. En d’autres termes, combien de temps faudra-t-il pour rembourser une hypothèque de 120 000 $ à un taux de 3, 10% et un versement mensuel de 1 100 $ ">
La formule que nous allons utiliser est NPER, comme indiqué dans la capture d'écran ci-dessus, et 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 principal emprunté. Les deux derniers arguments sont facultatifs, la valeur résiduelle par défaut étant zéro. 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)
Remarque: les données correspondantes dans le paiement mensuel doivent avoir 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 $, avec un taux annuel de 3, 10% et payer 1 100 $ par mois, nous devrions rembourser les échéances pendant 128 mois ou 10 ans et 8 mois.
Décomposer le prêt
Un paiement de 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 (pour 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 (rate; num_period; length; 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 au cours de laquelle le prêt est décomposé en fonction du capital 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 présenté dans la capture d'écran ci-dessus "Décomposition de prêt" sur la période analysée, soit "un;" c'est-à-dire la première période ou le premier mois. Nous payons 1 161, 88 $ répartis en 856, 20 $ en principal et 305, 68 $ en intérêts.
Calcul du prêt dans Excel
Il est également possible de calculer le remboursement du capital et des intérêts pour plusieurs périodes telles que les 12 premiers mois ou les 15 premiers mois.
= -CUMPRINC (taux; longueur; principal; date_début; date_fin, type)
Nous trouvons 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 "start_date" et "end_date". La "date_début" indique le début de la période à analyser et la "date_fin" 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 présenté dans la capture d'écran "Cumul 1ère année", de sorte que les périodes analysées vont de 1 à 12 de la première période (premier mois) au douzième (12e 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 tous les mois en capital et en intérêts et de savoir combien il reste à payer.
Créer un calendrier de prêt
Pour créer un calendrier de prêt, nous allons utiliser les différentes formules décrites ci-dessus et les développer 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, puisqu'un paiement de prêt sur 10 ans multiplié par 12 mois équivaut à 120.
La deuxième colonne est le montant mensuel que nous devons payer chaque mois, ce 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-1; 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 total mensuel de 1 161, 88 $.
Pour calculer le 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 l’intérêt pour lequel nous utilisons la formule pour calculer le capital remboursé sur notre montant mensuel afin de déterminer le montant de l’intérêt à 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 versement, nous devrons payer 83 994, 69 $ sur 120 000 $.
La formule est la suivante:
= $ B $ 3 + CUMPRINC (TP; TP $ 4 * 12; B $ 3; 1; A18; 0)
La formule utilise une combinaison de principal sous une période précédant 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 emprunt est remboursé.