Calcul d’un prêt et d’un échéancier avec Excel

Dans cet article nous allons voir comment calculer un prêt dans Excel et mettre en place un échéancier en fonction des éléments indiqués par l’utilisateur.

Livre calcul de prêt Excel

 

Cet article est en parti inspiré d’un extrait du livre présenté à gauche.

Vous pouvez obtenir davantage de renseignements ou acheter ce livre à cette adresse.

Formation finance Excel

Formation sur l’utilisation des outils financiers dans Excel.

Cette formation de 3h20 présentée sur Excel 2013 par un partenaire vous permettra d’appréhender les 40 fonctions principales du groupe finance dans Excel : La formation.

 

Nous verrons les trois principales façons de compléter le calcul d’un prêt.

La première est de rechercher la mensualité à payer (combien devais-je payer par mois ?), la seconde est la recherche du taux d’intérêt (quel sera le taux d’intérêt du prêt ?) et la troisième est la durée du prêt (combien de mensualité devrais-je rembourser ?).

1 – Calcul de prêt pour la mensualité
2 – Calcul de prêt pour le taux d’intérêt
3 – Calcul de prêt pour la durée
4 – Décomposer un prêt
5 – Créer un échéancier du prêt

Vous pouvez télécharger le fichier Excel ci-dessous pour tester les fonctions. Celui-ci contient trois feuilles pour présenter les différents modes de calcul.

Excel Icone

Fichier à télécharger contenant les formules utilisées dans cet article

 

Le résultat final d’un calcul de prêt avec échéancier sera le suivant :

Calcul de prêt dans Excel
Calcul de prêt dans Excel

1 – Calcul de prêt pour la mensualité

Dans un premier temps, nous allons voir comment mettre en place le calcul d’une mensualité pour mon prêt. Autrement dit, en fonction du taux annuel, du capital et de la durée de celui-ci je devrais retrouver le montant à rembourser mensuellement.

Formule VPM - Calcul de prêt Excel
Formule VPM – Calcul de prêt Excel

La formule utilisée sera donc VPM, comme le montre la capture ci-dessus, celle-ci s’écrit de la manière suivante :

=-VPM(taux;durée;capital;[résiduelle];[terme])

Le signe moins devant VPM est nécessaire sinon la formule retourne un nombre négatif. Les trois premiers arguments sont le taux de l’emprunt, la durée de l’emprunt (nombre de périodes) et le capital emprunté. Les deux derniers arguments sont facultatifs, la valeur résiduelle est par défaut à 0 et terme, qui permet de gérer le fait que l’échéance est payée à terme à échoir (pour 1) ou à terme échu (pour 0), est lui aussi facultatif.

La formule Excel utilisée pour calculer la mensualité du prêt est donc :

=-VPM((1+B2)^(1/12)-1;B4*12;B3) équivaut à =-VPM((1+3,10%)^(1/12)-1;10*12;120000)

Explication : Pour le taux j’utilise le taux de période, qui est le taux mensuel, si vous ne savez pas de quoi il s’agit je vous invite à consulter cet article, ensuite je calcul le nombre de périodes (en mois, ici 120 car 10 ans multiplié par 12 mois) et enfin j’indique le capital emprunté. Ma mensualité sera donc de 1 161,88 euros sur 10 ans.

2 – Calcul de prêt pour le taux d’intérêt

Nous venons de voir comment mettre en place le calcul d’une mensualité pour mon prêt. Mais je pourrais très bien savoir combien je souhaite emprunté, la mensualité maximum que je puisse payer, le nombre d’années sur lesquelles je souhaite rembourser, dès lors, j’aimerais connaître le taux d’intérêt annuel que je devrais obtenir de mon banquier pour atteindre mes objectifs. La formule que nous allons voir permet de déterminer le taux annuel à obtenir en fonction des critères indiqués précédemment.

Calcul du taux d'intérêt pour un prêt
Calcul du taux d’intérêt pour un prêt

Comme le montre la capture ci-dessus, je vais dans un premier temps calculer le taux de période (le taux mensuel dans notre cas), puis dans un second le taux annuel. La formule utilisée sera TAUX, comme le montre la capture ci-dessus, celle-ci s’écrit de la manière suivante :

