=VPM(B2/12;B3*12;-B1) renvoie la mensualité d'un prêt de B1 au taux d'intérêt annuel de B2, remboursé sur B3 années. Le taux est divisé par 12 et les années multipliées par 12 parce que les remboursements sont mensuels. VPM s'appelle PMT dans un Excel anglais ; le tableau affiche les formules sous cette forme, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =VPM(B2/12;B3*12;-B1).
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Monthly payment | $1,580.17 |
| 6 | Total paid | $568,861.22 |
| 7 | Total interest | $318,861.22 |
=VPM(B2/12;B3*12;-B1)Un prêt de 250 000 à 6,5 % sur 30 ans coûte $1,580.17 par mois, et les intérêts sur toute la durée s'élèvent à $318,861.22, plus que le prêt lui-même. Mettez 15 en B3 : la mensualité monte à $2,177.77, mais le total des intérêts tombe à moins de la moitié.
Syntaxe de VPM
=PMT(rate, nper, pv, [fv], [type])
rate(taux) : le taux d'intérêt par période. Pour des mensualités sur un taux annuel, utiliseztaux/12.nper(npm) : le nombre de remboursements. 30 ans de mensualités donnent30*12, soit 360.pv(va) : la valeur actuelle, le montant emprunté.fv(vc, facultatif) : le montant restant à la fin. 0 pour un prêt entièrement remboursé, c'est la valeur par défaut ; l'objectif d'épargne pour un plan d'épargne.type(facultatif) : 0 (par défaut) pour des versements en fin de période, comme la plupart des prêts ; 1 pour un début de période, comme un loyer ou un leasing.
VPM suppose un taux fixe et des versements égaux. Elle n'inclut ni les taxes, ni l'assurance, ni les frais qu'un prêteur ajoute à une mensualité de crédit immobilier.
Pourquoi VPM renvoie un nombre négatif
Les fonctions financières d'Excel (VPM, VA, VC, VAN, TRI) utilisent le signe d'un nombre pour le sens de l'argent. L'argent reçu est positif et l'argent versé négatif. Un prêt est de l'argent reçu, donc =VPM(B2/12;B3*12;B1) renvoie -1 580,17 : un versement qui sort.
C'est pourquoi la formule de cette page contient -B1 : le montant emprunté entre en négatif et la mensualité sort en positif. =-VPM(B2/12;B3*12;B1) fait la même chose. Choisissez une méthode et utilisez-la partout, car la même règle de signe s'applique à fv : un objectif d'épargne que vous recevrez se saisit comme une valeur future négative avec une valeur actuelle de 0.
Comparer des durées de prêt
Placez les durées dans une colonne, gardez le montant et le taux en références absolues, et recopiez la formule vers le bas pour comparer côte à côte les mensualités et le coût total.
| A | B | C | |
|---|---|---|---|
| 1 | Amount | $28,000 | |
| 2 | Rate | 7.9% | |
| 3 | Years | Monthly | Total interest |
| 4 | 3 | $876.13 | $3,540.58 |
| 5 | 4 | $682.25 | $4,747.92 |
| 6 | 5 | $566.40 | $5,984.00 |
| 7 | 6 | $489.56 | $7,248.66 |
=VPM($B$2/12;A4*12;-$B$1)Allonger le prêt de 3 à 6 ans fait passer la mensualité de $876.13 à $489.56, tandis que les intérêts passent de $3,540.58 à $7,248.66. Pour en savoir plus sur les signes $, voir les références absolues.
Combien épargner chaque mois
Avec pv à 0 et une valeur future, VPM répond à la question inverse : combien mettre de côté chaque mois pour atteindre un objectif. L'objectif est de l'argent que vous recevrez, donc il entre en négatif pour que le versement soit positif.
| A | B | |
|---|---|---|
| 1 | Goal | $20,000 |
| 2 | Annual rate | 4% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly deposit | $301.66 |
=VPM(B2/12;B3*12;0;-B1)Verser $301.66 par mois pendant 60 mois à 4 % permet d'atteindre 20 000 ; les versements totalisent environ 18 100, et les intérêts apportent le reste. Mettez 0 % en B2 et le versement devient exactement 20 000 divisé par 60.
Intérêts et capital : INTPER et PRINCPER
Chaque mensualité paie une part d'intérêts et une part du prêt. INTPER (IPMT en anglais) renvoie la part d'intérêts d'une mensualité et PRINCPER (PPMT) la part de capital ; ensemble, elles égalent VPM. Les deux prennent le numéro de la mensualité en deuxième argument, ce qui donne un tableau d'amortissement une fois recopié vers le bas.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Payment | Interest | Principal | Balance |
| 2 | 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 3 | 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 4 | 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
| 5 | 4 | $1,580.17 | $1,350.47 | $229.70 | $249,088.61 |
| 6 | 5 | $1,580.17 | $1,349.23 | $230.94 | $248,857.67 |
=INTPER(6,5%/12;A2;360;-250000)Le premier mois, $1,354.17 des $1,580.17 de la mensualité sont des intérêts et seulement $226.00 réduisent le capital restant. La part de capital augmente un peu chaque mois. Dans un vrai classeur, placez le taux, la durée et le montant dans des cellules et référencez-les avec $, comme dans les tableaux précédents.
Essayez : la mensualité d'un crédit auto
| A | B | |
|---|---|---|
| 1 | Loan | $32,000 |
| 2 | Annual rate | 6.9% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly payment |
À vous : En B5, calculez la mensualité du prêt de B1 au taux annuel de B2 sur le nombre d'années de B3. Affichez-la sous forme de nombre positif.
Indice : le taux et le nombre de remboursements doivent tous deux être mensuels.
Erreur fréquente : un taux annuel avec des mensualités
Le taux et nper doivent utiliser la même période. Laisser le taux annuel tout en comptant des mois applique 6,5 % chaque mois, et la mensualité sort environ dix fois trop élevée :
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Wrong | $16,250.00 |
| 6 | Right | $1,580.17 |
=VPM(B2;B3*12;-B1)La mauvaise formule demande $16,250.00 par mois, soit simplement les intérêts à 6,5 % par mois. Une autre version de la même erreur consiste à taper le taux 6,5 au lieu de 6,5% ou 0,065 : cela signifie 650 % par an. Si une mensualité paraît absurde, vérifiez d'abord l'unité du taux. Pour des versements annuels, utilisez le taux annuel et le nombre d'années tels quels.
Questions fréquentes
Comment calculer la mensualité d'un prêt dans Excel ?
Utilisez =VPM(taux/12;années*12;-montant), par exemple =VPM(6,5%/12;30*12;-250000) pour un prêt de 250 000 sur 30 ans à 6,5 %. Elle renvoie environ 1 580,17 par mois.
Pourquoi VPM est-elle négative dans Excel ?
Les fonctions financières d'Excel utilisent le signe pour le sens de l'argent : l'argent reçu est positif et l'argent versé négatif. Le prêt vous est versé, donc la mensualité qui sort est négative. Placez un signe moins devant le montant emprunté (-B1) ou devant VPM pour l'afficher en positif.
Comment calculer le coût total des intérêts d'un prêt dans Excel ?
Multipliez la mensualité par le nombre de mensualités et soustrayez le montant emprunté : =VPM(B2/12;B3*12;-B1)*B3*12-B1. Pour les intérêts payés la première année, utilisez =-CUMUL.INTER(B2/12;B3*12;B1;1;12;0).
Quelle est la différence entre VPM, INTPER et PRINCPER ?
VPM est la mensualité entière. INTPER est la part d'intérêts d'une mensualité et PRINCPER la part de capital, et les deux additionnées donnent VPM. Les premières mensualités sont surtout des intérêts.
Comment calculer combien épargner chaque mois dans Excel ?
Donnez à VPM une valeur future au lieu d'une valeur actuelle : =VPM(4%/12;5*12;0;-20000) est le versement mensuel qui atteint 20 000 en 5 ans à 4 % par an.
