Créer un calendrier d'amortissement de prêt dans Excel peut être une compétence précieuse, vous permettant de visualiser et de gérer efficacement les remboursements de votre prêt. Un tableau d'amortissement est un tableau qui détaille chaque versement périodique sur un prêt amortissable (généralement un prêt hypothécaire ou automobile). Il décompose chaque paiement en éléments d’intérêt et de principal, indiquant le solde restant après chaque paiement. Passons à un guide étape par étape pour créer un tel calendrier dans Excel.

Qu'est-ce qu'un tableau d'amortissement ?

Créer un plan d'amortissement dans Excel

Créer un plan d'amortissement pour un nombre variable de périodes

Créez un plan d'amortissement avec des paiements supplémentaires

Créez un plan d'amortissement (avec paiements supplémentaires) à l'aide du modèle Excel

Qu'est-ce qu'un tableau d'amortissement ?

Un calendrier d'amortissement est un tableau détaillé utilisé dans les calculs de prêt qui affiche le processus de remboursement d'un prêt au fil du temps. Les calendriers d'amortissement sont couramment utilisés pour les prêts à taux fixe tels que les prêts hypothécaires, les prêts automobiles et les prêts personnels, où le montant du paiement reste constant tout au long de la durée du prêt, mais la proportion du paiement en intérêts par rapport au principal change au fil du temps.

En effet, pour créer un tableau d'amortissement des prêts dans Excel, les fonctions intégrées PMT, PPMT et IPMT sont cruciales. Comprenons ce que fait chaque fonction :

• Fonction PMT: Cette fonction permet de calculer le paiement total par période d'un prêt sur la base de paiements constants et d'un taux d'intérêt constant.
• Fonction IPMT: Cette fonction calcule la partie intérêts d'un paiement pour une période donnée.
• Fonction PPMT: Cette fonction est utilisée pour calculer la partie principale d'un paiement pour une période spécifique.

En utilisant ces fonctions dans Excel, vous pouvez créer un plan d'amortissement détaillé qui montre les intérêts et le principal de chaque paiement, ainsi que le solde restant du prêt après chaque paiement.

Créer un plan d'amortissement dans Excel

Dans cette section, nous présenterons deux méthodes distinctes pour créer un plan d'amortissement dans Excel. Ces méthodes répondent aux différentes préférences des utilisateurs et niveaux de compétence, garantissant que quiconque, quelle que soit sa maîtrise d'Excel, puisse construire avec succès un calendrier d'amortissement détaillé et précis pour son prêt.

Les formules offrent une compréhension plus approfondie des calculs sous-jacents et offrent la flexibilité nécessaire pour personnaliser le calendrier en fonction d'exigences spécifiques. Cette approche est idéale pour ceux qui souhaitent avoir une expérience pratique et un aperçu clair de la façon dont chaque paiement est décomposé en composantes de principal et d’intérêts. Maintenant, décomposons étape par étape le processus de création d'un plan d'amortissement dans Excel :

⭐️ Étape 1 : Mettre en place les informations sur le prêt et le tableau d'amortissement

1. Entrez les informations relatives au prêt, telles que le taux d'intérêt annuel, la durée du prêt en années, le nombre de paiements par an et le montant du prêt dans les cellules comme illustré ci-dessous :
   
2. Ensuite, créez un tableau d'amortissement dans Excel avec les étiquettes spécifiées, telles que Période, Paiement, Intérêts, Principal, Solde restant dans les cellules A7:E7.
3. Dans la colonne Période, saisissez les numéros de période. Pour cet exemple, le nombre total de paiements est de 24 mois (2 ans), vous entrerez donc les chiffres de 1 à 24 dans la colonne Période. Voir capture d'écran :
   
4. Une fois que vous avez configuré le tableau avec les étiquettes et les numéros de période, vous pouvez procéder à la saisie des formules et des valeurs pour les colonnes Paiement, Intérêts, Principal et Solde en fonction des spécificités de votre prêt.

⭐️ Étape 2 : Calculez le montant total du paiement en utilisant la fonction PMT

La syntaxe du PMT est la suivante :

Entrez la formule suivante dans la cellule B7, puis faites glisser la poignée de recopie vers le bas pour remplir cette formule dans d'autres cellules, et vous verrez un montant de paiement constant pour toutes les périodes. Voir capture d'écran :

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Étape 3 : Calculez les intérêts en utilisant la fonction IPMT

Dans cette étape, vous calculerez les intérêts pour chaque période de paiement à l'aide de la fonction IPMT d'Excel.

Entrez la formule suivante dans la cellule C7, puis faites glisser la poignée de recopie vers le bas de la colonne pour remplir cette formule et obtenir les intérêts pour chaque période.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Étape 4 : Calculez le principal en utilisant la fonction PPMT

Après avoir calculé les intérêts pour chaque période, l’étape suivante de la création d’un calendrier d’amortissement consiste à calculer la partie principale de chaque paiement. Cela se fait à l'aide de la fonction PPMT, conçue pour déterminer la partie principale d'un paiement pour une période spécifique, sur la base de paiements constants et d'un taux d'intérêt constant.

