Créer un tableau d'amortissement de prêt dans Excel – Un tutoriel étape par étape

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

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

Créer un tableau d'amortissement dans Excel

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

Créer un tableau d'amortissement avec des paiements supplémentaires

Créer un tableau d'amortissement (avec des paiements supplémentaires) en utilisant un modèle Excel

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

Un tableau 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 tableaux d'amortissement sont couramment utilisés pour les prêts à taux fixe tels que les hypothèques, les prêts automobiles et les prêts personnels, où le montant du paiement reste constant pendant toute la durée du prêt, mais la proportion du paiement allouée aux intérêts par rapport au principal change au fil du temps.

Pour créer un tableau d'amortissement de prêt dans Excel, les fonctions intégrées PMT, PPMT et IPMT sont effectivement cruciales. Comprendre ce que fait chaque fonction :

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

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

Créer un tableau d'amortissement dans Excel

Dans cette section, nous présenterons deux méthodes distinctes pour créer un tableau d'amortissement dans Excel. Ces méthodes répondent à différents préférences et niveaux de compétences des utilisateurs, garantissant que n'importe qui, indépendamment de sa maîtrise d'Excel, puisse construire avec succès un tableau d'amortissement détaillé et précis pour son prêt.

Les formules offrent une compréhension plus approfondie des calculs sous-jacents et fournissent la flexibilité de personnaliser le tableau selon des exigences spécifiques. Cette approche est idéale pour ceux qui souhaitent avoir une expérience pratique et une vue claire de la façon dont chaque paiement est décomposé en composantes de capital et d'intérêt. Maintenant, décomposons étape par étape le processus de création d'un tableau d'amortissement dans Excel :

⭐️ Étape 1 : Configurer les informations du 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 le montre la capture d'écran suivante :
   
2. Ensuite, créez un tableau d'amortissement dans Excel avec les étiquettes spécifiées, telles que Période, Paiement, Intérêt, Principal, Solde restant dans les cellules A7:E7.
3. Dans la colonne Période, entrez les numéros de période. Pour cet exemple, le nombre total de paiements est de 24 mois (2 ans), donc vous entrerez les nombres de 1 à 24 dans la colonne Période. Voir la 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 à entrer les formules et valeurs pour les colonnes Paiement, Intérêt, Principal et Solde en fonction des spécificités de votre prêt.

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

La syntaxe de la fonction PMT est :

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 la capture d'écran :

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

⭐️ Étape 3 : Calculer l'intérêt en utilisant la fonction IPMT

Dans cette étape, vous calculerez l'intérêt pour chaque période de paiement en utilisant la fonction IPMT d'Excel.

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

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

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

Après avoir calculé l'intérêt pour chaque période, l'étape suivante dans la création d'un tableau d'amortissement consiste à calculer la partie principale de chaque paiement. Cela se fait en utilisant la fonction PPMT, qui est conçue pour déterminer la partie principale d'un paiement pour une période spécifique, basée sur des paiements constants et un taux d'intérêt constant.

La syntaxe de la fonction IPMT est :

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

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

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

⭐️ Étape 5 : Calculer le solde restant

Après avoir calculé à la fois l'intérêt et le principal de chaque paiement, l'étape suivante dans votre tableau d'amortissement consiste à calculer le solde restant du prêt après chaque paiement. C'est une partie cruciale du tableau 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 le 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 principal de la période actuelle du solde de la période précédente. Veuillez appliquer la formule suivante dans la cellule E8 :

   =E7-D8

    Remarque : La référence à la cellule de solde doit être relative, afin qu'elle se mette à jour lorsque vous faites glisser la formule vers le bas.
   
3. Et ensuite faites glisser la poignée de recopie vers le bas dans la colonne. Comme vous pouvez le voir, chaque cellule s'ajustera automatiquement pour calculer le solde restant en fonction des paiements principaux mis à jour.
   

⭐️ Étape 6 : Faire un résumé du prêt

Après avoir configuré votre tableau d'amortissement détaillé, créer un résumé du prêt peut fournir un aperçu rapide des aspects clés de votre prêt. Ce résumé inclura généralement le coût total du prêt, les intérêts totaux payés.

● Pour calculer les paiements totaux :

=SUM(B7:B30)

● Pour calculer les intérêts totaux :

=SUM(C7:C30)

⭐️ Résultat :

Maintenant, un tableau d'amortissement de prêt simple mais complet a été créé avec succès. Voir la capture d'écran :

Découvrez la magie d'Excel avec Kutools AI

• Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
• Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
• Codage VBA : Écrivez et implémentez du code VBA sans effort.
• Interprétation des formules : Comprenez facilement des formules complexes.
• Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.

Améliorez vos capacités Excel avec des outils alimentés par l'IA. Téléchargez maintenant et découvrez une efficacité sans précédent !

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

