
Faire son budget sur Excel qui se remplit tout seul
Une liste de catégories, le mois calculé tout seul, les totaux de chaque mois avec SOMME.SI.ENS, le reste à vivre et les dépassements en couleur.
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.


Vidéo hébergée par YouTube : au lancement, YouTube peut déposer des cookies (les cookies de YouTube expliqués).
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.
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.

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.

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.
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.
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.
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.

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.
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.
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.
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.
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.
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 liste de catégories, le mois calculé tout seul, les totaux de chaque mois avec SOMME.SI.ENS, le reste à vivre et les dépassements en couleur.

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

La liste simple en trente secondes, une liste qui s'allonge toute seule et la cascade Catégorie puis Article avec INDIRECT.