Calcul de la rente Pay in Excel

Avant de prendre le prêt, ce serait bien de calculer tous les paiements. Il sauvera l'emprunteur à l'avenir de divers problèmes et déceptions inattendus lorsqu'il s'avère que le trop-payé est trop grand. L'aide sur ce calcul peut les outils de programme Excel. Découvrons comment calculer les paiements de rente sur le prêt dans ce programme.

Calcul du paiement

Tout d'abord, il faut dire qu'il y a deux types de paiements de crédit:

• Différencié;
• Rente.

Avec un schéma différencié, le client apporte une part mensuelle égale des paiements sur le corps d'un prêt plus des paiements aux intérêts. L'ampleur des paiements d'intérêts Chaque mois diminue, car le corps du prêt est réduit à partir desquels ils sont calculés. Ainsi, le paiement mensuel global est également réduit.

L'Ann Tante Scheme utilise une approche légèrement différente. Le client fait une seule quantité de paiement total mensuel, qui consiste en paiements sur le corps d'un prêt et de paiement d'intérêts. Initialement, les contributions d'intérêts sont numérotées sur l'ensemble du montant du prêt, mais à mesure que le corps diminue, l'intérêt est réduit et des intérêts complets. Mais le montant total du paiement reste inchangé en raison d'une augmentation mensuelle du montant des paiements par le corps du prêt. Ainsi, au fil du temps, la proportion d'intérêt pour le paiement mensuel total diminue et la proportion du corps augmente. Dans le même temps, le paiement mensuel général lui-même ne change pas tout au long de la durée de crédit.

Juste sur le calcul du paiement de la rente, nous nous arrêterons. En particulier, cela est pertinent, car la plupart des banques utilisent ce schéma particulier. Il est pratique pour les clients, car dans ce cas, le montant total de paiement ne change pas, restant fixe. Les clients savent toujours combien vous devez payer.

Étape 1: calcul de la contribution mensuelle

Pour calculer une contribution mensuelle lors de l'utilisation d'un circuit de rente dans Excele, il existe une fonction spéciale - PPT. Il fait référence à la catégorie des opérateurs financiers. La formule de cette fonctionnalité est la suivante:

= Ppt (taux; kper; ps; bs; type)

Comme nous le voyons, la fonction spécifiée comporte un nombre assez important d'arguments. Certes, les deux derniers d'entre eux ne sont pas obligatoires.

L'argument "tarif" indique un taux de pourcentage pour une période spécifique. Si, par exemple, un taux annuel est utilisé, mais le paiement du prêt est effectué mensuellement, le taux annuel doit alors être divisé en 12 et le résultat est utilisé comme argument. Si un type de paiement trimestriel est appliqué, alors dans ce cas, le pari annuel doit être divisé en 4, etc.

« CPER » désigne le nombre total de périodes de paiements de prêt. Autrement dit, si le prêt est pris pour un an avec le paiement mensuel, le nombre de périodes est considéré comme 12, si deux ans, le nombre de périodes - 24. Si le prêt est pris pendant deux ans avec paiement trimestriel, puis le nombre de périodes est de 8.

« PS » indique la valeur actuelle au moment. En parlant avec des mots simples, c'est le montant total du prêt au début de prêt, qui est, le montant que vous empruntez, à l'exclusion des intérêts et autres paiements supplémentaires.

« BS » est un coût futur. Cette valeur sera un corps de prêt au moment de la conclusion de l'accord de prêt. Dans la plupart des cas, cet argument est « 0 », puisque l'emprunteur à la fin de la période de crédit devrait pleinement régler avec le prêteur. L'argument spécifié n'est pas obligatoire. Par conséquent, si elle est descendue, il est considéré comme nul.

L'argument « Type » détermine le temps de calcul: à la fin ou au début de la période. Dans le premier cas, il prend la valeur « 0 », et dans le second - « 1 ». La plupart des institutions bancaires utilisent exactement l'option avec paiement à la fin de la période. Cet argument est également en option, et si elle est omise, on croit qu'il est égal à zéro.

Maintenant, il est temps de passer à un exemple spécifique de calcul d'une contribution mensuelle en utilisant la fonction PL. Pour calculer, nous utilisons une table avec les données source, où le taux d'intérêt sur le prêt (12%) est indiqué, la valeur du prêt (500.000 roubles) et la durée du prêt (24 mois). En même temps, le paiement est effectué chaque mois à la fin de chaque période.

1. Sélectionnez l'élément sur la feuille dans laquelle sera affiché le résultat de résultat, et cliquez sur l'icône « Insérer une fonction », placé près de la ligne de formule.



