Saviez-vous que vous pouvez utiliser Excel pour calculer vos remboursements de prêts? Cet article vous guidera à travers toutes les étapes nécessaires pour le faire. (Voir aussi: Calculatrices hypothécaires: comment elles fonctionnent .)

Grâce à Excel, vous pouvez mieux comprendre votre hypothèque en trois étapes simples. La première étape consiste à déterminer le paiement mensuel. Le deuxième est de découvrir le taux d'intérêt, et le troisième est de trouver le calendrier des prêts. Pour ce faire, vous pouvez construire une table dans Excel qui vous dira: Les taux d'intérêt; le calcul du prêt pour la durée; la décomposition d'un prêt, ainsi que l'amortissement et le calcul du loyer mensuel.

Calcul du prêt pour le loyer mensuel

Voyons d'abord comment implémenter le calcul du paiement mensuel d'une hypothèque. En d'autres termes, 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 montré dans la capture d'écran ci-dessus, s'écrit comme suit:

= - PMT (taux; longueur; valeur_présent; [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 capital emprunté. Les deux derniers arguments sont optionnels, la valeur résiduelle par défaut à 0, payable d'avance (pour 1) ou à la fin (pour 0), est également facultative.

La formule Excel utilisée pour calculer le paiement mensuel 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 la période de taux, qui est le taux mensuel, puis nous calculons le nombre de périodes (mois ici 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.

Calcul hypothécaire des taux d'intérêt

Nous avons vu comment établir 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 affiche également le nombre d'années sur lequel nous devrions le rembourser. Pour cette raison, nous aimerions connaître le taux d'intérêt annuel correspondant.

Calcul du taux d'intérêt pour un prêt

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, elle s'écrit comme suit:

= RATE (Nper; pmt; valeur_présent; [valeur_avenir]; [type])

Les trois premiers arguments sont la longueur de le prêt (nombre de périodes), et le paiement mensuel pour rembourser le principal emprunté. Les trois derniers arguments sont facultatifs et la valeur résiduelle par défaut est 0, le terme argument pour gérer l'échéance à l'avance (pour 1) ou à la fin (pour 0) est également facultatif, et finalement l'argument d'estimation est facultatif, mais peut donne une estimation initiale du taux.

La formule Excel utilisée pour calculer le taux débiteur est:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Note: les données correspondantes dans le paiement mensuel doit recevoir un signe négatif. C'est pourquoi un signe moins avant la formule. Notre 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 à 3. 58 % En d'autres termes, pour emprunter 120 000 $ sur 13 ans pour payer mensuellement 960 $, nous devrions négocier un prêt à un taux annuel maximal de 3. 58%.

Calcul hypothécaire pour la durée d'un prêt

Nous verrons maintenant comment obtenir 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 faudra-t-il pour rembourser une hypothèque de 120 000 $ avec un taux de 3,10% et un paiement mensuel de 1 100 $?

Nombre de remboursements pour un prêt

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 optionnels, la valeur résiduelle par défaut est 0, le terme argument payable d'avance (pour 1) ou à la fin (pour 0) 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 être affectées d'un signe négatif. C'est pourquoi nous avons un signe moins avant la formule. La durée du 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 devrions rembourser les échéances pour 128 mois ou 10 ans et 8 mois.

Décomposition du prêt

Le remboursement d'un prêt se compose de deux choses: le principal et l'intérêt. L'intérêt est calculé pour chaque période, par exemple les remboursements mensuels sur 10 ans, nous donnera 120 périodes.

La capture d'écran 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 (taux; num_période; longueur; principal; [résiduel]; [terme])

= - INTPER (taux; num_période; length; principal; [residual]; [terme])

Les arguments sont les mêmes que pour la formule PMT vue dans la première partie, à l'exception de num_period qui est ajouté pour indiquer la période sur laquelle décomposer le prêt, en donnant le principal et l'intérêt pour cela. Prenons un exemple:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12 ) -1; 1; 10 * 12; 120000)

= - ENTRÉE ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = ENTRÉE ((1 + 3, 10 %) ^ (1/12) -1; 1; 10 * 12; 120000)

Le résultat est celui montré dans la capture d'écran "Loan Decomposition", sur la période analysée qui est "1", donc la première période ou le premier moisPour celui-ci, nous payons 1161 $. 88, décomposé en 856 $, 20 en capital et 305 $. 68 intérêt.

Excel Loan Computation

Il est maintenant possible de calculer le remboursement du principal 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)

= - CUMIPMT (taux; longueur; principal; date_début; date_fin; type)

On trouve les arguments, taux, longueur, principe et terme (qui sont obligatoires) que nous avons déjà vu dans la première partie avec la formule PMT. Mais ici, nous avons aussi besoin des arguments start_date et end_date. Le premier indique le début de la période à analyser et le second la fin. Prenons un exemple:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

= - CUMIPMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3 ; 1; 12; 0)

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

Le résultat est le celui 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). Au cours d'une année, nous verserions 10 419 $, 55 capital et 3 522 99 $ intérêt.

Amortissement du prêt

Les formules précédentes nous permettent de créer notre calendrier période par période, combien nous paierons mensuellement en principal et intérêts, et combien il reste à payer.

Créer un échéancier de prêt dans Excel

Pour créer un échéancier de prêt, nous utiliserons différentes formules discutées ci-dessus et les étendrons au nombre de périodes.

Dans la colonne de la première période, entrez simplement "1" comme première période, puis faites glisser la cellule vers le bas. Dans notre cas, nous avons besoin de 120 périodes depuis un paiement de prêt de 10 ans multiplié par 12 mois = 120.

La deuxième colonne est le montant mensuel que nous devons payer chaque mois, ce qui est constant sur l'ensemble du prêt. Pour le calculer, 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 capital qui sera remboursé mensuellement. Par exemple, pour la 40e période, nous rembourserons 945 $. 51 en capital sur notre montant mensuel total de 1 161 $. 88. Pour calculer le capital remboursé, nous utilisons la formule suivante:

= - TPMP (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 calculons le principal remboursé sur notre montant mensuel pour découvrir comment il faut payer beaucoup d'intérêts, en utilisant la formule:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTER ((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)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

La formule utilise une combinaison de principal dans une période à venir avec la cellule contenant le principal emprunté. Cette période commence à changer lorsque nous copions et faisons glisser la cellule vers le bas.La capture d'écran ci-dessous montre qu'à la fin de 120 périodes notre prêt est remboursé.