La syntaxe de l'IPMT est :

La syntaxe et les paramètres de la formule PPMT sont identiques à ceux utilisés dans la formule IPMT évoquée précédemment.

Entrez la formule suivante dans la cellule D7, puis faites glisser la poignée de recopie vers le bas de la colonne pour renseigner le principal pour chaque période. Voir capture d'écran :

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Étape 5 : Calculez le solde restant

Après avoir calculé les intérêts et le capital de chaque paiement, l’étape suivante de votre calendrier d’amortissement consiste à calculer le solde restant du prêt après chaque paiement. Il s’agit d’une partie cruciale du calendrier car elle montre comment le solde du prêt diminue au fil du temps.

1. Dans la première cellule de votre colonne de solde – E7, entrez la formule suivante, ce qui signifie que le solde restant sera égal au montant initial du prêt moins la partie principale du premier paiement :

   =B4-D7

   
2. Pour la deuxième période et toutes les périodes suivantes, calculez le solde restant en soustrayant le paiement du principal de la période en cours du solde de la période précédente. Veuillez appliquer la formule suivante dans la cellule E8 :

   =E7-D8

    Notes: La référence à la cellule de solde doit être relative, elle se met donc à jour lorsque vous faites glisser la formule vers le bas.
   
3. Et puis faites glisser la poignée de remplissage vers la colonne. Comme vous pouvez le voir, chaque cellule s'ajustera automatiquement pour calculer le solde restant en fonction des paiements de principal mis à jour.
   

⭐️ Étape 6 : Réaliser un récapitulatif de prêt

Après avoir établi votre calendrier d'amortissement détaillé, la création d'un résumé de prêt peut fournir un aperçu rapide des aspects clés de votre prêt. Ce résumé comprendra généralement le coût total du prêt, le total des intérêts payés.

● Pour calculer le total des paiements :

=SUM(B7:B30)

● Pour calculer les intérêts totaux :

=SUM(C7:C30)

⭐️ Résultat :

Aujourd’hui, un calendrier d’amortissement des prêts simple mais complet a été créé avec succès. voir capture d'écran :

Créer un plan d'amortissement pour un nombre variable de périodes

Dans l'exemple précédent, nous créons un échéancier de remboursement de prêt pour un nombre fixe de versements. Cette approche est parfaite pour gérer un prêt ou une hypothèque spécifique dont les conditions ne changent pas.

Mais si vous cherchez à créer un calendrier d'amortissement flexible qui peut être utilisé à plusieurs reprises pour des prêts avec des périodes variables, vous permettant de modifier le nombre de paiements selon les besoins pour différents scénarios de prêt, vous devrez suivre une méthode plus détaillée.

⭐️ Étape 1 : Mettre en place les informations sur le prêt et le tableau d'amortissement

1. Entrez les informations relatives au prêt, telles que le taux d'intérêt annuel, la durée du prêt en années, le nombre de paiements par an et le montant du prêt dans les cellules comme illustré ci-dessous :
   
2. Ensuite, créez un tableau d'amortissement dans Excel avec les étiquettes spécifiées, telles que Période, Paiement, Intérêts, Principal, Solde restant dans les cellules A7:E7.
3. Dans la colonne Période, saisissez le nombre de paiements le plus élevé que vous pourriez envisager pour un prêt, par exemple, indiquez les nombres allant de 1 à 360. Cela peut couvrir un prêt standard de 30 ans si vous effectuez des paiements mensuels.
   

⭐️ Étape 2 : Modifier les formules de paiement, d'intérêts et de principe avec la fonction IF

Saisissez les formules suivantes dans les cellules correspondantes, puis faites glisser la poignée de recopie pour étendre ces formules jusqu'au nombre maximum de périodes de paiement que vous avez défini.

● Formule de paiement :

Normalement, vous utilisez la fonction PMT pour calculer le paiement. Pour incorporer une instruction IF, la formule syntaxique est la suivante :

Donc la formule est la suivante :

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Formule d'intérêt :

La formule syntaxique est la suivante :

Donc la formule est la suivante :

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Formule de principe :

La formule syntaxique est la suivante :

Donc la formule est la suivante :

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Étape 3 : Ajustez la formule du solde restant

Pour le solde restant, vous pouvez généralement soustraire le capital du solde précédent. Avec une instruction IF, modifiez-la comme :

● La première cellule de solde : (E7)

=B4-D7

