Comment calculer TEG : le calcul pas à pas avec Excel

Le TEG, maintenant désigné par le sigle TAEG, représente le coût total du crédit exprimé en pourcentage annuel. Il doit obligatoirement figurer sur toute offre de prêt et permettre au consommateur de comparer différentes propositions. Le calcul le plus fiable consiste à égaliser, par un taux annuel, la valeur actuelle des flux entrants et sortants du prêt. Ce taux est le taux interne de rendement (TIR) appliqué aux flux datés incluant le capital versé, les mensualités, l’assurance et les frais exigés par le prêteur.

Formule et principe

Le principe peut se formaliser ainsi : la somme des flux actualisés doit être nulle. Mathématiquement on écrit Σ{k=0}^{n} CFk / (1+TAEG)^{tk} = 0, où CF0 est le montant net remis à l’emprunteur (positif) et les CFk suivants sont les sorties (mensualités, frais, primes d’assurance) exprimées en valeurs négatives. Les tk sont des durées en années entre le décaissement initial et chaque flux.

Variables principales et périodicité

Important : tk s’exprime en années. Si vous travaillez en mensualités régulières, vous pouvez calculer un taux périodique p via la fonction RATE d’Excel puis annualiser par (1+p)^{12}-1. Si vous utilisez des dates réelles (cas recommandé), la fonction XIRR d’Excel ou la méthode du TIR continu adaptée donne directement le taux annuel.

Exemples pratiques chiffrés

Exemple 1 – prêt immobilier : capital demandé 200 000 €, durée 20 ans (240 mensualités), taux nominal affiché 1,50 %, frais de dossier et garanties 2 500 €, assurance obligatoire (coût total estimé) 5 500 €. Montant net versé = 200 000 € – frais prélevés à l’entrée (si applicables) ; pour l’exemple on suppose frais ajoutés au calcul mais non déduits. En listant le flux initial +200 000 € puis 240 flux mensuels négatifs d’environ 1 004 € et en appliquant XIRR sur les dates réelles, on obtient un TAEG approximatif de 1,70 % (la valeur exacte dépend de la répartition des frais et de la date d’effet de l’assurance).

Exemple 2 – prêt personnel : capital 10 000 €, durée 5 ans, mensualité approximative 188 €, taux nominal 4,50 %, frais de dossier 300 €, assurance 120 € par an. En intégrant ces sommes dans l’échéancier (frais en sortie ou en entrée selon le contrat), XIRR renvoie un TAEG proche de 4,90 %.

Mode d’emploi Excel (méthode recommandée)

Créez trois colonnes : Date (colonne A), Flux (colonne B), Commentaire (colonne C).

En A2 mettez la date de décaissement, en B2 le montant net reçu par l’emprunteur (positif).

Inscrivez ensuite chaque date de paiement et la mensualité correspondante comme valeurs négatives dans la colonne B.

Ajoutez les frais ponctuels et primes d’assurance à leurs dates effectives comme flux négatifs.

Utilisez la formule =XIRR(B2:Bn;A2:An) pour obtenir le taux annuel effectif (TAEG).

Si vous préférez travailler en périodicité régulière sans dates précises, vous pouvez utiliser =RATE(npm; -mensualité; capital) pour obtenir un taux périodique, puis annualiser : TAEG = (1+rate)^{periodsper_year}-1.

Points de contrôle réglementaires et erreurs fréquentes

Le TAEG doit inclure tous les frais imposés par le prêteur liés à l’octroi du crédit ainsi que l’assurance si celle-ci est exigée pour obtenir le prêt. Vérifiez les éléments suivants :

Inclusion des frais de dossier, coût des garanties (hypothèque, caution) et coût de l’assurance si obligatoire.
Dates exactes : XIRR nécessite des dates réelles pour refléter l’effet de l’actualisation.
Précision sur le montant net réellement reçu (parfois certains frais sont prélevés à l’entrée et réduisent le net versé).
Prise en compte des commissions et frais annexes (frais de tenue de compte, pénalités éventuelles non prévues).

