La fonction PMT dans Excel 2013

La fonction Excel 2013 PMT du menu déroulant du bouton Financier de l'onglet Formules du ruban calcule le paiement périodique d'une rente, en supposant un flux de paiements égaux et un taux d'intérêt constant. La fonction PMT utilise la syntaxe suivante :

=PMT(taux,nper,pv,[fv],[type])

Comme pour les autres fonctions financières courantes, rate est le taux d'intérêt par période, nper est le nombre de périodes, pv est la valeur actuelle ou le montant actuel des paiements futurs, fv est la valeur future ou le solde de trésorerie que vous voulez après le dernier paiement est effectué (Excel suppose une valeur future de zéro lorsque vous omettez cet argument facultatif comme vous le feriez lors du calcul des paiements de prêt), et le type est la valeur 0 pour les paiements effectués à la fin de la période ou la valeur 1 pour les paiements effectués au début de la période.

(Si vous omettez l' argument de type facultatif , Excel suppose que le paiement est effectué à la fin de la période.)

La fonction PMT est souvent utilisée pour calculer le paiement des prêts hypothécaires à taux d'intérêt fixe. La figure vous montre un exemple de feuille de calcul contenant un tableau utilisant la fonction PMT pour calculer les paiements de prêt pour une plage de taux d'intérêt (de 2,75 % à 4,00 %) et les principaux (150 000 $ à 159 000 $).

Le tableau utilise le principal initial que vous entrez dans la cellule B2, le copie dans la cellule A7, puis l'augmente de 1 000 $ dans la plage A8:A16. La table utilise le taux d'intérêt initial que vous entrez dans la cellule B3, copie dans la cellule B6, puis augmente ce taux initial de 1/4 de pour cent dans la plage C6:G6. Le terme en années dans la cellule B4 est un facteur constant qui est utilisé dans l'ensemble du tableau des paiements de prêt.

Pour avoir une idée de la facilité de création de ce type de tableau de paiement de prêt avec la fonction PMT, suivez ces étapes pour le créer dans une nouvelle feuille de calcul :

Entrez les titres Paiements de prêt dans la cellule A1, Principal dans la cellule A2, Taux d'intérêt dans la cellule A3 et Durée (en années) dans la cellule A4.

Entrez 150 000 $ dans la cellule B2, entrez 2,75 % dans la cellule B3 et entrez 30 dans la cellule B4.

Ce sont les valeurs de départ avec lesquelles vous construisez la table des paiements de prêt.

Positionnez le pointeur de cellule dans B6 puis construisez la formule =B3.

En créant une formule de liaison qui avance la valeur du taux d'intérêt de départ dans B3 avec la formule, vous vous assurez que la valeur du taux d'intérêt dans B6 reflétera immédiatement tout changement que vous apportez dans la cellule B3.

Positionnez le pointeur de cellule dans la cellule C6, puis créez la formule =B6+.25%.

En ajoutant 1/4 de pour cent au taux d'intérêt à la valeur dans B6 avec la formule =B6+.25% dans C6 plutôt que de créer une série avec la poignée AutoFill, vous vous assurez que la valeur du taux d'intérêt dans la cellule C6 sera toujours 1/4 de pour cent de plus que toute valeur de taux d'intérêt entrée dans la cellule B6.

Faites glisser la poignée de remplissage dans la cellule C6 pour étendre la sélection vers la droite jusqu'à la cellule G6, puis relâchez le bouton de la souris.

Positionnez le pointeur de cellule dans la cellule A7, puis créez la formule =B2.

Encore une fois, en utilisant la formule =B2 pour faire avancer le principal initial dans la cellule A7, vous vous assurez que la cellule A7 a toujours la même valeur que la cellule B2.

Positionnez le pointeur de cellule dans A8 actif puis construisez la formule =A7+1000.

Ici aussi, vous utilisez la formule =A7+1000 plutôt que de créer une série avec la fonction Remplissage automatique afin que la valeur principale dans A8 soit toujours supérieure de 1 000 $ à toute valeur placée dans la cellule A7.

Faites glisser la poignée de remplissage de la cellule A8 vers le bas jusqu'à ce que vous étendiez la sélection à la cellule A16, puis relâchez le bouton de la souris.

Dans la cellule B7, cliquez sur le bouton Insérer une fonction dans la barre de formule, sélectionnez Financier dans la liste déroulante Ou sélectionnez une catégorie, puis double-cliquez sur la fonction PMT dans la zone de liste Sélectionner une fonction.

La boîte de dialogue Arguments de fonction qui s'ouvre vous permet de spécifier les arguments rate, nper et pv . Assurez-vous de déplacer la boîte de dialogue Arguments de fonction vers la droite afin qu'aucune partie de celle-ci ne masque les données des colonnes A et B de votre feuille de calcul avant de procéder aux étapes suivantes pour remplir les arguments.

Cliquez sur la cellule B6 pour insérer B6 dans la zone de texte Taux, puis appuyez deux fois sur F4 pour convertir la référence relative B6 en référence mixte B$6 (colonne relative, ligne absolue) avant de taper /12.

Vous convertissez la B6 de référence de la cellule par rapport à la référence mixte B 6 $ pour que Excel ne pas régler le numéro de la ligne lorsque vous copiez la formule VPM en bas de chaque ligne de la table, mais il ne régler la lettre de la colonne lorsque vous copiez la formule dans ses colonnes .

Étant donné que le taux d'intérêt initial entré dans B3 (puis reporté dans la cellule B6) est un taux d'intérêt annuel , mais que vous souhaitez connaître le paiement mensuel du prêt, vous devez convertir le taux annuel en un taux mensuel en divisant la valeur dans la cellule B6 par 12.

Cliquez sur la zone de texte Nper, cliquez sur la cellule B4 pour insérer cette référence de cellule dans cette zone de texte, puis appuyez une fois sur F4 pour convertir la référence relative B4 en référence absolue $B$4 avant de taper *12.

Vous devez convertir la référence de cellule relative B4 en référence absolue $B$4 afin qu'Excel n'ajuste ni le numéro de ligne ni la lettre de colonne lorsque vous copiez la formule PMT dans les lignes et dans les colonnes du tableau.

Étant donné que le terme dans B3 (qui est ensuite avancé dans la cellule B6) est une période annuelle , mais que vous souhaitez connaître le paiement mensuel du prêt, vous devez convertir les périodes annuelles en périodes mensuelles en multipliant la valeur de la cellule B4 par 12.

Cliquez sur la zone de texte Pv, cliquez sur A7 pour insérer cette référence de cellule dans cette zone de texte, puis appuyez trois fois sur F4 pour convertir la référence relative A7 en référence mixte $A7 (colonne absolue, ligne relative).

Vous devez convertir la référence de cellule relative A7 en référence mixte $ A7 afin qu'Excel n'ajuste pas la lettre de la colonne lorsque vous copiez la formule PMT dans chaque colonne du tableau, mais ajuste le numéro de ligne lorsque vous copiez la formule à travers ses rangées.

Cliquez sur OK pour insérer la formule =PMT(B$6/12,$B$4*12,$A7) dans la cellule B7.

Vous êtes maintenant prêt à copier cette formule PMT d'origine vers le bas, puis sur pour remplir l'intégralité du tableau des paiements de prêt.

Faites glisser la poignée de remplissage de la cellule B7 vers le bas jusqu'à ce que vous étendiez la plage de remplissage à la cellule B16, puis relâchez le bouton de la souris.

Après avoir copié la formule PMT d'origine dans la cellule B16, vous êtes prêt à la copier à droite dans G16.

Faites glisser la poignée de remplissage vers la droite jusqu'à ce que vous étendiez la plage de remplissage B7:B16 à la cellule G16, puis relâchez le bouton de la souris.

Après avoir copié la formule d'origine avec la poignée de remplissage, veillez à élargir suffisamment les colonnes B à G pour afficher leurs résultats. (Vous pouvez le faire en une seule étape en faisant glisser les en-têtes de ces colonnes, puis en double-cliquant sur la bordure droite de la colonne G.)

Après avoir créé une table de prêt comme celle-ci, vous pouvez ensuite modifier le principal ou le taux d'intérêt de départ, ainsi que la durée pour voir quels seraient les paiements dans divers autres scénarios. Vous pouvez également activer le recalcul manuel afin de pouvoir contrôler à quel moment la table des paiements de prêt est recalculée.