2. La fenêtre de l'assistant de fenêtre est lancé. Dans la catégorie « financière » attribuer le nom « PLT » et cliquez sur le bouton « OK ».



3. Après cela, ouvre la fenêtre des arguments de l'opérateur PL.

   Dans le champ « Rate », vous devez entrer la valeur en pourcentage pour la période. Cela peut être fait manuellement, juste mettre un pourcentage, mais il est indiqué dans une cellule séparée sur la feuille, nous allons donner un lien. Installez le curseur dans le champ, puis cliquez sur la case correspondante. Mais, comme nous nous souvenons, nous avons le taux d'intérêt annuel dans notre tableau, et la période de paiement est égal au mois. Par conséquent, nous divisons le pari annuel, et plutôt un lien vers la cellule dans laquelle elle est contenue par le numéro 12, correspondant au nombre de mois de l'année. Division exécuté directement dans le champ de la fenêtre Argument.

   Dans le domaine CPER, le prêt est réglé. Il est égal à 24 mois. Vous pouvez appliquer dans le numéro 24 champ manuellement, mais nous, comme dans le cas précédent, spécifiez un lien vers l'emplacement de cet indicateur dans la table source.

   Dans le champ « PS » indique la valeur du prêt initial. Il est égal à 500.000 roubles. Comme dans les cas précédents, nous spécifions un lien vers l'élément de feuille, qui contient cet indicateur.

   Dans le « BS » champ indique l'ampleur du prêt, après son paiement intégral. Comme vous le savez, cette valeur est presque toujours zéro. Installer dans ce champ le numéro « 0 ». Bien que peut généralement être omis cet argument.

   Dans le champ « Type », nous précisons au début ou à la fin du mois de paiement est effectué. Nous, comme dans la plupart des cas, il est produit à la fin du mois. Par conséquent, nous avons mis le numéro « 0 ». Comme dans le cas de l'argument précédent, il est possible de saisir quoi que ce soit dans ce domaine, le programme par défaut suppose qu'il est égal à zéro égal.

   Une fois que toutes les données sont entrées, appuyez sur la touche "OK".



4. Après cela, dans la cellule que nous avons prévu dans le premier paragraphe de ce manuel, le résultat du calcul est affiché. Comme vous pouvez le voir, l'importance du paiement mensuel général sur le prêt est 23536.74 roubles. Laissez-vous confondez pas le signe « - » avant ce montant. Donc, l'exil indique que c'est le flux d'argent, qui est une perte.



5. Pour calculer le montant total de paiement pour toute la durée du prêt, en tenant compte du remboursement du corps d'un prêt et des intérêts mensuels, plutôt multiplier le montant du paiement mensuel (23536.74 roubles) pour le nombre de mois (24 mois ). Comme vous pouvez le voir, le montant total des paiements pour toute la période de prêt dans notre cas était 564881.67 roubles.



6. Maintenant, vous pouvez calculer le montant de trop-perçu de prêt. Pour ce faire, il est nécessaire de retirer du montant total des paiements sur le prêt, y compris les intérêts et le corps de prêt, le montant initial réclamé. Mais nous nous rappelons que la première de ces valeurs déjà avec le signe « - ». Par conséquent, en particulier, notre cas, il se avère qu'ils doivent être pliés. Comme on le voit, le trop-perçu total du prêt sur toute la période était 64881.67 roubles.

Leçon: Maître des fonctions dans Excel

Étape 2: Détails du paiement

Et maintenant, avec l'aide d'autres opérateurs Excel, nous faisons un détail mensuel des paiements pour voir combien dans un mois spécifique que nous payons à travers le corps du prêt, et combien est le montant des intérêts. À ces fins, forgeron dans la table de l'exil, que nous remplirons les données. Les lignes de ce tableau seront responsables à la période correspondante, qui est le mois. Étant donné que la période de prêt est de 24 mois, le nombre de lignes sera également appropriée. Les colonnes ont indiqué un organisme de prêt, les paiements d'intérêts, un paiement mensuel total, qui est la somme des deux colonnes précédentes, ainsi que le montant restant à payer.

1. Pour déterminer le montant du paiement par le corps du prêt, utilisez la fonction OSP, qui est juste destiné à ces fins. Nous établissons le curseur dans la cellule, qui est situé dans la ligne « 1 » et dans la colonne « Paiement par le corps du prêt ». Cliquez sur le bouton "Fonction de coller".



2. Accédez au Maître des fonctions. Dans la catégorie « financière », nous notons le nom « OSPLT » et cliquez sur le bouton « OK ».



