Construire un échéancier d’emprunt avec Excel

Dernière mise à jour le 05/02/2024
Temps de lecture : 3 minutes

Un échéancier d'emprunt dans Excel vous permet de savoir pour chaque période

  1. La part de l'emprunt que vous remboursez réellement chaque mois

    Pour cela, il faut utiliser la fonction CUMUL.PRINCPER

  2. Le montant des intérêts que vous remboursez également chaque mois

    Connaître cette information est très importante pour des raisons de défiscalisation. En effet, la part de l'intérêt d'un emprunt, peut être déduite des impôts sur le revenu

Construire un échéancier d'emprunt dans la situation suivante

Nous allons prendre le cas où nous voulons faire un emprunt de 2500€ sur 1 an (12 mois) au taux de 10% annuel. Comme nous l'avons vu dans l'article sur le calcul des mensualités, nous pouvons déduire de ces informations le taux d'intérêt mensuel.

Ensuite, grâce à la fonction VPM, il est très facile de connaître le montant à rembourser tous les mois.

=VPM(B5;B3;B1)*-1 => 219,29

Calcul de la mensualite avec VPM

Sur les 219,29€ mensuels, quel montant est consacré au remboursement de l'emprunt le premier mois, le deuxième mois, ...

Calcul de la part de l'intérêt dans l'échéancier d'emprunt

La fonction CUMUL.INTER a été spécialement créée pour calculer la part de l'intérêt pour chaque période.

