Excel Nper Fonction avec des exemples de formule

Dans ce tutoriel, vous apprendrez: que signifie NPER dans Excel? Comment utiliser la fonction NPER pour calculer le nombre de périodes nécessaires pour rembourser un prêt ou le nombre d'investissements nécessaires pour gagner le montant cible? Et plus!

Lors de la création de fonds d'entreprise, les analystes financiers souhaitent souvent savoir combien de temps il faudra pour atteindre le corpus souhaité. Lorsque vous demandez un prêt, vous voudrez peut-être savoir combien de paiements sont nécessaires pour le rembourser en totalité. Pour de telles tâches, Excel fournit la fonction NPER, qui signifie "Nombre de périodes".

• Fonction Excel Nper
• Formule de base NPER à Excel
• Comment calculer les NPER dans Excel - Exemples de formule
  • Trouver le nombre de paiements périodiques
  • Calculer NPER en fonction des valeurs actuelles et futures
  • Une formule universelle pour trouver NPER dans Excel
• Obtenez NPER comme un entier
• Excel Nper Fonction ne fonctionne pas

Fonction Excel Nper

NPER est une fonction financière Excel qui calcule le nombre de périodes de paiement pour un prêt ou un investissement en fonction des paiements périodiques égaux et d'un taux d'intérêt constant.

La fonction est disponible dans toutes les versions Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 et Excel 2007.

La syntaxe est la suivante:

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

Où:

• Taux (requis) - Le taux d'intérêt par période. Si vous payez une fois par an, spécifiez un taux d'intérêt annuel; Si vous payez chaque mois, fournissez un taux d'intérêt mensuel, etc.
• PMT (requis) - Le montant payé à chaque période. Normalement, il comprend le principal et les intérêts, mais pas de taxes ni de frais.
• PV (requis) - La valeur actuelle du prêt ou de l'investissement, c'est-à-dire combien vaut une série de flux de trésorerie futurs en ce moment.
• FV (Facultatif) - La valeur future du prêt ou de l'investissement après le dernier paiement. S'il est omis, par défaut est 0.
• Type (facultatif) - Spécifie lorsque les paiements sont dus:
  • 0 ou omis (par défaut) - à la fin d'une période comptable
  • 1 - Au début d'une période comptable

4 choses que vous devez savoir sur la fonction NPER

Pour utiliser efficacement la fonction Excel NPER dans vos feuilles de travail et éviter les erreurs courantes, n'oubliez pas les choses suivantes:

1. Utilisez des nombres positifs pour les entrées (tous les montants que vous recevez) et des nombres négatifs pour les sorties (tous les montants que vous payez).
2. Si la valeur actuelle ( PV ) est nulle ou omise, la valeur future ( FV ) doit être incluse.
3. L'argument de taux peut être saisi en pourcentage ou nombre décimal, par exemple 5% ou 0,05.
4. Assurez-vous de fournir le taux correspondant aux périodes . Par exemple, si un prêt doit être payé mensuellement à un taux d'intérêt annuel de 8%, utilisez 8% / 12 ou 0,08 / 12 pour l'argument des taux .

Formule de base NPER à Excel

Connaissant la théorie, construisons une formule NPER sous sa forme la plus simple pour obtenir le nombre de périodes pour effacer le prêt en fonction des données suivantes:

• Taux d'intérêt annuel ( taux ): C2
• Paiement annuel ( PMT ): C3
• Montant du prêt ( PV ): C4

En rassemblant les arguments, nous obtenons cette formule simple:

=NPER(C2, C3, C4)

Les arguments facultatifs [ FV ] et [ type ] sont omis car la valeur future n'est pas pertinente dans ce cas, et les paiements sont dus à la fin de l'année, qui est le type par défaut.

En supposant que le prêt de 10 000 $ vous est accordé à un taux d'intérêt annuel de 8%, et vous paierez 3 800 $ à la banque chaque année, la formule montre que vous devrez effectuer 3 paiements annuels pour rembourser le montant du prêt.

Veuillez remarquer que PMT est un nombre négatif car il s'agit de l'argent sortant.