=TAUX(durée;mensualité;capital;[résiduelle];[terme];[estimation])

Les trois premiers arguments sont la durée de l’emprunt (nombre de périodes), la mensualité à rembourser et le capital emprunté. Les trois derniers arguments sont facultatifs, la valeur résiduelle est par défaut à 0, l’argument terme, qui permet de gérer le fait que l’échéance est payée à terme à échoir (pour 1) ou à terme échu (pour 0), est lui aussi facultatif, enfin l’argument estimation est facultatif, mais permet de donner une estimation initiale du taux.

La formule Excel utilisée pour calculer le taux du prêt est donc :

=TAUX(12*B4;-B2;B3) qui équivaut dans notre cas à =TAUX(12*13;-960;120000)

Remarque : la donnée correspondante à la mensualité doit être donnée de manière négative. C’est pourquoi un signe moins est devant dans la formule. Notre taux de période est de 0,294%

Je vais utiliser la formule =(1+B5)^12-1 soit =(1+0,294%)^12-1 pour obtenir le taux annuel de notre prêt qui sera 3,58%. Autrement dit, pour emprunter 120 000 euros sur 13 ans et afin de rembourser mensuellement 960 euros je devrais obtenir un prêt à un taux annuel de 3,58 % au maximum, si je négocie un taux en dessous c’est du bénéfice ;).

3 – Calcul de prêt pour la durée

Nous allons maintenant voir comment obtenir la durée d’un prêt lorsque l’on connaît le taux annuel, le capital emprunté et la mensualité que l’on souhaite rembourser. Autrement dit, combien de temps vais-je mettre à rembourser 120 000 euros à un taux de 3,10 % avec 1 100 de mensualité ? La formule va répondre à cette question.

Nombre de mensualités pour un prêt
Nombre de mensualités pour un prêt

La formule utilisée sera donc NPM, comme le montre la capture ci-dessus, celle-ci s’écrit de la manière suivante :

=NPM(taux;mensualité;capital;[résiduelle];[terme])

Les trois premiers arguments sont le taux annuel de l’emprunt, la mensualité à rembourser et le capital emprunté. Les deux derniers arguments sont facultatifs, la valeur résiduelle est par défaut à 0, l’argument terme, qui permet de gérer le fait que l’échéance est payée à terme à échoir (pour 1) ou à terme échu (pour 0), est lui aussi facultatif.

=NPM((1+B2)^(1/12)-1;-B4;B3) qui donne dans notre cas =NPM((1+3,10%)^(1/12)-1;-1100;120000)

Remarque : la donnée correspondante à la mensualité doit être donnée de manière négative. C’est pourquoi un signe moins est devant dans la formule. Notre durée de remboursement est de 127,97 périodes (donc en mois dans notre cas).

Je vais utiliser la formule =B5/12 soit =127,97/12 pour obtenir le nombre d’années afin d’achever le remboursement du prêt. Autrement dit, pour emprunter 120 000 euros à un taux annuel de 3,10% et afin de rembourser mensuellement 1 100 euros je devrais rembourser des échéances durant 128 mois soit 10 et 8 mois.

4 – Décomposer un prêt

Un remboursement de prêt est constitué, de deux choses, le capital et les intérêts (vous vous doutiez qu’on ne vous prêtait pas gratuitement 😉 ). Les intérêts sont calculés pour chaque période, par exemple pour des remboursements mensuels sur 10 ans, cela nous donnera 120 périodes.

Formule PRINCPER - Calcul de prêt Excel
Formule PRINCPER – Calcul de prêt Excel

La capture ci-dessus montre la décomposition d’une mensualité (une période parmi les 120) de notre prêt, pour cela, il suffit d’utiliser les formules PRINCPER et INTPER. Les arguments des deux formules sont les mêmes et se décomposent de la manière suivante :

=-PRINCPER(taux;num_période;durée;capital;[résiduelle];[terme])
=-INTPER(taux;num_période;durée;capital;[résiduelle];[terme])

Les arguments sont les mêmes que pour la formule VPM vu dans la première partie, sauf pour num_période qui est rajouté et qui permet d’indiquer la période à décomposer et ainsi connaître le capital ou les intérêts pour celle-ci. Prenons un exemple :

