- VPM calcule l’annuité constante d’un emprunt à partir du taux, de la durée et du capital : =-VPM(taux; durée; capital).
- INTPER donne la part d’intérêts d’une période et PRINCPER la part de capital ; leur somme égale l’annuité.
- Pour un prêt à échéances mensuelles, on divise le taux annuel par 12 et on multiplie la durée par 12.
- Le tableau alimente la comptabilité (compte 1481 pour le capital, 6311 pour les intérêts) et le plan de financement.
Un emprunt bancaire, c’est un calendrier : combien payer, quand, dont combien d’intérêts. Excel construit ce calendrier en quelques lignes grâce à trois fonctions financières. Ce tutoriel, illustré, en détaille la mise en place pour un prêt à annuités constantes.
Le cas
Un emprunt de 1 000 000 MAD à 6 % l’an, remboursable en 5 annuités constantes.
Étape 1 : les paramètres
Créez un bloc de paramètres (ici colonnes H et I) : Capital (I2), Taux annuel (I3), Durée en années (I4). Les cellules jaunes se saisissent ; les autres se calculent. Séparer les paramètres du tableau permet de recalculer un autre scénario en changeant seulement trois cellules.
Étape 2 : l’annuité avec VPM
La fonction VPM (PMT en anglais) renvoie le paiement périodique constant d’un prêt :
=-VPM($I$3;$I$4;$I$2)Arguments : taux par période, nombre de périodes, valeur actuelle (capital). Résultat : 237 396,40 MAD. Le signe moins affiche un nombre positif.
Cette annuité est la même chaque année : c’est la propriété de l’amortissement à annuités constantes.
Étape 3 : le tableau année par année
Figure 1 : le tableau d’amortissement. Repère 1 : paramètres ; 2 : annuité par VPM ; 3 : intérêts ; 4 : capital restant dû.
Formules de la ligne 2 (année 1), à recopier vers le bas :
| Colonne | Formule | Rôle |
|---|---|---|
| A (année) | 1 puis =A2+1 |
Numéro de période |
| B (capital début) | =$I$2 en ligne 2, puis =F2 en ligne 3… |
Capital restant dû en début de période |
| C (intérêts) | =B2*$I$3 |
Intérêts = capital début × taux |
| D (capital remboursé) | =E2-C2 |
Part de capital = annuité − intérêts |
| E (annuité) | =-VPM($I$3;$I$4;$I$2) |
Constante |
| F (capital fin) | =B2-D2 |
Capital restant dû en fin de période |
Résultats :
| Année | Capital début | Intérêts | Capital remboursé | Annuité | Capital fin |
|---|---|---|---|---|---|
| 1 | 1 000 000,00 | 60 000,00 | 177 396,40 | 237 396,40 | 822 603,60 |
| 2 | 822 603,60 | 49 356,22 | 188 040,18 | 237 396,40 | 634 563,42 |
| 3 | 634 563,42 | 38 073,81 | 199 322,59 | 237 396,40 | 435 240,83 |
| 4 | 435 240,83 | 26 114,45 | 211 281,95 | 237 396,40 | 223 958,88 |
| 5 | 223 958,88 | 13 437,53 | 223 958,87 | 237 396,40 | 0,00 |
| Total | 186 982,01 | 1 000 000,00 | 1 186 982,01 |
Le coût total du crédit (hors frais, assurance, commissions) est donc de 186 982,01 MAD. Les intérêts diminuent chaque année tandis que la part de capital augmente.
Variante avec INTPER et PRINCPER
Au lieu de calculer ligne à ligne, deux fonctions donnent directement la part d’intérêts et la part de capital de n’importe quelle période :
Intérêts de l’année 3 : =-INTPER($I$3;3;$I$4;$I$2) → 38 073,81
Capital de l’année 3 : =-PRINCPER($I$3;3;$I$4;$I$2) → 199 322,59Arguments : taux, numéro de période, nombre de périodes, capital. Utile pour extraire un seul exercice (par exemple, les intérêts de l’année pour la clôture) sans dérouler tout le tableau. Contrôle : intérêts + capital = annuité.
Pour le cumul des intérêts entre deux périodes, CUMUL.INTER donne la somme (=-CUMUL.INTER(taux;durée;capital;début;fin;0)).
Mensualités
Pour des échéances mensuelles :
Mensualité : =-VPM(6%/12;5*12;1000000) → 19 332,80Le tableau comporte 60 lignes, avec un taux mensuel de 0,5 % ; les formules sont identiques en remplaçant le taux annuel par le taux périodique. À taux nominal égal, un remboursement mensuel coûte un peu moins d’intérêts qu’un remboursement annuel, car le capital baisse plus vite.
Les écritures comptables
À la mise à disposition des fonds :
| Compte | Débit | Crédit |
|---|---|---|
| 5141 Banques | 1 000 000 | |
| 1481 Emprunts auprès des établissements de crédit | 1 000 000 |
À chaque échéance (année 1) :
| Compte | Débit | Crédit |
|---|---|---|
| 1481 Emprunts auprès des établissements de crédit | 177 396,40 | |
| 6311 Intérêts des emprunts et dettes | 60 000,00 | |
| 5141 Banques | 237 396,40 |
À la clôture, la fraction des échéances à moins d’un an est suivie dans l’annexe (ETIC) ; les intérêts courus non échus sont constatés en 4493 (écritures de fin d’exercice).
Les contrôles d’un tableau fiable
- Le capital remboursé total égale le capital emprunté.
- Le capital de fin de la dernière période est zéro (à l’arrondi près).
- Annuité = intérêts + capital sur chaque ligne.
- Les intérêts de chaque année, rapprochés des avis de la banque.
- Le TEG de la banque est supérieur au taux nominal à cause des frais et assurances : demandez le tableau de la banque et comparez.
Comparer deux offres de prêt
Deux banques proposent 1 000 000 MAD sur 5 ans, remboursables par annuités constantes :
| Offre A | Offre B | |
|---|---|---|
| Taux nominal annuel | 6,00 % | 5,60 % |
| Frais de dossier | 10 000 | 20 000 |
Annuité : =-VPM(taux;5;1000000) |
237 396 | 234 819 |
| Total remboursé sur 5 ans | 1 186 982 | 1 174 095 |
| Coût total avec les frais | 1 196 982 | 1 194 095 |
Le coût réel ne se lit pas dans le taux nominal. Pour mesurer le taux effectif (celui qui tient compte des frais retenus à la mise en place), on calcule le taux auquel les annuités actualisent la somme réellement reçue :
| Offre A | Offre B | |
|---|---|---|
| Somme réellement reçue (capital − frais) | 990 000 | 980 000 |
Taux effectif : =TAUX(5;-annuité;somme reçue) |
6,37 % | 6,34 % |
L’offre B, malgré des frais deux fois plus élevés, revient légèrement moins cher (écart de 2 887 MAD sur cinq ans) parce que son taux nominal est plus bas. L’écart est faible : d’autres critères pèsent (garanties exigées, souplesse de remboursement anticipé, qualité de la relation, assurance emprunteur). Le tableur permet de chiffrer ; la décision reste financière et commerciale.
La feuille de comparaison
| Cellule | Contenu |
|---|---|
| B2 | Capital emprunté |
| B3 | Durée en années |
| B4:C4 | Taux de chaque offre |
| B5:C5 | Frais de dossier |
| B6:C6 | =-VPM(B4;$B$3;$B$2) (annuité) |
| B7:C7 | =B6*$B$3 (total remboursé) |
| B8:C8 | =B7+B5 (coût total) |
| B9:C9 | =TAUX($B$3;-B6;$B$2-B5) (taux effectif) |
Un contrôle de cohérence
La somme des remboursements de capital du tableau d’amortissement doit égaler le capital emprunté (1 000 000) ; la somme des intérêts doit égaler le total remboursé moins le capital (186 982 pour l’offre A). Si ce n’est pas le cas, une formule du tableau est mal figée (signe dollar manquant) ou le nombre de périodes ne correspond pas à la fréquence du taux.
Comparer trois profils de remboursement sur le même prêt
Un prêt de 1 000 000 MAD à 7 % sur cinq ans peut se rembourser de plusieurs façons. Voici les trois profils courants, avec leurs formules Excel.
| Profil | Formule de l’échéance | Total des intérêts |
|---|---|---|
| Annuités constantes | =-VPM(7%;5;1000000) → 243 890,69 |
219 453 |
| Amortissement constant du capital (200 000 par an) | Capital : =1000000/5 ; intérêts : =capital_restant*7% |
210 000 |
| Différé d’un an (intérêts seuls) puis annuités constantes | Année 1 : =1000000*7% = 70 000 ; années 2 à 5 : =-VPM(7%;4;1000000) → 295 228,12 |
250 912 |
Tableau des annuités constantes
| Année | Capital restant dû au début | Intérêts | Capital remboursé | Annuité | Capital restant dû à la fin |
|---|---|---|---|---|---|
| 1 | 1 000 000 | 70 000 | 173 891 | 243 891 | 826 109 |
| 2 | 826 109 | 57 828 | 186 063 | 243 891 | 640 046 |
| 3 | 640 046 | 44 803 | 199 087 | 243 891 | 440 959 |
| 4 | 440 959 | 30 867 | 213 024 | 243 891 | 227 935 |
| 5 | 227 935 | 15 955 | 227 935 | 243 891 | 0 |
Tableau de l’amortissement constant
| Année | Capital restant dû au début | Intérêts | Capital remboursé | Annuité |
|---|---|---|---|---|
| 1 | 1 000 000 | 70 000 | 200 000 | 270 000 |
| 2 | 800 000 | 56 000 | 200 000 | 256 000 |
| 3 | 600 000 | 42 000 | 200 000 | 242 000 |
| 4 | 400 000 | 28 000 | 200 000 | 228 000 |
| 5 | 200 000 | 14 000 | 200 000 | 214 000 |
L’amortissement constant coûte moins cher en intérêts (210 000 contre 219 453) mais pèse plus lourd en début de période (270 000 la première année contre 243 891). Le différé d’un an soulage la trésorerie de la première année (70 000 d’intérêts seuls) mais renchérit le coût total (250 912 d’intérêts). Le choix dépend du profil de trésorerie du projet : un équipement qui ne produit ses effets qu’au bout d’un an justifie un différé.
Mesurer le coût réel avec les frais : le taux effectif global
Supposons des frais de dossier de 10 000 MAD prélevés à la mise à disposition. L’entreprise reçoit 990 000 MAD et rembourse 243 891 MAD par an pendant cinq ans. Le taux qui égalise les deux se calcule avec la fonction TAUX :
=TAUX(5;-243890,69;990000)Résultat : 7,38 %, contre 7 % annoncés. L’écart de 0,38 point correspond aux frais. Pour comparer des offres, c’est ce taux effectif, et non le taux facial, qui compte. Il faut y inclure aussi l’assurance emprunteur et les commissions de garantie, si elles sont obligatoires.
Décomposer les intérêts de chaque exercice comptable
Si le prêt est débloqué le 1er juillet, l’exercice (année civile) ne supporte que six mois d’intérêts de la première année. La fonction CUMUL.INTER calcule les intérêts cumulés entre deux périodes :
=-CUMUL.INTER(7%/12;60;1000000;1;6;0)Pour des mensualités, cette formule donne les intérêts des six premiers mois. Les intérêts courus non échus à la clôture sont comptabilisés en charges à payer (voir l’article sur les écritures de régularisation). Ce calcul alimente le compte 6311 (Intérêts des emprunts et dettes) et le tableau d’échéancier de l’annexe.
Ce qu’il faut vérifier avant de signer
| Question | Pourquoi |
|---|---|
| Taux fixe ou variable ? | Un taux variable expose à la hausse ; demander la formule de révision et un plafond |
| Périodicité (mensuelle, trimestrielle, annuelle) | Une périodicité plus fréquente augmente légèrement le coût effectif |
| Frais de dossier, assurance, garantie | Inclure dans le taux effectif |
| Remboursement anticipé | Indemnité éventuelle, conditions |
| Différé et profil | Cohérence avec la trésorerie du projet |
| Garanties | Hypothèque, nantissement, caution personnelle : étendue et durée |
| Clauses de défaut | Cas de déchéance du terme |
Un tableau Excel avec ces rubriques pour chaque offre facilite la décision. Voir aussi l’article sur le financement des PME.
À aller plus loin
- Taux effectif réel avec les frais de dossier : fonction
TAUXsur le flux net réellement reçu. - Remboursement anticipé partiel : recalculer l’annuité sur le capital restant dû et la durée restante.
- Comparer crédit et crédit-bail par la valeur actuelle des flux (VAN).
- Brancher le tableau sur le plan de trésorerie pour intégrer les échéances.
Cas particuliers et situations limites
Le prêt prévoit un taux variable. Le tableau se recalcule à chaque révision du taux : on découpe l’échéancier en tranches (une par période de taux) et on recalcule l’annuité sur le capital restant dû et la durée restante avec VPM. Conserver chaque version du tableau avec la date de révision.
Les échéances sont mensuelles mais le taux est annuel. Diviser le taux annuel par 12 et multiplier le nombre d’années par 12 : =-VPM(7%/12;60;1000000). Cette règle proportionnelle donne un coût légèrement différent d’un taux actuariel équivalent ; le contrat précise la méthode.
Un remboursement anticipé partiel est effectué. Deux options : réduire la durée en conservant l’annuité, ou réduire l’annuité en conservant la durée. Dans le tableau, on remplace le capital restant dû par le nouveau montant après le remboursement et on relance VPM sur la nouvelle durée ou le nouveau montant.
Le prêt est débloqué en plusieurs fois. Pendant la phase de déblocage, seuls les intérêts sur les sommes utilisées sont payés ; l’amortissement commence à la fin de la période de déblocage. Le tableau comporte alors deux phases, avec des formules distinctes.
On souhaite répartir les intérêts par exercice comptable. Avec CUMUL.INTER entre deux périodes, on obtient les intérêts de chaque exercice ; la différence avec les intérêts payés donne les intérêts courus à rattacher.
À retenir
VPM calcule l’annuité, INTPER et PRINCPER détaillent intérêts et capital, CUMUL.INTER agrège par période, TAUX mesure le taux effectif. Le tableau d’amortissement sert à la comptabilité, à la trésorerie et à la comparaison des offres.
Questions fréquentes
Pourquoi le signe moins devant VPM ?
Excel traite les flux sortants comme des nombres négatifs. VPM renvoie donc une annuité négative lorsque le capital est saisi positif. Le signe moins devant la fonction affiche le montant en positif, plus lisible dans un tableau.
Comment gérer un prêt avec mensualités ?
Divisez le taux annuel par 12 et multipliez la durée en années par 12 : =-VPM(6%/12;5*12;1000000) donne la mensualité. Dans le tableau, la ligne de chaque mois utilise le taux mensuel.
Et si la banque applique un différé de remboursement ?
Pendant le différé, seuls les intérêts sont payés (capital × taux). Le tableau d’amortissement démarre à la fin du différé avec une durée réduite d’autant. Les intérêts intercalaires de la période de différé se calculent séparément.
Comment calculer un amortissement à capital constant ?
Le capital remboursé est identique chaque période (capital ÷ durée) et les intérêts décroissent : annuité = capital remboursé + intérêts du capital restant dû. La fonction VPM ne s’applique pas ; on construit le tableau avec des formules simples.
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.