Un échéancier d'emprunt dans Excel vous permet de savoir pour chaque période
- La part de l'emprunt que vous remboursez réellement chaque mois
- Le montant des intérêts que vous remboursez également chaque mois
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
Sur les 219,29 € mensuels, quel montant est consacré au remboursement de l'emprunt le premier mois, le deuxième mois, ...
Calcul des intérêts dans un échéancier d’emprunt
Pour connaître la part d’intérêt remboursée à chaque échéance, Excel propose la fonction CUMUL.INTER. Sa syntaxe est la suivante :
=CUMUL.INTER(taux; nombre_échéances; montant_emprunt; période_début; période_fin; type)
- taux : le taux d’intérêt, exprimé en mensuel ou annuel selon le contexte
- nombre_échéances : le nombre total de périodes de remboursement
- montant_emprunt : le capital emprunté
⚠️ Vérifie bien si le taux que tu indiques est mensuel ou annuel. Une erreur à ce niveau faussera tout le calcul.
Les paramètres à surveiller
Les paramètres période_début et période_fin sont souvent source d’erreurs. Il faut indiquer la ou les périodes pour lesquelles tu veux calculer les intérêts cumulés.
💡 Pour obtenir le montant d’intérêt versé sur une seule période (par exemple la première), utilise la même valeur pour les deux paramètres.
=CUMUL.INTER(0,797%; 12; 2500; 1; 1; 0) * -1
➡️ Cette formule donne 19,94 €, soit le montant d’intérêt payé à la première échéance.
Pour la deuxième année, il faut écrire la formule
=CUMUL.INTER(0.797%;12;2500;2;2;0)*-1 => 18,35
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 la part de capital remboursée
Pour connaître la part du capital remboursée à chaque échéance, utilisez la fonction CUMUL.PRINCPER. Elle calcule la part de l’emprunt (hors intérêts) que vous remboursez sur une ou plusieurs périodes. Voici la syntaxe :
=CUMUL.PRINCPER(taux; nombre_échéances; montant_emprunt; période_début; période_fin; type)
Cette formule reprend exactement les mêmes paramètres que CUMUL.INTER, mais elle calcule cette fois le capital remboursé.
💡 Pour connaître la part de capital remboursée sur une seule période (comme la première échéance), indiquez la même valeur pour les périodes de début et de fin.
=CUMUL.PRINCPER(0,797%; 12; 2500; 1; 1; 0) * -1
➡️ Cette formule donne 199,35 €, soit le capital remboursé lors de la première mensualité.
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 :
- Pour chaque ligne le total équivalent à une mensualité
- En colonne E le total du montant emprunté
- Puis en colonne F le total des intérêts versés
- Colonne G le montant total déboursé au titre de l'emprunt
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é.
Articles liés
- Calculer une mensualité d’emprunt avec un taux fixe
- Calculer vos mensualités de prêt avec Excel
- Déterminer le Nombre de Mensualités
- Versements périodiques – Gains obtenus
- Construire un échéancier de prêt dynamique
Vidéo explicative
Vous pouvez aussi voir la construction complète de l'échéancier dans cette vidéo
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,
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.
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
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).
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
10/05/2022 @ 13:07
Comment ça ? Vous avez un exemple pour que je puisse controler ?