Table des matières
- Comprendre votre hypothèque
- Calculez le paiement mensuel
- Calculez 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éation d'un échéancier de prêt
Le remboursement d'un prêt est l'acte de rembourser de l'argent précédemment emprunté à un prêteur, généralement au moyen d'une série de paiements périodiques qui comprennent le capital plus les intérêts. Saviez-vous que vous pouvez utiliser le logiciel Excel pour calculer vos remboursements de prêt?
Cet article est un guide étape par étape pour configurer les calculs de prêt.
Points clés à retenir
- Utilisez Excel pour maîtriser votre prêt hypothécaire en déterminant votre paiement mensuel, votre taux d'intérêt et votre échéancier de prêt.Vous pouvez jeter un œil plus approfondi sur la répartition d'un prêt avec Excel et créer un échéancier de remboursement qui vous convient. Il y a des calculs disponibles pour chaque étape que vous pouvez modifier pour répondre à vos besoins spécifiques.La ventilation et l'examen de votre prêt étape par étape peuvent rendre le processus de remboursement moins écrasant et plus gérable.
Comprendre votre hypothèque
En utilisant Excel, vous pouvez mieux comprendre votre hypothèque 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 du 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 le paiement mensuel.
Calculez le paiement mensuel
Tout d'abord, voici comment calculer le paiement mensuel d'une hypothèque. 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_présente;;)
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 facultative.
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 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 configurer le calcul d'un versement mensuel pour une hypothèque. Mais nous pouvons vouloir fixer un paiement mensuel maximum que nous pouvons nous permettre d'afficher é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 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 TAUX, comme indiqué dans la capture d'écran ci-dessus. Il est écrit comme suit:
= RATE (Nper; pmt; present_value;;)
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 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 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 du paiement mensuel doivent recevoir un signe négatif. C'est pourquoi il y a un signe moins devant la formule. La période tarifaire 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 prêt, 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 au taux annuel maximal de 3, 58%.
L'utilisation d'Excel est un excellent moyen de garder une trace de ce que vous devez et d'élaborer 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 capital emprunté et le paiement mensuel à 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 versement mensuel de 1 100 $?
La formule que nous utiliserons est NPER, comme indiqué dans la capture d'écran ci-dessus, et elle est écrite comme suit:
= NPER (taux; pmt; valeur_actuelle;;)
Les trois premiers arguments sont le taux annuel du prêt, le versement 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 est 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 du paiement mensuel doivent recevoir un signe négatif. C'est pourquoi nous avons un signe moins devant 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. En d'autres termes, pour emprunter 120 000 $, avec un taux annuel de 3, 10% et payer 1 100 $ par mois, nous devons rembourser les échéances pour 128 mois ou 10 ans et 8 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 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 décomposent comme suit:
= -PPMT (rate; num_period; length; principal;;)
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 pendant laquelle le crédit est décomposé 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 versons 1 161, 88 $ répartis en 856, 20 $ de capital et 305, 68 $ d'intérêts.
Calcul de prêt dans Excel
Il est également possible de calculer le remboursement du principal 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)
On retrouve 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 montré dans la capture d'écran "Cumul 1ère année", donc les périodes analysées vont de 1 à 12 de la première période (premier mois) au douzième (12ème mois). Sur un an, nous paierions 10 419, 55 $ en capital 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 paierons mensuellement en capital et intérêts, et de savoir combien il nous reste à payer.
Création d'un échéancier de prêt
Pour créer un échéancier de prêt, nous allons utiliser les différentes formules décrites ci-dessus et les étendre 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, car un paiement 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-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 capital 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 pour découvrir combien d'intérêt doit être payé:
= -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'à l'issue de 120 périodes notre prêt est remboursé.