=CUMUL.INTER(taux;nombre d'échéances;montant emprunt;période début;période fin;type)

Les 3 premiers paramètres se comprennent facilement. Attention au calcul du taux annuel ou mensuel comme vu précédemment.

Ce qui est le plus difficile à paramétrer, ce sont les paramètres 4 et 5 de la fonction. Ce sont eux qui sont souvent à l'origine des erreurs. Maintenant, pour connaître le montant de l'intérêt versé sur la première période, il faut écrire la même valeur pour le 4e et 5e paramètre.

=CUMUL.INTER(0.797%;12;2500;1;1;0)*-1 => 19,94

Calcul de l'intérêt du premier mois

Pour la deuxième année, il faut écrire la formule

=CUMUL.INTER(0.797%;12;2500;2;2;0)*-1 => 18,35

Calcul de l'intérêt du deuxième mois

Et maintenant, si vous voulez connaître la part de l'intérêt que vous avez payé sur la première et deuxième périodes, il faut écrire

=CUMUL.INTER(0.797%;12;2500;1;2;0)*-1 => 38,28

Calcul de l'intérêt du mois 1 et 2

Calcul de la part de l'emprunt réellement remboursé

Si maintenant, vous voulez connaître la part du bien que vous avez remboursé à chaque période, il faut utiliser la fonction CUMUL.PRINCPER (le cumul du principal).

=CUMUL.PRINCPER(taux;nombre d'échéances;montant emprunt;période début;période fin;type)

Rien ne change dans la construction de cette formule par rapport à la fonction CUMUL.INTER. Seul le support (emprunt ou intérêt) est calculé. Pour le premier mois, le résultat est donc le suivant (regardez les 4e et 5e paramètres)

=CUMULPRINCPER(0.797%;12;2500;1;1;0)*-1 => 199,35€

Différence entre la part de l'emprunt remboursé et l'intérêt

Construction de l'échéancier par période

Et donc, il est facile, à partir de là, de construire dans Excel un échéancier d'emprunt, mois par mois avec :

  1. Pour chaque ligne le total équivalent à une mensualité
  2. En colonne E le total du montant emprunté
  3. Puis en colonne F le total des intérêts versés
  4. Colonne G le montant total déboursé au titre de l'emprunt
Échéancier bancaire pour un prêt

Vous pouvez également utiliser la fonction SEQUENCE d'Excel pour faire le même échéancier d'emprunt de façon dynamique. Comme ça, en changeant la valeur de la période, tout le tableau est automatiquement recalculé.

Création dun tableau de remboursement dynamique

Vidéo explicative

Vous pouvez aussi voir la construction complète de l'échéancier dans cette vidéo

6 Comments

  1. A
    03/03/2023 @ 17:45

    Bonjour,
    Merci pour votre vidéo . Elle est très claire et instructive...Juste une info : les emprunts immobiliers, en France, sont régis par les intérêts simples...Il convient donc de diviser le taux annuel par 12 pour obtenir le taux mensuel...puisqu'il est proportionnel au taux annuel...
    Vous avez, par ailleurs, totalement raison d'insister sur le fait que les emprunts à la consommation sont calculés à raison de taux actuariels puisque régis par la "mécanique" des intérêts composés...Il convient de passer par la formule que vous détaillez pour retrouver le taux périodique mensuel => (1+tA ^1/12) -1
    Cdt,

    Reply

    • Frédéric LE GUEN
      04/03/2023 @ 05:15

      Alors ça c'est un super commentaire. Merci infiniment. J'avais appris cette technique de calcul des taux d'intérêt mensuels quand j'étais étudiant mais je ne sais pas dans quel cas ça s'appliquait (ni d'ailleurs que pour les emprunts immobiliers ont juste besoin d'une division par 12).
      Pourriez-vous m'adresser à contact@excel-exercice.com des liens sur le sujet, histoire que je fasse un article sur ce point.
      C'est comme la méthode de l'arrondi des banquiers. Si on ne vous l'explique pas, jamais vous ne saurez ce que c'est et l'importance que cà a.

      Reply

  2. KOFFI Franck
    15/03/2021 @ 15:04

    Bonjour Frédéric LE GUEN

    j'aimerais connaitre la formule de calcul CUMUL.INTER (taux;nombre d'échéances;montant emprunt;période début;période fin;type) tenant compte d'un différé de paiement (en mois).

    Merci

    Reply

  3. Mirakle
    12/12/2020 @ 14:18

    Je pense qu'il y a une erreur dans l'explication sur les paramètres 4 et 5 : le texte indique qu'ils faut les mettre à 1 pour l'année 1 et à 2 pour l'année 2. Mais en fait d'année, ce sont plutôt les numéros des périodes (dans l'exemple ce sont donc des mois).

    Reply

    • YOX
      10/05/2022 @ 10:54

      la formule de calcul du cout sur une periode n'est pas bon, ....la mensualité en revanche a l'aire correcte

      Reply

      • Frédéric LE GUEN
        10/05/2022 @ 13:07

        Comment ça ? Vous avez un exemple pour que je puisse controler ?

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur la façon dont les données de vos commentaires sont traitées.

Microsoft MVP 2024

Newsletter

1 fois par mois :
Astuces et quiz

    Nous ne vous enverrons pas de spam. Vous pouvez vous désabonner à tout moment.

    Construire un échéancier d’emprunt avec Excel

    Reading time: 3 minutes
    Dernière mise à jour le 05/02/2024

    Un échéancier d'emprunt dans Excel vous permet de savoir pour chaque période

    1. La part de l'emprunt que vous remboursez réellement chaque mois

      Pour cela, il faut utiliser la fonction CUMUL.PRINCPER

    2. Le montant des intérêts que vous remboursez également chaque mois

      Connaître cette information est très importante pour des raisons de défiscalisation. En effet, la part de l'intérêt d'un emprunt, peut être déduite des impôts sur le revenu

    Construire un échéancier d'emprunt dans la situation suivante

    Nous allons prendre le cas où nous voulons faire un emprunt de 2500€ sur 1 an (12 mois) au taux de 10% annuel. Comme nous l'avons vu dans l'article sur le calcul des mensualités, nous pouvons déduire de ces informations le taux d'intérêt mensuel.

    Ensuite, grâce à la fonction VPM, il est très facile de connaître le montant à rembourser tous les mois.

    =VPM(B5;B3;B1)*-1 => 219,29

    Calcul de la mensualite avec VPM

    Sur les 219,29€ mensuels, quel montant est consacré au remboursement de l'emprunt le premier mois, le deuxième mois, ...

    Calcul de la part de l'intérêt dans l'échéancier d'emprunt

    La fonction CUMUL.INTER a été spécialement créée pour calculer la part de l'intérêt pour chaque période.

    =CUMUL.INTER(taux;nombre d'échéances;montant emprunt;période début;période fin;type)

    Les 3 premiers paramètres se comprennent facilement. Attention au calcul du taux annuel ou mensuel comme vu précédemment.

    Ce qui est le plus difficile à paramétrer, ce sont les paramètres 4 et 5 de la fonction. Ce sont eux qui sont souvent à l'origine des erreurs. Maintenant, pour connaître le montant de l'intérêt versé sur la première période, il faut écrire la même valeur pour le 4e et 5e paramètre.

    =CUMUL.INTER(0.797%;12;2500;1;1;0)*-1 => 19,94

    Calcul de l'intérêt du premier mois

    Pour la deuxième année, il faut écrire la formule

    =CUMUL.INTER(0.797%;12;2500;2;2;0)*-1 => 18,35

    Calcul de l'intérêt du deuxième mois

    Et maintenant, si vous voulez connaître la part de l'intérêt que vous avez payé sur la première et deuxième périodes, il faut écrire

    =CUMUL.INTER(0.797%;12;2500;1;2;0)*-1 => 38,28

    Calcul de l'intérêt du mois 1 et 2

    Calcul de la part de l'emprunt réellement remboursé

    Si maintenant, vous voulez connaître la part du bien que vous avez remboursé à chaque période, il faut utiliser la fonction CUMUL.PRINCPER (le cumul du principal).

    =CUMUL.PRINCPER(taux;nombre d'échéances;montant emprunt;période début;période fin;type)

    Rien ne change dans la construction de cette formule par rapport à la fonction CUMUL.INTER. Seul le support (emprunt ou intérêt) est calculé. Pour le premier mois, le résultat est donc le suivant (regardez les 4e et 5e paramètres)

    =CUMULPRINCPER(0.797%;12;2500;1;1;0)*-1 => 199,35€

    Différence entre la part de l'emprunt remboursé et l'intérêt

    Construction de l'échéancier par période

    Et donc, il est facile, à partir de là, de construire dans Excel un échéancier d'emprunt, mois par mois avec :

    1. Pour chaque ligne le total équivalent à une mensualité
    2. En colonne E le total du montant emprunté
    3. Puis en colonne F le total des intérêts versés
    4. Colonne G le montant total déboursé au titre de l'emprunt
    Échéancier bancaire pour un prêt

    Vous pouvez également utiliser la fonction SEQUENCE d'Excel pour faire le même échéancier d'emprunt de façon dynamique. Comme ça, en changeant la valeur de la période, tout le tableau est automatiquement recalculé.

    Création dun tableau de remboursement dynamique

    Vidéo explicative

    Vous pouvez aussi voir la construction complète de l'échéancier dans cette vidéo

    Newsletter

    1 fois par mois :
    Astuces et quiz

      Nous ne vous enverrons pas de spam. Vous pouvez vous désabonner à tout moment.

      6 Comments

      1. A
        03/03/2023 @ 17:45

        Bonjour,
        Merci pour votre vidéo . Elle est très claire et instructive...Juste une info : les emprunts immobiliers, en France, sont régis par les intérêts simples...Il convient donc de diviser le taux annuel par 12 pour obtenir le taux mensuel...puisqu'il est proportionnel au taux annuel...
        Vous avez, par ailleurs, totalement raison d'insister sur le fait que les emprunts à la consommation sont calculés à raison de taux actuariels puisque régis par la "mécanique" des intérêts composés...Il convient de passer par la formule que vous détaillez pour retrouver le taux périodique mensuel => (1+tA ^1/12) -1
        Cdt,

        Reply

        • Frédéric LE GUEN
          04/03/2023 @ 05:15

          Alors ça c'est un super commentaire. Merci infiniment. J'avais appris cette technique de calcul des taux d'intérêt mensuels quand j'étais étudiant mais je ne sais pas dans quel cas ça s'appliquait (ni d'ailleurs que pour les emprunts immobiliers ont juste besoin d'une division par 12).
          Pourriez-vous m'adresser à contact@excel-exercice.com des liens sur le sujet, histoire que je fasse un article sur ce point.
          C'est comme la méthode de l'arrondi des banquiers. Si on ne vous l'explique pas, jamais vous ne saurez ce que c'est et l'importance que cà a.

          Reply

      2. KOFFI Franck
        15/03/2021 @ 15:04

        Bonjour Frédéric LE GUEN

        j'aimerais connaitre la formule de calcul CUMUL.INTER (taux;nombre d'échéances;montant emprunt;période début;période fin;type) tenant compte d'un différé de paiement (en mois).

        Merci

        Reply

      3. Mirakle
        12/12/2020 @ 14:18

        Je pense qu'il y a une erreur dans l'explication sur les paramètres 4 et 5 : le texte indique qu'ils faut les mettre à 1 pour l'année 1 et à 2 pour l'année 2. Mais en fait d'année, ce sont plutôt les numéros des périodes (dans l'exemple ce sont donc des mois).

        Reply

        • YOX
          10/05/2022 @ 10:54

          la formule de calcul du cout sur une periode n'est pas bon, ....la mensualité en revanche a l'aire correcte

          Reply

          • Frédéric LE GUEN
            10/05/2022 @ 13:07

            Comment ça ? Vous avez un exemple pour que je puisse controler ?

      Laisser un commentaire

      Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

      Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur la façon dont les données de vos commentaires sont traitées.