Un prêt immobilier peut vite devenir un casse-tête : taux annuel, durée, assurance, frais de dossier, mensualités… Heureusement, Excel permet de transformer toutes ces données en un outil de simulation clair et personnalisable.

Dans ce tutoriel, nous allons construire un calculateur de prêt immobilier dans Excel. Vous pourrez estimer votre mensualité, le coût total du crédit et l’évolution du capital restant dû. Et pour gagner du temps, je vous propose également une structure de modèle gratuit à reproduire directement dans votre classeur.

Les informations nécessaires pour calculer un prêt immobilier

Avant d’ouvrir Excel, rassemblez les quelques données qui servent au calcul :

  • Le montant emprunté : la somme réellement financée par la banque.
  • Le taux d’intérêt annuel : par exemple 3,50 %.
  • La durée du prêt : exprimée en années, comme 20 ou 25 ans.
  • Le coût de l’assurance emprunteur : généralement exprimé en pourcentage du capital initial ou du capital restant dû.
  • Les frais annexes : frais de dossier, garantie, courtage et éventuels frais de notaire si vous souhaitez étudier le budget global du projet.

Dans un premier temps, concentrons-nous sur le crédit lui-même. L’assurance et les frais pourront ensuite être ajoutés pour obtenir une estimation plus complète.

Créer la zone de saisie dans Excel

Commencez par créer une petite zone de paramètres. Vous pouvez utiliser les colonnes A et B de votre feuille :

  • A2 : Montant emprunté
  • B2 : 250000
  • A3 : Taux annuel
  • B3 : 3,50 %
  • A4 : Durée en années
  • B4 : 20
  • A5 : Assurance annuelle
  • B5 : 0,30 %

Formatez la cellule B2 en euros, B3 et B5 en pourcentage, puis B4 en nombre entier. Cette étape semble basique, mais elle évite de nombreuses erreurs de lecture. Une cellule contenant 3,5 n’est pas équivalente à une cellule contenant 3,5 % : dans Excel, la seconde correspond à 0,035.

Pour rendre le modèle plus agréable à utiliser, vous pouvez appliquer une couleur de fond aux cellules de saisie. Par exemple, utilisez un bleu clair pour les valeurs modifiables et une autre couleur pour les résultats calculés. En un coup d’œil, on sait ainsi où intervenir.

Calculer la mensualité du prêt avec la fonction VPM

La fonction Excel la plus pratique pour calculer une mensualité est VPM. Elle permet de déterminer le montant d’un remboursement périodique à partir du taux, du nombre de périodes et du capital emprunté.

Ajoutez les lignes suivantes :

  • A7 : Mensualité hors assurance
  • A8 : Nombre total de mensualités
  • A9 : Intérêts totaux

Dans B8, saisissez :

=B4*12

Cette formule convertit la durée en années en nombre de mensualités. Pour un prêt sur 20 ans, le résultat sera donc 240.

Dans B7, utilisez la formule suivante :

=-VPM(B3/12;B8;B2)

Pourquoi le signe moins devant VPM ? Excel considère généralement le montant emprunté comme une entrée d’argent et les remboursements comme des sorties. Le signe moins permet d’afficher la mensualité sous forme positive, ce qui est plus naturel dans un tableau de simulation.

Avec un emprunt de 250 000 € sur 20 ans au taux de 3,50 %, la mensualité hors assurance est d’environ 1 449 €. Le résultat exact peut varier légèrement selon les paramètres et les arrondis utilisés par Excel.

Si votre version d’Excel utilise les fonctions en anglais, la fonction VPM s’appelle PMT. Dans la version française, le séparateur d’arguments est généralement le point-virgule. Sur certaines configurations, Excel utilise toutefois la virgule. Si une formule renvoie une erreur, vérifiez les paramètres régionaux de votre logiciel.

Ajouter le coût de l’assurance emprunteur

L’assurance est souvent oubliée dans les premières simulations. Pourtant, quelques dizaines d’euros par mois peuvent représenter plusieurs milliers d’euros sur la durée du crédit.

Si l’assurance est calculée sur le capital initial, ajoutez les lignes suivantes :

  • A11 : Assurance mensuelle
  • A12 : Mensualité avec assurance
  • A13 : Coût total de l’assurance

Dans B11 :

=B2*B5/12

Dans B12 :

=B7+B11

Dans B13 :

=B11*B8

Avec un capital de 250 000 € et une assurance à 0,30 % par an, l’assurance mensuelle s’élève à 62,50 €. Elle porte donc la mensualité globale à environ 1 511,50 €.

Attention : cette méthode correspond à une assurance calculée sur le capital initial. Certains contrats appliquent le taux au capital restant dû. Dans ce cas, le coût de l’assurance diminue au fil du temps et doit être intégré ligne par ligne dans le tableau d’amortissement.

Calculer le coût total du crédit

Une mensualité raisonnable ne signifie pas forcément que le crédit est peu coûteux. Plus la durée augmente, plus les intérêts s’accumulent. Excel permet de rendre cette différence très visible.

Ajoutez les lignes suivantes :

  • A15 : Total remboursé hors assurance
  • A16 : Coût total des intérêts
  • A17 : Total remboursé avec assurance
  • A18 : Coût global intérêts + assurance

Dans B15 :

=B7*B8

Dans B16 :

=B15-B2

Dans B17 :

=B12*B8

Dans B18 :

=B17-B2

Pour notre exemple, le total des intérêts représente plusieurs dizaines de milliers d’euros. En ajoutant l’assurance, le coût global du financement augmente encore. C’est précisément ce type de comparaison qui permet de mesurer l’impact réel d’un taux ou d’une durée différente.