Vérifier la conformité au taux d’usure

Comparez le TAEG recalculé au taux d’usure publié périodiquement par la Banque de France. Si le TAEG proposé dépasse le taux d’usure applicable à la catégorie de crédit, l’offre est illégale. Attention : un TAEG artificiellement bas peut résulter d’une omission de frais ou d’une mauvaise datation des flux ; vérifiez scrupuleusement le détail des lignes de l’offre.

Conseils pratiques

Arrondissez le TAEG à deux décimales pour la comparaison commerciale, mais conservez la valeur exacte pour le contrôle légal.
Si l’assurance est facultative, calculez le TAEG avec et sans assurance pour mesurer l’impact.
Conservez une copie datée de l’offre et de votre fichier Excel ; en cas de litige la reconstitution des flux peut servir de preuve.

Recalculer le TAEG est rapide avec un tableur et permet de vérifier la transparence d’une offre de prêt. Utilisez XIRR avec des dates réelles, incluez tous les frais et l’assurance exigée, puis comparez au taux d’usure pour vérifier la conformité. En respectant ces étapes vous savez exactement quel est le coût réel du crédit et vous pouvez comparer objectivement plusieurs propositions.

Informations complémentaires

Comment se calculé le TEG ?

Souvent, le TEG se voit comme la vraie météo du crédit, il rassemble tout ce qui pèse sur le prêt. Prenez un prêt de 200 000 € avec un taux nominal de 1,5 %, assurance 0,40 %, frais de dossier 1,2 % et garantie 1 %. On additionne ces coûts annuels pour obtenir un taux global apparent, ici 4,1 %. Ensuite on tient compte de la durée et des modalités de paiement pour convertir en TEG annuel effectif. En clair, le TEG montre ce que coûte réellement l’emprunt, pas seulement le petit chiffre du banquier. Et c’est utile.

Quelle est la formule du TEG ?

La formule pratique du TEG s’écrit comme un ratio qui ramène tous les frais au même dénominateur. On prend le coût total lié à l’opération divisé par le montant emprunté, multiplié par 365 divisé par la durée en jours. Par exemple, pour un coût de 211,16 € sur un découvert de 10 000 € pendant 60 jours, le calcul devient 211,16 divisé par 10 000 multiplié par 365 sur 60, soit environ 13,87 %. Ça montre combien le court terme peut paraître brutal en rendement apparent. Gardez en tête que c’est une projection utile pour comparer et agir sereinement.

Quelle est la formule du rendement du TEG ?

Le rendement évoqué ici n’est pas celui d’un crédit mais d’un générateur thermoélectrique, et c’est fascinant à expliquer avec des images simples. On calcule η comme la puissance utile délivrée divisée par l’énergie thermique fournie, donc η égale P out sur Q in. Q in se pense comme la chaleur absorbée, soit Q h moins Q c, la chaleur cédée au dissipateur. Concrètement, plus P out grimpe et plus Q c diminue, mieux c’est. C’est une balance de pertes et gains, comme isoler un grenier pour garder la chaleur et récupérer de l’électricité. Cela guide les choix de conception pratiques.

Quel est le TEG actuel ?

Impossible de donner un unique TEG actuel, les taux bougent selon les banques, la durée, l’assurance et la garantie, sans oublier le dossier. Quand une donnée est not available, mieux vaut faire une simulation avec son propre profil et quelques banques, ou consulter un courtier. En pratique, comparer le TEG plutôt que le seul taux nominal évite les mauvaises surprises. Une petite anecdote, la première offre reçue affichait un beau taux, mais le TEG plus élevé m’a réveillé, et heureusement j’ai demandé des ajustements. Bref, rester curieux et vérifier les chiffres avant de signer. Et célébrer la petite victoire finale.