Vous souhaitez faire le calcul d’un prêt dans Excel et mettre en place un échéancier en fonction des éléments indiqués par vous-même ou un utilisateur ?

Dans cet article, vous trouverez toutes les informations pour réaliser ces deux calculs (calculer un prêt et un échéancier). Vous trouverez également un tableau avec toutes les fonctions nécessaires qu’il vous suffira de télécharger pour pouvoir appliquer. Un prêt bancaire est toujours en enjeu important, pouvoir le comprendre et le calculer est essentiel avant de se lancer.

Calcul d'un prêt et un échéancier dans Excel

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

On se pose souvent plusieurs questions lorsqu’on signe un prêt. Dans cet article, vous découvrirez comment répondre aux trois principales questions que l’on peut se demander. La première, c’est de rechercher la mensualité à payer (combien devrais-je payer par mois ?), la seconde c’est de rechercher le taux d’intérêt (quel sera le taux d’intérêt du prêt ?) et la troisième c’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. Les différents calculs présents dans ces dernières sont expliqués dans l’article ci-dessous.

Ci-dessous, le 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