● La deuxième cellule de bilan :(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Étape 4 : Réaliser un récapitulatif de prêt

Après avoir établi le plan d'amortissement avec les formules modifiées, l'étape suivante consiste à créer un récapitulatif du prêt.

● Pour calculer le total des paiements :

=SUM(B7:B366)

● Pour calculer les intérêts totaux :

=SUM(C7:C366)

⭐️ Résultat :

Vous disposez désormais d’un tableau d’amortissement complet et dynamique dans Excel, accompagné d’un récapitulatif détaillé du prêt. Chaque fois que vous ajustez la durée de la période de paiement, l’ensemble du calendrier d’amortissement sera automatiquement mis à jour pour refléter ces changements. Voir la démo ci-dessous :

Créez un plan d'amortissement avec des paiements supplémentaires

En effectuant des versements supplémentaires au-delà de ceux prévus, un prêt peut être remboursé plus rapidement. Un plan d'amortissement intégrant des paiements supplémentaires, lorsqu'il est créé dans Excel, montre comment ces paiements supplémentaires peuvent accélérer le remboursement du prêt et réduire le total des intérêts payés. Voici comment vous pouvez le configurer :

⭐️ Étape 1 : Mettre en place les informations sur le prêt et le tableau d'amortissement

1. Entrez les informations relatives au prêt, telles que le taux d'intérêt annuel, la durée du prêt en années, le nombre de paiements par an, le montant du prêt et le paiement supplémentaire dans les cellules comme illustré ci-dessous :
   
2. Ensuite, calculez le paiement prévu.
   En plus des cellules de saisie, une autre cellule prédéfinie est nécessaire pour nos calculs ultérieurs : le montant du paiement prévu. Il s’agit du montant du paiement régulier d’un prêt en supposant qu’aucun paiement supplémentaire n’est effectué. Veuillez appliquer la formule suivante dans la cellule B6 :

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Ensuite, créez un tableau d'amortissement dans Excel :
   • Définissez les étiquettes spécifiées, telles que Période, Calendrier de paiement, Paiement supplémentaire, Paiement total, Intérêts, Principal, Solde restant dans les cellules A8: G8 ;
   • Dans la colonne Période, saisissez le nombre de paiements le plus élevé que vous pourriez envisager pour un prêt. Par exemple, remplissez les chiffres allant de 0 à 360. Cela peut couvrir un prêt standard sur 30 ans si vous effectuez des versements mensuels ;
   • Pour la Période 0 (ligne 9 dans notre cas), récupérez la valeur du Solde avec cette formule = B4, qui correspond au montant initial du prêt. Toutes les autres cellules de cette ligne doivent rester vides.
   • 

⭐️ Étape 2 : Créer les formules de plan d'amortissement avec versements supplémentaires

Entrez les formules suivantes dans les cellules correspondantes une par une. Pour améliorer la gestion des erreurs, nous enfermons cette formule et toutes les formules futures dans la fonction SIERREUR. Cette approche permet d'éviter plusieurs erreurs potentielles qui pourraient survenir si l'une des cellules d'entrée est laissée vide ou contient des valeurs incorrectes.

● Calculez l'échéancier :

Entrez la formule suivante dans la cellule B10 :

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Calculez le paiement supplémentaire :

Entrez la formule suivante dans la cellule C10 :

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Calculez le paiement total :

Entrez la formule suivante dans la cellule D10 :

=IFERROR(B10+C10, "")

● Calculez le principal :

Entrez la formule suivante dans la cellule E10 :

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Calculez les intérêts :

Entrez la formule suivante dans la cellule F10 :

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Calculer le solde restant

Entrez la formule suivante dans la cellule G10 :

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Une fois que vous avez terminé chacune des formules, sélectionnez la plage de cellules B10: G10 et utilisez la poignée de recopie pour faire glisser et étendre ces formules sur l'ensemble des périodes de paiement. Pour toute période non utilisée, les cellules afficheront des 0. Voir capture d'écran :

⭐️ Étape 3 : Réaliser un récapitulatif de prêt

● Obtenez le nombre de paiements planifiés :

=B2:B3

● Obtenez le nombre réel de paiements :

=COUNTIF(D10:D369,">"&0)

● Obtenez le total des paiements supplémentaires :

=SUM(C10:C369)

● Obtenez un intérêt total :

=SUM(F10:F369)

⭐️ Résultat :

En suivant ces étapes, vous créez un plan d'amortissement dynamique dans Excel qui prend en compte les paiements supplémentaires.

Créez un plan d'amortissement à l'aide du modèle Excel

Créer un plan d'amortissement dans Excel à l'aide d'un modèle est une méthode simple et rapide. Excel fournit des modèles intégrés qui calculent automatiquement les intérêts, le principal et le solde restant de chaque paiement. Voici comment créer un plan d'amortissement à l'aide d'un modèle Excel :

1. Cliquez Déposez votre dernière attestation > Nouveauté, dans la zone de recherche, saisissez Calendrier d'amortissementet appuyez sur Entrer clé. Ensuite, sélectionnez le modèle qui correspond le mieux à vos besoins en cliquant dessus. Par exemple, ici, je sélectionnerai un modèle de calculateur de prêt simple. Voir capture d'écran :
   
2. Une fois que vous avez sélectionné un modèle, cliquez sur le Création bouton pour l’ouvrir en tant que nouveau classeur.
3. Ensuite, entrez les détails de votre prêt, le modèle devrait automatiquement calculer et remplir le calendrier en fonction de vos entrées.
4. Enfin, enregistrez votre nouveau classeur de plan d’amortissement.