Maîtriser Excel

Tableau d'amortissement d'un crédit immobilier sur Excel

Avant de signer un prêt, mieux vaut comprendre où part chaque mensualité. Dans ce guide, vous calculez la mensualité d'un crédit immobilier avec une seule formule puis vous construisez le tableau d'amortissement mois par mois. Vous obtenez ensuite le coût total assurance comprise puis le montant maximal que vous pouvez emprunter.

Par Anthony, formateur ExcelPublié le · 3 min de lecture
Un couple vu de dos étudie un tableau sur un ordinateur portable, le plan d'un appartement et des clés posés sur la table

Vidéo hébergée par YouTube : au lancement, YouTube peut déposer des cookies (les cookies de YouTube expliqués).

Chapitres de la vidéo (8:46)

Les paramètres du crédit

Julien veut acheter un appartement et emprunter 200 000 euros sur 25 ans au taux de 3,5 %. Rangez ces chiffres dans une feuille Paramètres, une information par ligne. Le montant va en B3, le taux annuel en B4 avec le signe %, la durée en années en B5. Ajoutez le taux de l'assurance de 0,30 % par an puis les revenus nets du foyer, 3 400 euros par mois.

Toutes les formules liront ces cases. Le jour où la banque annonce un autre taux, vous changez une seule valeur et tout le simulateur se met à jour. Le taux, la durée, le montant emprunté et l'assurance restent ainsi visibles d'un coup d'œil en haut de la feuille.

La mensualité avec VPM

VPM calcule le remboursement constant d'un prêt. Elle attend le taux d'une période, le nombre de périodes et le montant emprunté. La formule =-VPM(B4/12;B5*12;B3) donne 1 001,25 euros par mois, hors assurance. Le taux est divisé par 12 pour obtenir le taux d'un mois et la durée multipliée par 12 pour compter les mois. Le signe moins placé devant VPM affiche le résultat en positif, car Excel compte l'argent qui sort comme une valeur négative.

Deux erreurs donnent des mensualités qui paraissent folles. Sans la division par 12, Excel calcule comme si on remboursait une fois par an et affiche 7 000,23 euros. Avec la durée en années au lieu des mois, il affiche 8 306,87 euros.

Feuille Paramètres d'Excel avec 200 000 euros, 3,50 %, 25 ans et le résultat de VPM, -1 001,25 euros
=VPM(B4/12;B5*12;B3) affiche -1 001,25 € en rouge. Un moins devant VPM la donne en positif.

Le tableau mois par mois

Sur une feuille Amortissement, créez six colonnes, le mois, le capital au début, les intérêts, le capital remboursé, la mensualité et le capital restant. Au premier mois, le capital de départ vaut =Paramètres!$B$3. Les intérêts valent =B2*Paramètres!$B$4/12, soit 583,33 euros. Le capital remboursé est la mensualité moins les intérêts, 417,91 euros. Le capital restant est le capital du début moins ce qui vient d'être remboursé.

Au deuxième mois, le numéro vaut =A2+1 et le capital du début reprend le capital restant du mois d'avant avec =F2. Recopiez les autres formules puis recopiez cette ligne jusqu'à la ligne 301, soit 300 mois. Les dollars, ajoutés avec la touche F4, gardent les références de la feuille Paramètres fixes pendant la recopie.

Tableau d'amortissement Excel avec le mois, le capital, les intérêts, le capital remboursé, la mensualité et le capital restant
Chaque ligne sépare les intérêts du capital remboursé.

Deux contrôles avant de faire confiance

La dernière ligne doit tomber à 0,00 euro et la somme du capital remboursé doit valoir le montant emprunté au centime près, 200 000 euros. Ces deux contrôles valent aussi pour une formule demandée à une IA. Une réponse qui ne les passe pas cache une erreur.

Ce que le tableau montre

