Créer un tableau d’amortissement de prêt dans Excel – Tutoriel pas à pas

Créer un tableau d’amortissement d’emprunt dans Excel est une compétence précieuse qui vous permet de visualiser et de gérer efficacement vos remboursements. Un tableau d’amortissement détaille chaque paiement périodique d’un emprunt amortissable — typiquement un prêt immobilier ou automobile — en décomposant chaque versement entre intérêts et capital, tout en indiquant le solde restant après chaque échéance. Plongeons ensemble dans un guide pas à pas pour créer ce 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 paiements supplémentaires) à l’aide d’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 d’emprunt qui illustre le processus de remboursement d’un prêt au fil du temps. Les tableaux d’amortissement sont Couramment utilisé pour les emprunts à taux fixe tels que les prêts immobiliers, les prêts automobiles et les prêts personnels, où le montant du remboursement reste constant pendant toute la durée de l’emprunt, mais où la proportion du remboursement affectée aux intérêts par rapport au capital évolue au fil du temps.

Pour créer un tableau d’amortissement d’emprunt dans Excel, les fonctions intégrées VPM, PRINCPER et INTPER sont effectivement indispensables. Découvrons précisément le rôle de chacune d’elles :

• Fonction VPM : Cette fonction calcule le montant à rembourser à chaque période pour un emprunt, en supposant des remboursements et un taux d’intérêt constants.
• Fonction INTPER : Cette fonction calcule la part des intérêts dans un remboursement pour une période donnée.
• Fonction PRINCPER : Cette fonction calcule la part du capital remboursée lors d’une période donnée.

En exploitant ces fonctions dans Excel, vous pouvez générer un tableau d’amortissement détaillé présentant la part des intérêts et celle du capital pour chaque remboursement, ainsi que le solde restant de l’emprunt après chaque paiement.

Créer un tableau d’amortissement dans Excel

Dans cette section, nous vous présentons deux méthodes distinctes pour créer un tableau d’amortissement dans Excel. Conçues pour s’adapter à différents niveaux de compétence et préférences, elles permettent à chacun, quels que soient ses talents en Excel, de construire facilement un tableau d’amortissement détaillé et précis pour son emprunt.

Les formules offrent une compréhension plus approfondie des calculs sous-jacents et permettent d’adapter le tableau à des besoins spécifiques. Cette approche convient particulièrement aux personnes recherchant une expérience pratique ainsi qu’une vision claire de la répartition, à chaque échéance, entre capital et intérêts. Découvrons maintenant, étape par étape, comment créer un tableau d’amortissement dans Excel :

⭐️ Étape 1 : Configurez les informations relatives à l’emprunt et le tableau d’amortissement

1. Saisissez les informations relatives à l’emprunt, telles que le taux d’intérêt annuel, la durée de l’emprunt en années, le nombre de remboursements par an et le montant de l’emprunt, dans les cellules comme indiqué sur la capture d’écran suivante :
   
2. Ensuite, créez un tableau d’amortissement dans Excel en insérant les libellés suivants dans les cellules A7 à E7 : Période, Remboursement, Intérêts, Capital et Solde restant.
3. Dans la colonne **Période**, saisissez les numéros de période correspondants. Dans cet exemple, le remboursement s’étale sur 24 mois (soit 2 ans), vous devrez donc entrer les chiffres de **1 à 24** dans cette colonne. Voir la capture d’écran :
   
4. Une fois le tableau configuré avec les libellés et les numéros de période, saisissez les formules et les valeurs dans les colonnes Remboursement, Intérêts, Capital et Solde selon les caractéristiques spécifiques de votre emprunt.

⭐️ Étape 2 : Calculez le montant total du remboursement à l’aide de la fonction VPM

La syntaxe de la fonction VPM est la suivante :

Saisissez la formule suivante dans la cellule B7, puis faites glisser la poignée de recopie vers le bas pour l’appliquer aux autres cellules : vous obtiendrez ainsi un montant de remboursement constant sur toutes les périodes. Voir la capture d’écran :

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

⭐️ Étape 3 : Calculez les intérêts à l’aide de la fonction INTPER

À cette étape, vous allez calculer les intérêts de chaque période de remboursement à l’aide de la fonction INTPER d’Excel.

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

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

⭐️ Étape 4 : Calculez le capital à l’aide de la fonction PRINCPER

Après avoir calculé les intérêts pour chaque période, l’étape suivante dans la création d’un tableau d’amortissement consiste à déterminer la part du capital de chaque remboursement à l’aide de la fonction PRINCPER. Conçue pour isoler la portion en capital d’un versement donné, cette fonction s’appuie sur des remboursements constants et un taux d’intérêt fixe.

La syntaxe de la fonction INTPER est la suivante :

La syntaxe et les paramètres de la formule PRINCPER sont identiques à ceux de la formule INTPER présentée précédemment.