=-PRINCPER((1+B2)^(1/12)-1;1;B4*12;B3) qui donne =-PRINCPER((1+3,10%)^(1/12)-1;1;10*12;120000) 
=-INTPER((1+B2)^(1/12)-1;1;B4*12;B3) qui donne =-INTPER((1+3,10%)^(1/12)-1;1;10*12;120000)

Le résultat est celui affiché dans la capture « Décomposition de la mensualité » au dessus, la période analysée est « 1 » donc la première période, autrement dit le premier mois. Pour celui-ci, je paierais1 161,88 Euros (comme vu avec le calcul de mensualité), décomposé en 856,2o Euros de capital et 305,68 Euros d’intérêts.

Calcul des prêts sous Excel
Calcul des prêts sous Excel

Maintenant il est également possible de calculer le remboursement du capital et le remboursement de l’intérêt pour plusieurs périodes. Par exemple, les 12 premiers mois, ou les 17 premiers mois ou du 27ème mois au 42ème… Pour cela, il faut utiliser les formules

=-CUMUL.PRINCPER(taux;durée;capital;début_période;fin_période;terme)
=-CUMUL.INTER(taux;durée;capital;début_période;fin_période;terme)

On retrouve les arguments taux, durée, capital et terme (qui sont obligatoires) que nous avons déjà vus en première partie avec la formule VPM. Ici, on constate l’apparition des arguments début_période et fin_période, le premier indique donc le début de la période à analyser et le deuxième argument la fin. Prenons un exemple :

=-CUMUL.PRINCPER((1+B2)^(1/12)-1;B4*12;B3;1;12;0) qui donne =-CUMUL.PRINCPER((1+3,10%)^(1/12)-1;10*12;120000;1;12;0)
=-CUMUL.INTER((1+B2)^(1/12)-1;B4*12;B3;1;12;0) qui donne =-CUMUL.INTER((1+3,10%)^(1/12)-1;10*12;120000;1;12;0)

Le résultat est celui affiché dans la capture « Cumul 1er année » au-dessus, la plage de périodes analysée va donc de 1 à 12, de la première période (premier mois) à la douzième (12ème mois), autrement dit sur une année. Sur un an je paierais donc 10 419,55 Euros de capital et 3 522,99 Euros d’intérêts.

5 – Créer un échéancier du prêt

Les formules vues auparavant vont me permettre de créer l’échéancier de mon prêt, afin de savoir période par période (donc mois par mois), combien je paierai en mensualité, en capital, en intérêts et combien me reste-t-il à payer.

Créer un échéancier de prêt dans Excel
Créer un échéancier de prêt dans Excel

Pour cela nous allons utiliser les différentes formules vu précédemment et les faire glisser en fonction du nombre de périodes.

Dans la première colonne période, il suffit de rentrer 1 comme première période, puis de faire glisser de manière incrémentale ma cellule vers le bas, dans mon cas, j’ai besoin de 120 périodes, puisque 10 ans de remboursement de prêt multiplié par 12 mois = 120.

Dans la seconde colonne, il s’agit de la mensualité que je devrais rembourser chaque mois, celle-ci est constante sur l’ensemble du prêt. Pour la calculer, j’insère la formule suivante dans la cellule de ma première période :

=-VPM(TP-1;B4*12;B3) qui donne =-VPM((1+3,10%)^(1/12)-1;10*12;120000)

Puis dans la seconde cellule, je n’utilise pas la formule, mais simplement le numéro de la cellule contenant la formule et donc le résultat, cela me permettra de le reproduire, puis je fais glisser vers le bas.

Dans la troisième colonne, il s’agit du capital qui sera remboursé par mensualité. Par exemple, pour la 40ème période (donc 40ème mois), je rembourserais 945,51 € en capital sur ma mensualité de 1 161,88 €. Pour calculer le capital remboursé j’utilise la formule suivante :

=-PRINCPER(TP;A18;$B$4*12;$B$3) qui équivaut à =-PRINCPER((1+3,10%)^(1/12);1;10*12;120000)

