Fonction Excel PMT avec des exemples de formule

Le tutoriel montre comment utiliser la fonction PMT dans Excel pour calculer les paiements pour un prêt ou un investissement en fonction du taux d'intérêt, du nombre de paiements et du montant total du prêt.

Avant d'emprunter de l'argent, il est bon de savoir comment fonctionne un prêt. Grâce aux fonctions financières Excel telles que le taux, le PPMT et l'IPMT, le calcul du paiement mensuel ou tout autre paiement périodique pour un prêt est facile. Dans ce tutoriel, nous aurons un examen de plus en plus sur la fonction PMT, discuterons en détail de sa syntaxe et montrerons comment construire votre propre calculatrice PMT dans Excel.

• Quelle est la fonction PMT dans Excel?
• Fonction Excel PMT - Syntaxe et utilisations de base
• Comment utiliser la formule PMT dans Excel - Exemples
  • Forme complète de la fonction PMT
  • Formules PMT pour les paiements hebdomadaires, mensuels, trimestriels et semestriels
• Comment créer une calculatrice PMT dans Excel
• La fonction Excel PMT ne fonctionne pas

Quelle est la fonction PMT dans Excel?

La fonction Excel PMT est une fonction financière qui calcule le paiement d'un prêt basé sur un taux d'intérêt constant, le nombre de périodes et le montant du prêt.

"PMT" signifie "paiement", d'où le nom de la fonction.

Par exemple, si vous demandez un prêt automobile de deux ans avec un taux d'intérêt annuel de 7% et le montant du prêt de 30 000 $, une formule PMT peut vous dire quels seront vos paiements mensuels.

Pour que la fonction PMT fonctionne correctement dans vos feuilles de travail, veuillez garder à l'esprit ces faits:

• Pour être conforme au modèle de flux de trésorerie général, le montant du paiement est sorti comme un nombre négatif car il s'agit d'une sortie de trésorerie.
• La valeur renvoyée par la fonction PMT comprend le capital et les intérêts , mais n'inclut aucun frais, taxes ou paiements de réserve qui peuvent être associés à un prêt.
• Une formule PMT à Excel peut calculer un paiement de prêt pour différentes fréquences de paiement telles que hebdomadaire , mensuelle , trimestrielle ou annuelle . Cet exemple montre comment le faire correctement.

La fonction PMT est disponible dans Excel pour Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 et Excel 2007.

Fonction Excel PMT - Syntaxe et utilisations de base

La fonction PMT a les arguments suivants:

PMT (Rate, NPER, PV, [FV], [Type])

Où:

• Taux (requis) - Le taux d'intérêt constant par période. Peut être fourni en pourcentage ou en décimal.

  Par exemple, si vous effectuez des paiements annuels sur un prêt à un taux d'intérêt annuel de 10%, utilisez 10% ou 0,1 pour le taux. Si vous effectuez des paiements mensuels sur le même prêt, utilisez 10% / 12 ou 0,00833 pour le taux.

• NPER (requis) - Le nombre de paiements pour le prêt, c'est-à-dire le nombre total de périodes sur lesquelles le prêt doit être payé.

  Par exemple, si vous effectuez des paiements annuels sur un prêt sur 5 ans, approvisionnez 5 pour NPER. Si vous effectuez des paiements mensuels sur le même prêt, multipliez le nombre d'années par 12 et utilisez 5 * 12 ou 60 pour NPER.

• PV (requis) - La valeur actuelle, c'est-à-dire le montant total que tous les paiements futurs valent maintenant. En cas de prêt, c'est simplement le montant d'origine emprunté.
• FV (Facultatif) - La valeur future ou le solde de trésorerie que vous souhaitez avoir après le dernier paiement. S'il est omis, la valeur future du prêt est supposée nul (0).
• Type (facultatif) - Spécifie lorsque les paiements sont dus:
  • 0 ou omis - les paiements sont dus à la fin de chaque période.
  • 1 - Les paiements sont dus au début de chaque période.

