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
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.
Yacinthe Natacha
Frédéric LE GUEN
hortense
patrick
Leveque
MAHAMAT
Remusen
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
22/09/2021 @ 11:37
Ben, la même formule que celle expliquée dans l'article
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
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.
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 .
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
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!
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 ?
03/07/2020 @ 18:40
Bonjour,
Quelle formule ? Celle du calcul des mensualités ou du taux mensuel ?
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
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é...