Si vous entrez un paiement périodique en tant que nombre positif , mettez le signe moins avant l'argument PMT directement dans la formule:

=NPER(C2, -C3, C4)

Comment utiliser la fonction NPER dans Excel - Exemples de formule

Ci-dessous, vous trouverez quelques autres exemples de formule Excel NPER qui montrent comment calculer le nombre de périodes de paiement pour différents scénarios.

Calculez le nombre de paiements périodiques pour un prêt

La plupart des prêts hypothécaires et autres prêts à long terme sont payés en versements mensuels. Certains sont payés trimestriellement ou semestriels. La question est - comment trouvez-vous le nombre de paiements périodiques nécessaires pour rembourser le montant total du prêt?

La clé est de convertir un taux d'intérêt annuel à un taux périodique . Pour cela, divisez simplement le taux annuel par le nombre de périodes par an:

Paiements mensuels: taux = taux d'intérêt annuel / 12

Paiements trimestriels: taux = taux d'intérêt annuel / 4

Paiements semestriels: taux = taux d'intérêt annuel / 2

Supposons que vous ayez emprunté 10 000 $ à un taux d'intérêt annuel de 8%, avec un paiement mensuel de 500 $.

Nous commençons par entrer dans les données dans les cellules correspondantes:

• Taux d'intérêt annuel (C2): 8%
• Paiement mensuel (C3): -500
• Montant du prêt (C4): 10 000

Pour obtenir une valeur correcte pour l'argument des taux , divisez C2 par 12. Et vous trouverez la formule suivante pour calculer le nombre de paiements mensuels sur le prêt:

=NPER(C2/12, C3, C4)

Le résultat montre qu'il faudra 22 mois (ou 1 an et 10 mois) pour rembourser le prêt:

Si vous effectuez des paiements trimestriels , utilisez C2 / 4 pour le taux pour forcer NPER à retourner les périodes en trimestres:

=NPER(C2/4, C3, C4)

Alternativement, vous pouvez saisir le nombre de périodes par an dans une cellule séparée (C5) et diviser le taux d'intérêt annuel (C2) par cette cellule:

=NPER(C2/C5, C3, C4)

Calculer NPER en fonction des valeurs actuelles et futures

Lorsque vous investissez ou économisez de l'argent, vous savez généralement combien vous souhaitez gagner à la fin du trimestre. Dans ce cas, vous pouvez inclure la valeur future ( FV ) souhaitée dans la formule.

Supposons que vous souhaitiez investir 1 000 $ à un taux d'intérêt annuel de 3% et apporter une contribution supplémentaire de 500 $ à la fin de chaque mois. Votre objectif est d'atteindre 10 000 $.

Pour trouver le nombre d'investissements mensuels, saisissez les données suivantes dans les cellules prédéfinies:

• Taux d'intérêt annuel (C2): 3%
• Paiement mensuel (C3): -500
• Valeur actuelle (C4): -1 000
• Valeur future (C5): 10 000
• Périodes par an (C6): 12

Entrez cette formule en C8:

=NPER(C2/C6, C3, C4, C5)

Et vous verrez qu'il faut 18 investissements mensuels pour atteindre le montant cible:

Veuillez remarquer que:

• Pour le taux , nous divisons un taux d'intérêt annuel par le nombre de périodes par an (mois dans notre cas) pour que la fonction NPER ait des périodes de retour en mois.
• PMT et PV sont des nombres négatifs car ils représentent une sortie.
• On suppose que les paiements sont dus à la fin de la période, donc l'argument de type est omis.

Une formule universelle pour trouver NPER dans Excel

Gérer les cas spécifiques était facile, n'est-ce pas? Que diriez-vous de construire une formule générale qui fait un travail parfait de calcul de la NPER dans Excel, que vous empruntiez ou économisez et peu importe la fréquence à laquelle vous effectuez des paiements? Pour cela, nous utiliserons la fonction NPER sous sa forme complète et allouons les cellules à tous les arguments, y compris ceux en option. Dans notre cas, ce sera:

