Financières

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 fait 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 ou 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 cours d'un mois sur l'autre et donc le montant à rembourser sont recalculés 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

Related posts

Calcul de la TVA (HT ou TTC)

Frédéric LE GUEN

Calculer le pourcentage d’augmentation

Frédéric LE GUEN

Taux de change monétaire dans Excel

Frédéric LE GUEN

9 commentaires

hortense 25/02/2021 at 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

Répondre
patrick 17/02/2021 at 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.

Répondre
Leveque 19/01/2021 at 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 .

Répondre
Frédéric LE GUEN 21/01/2021 at 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

Répondre
MAHAMAT 27/10/2020 at 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!

Répondre
Remusen 03/07/2020 at 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 ?

Répondre
Frédéric LE GUEN 03/07/2020 at 18:40

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

Répondre
Remusen 04/07/2020 at 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

Répondre
Remusen 04/07/2020 at 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é...

Répondre

Laissez un commentaire

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