Le signe $ permet de faire glisser la formule dans le champ en dessous sans décaler la cellule sinon, en faisant glisser la formule dans les autres cellules la sélection aurait été sur B5 puis B6… Le signe $ nous permet de bloquer la cellule en quelque sorte, ainsi notre cellule contenant le taux annuel sera toujours sélectionnée ainsi que la durée et le capital emprunté. TP correspond au taux de période, c’est simplement par gain de place qu’il est écrit sous cette forme.

La variable qui sera modifiée lors de la recopie de la cellule vers le bas sera donc A18, qui passera à A19 puis A20…. ce qui modifiera le numéro de période pour nous donner la période correspondante.

Dans la quatrième colonne, il s’agit cette fois des intérêts, je viens de calculer le capital remboursé sur notre mensualité, maintenant je vais savoir pour chaque période combien je paierai d’intérêt. J’utilise la formule :

=-INTPER(TP;A18;$B$4*12;$B$3) qui donne =-INTPER((1+3,10%)^(1/12);1;10*12;120000)

Le signe $ à la même vocation que pour la troisième colonne.

La variable qui sera modifiée lors de la recopie des cellules sera également A18 qui correspond au numéro de période.

Dans la cinquième colonne, je vais calculer le reste (résiduel), autrement dit, combien restera-t-il à payer après la mensualité en question. Par exemple, après la mensualité correspond à la 40ème période il me restera à payer 83 994,69 euros sur les 120 000 euros. La formule sera la suivante :

=$B$3+CUMUL.PRINCPER(TP;$B$4*12;$B$3;1;A18;0) qui donne:
=120000+CUMUL.PRINCPER((1+3,10%)^(1/12);10*12;120000;1;1;0)

La formule utilise donc le cumul du capital selon une période avec en amont la cellule contenant le capital emprunté. Cette période commence à un et évolue lorsque je recopie la cellule vers le bas. La capture ci-dessous montre bien qu’au bout des 120 périodes (donc 120 mois qui égal 10 ans) mon prêt est remboursé.

Fin de remboursement échéancier
Fin de remboursement échéancier

Articles en relation