Au premier mois, sur 1 001,25 euros, 583,33 euros partent en intérêts. La première année, Julien paie 6 918,76 euros d'intérêts et ne rembourse que 5 096,20 euros de capital. Il faut attendre le 64e mois pour que le capital remboursé dépasse les intérêts. Au bout de dix ans, il reste encore 140 057,57 euros à rembourser. Les intérêts se calculent sur ce qui reste dû, ils pèsent donc lourd au début du prêt.

Le coût total, assurance comprise

Les intérêts valent la mensualité fois le nombre de mois moins le montant emprunté, soit 100 374,14 euros. Dans l'exemple, l'assurance se calcule sur le capital de départ, 50 euros par mois, soit 15 000 euros sur 25 ans. Certains contrats la calculent sur le capital qui reste dû et elle baisse alors avec le temps. Le coût total du crédit atteint 115 374,14 euros.

Taux d'endettement et montant maximal

Le Haut Conseil de stabilité financière fixe deux limites dans sa décision D-HCSF-2021-7, un taux d'effort de 35 % au plus assurance comprise et une durée de 25 ans au plus. Le taux d'endettement de Julien vaut la mensualité totale, 1 051,25 euros, divisée par ses revenus, soit 30,9 %.

Le calcul inverse se fait avec VA. Trente-cinq pour cent de 3 400 euros donnent 1 190 euros par mois. Une fois l'assurance retirée, =VA(B4/12;B5*12;-(B18-B10)) donne 227 716,01 euros, le montant maximal sur 25 ans à ce taux.

Feuille Paramètres d'Excel avec un taux d'endettement de 30,9 % et un montant maximal de 227 716,01 euros
Le taux d'endettement et le montant maximal calculé avec VA.

Vingt ou vingt-cinq ans

Sur 20 ans, la mensualité passe à 1 159,92 euros et le taux d'endettement monte à 35,6 %, au-dessus de la limite. Les intérêts tombent pourtant à 78 380,66 euros, environ 22 000 euros de moins. Une durée plus courte coûte moins cher mais demande une mensualité plus forte. Le tableau montre les deux d'un coup d'œil et vous aide à en parler avec votre banque.

Les chiffres de ce guide sont des exemples. Ils ne remplacent pas la proposition de prêt écrite de votre banque. La formation IniTableur reprend les formules, les références et les fonctions dans l'ordre avec des exercices corrigés.

Questions fréquentes

Comment calculer la mensualité d'un crédit sur Excel ?

Utilisez VPM avec le taux d'un mois, le nombre de mois et le montant, par exemple =-VPM(B4/12;B5*12;B3). Le signe moins affiche le résultat en positif.

Comment faire un tableau d'amortissement sur Excel ?

Pour chaque mois, les intérêts valent le capital restant fois le taux divisé par 12, le capital remboursé vaut la mensualité moins les intérêts et le capital restant baisse d'autant. Recopiez la ligne sur toute la durée du prêt.

Pourquoi diviser le taux par 12 dans VPM ?

Le crédit se rembourse chaque mois. VPM attend donc le taux d'un mois. Sans la division, la mensualité calculée correspond à un remboursement annuel.

Quel taux d'endettement maximal pour un crédit immobilier ?

La décision D-HCSF-2021-7 du Haut Conseil de stabilité financière fixe un taux d'effort de 35 % au plus, assurance comprise. La durée est limitée à 25 ans, avec une part de dérogations laissée aux banques.

Comment calculer combien je peux emprunter sur Excel ?

La fonction VA calcule le montant qu'une mensualité donnée autorise à emprunter, par exemple =VA(taux/12;durée*12;-mensualité). Retirez d'abord l'assurance de la mensualité.

Cet article vous a-t-il aidé ?

Une note de 1 à 5 étoiles

À lire ensuite

Une graphiste travaille à son bureau entre ses maquettes, ses nuanciers et son ordinateur portable

Maîtriser Excel4 min de lecture

Faire un devis sur Excel de A à Z

Les mentions demandées, un catalogue de prestations, des prix automatiques avec RECHERCHEX, puis le PDF et un modèle réutilisable.