- VPM donne la mensualité constante, INTPER la part d’intérêts d’un mois, PRINCPER la part de capital ; en mensuel, on divise le taux annuel par 12 et on compte la durée en mois.
- Comparer deux offres sur la mensualité seule est trompeur : il faut le coût total (intérêts + frais) et le taux effectif, qui se retrouve avec TAUX.
- Un différé en intérêts seuls allège les premiers mois, mais relève la mensualité suivante et augmente le coût total du crédit.
- Un contrôle simple valide le tableau : le capital remboursé cumulé égale le capital emprunté et le capital final est nul.
Un emprunt est l’autre moitié de la décision d’investissement : on a décidé d’acheter, il faut maintenant le financer. Banquiers et dirigeants se parlent en mensualités, mais les bonnes décisions se prennent sur le coût total et le taux effectif. Excel contient tout ce qu’il faut. Cet épisode complète notre article sur le tableau d’amortissement d’un emprunt (annuités) en passant au rythme mensuel, à la comparaison de deux offres et au différé.
Ce que vous saurez faire à la fin de l’épisode
- calculer une mensualité avec
VPMen respectant les unités ; - séparer intérêts et capital avec
INTPERetPRINCPER; - bâtir un tableau d’amortissement de 60 lignes et le contrôler ;
- comparer deux offres sur le coût total et le taux effectif (
TAUX) ; - chiffrer le surcoût d’un différé.
Le cas : 600 000 MAD sur 60 mois
Atlas Négoce finance un véhicule utilitaire et du matériel : 600 000 MAD, remboursables sur 60 mois. Deux banques répondent.
Figure 1 : les deux offres. Repère 1 : la mensualité ; repère 2 : le coût total ; repère 3 : le taux effectif.
| Offre A | Offre B | |
|---|---|---|
| Taux nominal annuel | 6,20 % | 5,90 % |
| Frais de dossier | 1,00 % | 1,50 % |
La mensualité
=ARRONDI(-VPM(B3/12;B4;B2);2)VPM(taux par période; nombre de périodes; valeur actuelle) : le taux est annuel ÷ 12, la durée est en mois. Le signe moins affiche un nombre positif (Excel compte les sorties de trésorerie en négatif). Résultat : 11 655,56 pour A et 11 571,80 pour B. La mensualité de B est inférieure de 83,76 MAD : si l’on s’arrêtait là, B gagnerait, mais ses frais sont plus élevés.
Le coût total
Total des mensualités : =ARRONDI(B6*B4;2) → 699 333,60 (A)
Intérêts totaux : =B7-B2 → 99 333,60 (A)
Frais de dossier : =ARRONDI(B2*B5;2) → 6 000,00 (A)
Coût total du crédit : =B8+B9 → 105 333,60 (A)| Offre A | Offre B | |
|---|---|---|
| Intérêts totaux | 99 333,60 | 94 308,00 |
| Frais de dossier | 6 000,00 | 9 000,00 |
| Coût total du crédit | 105 333,60 | 103 308,00 |
B coûte 2 025,60 MAD de moins sur cinq ans : son taux plus bas compense ses frais plus élevés, mais l’écart est modeste et disparaîtrait si B imposait une assurance plus chère. Raison de plus pour comparer toutes les composantes.
Le taux effectif
Les frais de dossier sont prélevés à la mise à disposition : l’entreprise reçoit en réalité 594 000 MAD (A) alors qu’elle rembourse sur 600 000. Le taux qui égalise les sommes reçues et les mensualités se trouve avec TAUX :
=(1+TAUX(B4;-B6;B2-B9))^12-1TAUX(nper; vpm; va) renvoie le taux mensuel ; élevé à la puissance 12, il donne un taux annuel effectif : 6,82 % pour A, 6,73 % pour B, nettement au-dessus des taux nominaux affichés (6,20 % et 5,90 %). Cette formule ne retient que les frais de dossier ; un TEG réel inclut aussi l’assurance et d’autres frais du contrat. Elle suffit pour classer les offres entre elles.
Le SI final désigne l’offre la moins coûteuse : =SI(B10<=C10;"A";"B"), soit B.
Le tableau d’amortissement mensuel
L’onglet Tableau déroule les 60 mois de l’offre A. Chaque ligne répartit la mensualité entre intérêts et capital :
Intérêts : =-INTPER(Offres!$B$3/12;A2;Offres!$B$4;Offres!$B$2)
Capital : =-PRINCPER(Offres!$B$3/12;A2;Offres!$B$4;Offres!$B$2)
Capital fin : =B2-E2INTPER et PRINCPER demandent le numéro de la période (colonne A), c’est pourquoi le tableau porte un numéro de mois.
Figure 2 : les premiers mois. Repère 1 : intérêts du mois 1 ; repère 2 : capital du mois 1 ; repère 3 : capital restant dû après sept mois.
Au mois 1, les intérêts valent 600 000 × 6,2 % ÷ 12 = 3 100,00, et 8 555,56 de capital sont remboursés. Au mois 2, le capital restant est plus bas, donc les intérêts baissent (3 055,80) et la part de capital monte (8 599,77). À la fin, au mois 60, les intérêts ne sont plus que de 59,91 et 11 595,65 de capital soldent le prêt.
Les contrôles
- Capital final nul : après le mois 60, le capital restant dû est de 0,00.
- Capital remboursé cumulé = capital emprunté : la somme de la colonne E donne 600 000,00.
- Mensualité = intérêts + capital sur chaque ligne.
- Un écart de quelques centimes apparaît entre le total des mensualités arrondies (699 333,60) et la somme intérêts + capital (699 333,68) : la mensualité exacte, non arrondie, est de 11 655,5613… ; l’arrondi au centime explique les 0,08 MAD. En pratique, la banque ajuste la dernière échéance.
Le différé : un cadeau qui se paie
Une banque propose un différé de 6 mois : pendant les six premiers mois, on ne paie que les intérêts, puis on rembourse sur 54 mois.
Mensualité du différé : =ARRONDI(B2*B3/12;2) → 3 100,00
Mensualité ensuite : =ARRONDI(-VPM(B3/12;B4-B5;B2);2) → 12 761,59Pendant six mois, la trésorerie est soulagée (3 100 au lieu de 11 655,56). En contrepartie, la mensualité suivante grimpe de 1 106 MAD, et le coût du crédit augmente : 107 725,86 d’intérêts avec différé contre 99 333,60 sans, soit un surcoût de 8 392,26 MAD. Le différé est un prêt supplémentaire sur six mois : il se justifie si l’investissement ne produit pas de recettes avant (ouverture d’un point de vente, mise en route d’une ligne), pas par confort.
Les erreurs classiques
- Mélanger les unités : taux annuel avec durée en mois. La mensualité obtenue est absurde mais l’erreur n’est pas signalée.
- Oublier le signe :
VPMrenvoie un négatif ;INTPERetPRINCPERaussi. - Comparer des mensualités au lieu de coûts totaux et de taux effectifs.
- Ignorer l’assurance, les commissions qui changent le classement des offres.
- Ne pas intégrer le crédit au budget de trésorerie : le remboursement tombe à date fixe ; l’épisode 41 le placera dans un budget de trésorerie.
À vous de jouer
Dans le classeur d’exercice :
- Onglet Offres : complétez les lignes 6 à 12 pour A et B. Retrouvez 105 333,60 et 103 308,00.
- Onglet Tableau : complétez les colonnes D et E. Que valent les intérêts du mois 30 ?
- Passez la durée de l’offre A à 48 mois : la mensualité, le coût total et le classement changent-ils ?
- Onglet Différé : faites passer le différé à 3 mois, puis à 12 mois. Que devient le surcoût ?
Correction. (2) Au mois 30, les intérêts valent environ 1 721 MAD : le capital restant dû a déjà beaucoup diminué (3 100 au mois 1). (3) Sur 48 mois, la mensualité de A passe à environ 14 146 MAD et les intérêts totaux à environ 79 013 (contre 99 333,60) : une durée plus courte coûte moins cher au total, au prix d’une mensualité plus lourde. Avec 6 000 MAD de frais, A revient à environ 85 013 MAD, bien moins que B sur 60 mois (103 308) : comparer deux offres impose donc de les comparer à durée égale. (4) Le surcoût du différé croît avec sa durée : environ 4 184 MAD pour 3 mois, 8 392 pour 6 mois et environ 16 879 pour 12 mois.
À retenir
VPM: taux ÷ 12 et durée en mois pour une mensualité ; le signe moins affiche en positif.INTPERetPRINCPERséparent la mensualité ; le tableau se contrôle par un capital final nul.- Comparez les offres sur le coût total (intérêts + frais) et sur le taux effectif (
TAUX), à durée égale. - Un différé soulage la trésorerie mais coûte plus cher et relève la mensualité suivante.
- Mensualités arrondies et mensualités exactes diffèrent de quelques centimes : documentez la règle.
Épisode 41 : le budget de trésorerie, pour prévoir mois par mois encaissements, décaissements et solde.
Questions fréquentes
Pourquoi diviser le taux par 12 et multiplier la durée par 12 ?
Parce que VPM attend un taux et un nombre de périodes exprimés dans la même unité. Une mensualité suppose un taux mensuel (taux annuel ÷ 12) et un nombre de mois. Mélanger un taux annuel et une durée en mois donne une mensualité fausse sans aucun message d’erreur.
Pourquoi le taux effectif est-il supérieur au taux nominal ?
Parce que les frais de dossier réduisent la somme réellement reçue alors que les mensualités restent calculées sur le capital entier, et parce que l’actualisation mensuelle ramenée à l’année (capitalisation) donne un taux annuel supérieur au taux nominal. Le taux effectif mesure le coût réel du crédit.
Mon tableau donne un total d’intérêts légèrement différent de la différence entre mensualités et capital. Est-ce une erreur ?
Non : c’est un effet d’arrondi. La mensualité affichée est arrondie au centime, alors que INTPER et PRINCPER travaillent sur la mensualité exacte. L’écart est de quelques centimes ; en pratique, la banque ajuste la dernière échéance.
Le taux effectif du classeur est-il le TEG de la banque ?
Non. Le classeur intègre uniquement les frais de dossier ; le TEG réel inclut aussi, selon le contrat, l’assurance emprunteur et d’autres frais. Il faut se fier au document remis par la banque.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.