24 thoughts on “Calcul d’un prêt et d’un échéancier avec Excel”

    1. Pour la base de données il faut travailler avec Access, pour une application maps, autrement-dit vous souhaitez intégrer une carte (par exemple d’une région) qui se modifie selon les données rentrées ? Si c’est bien cela je compte faire un tuto la dessus dans le courant du mois d’aout, car en ce moment je n’ai malheureusement pas le temps, donc à bientôt j’espère ;).

  1. Merci !

    Une remarque importante vis à vis de votre point n°1 : Le taux de période tel que vous le calculer { (1+B4)^(1/12)-1 } est vrai pour un taux annuel actuariel. votre formule est donc pertinente pour un prêt épargne logement où la méthode actuarielle est obligatoire pour exprimer le taux annuel.

    Par contre pour les prêts bancaires, les banques expriment majoritairement leurs taux annuels avec la méthode proportionnelle. Le taux sur la période s’exprime alors simplement : B4/12.

    Votre feuille Excel pourrait éventuellement être améliorée en ajoutant un menu déroulant à 2 options permettant de choisir entre l’une ou l’autre des méthodes, le choix induisant une modification de la méthode de calcul.

    Encore merci pour votre article.

    1. Merci pour la précision ;), il ne s’agit que d’une base de travail, celle-ci peut toujours être améliorée, votre remarque permettra à certain d’améliorer ce fichier à leur convenance, je vous en remercie.

  2. Salut à tous,
    je souhaiterai entrée la date du début d’emprunt et d’automatiser le calcul de la somme des intérets jusqu’à la fin de l’année.
    Exemple:
    Date début: 01/11/2014
    Case C23: Somme intérêts 2014 = somme (« interet de novembre » + « interet de décembre »)
    Case C24: Somme intérêts 2015 de janvier à décembre

    Avez vous une solution pour rendre la fonction dépendante de la date de début qu’on renseigne ?

  3. bonjour

    je souhaiterai avoir un tableau de financement avec taux de participation concessionnaire modifiable
    comment faire ?
    merci pour votre réponse

  4. Bonjour,

    Préférant d’abord lire les explications avant de télécharger des tableaux finis,
    J’ai trouvé une grosse a une coquille dans vos exemples, dès le début pour le calcul de la mensualité du prêt.

    Dans les 2 premières copies d’écran, la formule utilisée pour le calcul de la mensualité « =-VPM((1+B4)^(1/12)-1;B6*12;B5) » fait référence à des cellules vides (B6).
    Dans l’état cela ne peut fonctionner. Étonnamment, personne ne la remarqué.

    Merci d’avance pour vos corrections.

    1. Bonjour,

      Effectivement, petit erreur de frappe, merci pour votre remarque, j’ai fait les modifications. Je pense que personne ne l’a remarqué car la formule dans la cellule de calcul était la bonne. En tout cas merci beaucoup 😉

  5. bonjour,
    je souhaite faire calculer par excel le capital restant du à une date intermédiaire donnée avec rappel de la mensualité fixe
    on rentre le montant emprunté , la durée en mois, le taux.
    pouvez vous m’aider a trouver la formule
    merci

  6. Bonjour,

    J’essaye de déterminer le TEG qui a été appliqué a mon prêt. Pour cela j’utilise la formule TRI. j’obtiens alors un taux périodique mensuel que je multiplie par 12 pour connaitre le taux annuel.

    Or je souhaiterai savoir si Excel, lorsqu’il calcule avec la fonction TRI le taux périodique, calibre son calcul, par convention, sur une base de 30 jours ou si au contraire il considère qu’il y a 30.4166667 jours?

    Cette précision est importante car si Excel fait sont calcul sur 30 jours, lorsque je multiplie ensuite par 12, comme le font d’ailleurs les banques, mon TEG n’est pas calculé sur une base de 365 jours mais seulement de 360 jours (30*12).

    Merci pour votre aide.

    1. Bonjour,

      Je suis expert TEG pour le compte d’emprunteurs.
      Effectivement, excel calcul sur une base de 360 jours, comme la plupart des banques. selon la méthode appelée « Lombarde »., mais il est assez simple de contourner ce point.
      Toutefois… Attention. Il y a un risque en s’appuyant sur ce constat. les banques risquent de se défendre et de dire que R313-1 du code de la conso « le taux effectif global est un taux annuel, proportionnel au taux de période », soit le TEG correspond à 12 fois le taux de période en cas de remboursement mensuel. Difficile de multiplier par 12 un taux de période qui sera différent selon que le mois comporte 28 – 29 – 30 – 31 jours….

      Si la loi devait être révisée, il y aurait de fortes chances qu’elle valide la pratique de l’année Lombarde… Si l’état peut venir au secours des banques… Elle ne se privera pas…

      Les seuls cas où la l’année lombarde est plaidable, c’est quand le prêteur l’écrit très clairement dans ces offres. (ex la caisse d’épargne). Il n’y a plus besoin de calculs puis le prêteur écrit en toute lettre qu’il calcul ses intérêts sur la base d’une année de 360 jours

  7. Bonjour,
    Pouvez- vous me fournir un tableau excel pour calculer mes mensualités sachant que j’emprunte 45000€ en 12 ans à un taux fixe y compris assurance de 2.1%. Merci de votre coopération.

  8. Bonjour,

    Pourriez-vous m’expliquer comment puis-je faire un tableau excel pour insérer une colonne permettant des versements exceptionnels en plus des mensualités fixe avec diminution du capital restant du.

    Merci pour votre retour

  9. Bonjour;

    j’ai obtenu un prêt sur 30 ans avec 06 mois de différé

    Pourriez vous me communiquer la formule qui s’adapte à mon cas

    Merci pour votre retour

  10. Pour info, la formule enoncée dans cet article et dans le fichier n’est pas la bonne et ne correspond pas au calcul des banques … voici la bonne :
    Montant_Pret*Taux/12*(1+Taux/12)^(Nb_Mensualité*12)/(((1+Taux/12)^(Nb_Mensualité*12)-1))

  11. Bonjour,

    J’aimerai faire un tableau d’amortissement mais avec une mensualité fixe et un assurance décès sur capital restant dû.
    Pouvez-vous m’aider?
    Merci

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *