Les programmes de tableur, comme Google Sheets, sont extrêmement polyvalents et vous permettent de réaliser de nombreuses tâches utiles. Vous pouvez effectuer des tâches simples, comme créer des budgets, des graphiques, des diagrammes et utiliser des formules mathématiques simples. Ou vous pouvez faire des choses plus complexes, comme utiliser les fonctions financières intégrées de Google Sheets pour créer des tableaux d’amortissement.
Créer un calendrier d’amortissement de prêt est utile pour comprendre comment les paiements mensuels affectent le coût d’un prêt, tel qu’une hypothèque, un prêt automobile ou d’autres types de prêts. Dans ce tutoriel, je vais vous montrer comment créer un calendrier d’amortissement de prêt dans Google Sheets.
Qu’est-ce qu’un prêt amorti ?
Un prêt amorti est un type de prêt qui comporte des paiements réguliers prévus pour rembourser le capital et les intérêts du prêt. Tout paiement effectué pour un prêt amorti remboursera d’abord les intérêts pour la période donnée, puis, une fois les intérêts payés, le montant restant réduira le capital. Les types les plus courants de prêts amortis sont les prêts immobiliers, les prêts automobiles, les prêts personnels et le regroupement de dettes.
Calendrier d’amortissement de prêt
Les tableaux d’amortissement sont utiles car ils vous permettent de comprendre comment votre prêt sera remboursé. Un calendrier d’amortissement vous montrera chaque paiement mensuel du prêt et combien de ce paiement est destiné à rembourser le capital et combien est destiné à payer les intérêts au fil du temps. Ainsi, un calendrier d’amortissement de prêt comprendra :
- Paiements prévus : Les montants des paiements mensuels réguliers à effectuer sur le prêt
- Remboursement du capital : Le montant du paiement destiné à rembourser le capital
- Paiement des intérêts : Le montant du paiement destiné à payer les intérêts sur le prêt
Les tableaux d’amortissement de prêt peuvent vous aider à mieux comprendre les remboursements de prêt, c’est pourquoi il est très utile d’apprendre à créer votre propre calendrier.
Dans la prochaine section, je vais vous montrer comment créer votre propre calendrier dans une feuille de calcul.
Comment créer un calendrier d’amortissement de prêt dans Google Sheets
La première chose à faire pour créer notre calendrier de prêt dans notre feuille de calcul est de créer un modèle de base que nous pourrons remplir avec les informations pertinentes.
Vous devrez avoir une zone dans votre modèle pour saisir :
- Montant du capital
- Taux d’intérêt
- Durée du prêt
- Date de début du prêt
- Période
- Date
- Paiement mensuel
- Paiement des intérêts
- Remboursement du capital
- Solde du prêt
Voici un exemple de la façon dont ce modèle peut être configuré :
Informations sur le prêt
Une fois que nous avons notre modèle, nous pouvons commencer à saisir les informations importantes. Nous commencerons par remplir les informations dans la partie supérieure de la feuille.
Nous devons saisir :
- Le montant du capital : le montant total du prêt qui doit être remboursé
- Le taux d’intérêt : le taux d’intérêt du prêt
- La durée (en années) : la période de remboursement du prêt
- La date de début : la date à laquelle commence le remboursement du prêt
Je vais remplir ces valeurs dans mon exemple avec un prêt immobilier de 252 000 $ à rembourser sur une période de 30 ans avec un taux d’intérêt de 2,875%.
Une fois les informations de base sur le prêt saisies, nous pouvons commencer à remplir le reste du tableau.
Période
La première chose que nous allons saisir est la colonne de la période. Il s’agira simplement d’un numéro de série pour chaque paiement effectué. Vous pouvez commencer votre première ligne par 0, puis incrémenter à partir de là dans votre feuille de calcul. Vous obtiendrez quelque chose qui ressemble à ceci :
Date
La prochaine colonne que nous allons remplir dans notre tableau est la colonne de la date. La première date sera la même que votre date de début, puis chaque date suivante augmentera d’un mois.
Dans notre première ligne, nous pouvons copier et coller la date de début. Pour augmenter le mois sur les lignes suivantes, nous pouvons utiliser une formule de base à l’aide de la fonction EDATE.
Dans la deuxième ligne, entrez cette formule pour augmenter le mois :
=EDATE(B7,1)Votre feuille de calcul devrait maintenant ressembler à ceci :
Paiement mensuel
Ensuite, nous allons calculer et saisir le paiement mensuel sur chaque ligne.
Pour ce faire, nous pouvons utiliser la fonction PMT. Cette fonction calculera le paiement mensuel qui doit être effectué sur le prêt.
Pour notre calendrier de prêt, nous utiliserons la fonction PMT avec cette syntaxe de base :
=PMT(taux, nombre_de_périodes, valeur_actuelle)- taux : il s’agit du taux d’intérêt
- nombre_de_périodes : il s’agit du nombre de paiements qui seront effectués
- valeur_actuelle : il s’agit de notre montant du capital
Donc, dans ma feuille de calcul, la formule sera :
=PMT($B$2/12,$B$3*12,$B$1)Quelques points importants concernant cette formule :
- Toutes les références de cellules sont verrouillées avec le symbole du dollar ($) car la référence à la cellule ne change pas lorsque nos lignes changent. Nous voulons nous assurer de prendre les mêmes valeurs à chaque fois.
- Notre taux d’intérêt est divisé par 12 car il y a 12 mois dans une année.
- Notre nombre de périodes est multiplié par 12 car il y a 12 mois dans une année.
Après avoir entré cette colonne, voici à quoi ressemble la feuille de calcul maintenant :
Vous pouvez commencer votre première ligne à 0 car aucun paiement n’est nécessaire pour la première période. Pour les lignes suivantes, vous utiliserez la même formule et la copierez et la collerez tout au long de votre tableau.
Il est normal que les valeurs de cette colonne soient négatives car cela indique que les paiements sortent.
Paiement des intérêts
Ensuite, nous pouvons commencer à remplir la colonne du paiement des intérêts dans notre feuille de calcul. Cela indiquera le montant du paiement mensuel qui est destiné à payer les intérêts.
Pour calculer le paiement des intérêts, nous utiliserons la fonction IPMT.
La fonction IPMT calculera le paiement des intérêts sur un investissement nécessitant des paiements constants et un taux d’intérêt constant.
La syntaxe de la fonction IPMT est la suivante :
=IPMT(taux, période, nombre_de_périodes, valeur_actuelle, [valeur_future], [fin_ou_début])- taux : le taux d’intérêt
- période : il s’agit de la période d’amortissement. En d’autres termes, il s’agit d’un numéro de série pour le paiement qui est effectué
- nombre_de_périodes : le nombre de paiements qui seront effectués
- valeur_actuelle : la valeur de l’annuité (le montant du capital dans notre exemple)
- valeur_future : il s’agit d’un argument facultatif. Il s’agit de la valeur future restante après le dernier paiement.
- fin_ou_début : il s’agit d’un argument facultatif et sera défini par défaut sur 0. Cela déterminera si les paiements sont effectués au début ou à la fin de chaque période.
Dans notre exemple, ma formule pour IPMT sera :
=IPMT($B$2/12,A8,$B$3*12,$B$1)Voici à quoi cela ressemble dans ma feuille de calcul :
Dans l’exemple ci-dessus, assurez-vous de verrouiller chaque référence de cellule, sauf l’argument de période (2ème argument). Celui-ci doit changer au fur et à mesure de l’augmentation des lignes, vous voulez donc vous assurer qu’il n’est pas verrouillé dans la formule.
Votre première ligne peut rester à 0 car aucun paiement n’est effectué. Placez votre formule dans votre deuxième ligne et copiez-la et collez-la tout au long de votre tableau.
À chaque période, vous devriez voir que le paiement des intérêts diminue au fil du temps.
Remboursement du capital
Ensuite, nous allons remplir la colonne du remboursement du capital.
Pour ce faire, nous allons saisir une formule assez simple dans notre feuille de calcul.
Dans la première ligne, mettez zéro car aucun paiement n’est effectué. Ensuite, dans la deuxième ligne, vous saisirez cette formule :
(Paiement mensuel - Paiement des intérêts)Dans notre exemple, cela donnera :
(C8-D8)Cela ressemblera à ceci :
À chaque période, vous devriez constater que le paiement du capital augmente à mesure que le paiement des intérêts diminue.
Solde du prêt
Enfin, nous devons remplir la colonne du solde du prêt.
Il s’agit du montant total du prêt qui doit encore être remboursé.
Pour entrer cela dans notre feuille de calcul, dans la première ligne, nous devons mettre tout le montant du capital.
Dans cet exemple, je vais simplement mettre une référence de cellule au montant du capital dans la feuille de calcul, comme ceci :
Après avoir rempli la première ligne, vous devez entrer une formule dans la deuxième ligne pour obtenir le reste de la colonne du solde du prêt.
Dans cet exemple, j’entrerai cette formule dans ma feuille de calcul :
=E8+F7Cela soustraira le remboursement du capital pour chaque période du solde total du prêt. Nous ajoutons cette formule car la valeur du remboursement du capital est déjà négative. Donc, pour soustraire chaque remboursement du capital du solde du prêt, il nous suffit de l’ajouter au solde du prêt précédent.
Après avoir entré cette formule dans la deuxième ligne de votre tableau, vous pouvez la copier et la coller tout au long de votre feuille.
Voici à quoi cela devrait ressembler :
Conclusion
Voilà ! Si vous avez suivi jusqu’à présent, vous avez maintenant créé votre propre calendrier d’amortissement de prêt dans votre feuille de calcul.
Cela vous aidera à mieux comprendre les paiements d’intérêts et de capital pour un prêt au fil du temps. Vous devriez constater que les paiements d’intérêts diminuent et que les paiements de capital augmentent au fil du temps.
À la fin de votre tableau, le solde du prêt devrait atteindre zéro.
Si vous le souhaitez, vous pouvez également additionner les paiements d’intérêts et de capital pour avoir une idée du montant total qui sera versé sur le prêt tout au long de sa durée.
Pour en savoir plus sur Google Sheets, rendez-vous sur Crawlan.com.
Plus de tutoriels sur Google Sheets : Comment utiliser la fonction NPV, Comment créer un budget