3. Les arguments des arguments de l'opérateur OSP commencent. Il a la syntaxe suivante:

   = Ospult (taux; période; KPER, PS, BS)

   Comme on le voit, les arguments de cette fonction presque complètement coïncident avec les arguments de l'opérateur du PLT, seulement au lieu de l'argument optionnel « Type » a ajouté un argument obligatoire « période ». Il indique le numéro de la période de paiement, et dans notre cas particulier sur le nombre de mois.

   Remplissez les arguments des arguments de la fonction OSR déjà que nous connaissons par les mêmes données, qui a été utilisé pour la fonction PL. Tout compte tenu du fait que, dans l'avenir, la copie d'une formule sera utilisée par un marqueur de remplissage, vous devez faire tous les liens dans les domaines absolu afin qu'ils ne changent pas. Cela nécessite de mettre un signe de dollar avant chaque valeur de coordonnées verticales et horizontales. Mais il est plus facile de le faire, en sélectionnant simplement les coordonnées et en cliquant sur la touche de fonction F4. Le signe dollar sera placé dans les bons endroits automatiquement. Nous n'oublions pas que le pari annuel doit être divisé en 12.



4. Mais nous avons un autre nouvel argument, qui n'a pas été de la fonction PL. Cet argument « période ». Dans le champ approprié, définir une référence à la première cellule de la colonne « période ». Cet élément de la feuille contient le numéro « 1 », qui désigne le nombre du premier mois de prêt. Mais contrairement aux champs précédents, nous laissons le lien relatif dans le domaine spécifié, et non absolue de celui-ci ne.

   Après toutes les données dont nous avons parlé ci-dessus sont introduits, appuyez sur le bouton « OK ».



5. Après cela, dans la cellule, ce qui nous avons attribué précédemment, le montant du paiement par le corps du prêt pour le premier mois apparaîtra. Il sera 18536.74 roubles.



6. Puis, comme mentionné ci-dessus, nous devrions copier cette formule pour les cellules de colonne restantes à l'aide d'un marqueur de remplissage. Pour ce faire, placez le curseur sur le coin inférieur droit de la cellule qui contient la formule. Le curseur est converti en une croix, qui est appelé un marqueur de remplissage. Cliquez sur le bouton gauche de la souris et tirer vers le bas jusqu'à la fin de la table.



7. En conséquence, toutes les colonnes de cellules sont remplies. Maintenant, nous avons un tableau de payer un prêt mensuel. Comme mentionné ci-dessus, le montant du paiement sur cet article augmente avec chaque nouvelle période.



8. Maintenant, nous devons faire un calcul de paiement mensuel des intérêts. À ces fins, nous allons utiliser l'opérateur PRT. Nous attribuons la première cellule vide dans la colonne « pourcentage de paiements ». Cliquez sur le bouton "Fonction de coller".



9. Dans les fonctions du Maître des fonctions dans la catégorie « financière », nous produisons les noms des NAMP. Effectuez un clic sur le bouton "OK".



10. La fenêtre des arguments de la fonction TRP commence. Sa syntaxe ressemble à ceci:

    = PRT (taux; période; CPU, PS, BS)

    Comme on le voit, les arguments de cette fonction sont absolument identiques à des éléments similaires de l'opérateur OSP. Par conséquent, il suffit d'entrer les mêmes données dans la fenêtre que nous sommes entrés dans la fenêtre précédente des arguments. Nous ne faut pas oublier que la référence dans le domaine « période » doit être relatif, et dans tous les autres domaines, les coordonnées devraient être portées à la forme absolue. Après cela, cliquez sur le bouton "OK".



11. Ensuite, le résultat du calcul du montant du paiement des intérêts pour un prêt pour le premier mois est affiché dans la cellule correspondante.



12. En appliquant le marqueur de remplissage, la copie de la rendent formule dans les autres éléments de la colonne, de cette façon de recevoir un calendrier mensuel des pourcentages pour le prêt. Comme on peut le voir, comme il a été dit plus tôt, de mois en mois la valeur de ce type de paiement diminue.



13. Maintenant, nous devons calculer le paiement mensuel global. Pour ce calcul, on ne doit pas recourir à tout opérateur, comme vous pouvez utiliser la formule simple arithmétique. Nous plier le contenu des cellules du premier mois de la colonne « Paiement par le corps du prêt » et « intérêt complet ». Pour ce faire, définissez le signe « = » dans la première cellule vide de la colonne « paiement mensuel total ». Ensuite, cliquez sur les deux éléments ci-dessus en définissant le signe « + » entre eux. Cliquez sur la touche Entrée.