• Taux d'intérêt annuel ( taux ) - C2
• Paiement périodique ( PMT ) - C3
• Valeur actuelle ( PV ) - C4
• Valeur future ( FV ) - C5
• Lorsque les paiements sont dus ( type ) - C6
• Périodes par an - C7

Disons que vous souhaitez investir 10 000 $ initial à un taux d'intérêt annuel de 5%, puis effectuer un paiement supplémentaire de 1 000 $ au début de chaque trimestre, visant à atteindre 50 000 $. Combien de paiements trimestriels de vous faire?

Pour obtenir le taux périodique correct, nous divisons le taux d'intérêt annuel (C2) par le nombre de périodes par an (C7). Pour les autres arguments, fournissez simplement les références cellulaires correspondantes:

=NPER(C2/C7, C3, C4, C5, C6)

Conseils:

• Pour laisser moins de place pour les erreurs, vous pouvez créer une liste déroulante qui ne permet que 0 et 1 des valeurs pour l'argument de type .
• Normalement, lorsque vous empruntez de l'argent, vous effectuez le paiement à la fin d'une période pour laisser les intérêts s'accumuler ( le type est réglé sur 0). Lorsque vous essayez d'économiser, vous êtes intéressé à obtenir l'argent dès que possible afin que vous accumulerez les intérêts, c'est pourquoi vous payez au début d'une période ( le type est défini sur 1).

Comment obtenir la fonction NPER pour renvoyer un numéro entier

Dans les exemples ci-dessus, le nombre calculé de périodes est affiché comme un entier car la cellule de formule est formatée pour ne montrer aucune décimale. Si vous formatez la cellule pour montrer une ou plusieurs décimales, vous verrez que le résultat de NPER est en fait un numéro décimal:

Dans la vraie vie, cependant, il ne peut y avoir de périodes décimales. Dans de telles situations, le dernier paiement sera juste inférieur à celui des périodes précédentes.

Pour obtenir le nombre correct de périodes requises, vous pouvez envelopper votre formule NPER dans la fonction Roundup qui arrondait la valeur vers le haut à un nombre spécifié de chiffres. Puisque nous voulons que le résultat soit un entier, nous définissons l'argument NUM_DIGITS sur zéro:

Roundup (nper (), 0)

Avec le taux d'intérêt annuel en C2, le montant du paiement en C3 et le montant du prêt en C4, la formule prend ce formulaire:

=ROUNDUP(NPER(C2, C3, C4), 0)

Comme vous le voyez, le nombre complet de périodes est de 4, et non 3 comme vous le pensez à regarder la capture d'écran ci-dessus. Vous n'aurez qu'à payer un très petit montant au cours de la 4 ^e année.

Excel Nper Fonction ne fonctionne pas

Si votre formule NPER renvoie une erreur ou un mauvais résultat, ce sera probablement l'un des éléments suivants.

#Num! erreur

Se produit si la valeur future spécifiée ( FV ) ne peut jamais être atteinte avec un taux d'intérêt ( taux ) et un paiement ( PMT ) donné. Pour obtenir un résultat valide, essayez d'augmenter un taux d'intérêt périodique et / ou un montant de paiement.

#VALEUR! erreur

Se produit si un argument n'est pas numerique. Pour corriger l'erreur, assurez-vous qu'aucun nombre utilisé dans une formule n'est formaté sous forme de texte. Pour plus d'informations, veuillez voir comment convertir le texte en numéro.

Le résultat de la fonction NPER est un nombre négatif

Habituellement, ce problème se produit lorsque les paiements sortants sont représentés par des nombres positifs. Par exemple, lors du calcul des périodes de paiement pour un prêt, fournissez le montant du prêt ( PV ) comme nombre positif et le paiement périodique ( PMT ) comme nombre négatif. Lors de l'investissement ou de l'économie d'argent, l'investissement initial ( PV ) et le paiement périodique ( PMT ) devraient à la fois être négatifs et la valeur future ( FV ) positive.

Voilà comment trouver NPER dans Excel. 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

Exemples de formule NPER dans Excel (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!