Calculer vos mensualités avec Excel

Temps de lecture : 2 minutes

Il est assez facile de calculer des mensualités avec Excel grâce à la fonction VPM.

Utilisation de la fonction VPM

La fonction VPM est la traduction de Valeur de PaieMent.

=VPM(taux;npm;va;vc;type)

  • taux Correspond au taux d’intérêt de l’emprunt.
  • npm Le nombre de remboursements pour l’emprunt.
  • va Représente le montant de l'emprunt (valeur actuelle).
  • vc C'est la valeur capitalisée. Facultatif et souvent = 0.
  • type 0 ou 1 pour indiquer si les remboursements sont faits au début (1) ou en fin (0) de période (cela peut être source d'erreur).

Prenons le cas d'un emprunt de 50 000€ sur 15 ans au taux de 4%.

Faire le calcul avec la fonction VPM

Exemple de calcul de mensualités

La formule est la suivante :

=VPM(4%;15;50000)   =>  -4 497,06 €

La fonction retourne une valeur négative car il s'agit d'un décaissement. Il est très facile de retourner le résultat positivement soit en multipliant par -1 soit avec la fonction ABS (valeur absolue).

=VPM(4%;15;50000)*-1

=ABS(VPM(4%;15;50000))

Mais ce résultat c'est le versement annuel et pas mensuel 🤔🤨

Calcul des mensualités

Pour que la fonction VPM retourne une mensualité, il faut transformer la durée de l'emprunt et le taux d'intérêt.

Changer le nombre de périodes

Si vous souhaitez que la fonction vous retourne une mensualité, il faut indiquer le nombre de mois sur lequel va porter votre remboursement.

C'est un calcul très simple

=nombre d'années*12

Transformer le taux d'intérêt annuel en mensuel

De la même façon, il faut convertir le taux d'intérêt annuel en taux d'intérêt mensuel.

Beaucoup d'articles vous disent que pour convertir un taux annuel en taux mensuel il suffit de diviser le taux par 12. Ce n'est pas exact car le taux court d'un mois à l'autre et donc le montant à rembourser est recalculé chaque mois.

La formule qui permet de convertir un taux annuel en taux mensuel est

=PUISSANCE(1+taux annuel;1/12)-1

ou

=(1+taux annuel)^(1/12)-1

Le 1/12 c'est pour les 12 mois (cf. l'article sur le calcul de la racine cubique). Si vous voulez convertir en taux trimestriel, il faut indiquer 1/4.

Montant des mensualités

Pour reprendre notre exemple, le calcul montant des mensualités est donc de :

=VPM((1+4%)^(1/12)-1;15*12;50000)

Comme il s'agit d'un décaissement, le résultat est négatif. Pour retourner un résultat positif, il suffit de multiplier la formule par -1

=VPM((1+4%)^(1/12)-1;15*12;50000)*-1

Vous trouverez des informations complémentaires sur la fonction VPM sur le site de Microsoft.

11 Comments

  1. Yacinthe Natacha
    21/09/2021 @ 23:21

    Bonjour,
    Quelle est la formule à appliquer dans ce cas-ci
    J'emprunte 25 000 $, mon taux d'intérêt est de 3% par mois et ce sera le même tous les mois et je dois rembourser la totalité en 5 mois.

    Merci

    Reply

    • Frédéric LE GUEN
      22/09/2021 @ 11:37

      Ben, la même formule que celle expliquée dans l'article

      Reply

  2. hortense
    25/02/2021 @ 19:20

    Bonjour,
    Je cherche à modeliser un emprunt avec une partie amortissable et une partie bullet. Est ce que vc dans la formule VPM correspond à la portion bullet du prêt?
    MErci de votre aide, je ne sais pas trop comment modéliser si ce n'est en séparant le prêt en deux!
    Hortense

    Reply

  3. patrick
    17/02/2021 @ 07:02

    Bonjour,

    Une précision :
    "Beaucoup d'articles vous disent que pour convertir un taux annuel en taux mensuel il suffit de diviser le taux par 12. Ce n'est pas exact car le taux cours d'un mois sur l'autre et donc le montant à rembourser sont recalculés chaque mois."

    Je suis bien d'accord avec vous, cette formule a la vie dure. Ce calcul correspond à une moyenne arithmétique, en référence aux suites arithmétiques ; ce taux n'est qu'approximatif, il s'appuie sur la relation (1+t)^n≈1+nt lorsque t est faible.

    La formule =PUISSANCE(1+taux annuel;1/12)-1 donne la moyenne géométrique, en référence aux suites géométriques, ce calcul est exact. Je suppose que le premier calcul a été longtemps utilisé par le passé car on ne disposait pas de moyens suffisants pour calculer une racine 12e.

    Reply

  4. Leveque
    19/01/2021 @ 15:24

    Bonjour,

    Merci pour ces explications très claires. Il reste un élément que je ne comprends pas : pourquoi si l'on prend la cellule C10 (calcul officiel) et qu'on la multiplie par 12 pour retrouver des annuités, on obtient un chiffre différent (4 416€) du montant d'annuité.

    En d'autres termes, pourquoi sur excel on a l'inégalité suivante : =VPM (4%;15;50000) différent de =VPM((1+4%)^(1/12)-1;15*12;50000)*12 .

    Reply

    • Frédéric LE GUEN
      21/01/2021 @ 15:25

      C'est tout simplement parce que ce n'est pas la même égalité. La première formule 4%;15;50000 considère que le 4% c'est le taux constant sur l'année et la deuxième formule réajuste tous les mois les mensualités après chaque mensualités versées. Je sais que la différence n'est pas simple à comprendre mais c'est le principe des mathématiques financières

      Reply

  5. MAHAMAT
    27/10/2020 @ 16:08

    Bonjour Monsieur, je comprend mais quelque souci au niveau du résultat qui m'affiche en EURO peut-on convertir en FCFA SVP. Merci!

    Reply

  6. Remusen
    03/07/2020 @ 17:23

    Bonjour,
    Merci pour cette formule qui fonctionne bien pour un taux supérieur à 1%
    Je bloque avec un taux d'assurance à 0.29% pour lequel votre formule (et ce que j'ai pu essayer de mon côté) ne fonctionne pas.
    Auriez-vous une solution ?

    Reply

    • Frédéric LE GUEN
      03/07/2020 @ 18:40

      Bonjour,
      Quelle formule ? Celle du calcul des mensualités ou du taux mensuel ?

      Reply

      • Remusen
        04/07/2020 @ 08:30

        Et je m'aperçois qu'en renseignant les cases via l'onglet formule / financier / VPM le résultat est le même.
        C'est Excel qui bloque

      • Remusen
        04/07/2020 @ 08:57

        Et au final à force de me creuser la tête il est possible que le montant mensuel de l'assurance ne soit pas pris sur la capitale emprunté...

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

MICROSOFT MVP

RECHERCHE D’EMPLOI AVEC