Saisissez la formule suivante dans la cellule D7, puis faites glisser la poignée de recopie vers le bas de la colonne afin de remplir automatiquement les montants du capital pour chaque période. Voir la 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é à la fois les intérêts et le capital de chaque remboursement, l’étape suivante de votre tableau d’amortissement consiste à déterminer le solde restant de l’emprunt après chaque échéance. Cette étape est essentielle, car elle illustre clairement la manière dont le solde diminue progressivement au fil du temps.

1. Dans la première cellule de votre colonne Solde – E7, saisissez la formule suivante, qui signifie que le solde restant sera égal au montant initial de l’emprunt moins la part du capital du premier remboursement :

   =B4-D7

   
2. Pour la deuxième période et toutes les périodes suivantes, calculez le solde restant en soustrayant le remboursement en capital de la période courante du solde de la période précédente. Saisissez la formule suivante dans la cellule E8 :

   =E7-D8

   Remarque : La référence à la cellule du solde doit être relative afin qu’elle soit automatiquement mise à jour lorsque vous étirez la formule vers le bas.
   
3. Ensuite, faites glisser la poignée de recopie vers le bas de la colonne. Comme vous pouvez le constater, chaque cellule s’ajuste automatiquement pour calculer le solde restant en fonction des remboursements en capital mis à jour.
   

⭐️ Étape 6 : Créez un résumé de l’emprunt

Après avoir configuré votre tableau d’amortissement détaillé, la création d’un résumé de l’emprunt permet d’obtenir rapidement un aperçu des aspects clés de celui-ci. Ce résumé inclura généralement le coût total de l’emprunt et le montant total des intérêts payés.

● Pour calculer le total des remboursements :

=SUM(B7:B30)

● Pour calculer le total des intérêts :

=SUM(C7:C30)

⭐️ Résultat :

Vous avez désormais créé avec succès un tableau d’amortissement d’emprunt à la fois simple et complet. Voir la capture d’écran :

Libérez la magie d’Excel avec Kutools IA

• Exécution intelligente : Effectuez des opérations sur les cellules, analysez des données et créez des graphiques — le tout grâce à de simples commandes.
• formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
• Programmation VBA : rédigez et implémentez du code VBA en toute simplicité.
• Interprétation de formules : Comprenez facilement les formules complexes.
• Traduction de texte : Franchissez les barrières linguistiques directement dans vos feuilles de calcul.

Améliorez vos compétences Excel grâce aux outils alimentés par l’IA.Téléchargez dès maintenantet découvrez une efficacité inégalée !

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

Dans l’exemple précédent, nous avons établi un calendrier de remboursement d’emprunt pour un nombre fixe d’échéances. Cette approche s’adapte parfaitement à la gestion d’un prêt ou d’un emprunt immobilier dont les conditions restent inchangées.

Mais si vous souhaitez créer un tableau d’amortissement flexible, réutilisable pour des prêts de durées variables et vous permettant d’ajuster le nombre d’échéances selon différents scénarios, une méthode plus détaillée s’impose.

⭐️ Étape 1 : Saisir les informations relatives au prêt et créer le tableau d’amortissement

1. Saisissez les informations relatives à l’emprunt, telles que le taux d’intérêt annuel, la durée de l’emprunt en années, le nombre de remboursements par an et le montant de l’emprunt, dans les cellules comme indiqué sur la capture d’écran suivante :
   
2. Ensuite, créez un tableau d’amortissement dans Excel en insérant les libellés suivants dans les cellules A7 à E7 : Période, Remboursement, Intérêts, Capital et Solde restant.
3. Dans la colonne Période, saisissez le nombre maximal de remboursements que vous pourriez envisager pour n’importe quel emprunt — par exemple, entrez des valeurs allant de 1 à 360, ce qui couvre un prêt standard sur 30 ans avec des remboursements mensuels.
   

⭐️ Étape 2 : Modifier les formules de remboursement, d’intérêts et de capital à l’aide de la fonction SI

Saisissez les formules suivantes dans les cellules correspondantes, puis faites glisser la poignée de recopie vers le bas jusqu’au nombre maximal de périodes de remboursement que vous avez défini.

● Formule de remboursement :

Normalement, vous utilisez la fonction VPM pour calculer le montant de votre remboursement. Pour y intégrer une instruction SI, la syntaxe de la formule est la suivante :

La formule est donc la suivante :

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

● Formule des intérêts :

La syntaxe de la formule est la suivante :

La formule est donc la suivante :

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

● Formule du capital :

La syntaxe de la formule est la suivante :

La formule est donc la suivante :

=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 soustrayez généralement le capital remboursé du solde précédent. À l’aide d’une instruction SI, modifiez-la comme suit :

● Première cellule du solde : (E7)

=B4-D7

● Deuxième cellule du solde : (E8)

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

⭐️ Étape 4 : Créer un récapitulatif du prêt