14. Ensuite, en utilisant le marqueur de remplissage, comme dans les cas précédents, remplir la colonne de données. Comme on le voit, à travers toute l'action du contrat, le montant du paiement mensuel total, qui comprend le paiement par le corps du prêt et le paiement des intérêts, sera 23536.74 roubles. En fait, nous avons déjà calculé cet indicateur avant d'utiliser PPT. Mais dans ce cas, il est présenté plus clairement, plus précisément le montant du paiement par le corps du prêt et des intérêts.



15. Maintenant, vous devez ajouter des données à la colonne, où le solde du montant du prêt est affiché par mois, ce qui est toujours tenu de payer. Dans la première cellule de la colonne « Solde à payer » le calcul sera le plus facile. Nous devons prendre loin de l'ampleur du prêt initial, qui est spécifié dans le tableau des données primaires, le paiement par le corps du prêt pour le premier mois dans le tableau calculé. Mais, compte tenu du fait que l'un des chiffres que nous allons déjà avec le signe « - », ils ne devraient pas être enlevés, mais pour plier. Nous faisons et cliquez sur le bouton ENTRER.



16. Mais le calcul du solde à payer après les mois deuxième et suivantes sera un peu plus compliqué. Pour ce faire, il faut enlever le corps du prêt au début de prêt du montant total des paiements par le corps du prêt pour la période précédente. Installer le signe « = » dans la deuxième cellule de la colonne « Palais de paiement ». Ensuite, indiquez un lien vers la cellule qui contient le montant du prêt initial. Maintenant, il est absolu, et en appuyant sur la mettant en lumière la touche F4. Ensuite, nous avons mis le signe « + », puisque nous avons le deuxième sens et si négatif. Après cela, cliquez sur le bouton « Insérer la fonction ».



17. Le maître des fonctions est lancé, dans lequel vous devez passer à la catégorie « mathématique ». Il nous allouons l'inscription « sommes » et appuyez sur le bouton « OK ».



18. La fenêtre des arguments commence les arguments de la fonction. L'opérateur spécifié sert à résumer les données dans les cellules que nous devons effectuer dans la colonne « Paiement par le corps prêt ». Il a la syntaxe suivante:

    = Somme (numéro1; numéro2; ...)

    Comme arguments, les références à des cellules dans lesquelles les numéros sont contenus. Nous avons mis le curseur dans le champ « Nombre1 ». Puis épingler le bouton gauche de la souris et sélectionnez les deux premières cellules de la colonne du corps de crédit sur la feuille. Sur le terrain, comme nous le voyons, un lien vers la plage est apparu. Il se compose de deux parties séparées par deux points: les références à la première plage de la plage et sur le dernier. Pour être en mesure de copier la formule spécifiée dans le futur par un marqueur de remplissage, nous faisons le premier maillon de la gamme absolue. Nous mettons en évidence et cliquez sur la touche de fonction F4. La deuxième partie de la référence et du congé relative. Or, lors de l'utilisation d'un marqueur de remplissage, la première gamme de la gamme sera fixé, et celui-ci va tendre comme il se déplace vers le bas. Cela est nécessaire pour nous d'atteindre les objectifs. Ensuite, cliquez sur le bouton "OK".



19. Ainsi, le résultat du solde de la dette de crédit après le deuxième mois est déchargé dans la cellule. Maintenant, en commençant par cette cellule, nous faisons la copie de la formule en éléments de colonne vides à l'aide d'un marqueur de remplissage.



20. Le calcul mensuel des restes à payer sur le prêt est effectué pour toute la période de crédit. Comme il devrait être, à la fin de la date limite, ce montant est égal à zéro.

Ainsi, nous n'avons pas simplement calculé le paiement du prêt, mais nous avons organisé une sorte de calculatrice de crédit. Qui agira sur un régime de rente. Si, dans la table source, changez par exemple le montant du prêt et le taux d'intérêt annuel, puis dans la table finale, il y aura une recalcul automatique des données. Par conséquent, il peut être utilisé non seulement une fois pour un cas particulier, mais également à appliquer dans diverses situations pour calculer les options de crédit sur un régime de rente.

Leçon: Fonctions financières dans Excel

Comme vous pouvez le constater, en utilisant le programme Excel à la maison, vous pouvez facilement calculer le paiement global du prêt mensuel sur le schéma de rente, à l'aide de l'opérateur PL à ces fins. En outre, avec l'aide des fonctions OSR et du PRT, il est possible de calculer la quantité de paiements par l'organe du prêt et des pourcentages pour la période spécifiée. Application de toutes ces fonctions de bagages ensemble, il est possible de créer une calculatrice de crédit puissante pouvant être utilisée plus d'une fois pour calculer le paiement de la rente.