Qu'est-ce que la fonction VPM ?
VPM (PMT en anglais) te sert dès que tu dois caler un budget sur un chiffre précis, avant de t'engager sur un prêt ou de fixer un rythme d'épargne, sans avoir à dérouler les intérêts mois par mois pour y arriver.
Que tu compares deux offres de crédit immobilier, que tu boucles un business plan ou que tu planifies simplement ton épargne, VPM te donne le montant exact en quelques secondes, sans tableau d'amortissement à rallonge.
Comprendre chaque paramètre de la fonction VPM
taux
: le taux d'intérêt par périodePour un prêt à taux annuel de 3 % remboursé par mensualités, divise par 12 : 3%/12 vaut 0,25 % par mois.
C'est l'erreur la plus fréquente : saisir le taux annuel sans le diviser. Le taux doit toujours correspondre à la périodicité des paiements (mensuelle, trimestrielle, annuelle).
Attention : Si tu oublies de diviser le taux annuel par 12, le résultat est complètement faux. =VPM(3%; 240; -200000) donne une mensualité aberrante, alors que =VPM(3%/12; 240; -200000) donne le bon montant.
npm
: le nombre total de paiementsPour un prêt sur 20 ans réglé chaque mois : 20*12, soit 240 paiements.
Assure-toi que cette valeur correspond à la périodicité du taux. Si le taux est mensuel, npm doit être exprimé en mois, pas en années.
va
: la valeur actuelle : le montant emprunté ou le capital initialPour un prêt de 200 000 €, c'est l'argent que tu reçois aujourd'hui.
La convention de signe compte. Selon que tu saisis 200000 ou -200000, la mensualité renvoyée sera négative ou positive. Dans la pratique, on préfixe souvent la formule d'un - pour obtenir directement un paiement positif.
[vc]
: la valeur capitalisée souhaitée à la fin(facultatif)Par défaut elle vaut 0, ce qui correspond à un prêt entièrement remboursé.
Pour un calcul d'épargne, vc devient ton objectif final. Par exemple =VPM(3%/12; 8*12; 0; 50000) calcule combien épargner chaque mois pour atteindre 50 000 € au bout de 8 ans.
[type]
: le moment du paiement dans la période(facultatif)0 (la valeur par défaut) correspond à un paiement en fin de période, le cas de la plupart des prêts. 1 correspond à un paiement en début de période.
Les prélèvements en début de mois réduisent légèrement la mensualité, car le capital est remboursé un peu plus tôt à chaque échéance.
Exemples pratiques pas à pas
Comment calculer la mensualité d'un prêt avec la fonction VPM
Tu compares plusieurs biens avant de te lancer, et la seule question qui compte vraiment, c'est combien tu vas sortir chaque mois. Ton tableau liste chaque bien avec son capital, son taux annuel et sa durée en années, et tu veux la mensualité en face de chaque ligne pour trancher. La fonction VPM prend ces trois informations et renvoie le paiement constant à verser, prêt à recopier sur toute la colonne.
- 1Dans une cellule, écris
=-VPM(. Le signe moins placé juste devant la fonction n'est pas un argument. Il inverse la convention d'Excel, qui rend un remboursement négatif, pour afficher une mensualité positive. - 2En 1er argument : saisis le taux d'une seule période,
C2/12. Le taux annoncé par la banque est annuel, mais comme tu rembourses chaque mois, tu le divises par12pour le ramener au mois. - 3En 2ᵉ argument : saisis le nombre total de paiements,
D2*12. La durée est en années dans la colonne, alors tu la multiplies par12pour la convertir en300mensualités et rester dans la même unité que le taux. - 4En 3ᵉ argument : clique sur la cellule du capital emprunté,
B2, soit250 000 €. C'est l'argent que la banque te verse aujourd'hui et que la fonction VPM étale sur les 300 échéances. - 5Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas pour obtenir la mensualité de chaque bien.
=-VPM(C2/12; D2*12; B2)12 devient un taux mensuel, et les 25 ans se transforment en 300 échéances. Le signe - placé devant inverse la convention d'Excel (un paiement sort de ta poche, donc négatif) pour afficher 1 251,56 € en positif. Recopiée vers le bas, la colonne donne la mensualité de chaque bien.
Comment calculer l'épargne mensuelle pour atteindre un objectif avec la fonction VPM
Tu t'es fixé plusieurs objectifs d'argent, un apport, une voiture, les études des enfants, et tu ignores l'effort mensuel que chacun représente réellement. Chaque ligne indique le montant visé, le rendement annuel du placement et le nombre d'années devant toi. La fonction VPM ne sert pas qu'aux prêts : tu lui donnes ton objectif final et elle te dit combien mettre de côté chaque mois pour y arriver.
- 1Dans une cellule, écris
=-VPM(. Le signe moins joue le même rôle que pour un prêt. Un versement d'épargne reste une sortie d'argent pour Excel, donc négatif, et ce moins te rend un montant positif. - 2En 1er argument : saisis le rendement du placement ramené au mois,
C2/12. Tu épargnes tous les mois, donc le taux annuel se divise par12, exactement comme pour un prêt. - 3En 2ᵉ argument : saisis le nombre de versements,
D2*12. Les8années deviennent96versements mensuels, alignés sur la périodicité du taux. - 4En 3ᵉ argument : saisis le capital de départ,
0, parce que tu commences sans rien de côté. Si tu avais déjà5 000 €placés, c'est ici que tu les mettrais, et l'effort mensuel baisserait d'autant. - 5En 4ᵉ argument : clique sur la cellule du montant visé,
B2. C'est la somme que tu veux réunir à la fin, et cet argument facultatif fait basculer le calcul du prêt vers l'épargne. On le laissait à0dans l'exemple précédent. - 6Ferme la parenthèse et appuie sur Entrée.
=-VPM(C2/12; D2*12; 0; B2)0 et c'est l'arrivée qui est connue, d'où le montant visé placé dans vc. Le résultat, 461,48 €, est la somme à placer chaque mois pendant 8 ans pour réunir les 50 000 €, intérêts du placement compris.
Comment comparer une mensualité à un budget avec les fonctions VPM et SI
La banque t'a donné une enveloppe de 1 400 € par mois, et tu as quatre biens en tête. Tu veux savoir d'un coup d'œil lesquels tiennent dans cette enveloppe, sans calculer chaque mensualité à part puis la comparer de tête. En glissant la fonction VPM à l'intérieur du test d'une fonction SI, chaque ligne affiche son verdict toute seule.
- 1Dans une cellule, écris
=SI(. - 2En 1er argument : saisis le test qui départage les biens,
-VPM(C2/12; D2*12; B2)<=E2. La fonction VPM calcule la mensualité du bien à partir deC2/12,D2*12etB2, le signe moins la rend positive, et<=E2la confronte au budget mensuel de la ligne. C'est cette comparaison qui vaut vrai ou faux, et donc tout le premier argument de la fonction SI. - 3En 2ᵉ argument : saisis ce qui s'affiche quand le test est vrai,
"Finançable". Les guillemets sont obligatoires pour qu'Excel affiche ce texte tel quel. - 4En 3ᵉ argument : saisis ce qui s'affiche dans le cas contraire,
"Hors budget". Chaque ligne tombe forcément dans l'un des deux camps, et tu lis le verdict directement dans la colonne. - 5Ferme la parenthèse et appuie sur Entrée.
=SI(-VPM(C2/12; D2*12; B2)<=E2; "Finançable"; "Hors budget")1 251,56 € pour l'appartement 1), et la fonction SI la confronte ensuite au budget de la colonne E. Le studio 3 ressort « Hors budget » parce que ses 1 706,80 € dépassent l'enveloppe, alors que les trois autres passent sans problème.
Comment gérer l'erreur #NOMBRE! de la fonction VPM avec SIERREUR
Ton tableau de simulation est presque prêt à partir, sauf qu'une erreur #NOMBRE! s'affiche en plein milieu de la colonne parce qu'un bien n'a pas encore de durée arrêtée. Tu ne veux ni supprimer la ligne ni bricoler une valeur au hasard, juste garder une colonne lisible tant que l'information manque. La fonction SIERREUR encadre la fonction VPM et remplace l'erreur par un message clair.
- 1Dans une cellule, écris
=SIERREUR(. - 2En 1er argument : saisis la formule à surveiller,
-VPM(C2/12; D2*12; B2). C'est exactement celle de l'exemple 1, et tant qu'elle se calcule son résultat passe sans être modifié. - 3En 2ᵉ argument : saisis le message qui remplace l'erreur,
"Durée à saisir". Quand la durée manque,D2*12vaut0et la fonction VPM renvoie#NOMBRE!faute d'échéances sur lesquelles étaler le capital. Ce texte s'affiche alors à sa place, pour dire à la personne qui remplit le tableau ce qui manque. - 4Ferme la parenthèse et appuie sur Entrée.
=SIERREUR(-VPM(C2/12; D2*12; B2); "Durée à saisir")npm tombe à zéro et la fonction VPM n'a plus aucune échéance sur laquelle répartir le capital. Un nombre de paiements nul ne décrit aucun prêt possible, donc Excel refuse le calcul et renvoie l'erreur #NOMBRE!. La fonction SIERREUR intercepte cette erreur et affiche « Durée à saisir » à sa place, sans rien changer aux lignes qui se calculent normalement.
Les erreurs fréquentes avec la fonction VPM
Oublier de diviser le taux annuel
C'est l'erreur la plus courante. Pour des mensualités, le taux doit être mensuel : =VPM(3%; 240; -200000) utilise un taux de 3 % par mois au lieu de 3 % par an et donne un résultat totalement aberrant.
Solution : Solution : divise toujours le taux annuel par 12 pour des mensualités : =VPM(3%/12; 240; -200000). Le taux et npm doivent partager la même unité de temps.
Mauvais signe sur le capital
Le capital emprunté est généralement saisi en positif (argent reçu), et VPM renvoie alors un résultat négatif (argent payé). Beaucoup d'utilisateurs sont surpris de voir une mensualité précédée d'un -.
Solution : Solution : c'est normal, c'est la convention de signe. Pour obtenir directement un montant positif, préfixe la formule d'un - : =-VPM(3%/12; 240; 200000) renvoie 1 109,20 au lieu de -1 109,20.
VPM vs VA vs VC vs TAUX
Ces quatre fonctions financières décrivent la même équation de prêt, mais chacune calcule une variable différente. Tu choisis celle qui correspond à l'inconnue que tu cherches.
| Fonction | Ce qu'elle calcule | Question type |
|---|---|---|
| VPM | Le paiement périodique | Quelle mensualité ? |
| VA | Le capital empruntable | Combien puis-je emprunter ? |
| VC | La valeur finale | Combien aurai-je dans X ans ? |
| TAUX | Le taux d'intérêt | Quel taux effectif ? |
Questions fréquentes
VPM renvoie un nombre négatif car un paiement est une sortie d'argent de ta poche. C'est la convention de signe des fonctions financières : ce qui sort est négatif, ce qui entre est positif.
Pour obtenir un résultat positif, encadre la formule avec ABS(...) ou place simplement un signe moins devant : =-VPM(...).
Divise le taux annuel par 12 pour obtenir le taux mensuel, et multiplie la durée en années par 12 pour le nombre de mensualités.
Par exemple, =VPM(5%/12; 20*12; -200000) calcule la mensualité d'un prêt de 200 000 € sur 20 ans à 5 %. Le secret est de toujours garder la même unité de temps pour le taux et la durée.
Le paramètre type indique quand le paiement intervient dans la période. 0 (la valeur par défaut) correspond à un paiement en fin de période, le cas de la plupart des prêts.
1 correspond à un paiement en début de période. Comme le capital est remboursé un peu plus tôt à chaque échéance, les prélèvements en début de mois réduisent légèrement la mensualité.
Oui, c'est l'un des usages les plus utiles de VPM. Mets va à 0 (tu pars de zéro) et place ton objectif dans vc.
Par exemple, =VPM(4%/12; 10*12; 0; -50000) te dit combien épargner chaque mois pour réunir 50 000 € dans 10 ans avec un rendement de 4 %. VPM planifie l'épargne aussi bien que le remboursement.
Multiplie la mensualité par le nombre de paiements : VPM(...)*npm. Cela te donne la somme totale que tu auras versée sur toute la durée.
La différence entre ce total et le capital emprunté correspond au montant des intérêts payés. C'est le meilleur indicateur pour comparer deux offres, bien plus parlant que la seule mensualité.
Pour aller plus loin
Continue sur ta lancée après la fonction VPM : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voirLes fonctions financières dans Excel
Le modèle Excel « Amortissement de prêt »
Le guide complet pour bien démarrer sur Excel
