Comment Créer votre Calculateur d'Amortissement de Prêt |

La plupart des gens pensent qu'il existe une sorte de magie vaudou pour créer un prêt tableau d'amortissement . Mais je suis heureux de vous dire qu'il n'y a pas - et même mieux - même un débutant peut en créer un facilement en utilisant Microsoft Excel.

Un tableau d'amortissement du prêt vous donne beaucoup de bonnes informations: le montant total à payer au prêteur, la partie qui est le paiement d'intérêts, la partie qui est le paiement du principal et le solde du prêt en cours.

Lorsqu'un prêt est amorti, cela signifie simplement qu'un paiement d'intérêts et un paiement de principal sont combinés en un seul paiement périodique et que les montants de paiement sont égaux pour chaque période de paiement. Mais l'une des caractéristiques les plus intéressantes d'un prêt amorti est que pour chaque paiement, le montant de l'intérêt et du capital payé change.

Excel est un outil extrêmement utile pour calculer votre propre tableau d'amortissement. Vous pouvez même expérimenter avec les différentes entrées pour voir comment différents taux d'intérêt, périodes de remboursement et principaux montants de prêt affectent les paiements de prêt que vous allez faire.

Regardons un exemple pour illustrer le processus.

Supposons que vous venez d'acheter une voiture avec un prêt de 20 000 $ qui doit être entièrement remboursé en 4 ans. Le taux d'intérêt annuel sur le prêt est de 3%. Puisque le prêt est amorti, les paiements mensuels seront tous les mêmes.

La première étape consiste à calculer le montant du paiement mensuel. Cela peut être fait en utilisant la fonction PMT (qui signifie "paiement") dans Excel.

Commencez par créer une nouvelle feuille de calcul et entrez les données suivantes nécessaires pour le calcul du montant du paiement.

La colonne A contiendra les libellés suivants pour nos données:

A1 = le principal du prêt initial (montant de votre prêt)

A2 = durée du prêt en années

A3 = le taux d'intérêt annuel

A4 = le nombre de paiements par an

A5 = le montant du paiement (ce que nous calculons)

La colonne B contient les valeurs correspondantes pour chaque étiquette dans la colonne A:

B1 = 20 000 $

B2 = 4

B3 = 3%

B4 = 12

B5 = la valeur que nous recherchons

La fonction PMT d'Excel se trouve dans le menu Fonctions financières. Sélectionnez d'abord la cellule B5, puis cliquez sur le menu Fonctions financières sous l'onglet formules et sélectionnez la fonction PMT dans la liste. Une boîte sera affichée où vous devriez entrer les données de la feuille de calcul exactement comme sur cette image:

Il y a quelques choses importantes à noter ici. Premièrement, nous devons procéder à deux ajustements importants, car nous calculons des paiements mensuels. Nous devons diviser notre taux annuel par 12 pour obtenir le taux d'intérêt mensuel (taux), et nous devons multiplier 4 par 12 pour obtenir le nombre total de mois dans notre période de remboursement (Nper).

Deuxièmement, la valeur actuelle (PV) est entrée en tant que valeur négative parce que ce montant est dû au prêteur.

Une fois vos cases remplies, appuyez sur "OK".

Votre feuille de calcul devrait maintenant ressembler à ceci:

Nous allons maintenant commencer à construire la table d'amortissement.

Passer une ligne sous la feuille de calcul faite dans la partie 1 et commencer par la ligne 7 étiquettes commençant à la colonne A et se terminant dans la colonne E:

A7 = Mois

B7 = Paiement

C7 = Intérêt

D7 = Principal

E7 = Solde

Ce sont les cinq rubriques pour le tableau d'amortissement.