Dans l'exemple précédent, nous avons créé un calendrier de remboursement de prêt pour un nombre fixe de paiements. Cette approche est parfaite pour gérer un prêt ou une hypothèque spécifique où les conditions ne changent pas.

Cependant, si vous souhaitez créer un tableau d'amortissement flexible qui peut être réutilisé 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 : Configurer les informations du 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 le montre la capture d'écran suivante :
   
2. Ensuite, créez un tableau d'amortissement dans Excel avec les étiquettes spécifiées, telles que Période, Paiement, Intérêt, Principal, Solde restant dans les cellules A7:E7.
3. Dans la colonne Période, entrez le nombre maximum de paiements que vous pourriez envisager pour n'importe quel prêt, par exemple, remplissez 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êt et de principal avec la fonction SI

Entrez 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 SI, la syntaxe de la formule est :

Donc les formules sont celles-ci :

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

● Formule d'intérêt :

La syntaxe de la formule est :

Donc les formules sont celles-ci :

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

● Formule de principal :

La syntaxe de la formule est :

Donc les formules sont celles-ci :

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

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

Pour le solde restant, vous pourriez habituellement soustraire le principal du solde précédent. Avec une instruction SI, modifiez-la comme suit :

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

=B4-D7

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

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

⭐️ Étape 4 : Faire un résumé du prêt

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

● Pour calculer les paiements totaux :

=SUM(B7:B366)

● Pour calculer les intérêts totaux :

=SUM(C7:C366)

⭐️ Résultat :

Maintenant, vous disposez d'un tableau d'amortissement complet et dynamique dans Excel, accompagné d'un résumé détaillé du prêt. Chaque fois que vous ajustez la durée des périodes de paiement, l'ensemble du tableau d'amortissement se mettra automatiquement à jour pour refléter ces changements. Voir la démonstration ci-dessous :

Créer un tableau d'amortissement avec des paiements supplémentaires

En effectuant des paiements supplémentaires au-delà de ceux prévus, un prêt peut être remboursé plus rapidement. Un tableau d'amortissement qui incorpore 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 diminuer le montant total des intérêts payés. Voici comment vous pouvez le configurer :

⭐️ Étape 1 : Configurer les informations du 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 le montre la capture d'écran suivante :
   
2. Ensuite, calculez le paiement prévu.
   En plus des cellules d'entrée, 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 de paiement régulier sur 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, Paiement prévu, Paiement supplémentaire, Paiement total, Intérêt, Principal, Solde restant dans les cellules A8:G8 ;
   • Dans la colonne Période, entrez le nombre maximum de paiements que vous pourriez envisager pour n'importe quel prêt. Par exemple, remplissez les nombres allant de 0 à 360. Cela peut couvrir un prêt standard de 30 ans si vous effectuez des paiements 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 être laissées vides.
   • 

⭐️ Étape 2 : Créer les formules pour le tableau d'amortissement avec des paiements supplémentaires

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

● Calculer le paiement prévu :

Entrez la formule suivante dans la cellule B10 :

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

● Calculer le paiement supplémentaire :

Entrez la formule suivante dans la cellule C10 :

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

● Calculer le paiement total :

Entrez la formule suivante dans la cellule D10 :

=IFERROR(B10+C10, "")

● Calculer le principal :

Entrez la formule suivante dans la cellule E10 :

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

● Calculer l'intérêt :

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 complété 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 à travers l'ensemble des périodes de paiement. Pour toutes les périodes non utilisées, les cellules afficheront des 0. Voir la capture d'écran :

⭐️ Étape 3 : Faire un résumé du prêt

● Obtenir le nombre de paiements prévus :

=B2:B3

● Obtenir le nombre réel de paiements :

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

● Obtenir les paiements supplémentaires totaux :

=SUM(C10:C369)

● Obtenir les intérêts totaux :

=SUM(F10:F369)

⭐️ Résultat :

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

Créer un tableau d'amortissement en utilisant un modèle Excel

Créer un tableau d'amortissement dans Excel en utilisant un modèle est une méthode simple et efficace en termes de temps. Excel fournit des modèles intégrés qui calculent automatiquement l'intérêt, le principal et le solde restant de chaque paiement. Voici comment créer un tableau d'amortissement en utilisant un modèle Excel :

1. Cliquez Fichier > Nouveau, dans la zone de recherche, tapez tableau d'amortissement, et appuyez sur Entrée . Ensuite, sélectionnez le modèle qui correspond le mieux à vos besoins en cliquant dessus. Par exemple, ici, je sélectionnerai le modèle Simple calculateur de prêt. Voir la capture d'écran :
   
2. Une fois que vous avez sélectionné un modèle, cliquez sur le bouton Créer pour l'ouvrir comme un nouveau classeur.
3. Ensuite, entrez vos propres détails de 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 tableau d'amortissement.