Vous pouvez également calculer le taux d’endettement en ajoutant vos revenus mensuels et vos charges. Cette estimation ne remplace pas l’analyse de la banque, mais elle donne un premier repère utile :

=(Mensualité avec assurance + autres charges) / revenus mensuels

Construire un tableau d’amortissement automatique

Le tableau d’amortissement détaille chaque mensualité. Il indique la part consacrée aux intérêts, la part consacrée au remboursement du capital et le capital restant dû.

Dans une nouvelle zone de la feuille, créez les colonnes suivantes à partir de la ligne 22 :

  • A22 : Échéance
  • B22 : Mensualité
  • C22 : Intérêts
  • D22 : Capital remboursé
  • E22 : Capital restant dû

Sur la première ligne du tableau, à la ligne 23, saisissez :

A23 : 1

B23 : =$B$7

C23 : =$B$2*$B$3/12

D23 : =B23-C23

E23 : =$B$2-D23

La première échéance est calculée à partir du capital initial. Les intérêts correspondent au capital restant dû multiplié par le taux mensuel. Le capital remboursé correspond ensuite à la différence entre la mensualité et les intérêts.

À la ligne 24, utilisez les formules suivantes :

A24 : =A23+1

B24 : =$B$7

C24 : =E23*$B$3/12

D24 : =B24-C24

E24 : =E23-D24

Recopiez ensuite la ligne 24 vers le bas jusqu’à atteindre le nombre total de mensualités indiqué en B8. Pour un prêt sur 20 ans, le tableau contiendra 240 lignes.

Au début du crédit, la part d’intérêts est importante car elle est calculée sur un capital encore élevé. Au fil des remboursements, cette part diminue tandis que la part de capital augmente. C’est parfois surprenant de constater qu’après plusieurs années, le capital restant dû a moins diminué que prévu. Excel est là pour éviter les mauvaises surprises.

Gérer la dernière échéance et les arrondis

Les calculs financiers utilisent des nombres décimaux, alors que les prélèvements bancaires sont affichés au centime près. De petits écarts peuvent donc apparaître dans la dernière ligne du tableau.

Pour limiter ces différences, vous pouvez arrondir les résultats à deux décimales :

=ARRONDI(E23-D23;2)

Il est également possible de sécuriser la dernière mensualité afin qu’elle ne dépasse jamais le capital restant dû. Une formule plus robuste pour la colonne D peut être :

=MIN(B23-C23;E22)

Cette formule doit être adaptée selon la structure de votre tableau. Dans un modèle professionnel, il est préférable de prévoir une condition lorsque le capital restant dû devient inférieur au remboursement habituel.

Comparer plusieurs durées de prêt

L’un des grands intérêts d’un modèle Excel est de pouvoir comparer rapidement plusieurs scénarios. Vous pouvez créer un tableau avec une ligne par durée :

  • 15 ans
  • 20 ans
  • 25 ans
  • 30 ans

Pour chaque durée, utilisez la formule VPM en remplaçant la référence de la durée :

=-VPM($B$3/12;15*12;$B$2)

=-VPM($B$3/12;20*12;$B$2)

=-VPM($B$3/12;25*12;$B$2)

Ajoutez ensuite une colonne pour le total des intérêts. Vous constaterez généralement qu’une durée plus longue réduit la mensualité, mais augmente fortement le coût global. À l’inverse, une durée courte allège la facture d’intérêts, mais demande une capacité de remboursement plus importante.

Pour visualiser les résultats, sélectionnez les durées et les mensualités, puis insérez un graphique en colonnes. Un second graphique peut comparer le coût total des intérêts. Deux minutes de mise en forme peuvent rendre la décision beaucoup plus lisible.

La structure du modèle gratuit à reproduire

Voici la structure minimale du modèle :

  • Zone Paramètres : montant emprunté, taux annuel, durée et assurance.
  • Zone Résultats : mensualité, total remboursé, intérêts, assurance et coût global.
  • Tableau d’amortissement : échéance, mensualité, intérêts, capital remboursé et capital restant dû.
  • Zone Comparaison : plusieurs durées et plusieurs scénarios de taux.

Pour le rendre plus pratique, ajoutez une validation des données sur la durée et le taux. Vous pouvez aussi utiliser des cellules nommées comme Montant, Taux et Duree. Une formule telle que =-VPM(Taux/12;Duree*12;Montant) devient alors beaucoup plus facile à comprendre.

Enfin, protégez les cellules contenant les formules et laissez uniquement les cellules de saisie modifiables. Cette précaution évite d’effacer accidentellement une formule après plusieurs simulations.

Les limites d’une simulation Excel

Un calculateur Excel fournit une estimation, pas une offre de prêt. Le taux proposé par une banque peut dépendre de votre profil, de votre apport, de vos revenus, de la garantie et de votre projet.

Le coût réel peut également inclure :

  • les frais de dossier ;
  • les frais de garantie ou d’hypothèque ;
  • les frais de courtage ;
  • les frais liés à l’assurance ;
  • les intérêts intercalaires en cas de construction ;
  • les éventuelles pénalités de remboursement anticipé.

Pour comparer des offres bancaires, ne vous limitez donc pas au taux nominal. Regardez surtout le TAEG, qui intègre une partie des frais obligatoires liés au financement.

Avec quelques cellules et les fonctions VPM, ARRONDI et MIN, Excel devient un véritable tableau de bord pour votre projet immobilier. Vous pouvez tester plusieurs taux, modifier la durée, mesurer l’impact de l’assurance et visualiser précisément la progression du remboursement. De quoi préparer un rendez-vous bancaire avec des chiffres clairs plutôt qu’avec une calculatrice et beaucoup d’espoir.