Par exemple, si vous empruntez 100 000 $ pour 5 ans avec un taux d'intérêt annuel de 7%, la formule suivante calculera le paiement annuel :

=PMT(7%, 5, 100000)

Pour trouver le paiement mensuel du même prêt, utilisez cette formule:

=PMT(7%/12, 5*12, 100000)

Ou, vous pouvez saisir les composants connus d'un prêt dans des cellules séparées et référencer les cellules de votre formule PMT. Avec le taux d'intérêt en B1, no. Des années en B2 et le montant du prêt en B3, la formule est aussi simple que celle-ci:

=PMT(B1, B2, B3)

N'oubliez pas que le paiement est retourné comme un nombre négatif car ce montant sera débité (soustrait) de votre compte bancaire.

Par défaut, Excel affiche le résultat dans le format de devise , arrondi à 2 décimales, mis en évidence en rouge et enfermé entre parenthèses, comme indiqué dans la partie gauche de l'image ci-dessous. L'image de droite montre le même résultat au format général .

Si vous souhaitez avoir le paiement en tant que nombre positif , mettez un signe moins avant toute la formule PMT ou l'argument PV (montant du prêt):

=-PMT(B1, B2, B3)

ou

=PMT(B1, B2, -B3)

Conseil. Pour calculer le montant total payé pour le prêt, multipliez la valeur PMT retournée par le nombre de périodes (valeur NPER). Dans notre cas, nous utiliserions cette équation: 24 389.07 * 5 et constatrions que le montant total équivaut à 121 945,35 $.

Comment utiliser la fonction PMT dans Excel - Exemples de formule

Vous trouverez ci-dessous quelques autres exemples d'une formule Excel PMT qui montrent comment calculer différents paiements périodiques pour un prêt automobile, un prêt immobilier, un prêt hypothécaire, etc.

Forme complète de la fonction PMT dans Excel