Il est maintenant temps de commencer à remplir le tableau. La première étape consiste à entrer le nombre de mois dans la vie du prêt. Sous la rubrique "mois", tapez "0" dans la cellule A8 (l'heure à laquelle le prêt a été émis). Remplissez les cellules suivantes dans l'ordre séquentiel par le numéro 48 (qui vous mettra à la cellule A56). Une astuce: une fois que vous avez entré 0, assurez-vous que la cellule est sélectionnée (A8), puis faites glisser le coin inférieur droit de la cellule jusqu'à la ligne 56. Puis sélectionnez "remplir série" à partir de la petite boîte qui apparaît à droite de la dernière cellule. Cela devrait automatiquement remplir la série numérique pour les 48 mois.

Ensuite, tous les paiements peuvent être complétés car la valeur de chaque paiement mensuel a déjà été déterminée. À partir du mois 1 (il n'y a pas de paiement au mois 0 car les paiements ont lieu à la fin de chaque mois) remplissez la valeur $ 442.69 pour chaque mois 1-48. Après cela, les intérêts et les montants en principal peuvent être calculés en utilisant leurs fonctions Excel respectives.

Calculer l'intérêt et les composantes principales des paiements en utilisant la fonction Excel est simple et peut être utilisé pour compléter le tableau d'amortissement complet. Pour calculer le paiement des intérêts, sélectionnez la cellule C9 puis cliquez sur le menu Fonctions financières sous l'onglet formules. Sélectionnez la fonction IPMT (pour "paiement d'intérêts") dans la liste. Une boîte sera affichée où vous devriez entrer les données de la feuille de calcul comme il est dans cette image:

Note: Les signes "$" entre les lettres et les chiffres de la cellule sont nécessaires pour copier les formules jusqu'aux cellules restantes plus tard. Le "$" verrouille la cellule référencée, de sorte que les formules restent intactes lorsque nous les copions dans les cellules restantes. Vous pouvez verrouiller les cellules (et voir le "$") en appuyant sur F4.

Ensuite, nous pouvons calculer le montant du principal dans le paiement. Similaire au calcul IPMT, sélectionnez la cellule D9 puis cliquez sur le menu Fonctions financières sous l'onglet formules. Sélectionnez la fonction PPMT (pour le "paiement principal") dans la liste.Une boîte de dialogue sera affichée dans laquelle vous devez entrer les données de la feuille de calcul comme sur cette image:

Note: Il est également possible de calculer l'intérêt en calculant le principal en utilisant d'abord la fonction PPMT, puis en soustrayant cette valeur du paiement total. pour savoir comment utiliser les deux formules, ce qui permet également de remplir le reste du tableau plus efficacement, comme vous le verrez ci-dessous:

La dernière colonne (colonne E) est la colonne du solde restant. chaque fois que nous avons déterminé les montants d'intérêt et de capital Pour le mois 0, aucun paiement n'a encore été effectué, le solde est donc de 20 000 $, mais nous demandons à Excel de calculer automatiquement le solde de chaque période. manu entrez «20 000 $», entrez «= B1», en vous reportant au tableau ci-dessus pour le calcul du paiement. Pour le mois 1, puisque le solde est calculé en soustrayant le solde du mois précédent par le paiement du principal du mois en cours, entrez "= E8-D9" dans la cellule E9. La balance sera automatiquement calculée. Votre table d'amortissement est maintenant terminée pour le mois 1 et devrait ressembler exactement à ceci:

Toutes les données pour les mois restants peuvent être remplies en sélectionnant simplement les cellules B9-E9, en copiant la sélection, puis en collant la sélection dans les cellules B10-E10. Puisque nous avons entré les formules pour chaque cellule et verrouillé nos cellules qui seront utilisées dans chaque calcul, les valeurs d'intérêt, de capital et d'équilibre sont automatiquement calculées. Voyant ce travail pour le mois 2, l'ensemble du tableau peut être complété en sélectionnant simplement les colonnes B-E jusqu'au mois 48 et en collant les informations déjà copiées. Excel a maintenant calculé chaque valeur pour la table d'amortissement en utilisant les formules que nous avons entrées. Votre tableau complété devrait ressembler à ceci:

Première année de prêt:

Dernière année de prêt:

Note: Ces tableaux ne contiennent que la première et la dernière année du table d'amortissement. Les années 2 et 3 se situeraient entre ces deux tableaux et formeraient un tableau d'amortissement continu pour tous les 48 mois.

Si vous êtes intéressé par le calcul des paiements hypothécaires pour un prêt hypothécaire à taux fixe, vous pouvez effectuer vous-même un tableau d'amortissement. ou vous pouvez utiliser notre calculatrice financière pour le faire pour vous. Notre calculateur d'hypothèque vous montrera votre paiement mensuel ainsi qu'un calendrier d'amortissement complet.