La fonction PMT dans Excel 2013 - les nuls

Excel 2013 PMT fonction dans le menu déroulant bouton financier sur l'onglet Formules du ruban calcule le paiement périodique pour une annuité, 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 communes, taux 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 que les futurs paiements valent actuellement, fv est la valeur future ou en espèces solde que vous voulez après le dernier paiement (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 tapez est la valeur 0 pour les paiements effectués au 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 facultatif type , Excel suppose que le paiement est effectué à la fin de la période.)

La fonction PMT est souvent utilisée pour calculer le paiement pour les prêts hypothécaires qui ont un 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êts pour une fourchette de taux d'intérêt (de 2.75% à 4.00%) et les principaux (150.000 $ à 159.000 $).

La table 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 de paiement des prêts.

Pour vous faire une idée de la facilité de création de ce type de table de prêt avec la fonction PMT, procédez comme suit pour la créer dans une nouvelle feuille de calcul:

1. 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 Terme (en années) dans la cellule A4.

2. 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 le tableau Paiements de prêts.

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

   En créant une formule de liaison qui fait remonter 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ète immédiatement tout changement effectué dans la cellule B3.

4. Positionnez le pointeur de cellule dans la cellule C6, puis construisez la formule = B6 +. 25%.

   En ajoutant 1/4 de% au taux d'intérêt à la valeur de B6 avec la formule = B6 +. 25% en 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 pourcentage supérieur à toute valeur de taux d'intérêt entrée dans la cellule B6.

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

6. Positionnez le pointeur de cellule dans la cellule A7, puis construisez la formule = B2.

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

7. Positionnez le pointeur de cellule dans A8, 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 de remplissage automatique de sorte que la valeur principale dans A8 sera toujours supérieure de 1 000 $ à toute valeur placée dans la cellule A7.

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

9. 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 la fonction vers la droite afin qu'aucune partie de celle-ci ne masque les données dans les colonnes A et B de votre feuille de calcul avant de procéder aux étapes suivantes pour renseigner les arguments.

10. 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 référence de cellule relative B6 en référence mixte B $ 6 pour qu'Excel ajuste pas le numéro de ligne lorsque vous copiez la formule PMT sur chaque ligne de la table, mais ajustez la lettre de la colonne lorsque vous copiez la formule dans ses colonnes.

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

11. 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 sur F4 une fois pour convertir référence B4 à la 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 de la table.

    Comme le terme en B3 (qui est ensuite reporté à la cellule B6) est une période annuelle , mais que vous voulez connaître le paiement du prêt mensuel , vous devez convertir périodes annuelles à des périodes mensuelles en multipliant la valeur dans la cellule B4 par 12.

12. Cliquez sur la zone de texte Pv, cliquez sur A7 pour insérer cette référence 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, relative à la ligne).

    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 colonne lorsque vous copiez la formule PMT sur chaque colonne de la table, mais ajuste le numéro de ligne lorsque vous copiez la formule vers le bas à travers ses rangées.

13. 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 originale, puis à remplir l'ensemble du tableau Paiements de prêt.

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

    Une fois que vous avez copié la formule PMT originale dans la cellule B16, vous êtes prêt à la copier vers la droite sur G16.

15. 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 é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 changer le début ou taux d'intérêt, ainsi que le terme pour voir ce que les paiements seraient dans divers autres scénarios. Vous pouvez également activer le recalcul manuel pour pouvoir contrôler le recalcul de la table Paiements de prêt.