Pour la plupart, vous pouvez omettre les deux derniers arguments de vos formules PMT (comme nous l'avons fait dans les exemples ci-dessus) car leurs valeurs par défaut couvrent les cas d'utilisation les plus typiques:

• FV OMITS - implique un solde zéro après le dernier paiement.
• Type omis - Les paiements sont dus à la fin de chaque période.

Si vos conditions de prêt sont différentes des valeurs par défaut, utilisez la forme complète de la formule PMT.

Par exemple, calculons le montant des paiements annuels en fonction de ces cellules d'entrée:

• B1 - taux d'intérêt annuel
• B2 - terme de prêt (en années)
• B3 - Montant du prêt
• B4 - valeur future (solde après le dernier paiement)
• B5 - Type de rente:
  • 0 (rente régulière) - Les paiements sont effectués à la fin de chaque année.
  • 1 (rente due) - Les paiements sont effectués au début de la période, par exemple, les paiements de loyer ou de location.

Fournissez ces références à votre formule Excel PMT:

=PMT(B1, B2, B3, B4, B5)

Et vous aurez ce résultat:

Calculer les paiements hebdomadaires, mensuels, trimestriels et semestriels

Selon la fréquence de paiement, vous devez utiliser les calculs suivants pour les arguments de taux et de NPER :

• Pour le taux , divisez le taux d'intérêt annuel par le nombre de paiements par an (qui est jugé égal au nombre de périodes de composition).
• Pour NPER , multipliez le nombre d'années par le nombre de paiements par an.

Le tableau ci-dessous fournit les détails:

Fréquence de paiement	Taux	NPER
Hebdomadaire	Taux d'intérêt annuel / 52	années * 52
Mensuel	Taux d'intérêt annuel / 12	années * 12
Trimestriel	Taux d'intérêt annuel / 4	années * 4
Semestriel	Taux d'intérêt annuel / 2	années * 2

Par exemple, pour trouver le montant d'un paiement périodique sur un prêt de 5 000 $ avec un taux d'intérêt annuel de 8% et une durée de 3 ans, utilisez l'une des formules ci-dessous.

Paiement hebdomadaire :

=PMT(8%/52, 3*52, 5000)

Paiement mensuel :

=PMT(8%/12, 3*12, 5000)

Paiement trimestriel :

=PMT(8%/4, 3*4, 5000)

Paiement semi-annuel :

=PMT(8%/2, 3*2, 5000)

Dans tous les cas, le solde après le dernier paiement est supposé être de 0 $, et les paiements sont dus à la fin de chaque période.

La capture d'écran ci-dessous montre les résultats de ces formules:

Comment faire une calculatrice PMT dans Excel

Avant d'aller de l'avant et d'emprunter de l'argent, il va raisonner de comparer différentes conditions de prêt pour découvrir les options qui vous conviennent le plus. Pour cela, créons notre propre calculatrice de paiement de prêt Excel.

1. Pour commencer, entrez le montant du prêt, le taux d'intérêt et la durée du prêt dans les cellules distinctes (B3, B4, B5, respectivement).
2. Pour pouvoir choisir différentes périodes et spécifier lorsque les paiements sont dus, créez des listes déroulantes avec les options prédéfinies suivantes (B6 et B7):

   

3. Configurez les tables de recherche pour les périodes (E2: F6) et les paiements sont dus (E8: F9) comme Show dans la capture d'écran ci-dessous. Il est important que les étiquettes de texte dans les tables de recherche correspondent exactement aux éléments de la liste déroulante correspondante. Dans les cellules à côté des listes déroulantes, entrez les formules IFRERROR VLOOKUP suivantes qui tireront le numéro de la table de recherche correspondant à l'élément sélectionné dans la liste déroulante.

   Formule pour les périodes (C6):

   =IFERROR(VLOOKUP(B6, E2:F6, 2, 0), "")

   La formule pour les paiements est due (C7):

   =IFERROR(VLOOKUP(B7, E8:F9, 2, 0), "")

   

4. Écrivez une formule PMT pour calculer le paiement périodique en fonction de vos cellules. Dans notre cas, la formule se déroule comme suit:

   =IFERROR(-PMT(B4/C6, B5*C6, B3, 0, C7), "")

   Veuillez remarquer les choses suivantes:

   • L'argument FV (0) est codé en dur dans la formule car nous voulons toujours un solde zéro après le dernier paiement. Dans le cas où vous souhaitez permettre à vos utilisateurs de saisir une valeur future, allouer une cellule d'entrée distincte pour l'argument FV.
   • La fonction PMT est précédée du signe moins pour afficher le résultat en tant que nombre positif .
   • La fonction PMT est enveloppée dans IFERROR pour masquer les erreurs lorsque certaines valeurs d'entrée ne sont pas définies.

   La formule ci-dessus va en B9. Et dans la cellule voisine (A9), nous affichons une étiquette correspondant à la période sélectionnée (B6). Pour cela, concaténez simplement la valeur en B6 et le texte souhaité:

   =B6&" Payment"

   

5. Enfin, vous pouvez masquer les tables de recherche de la vue, ajouter quelques touches de formatage de finition, et votre calculatrice Excel PMT est bonne à aller:

   

La fonction Excel PMT ne fonctionne pas

Si votre formule Excel PMT ne fonctionne pas ou ne produit pas de mauvais résultats, il est probable que ce soit pour les raisons suivantes:

• Un #num! Une erreur peut se produire si l'argument de taux est un nombre négatif ou NPER est égal à 0.
• Un #value! Une erreur se produit si un ou plusieurs arguments sont des valeurs de texte.
• Si le résultat d'une formule PMT est beaucoup plus élevé ou inférieur aux prévisions, assurez-vous que vous êtes conforme aux unités fournies pour le taux et les arguments NPER , ce qui signifie que vous avez correctement converti le taux d'intérêt annuel au taux de la période et au nombre d'années à des semaines, des mois ou des trimestres comme indiqué dans cet exemple.

C'est ainsi que vous calculez la fonction PMT dans Excel. Pour examiner de plus près les formules discutées dans ce tutoriel, vous êtes invités à télécharger notre exemple de classeur ci-dessous. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Formule PMT dans Excel - Exemples (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!