Une fois le tableau d’amortissement configuré avec les formules ajustées, l’étape suivante consiste à créer un récapitulatif clair et percutant de votre prêt.

● Pour calculer le total des remboursements :

=SUM(B7:B366)

● Pour calculer le total des intérêts :

=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é de votre prêt. Dès que vous modifiez la durée du prêt, le tableau d’amortissement s’actualise automatiquement pour refléter ces changements. Découvrez la démonstration ci-dessous :

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

En effectuant des remboursements supplémentaires au-delà des échéances prévues, vous accélérez le remboursement de votre prêt. Un tableau d’amortissement intégrant ces paiements additionnels, une fois créé dans Excel, montre clairement comment ces versements anticipés réduisent à la fois la durée du prêt et le montant total des intérêts payés. Voici comment le configurer :

⭐️ Étape 1 : Saisir les informations relatives au prêt et créer le tableau d’amortissement

1. Saisissez les informations relatives à l’emprunt, telles que le taux d’intérêt annuel, la durée de l’emprunt en années, le nombre de remboursements par an, le montant de l’emprunt et le remboursement supplémentaire dans les cellules comme indiqué sur la capture d’écran suivante :
   
2. Ensuite, calculez le remboursement prévu.
   Outre les cellules de saisie, une autre cellule prédéfinie est indispensable pour nos calculs ultérieurs : le montant du remboursement prévu. Il correspond au montant régulier à rembourser pour un emprunt, sous l’hypothèse qu’aucun remboursement supplémentaire n’est effectué. Saisissez 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 libellés spécifiés, tels que Période, Échéance, Paiement supplémentaire, Paiement total, Intérêts, Capital, Solde restant dans les cellules A8:G8 ;
   • Dans la colonne Période, saisissez le nombre maximal d’échéances que vous envisagez pour tout prêt — par exemple, une plage allant de 0 à 360, ce qui couvre un prêt standard sur 30 ans avec des paiements mensuels.
   • Pour la période 0 (ligne 9 dans notre cas), saisissez le solde initial à l’aide de 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 du tableau d’amortissement avec remboursements supplémentaires

Saisissez les formules suivantes, une par une, dans les cellules correspondantes. Pour renforcer la gestion des erreurs, nous encapsulons chacune de ces formules — y compris celle-ci — dans la fonction SIERREUR, évitant ainsi de nombreuses erreurs potentielles liées à des cellules d’entrée vides ou contenant des valeurs incorrectes.

● Calculer le remboursement prévu :

Saisissez la formule suivante dans la cellule B10 :

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

● Calculer le remboursement supplémentaire :

Saisissez la formule suivante dans la cellule C10 :

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

● Calculer le remboursement total :

Saisissez la formule suivante dans la cellule D10 :

=IFERROR(B10+C10, "")

● Calculer le capital :

Saisissez la formule suivante dans la cellule E10 :

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

● Calculer les intérêts :

Saisissez la formule suivante dans la cellule F10 :

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

● Calculer le solde restant

Saisissez la formule suivante dans la cellule G10 :

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

Une fois toutes les formules saisies, sélectionnez la plage de cellules B10:G10 et étirez-les à l’aide de la poignée de recopie sur l’ensemble des périodes de remboursement. Les périodes inutilisées afficheront automatiquement la valeur 0. Voir capture d’écran :

⭐️ Étape 3 : Créer un récapitulatif du prêt

● Obtenir le nombre prévu d’échéances :

=B2:B3

● Obtenir le nombre réel d’échéances :

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

● Obtenir le montant total des remboursements supplémentaires :

=SUM(C10:C369)

● Obtenir le montant total des intérêts :

=SUM(F10:F369)

⭐️ Résultat :

En suivant ces étapes, vous créez un tableau d’amortissement dynamique dans Excel qui intègre automatiquement les remboursements supplémentaires.

Créer un tableau d’amortissement à l’aide d’un modèle Excel

Créer un tableau d’amortissement dans Excel à l’aide d’un modèle intégré est une solution simple et rapide. Excel met à disposition des modèles prêts à l’emploi qui calculent automatiquement, pour chaque échéance, les intérêts, le capital remboursé et le solde restant dû. Voici comment créer facilement votre tableau d’amortissement avec un modèle Excel :

1. Cliquez sur Fichier > Nouveau, saisissez tableau d’amortissement dans la zone de recherche, puis appuyez sur la touche Entrée. Ensuite, sélectionnez le modèle qui correspond le mieux à vos besoins en cliquant dessus. Par exemple, ici, je vais choisir le modèle Calculatrice simple d’emprunt. 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 en tant que nouveau classeur.
3. Ensuite, saisissez les détails de votre propre emprunt ; le modèle calculera automatiquement et remplira le tableau en fonction de vos données.
4. Enfin, enregistrez votre nouveau classeur de tableau d’amortissement.