Formule de calcul d'un versement de rente dans Excel

Les paiements de prêt sont plus faciles et plus rapides à calculer avec Microsoft Office Excel. Beaucoup plus de temps est consacré au calcul manuel. Cet article se concentrera sur les paiements de rente, les caractéristiques de leur calcul, les avantages et les inconvénients.

Qu'est-ce qu'un versement de rente

Méthode de remboursement mensuel d'un prêt, dans laquelle le montant déposé ne change pas pendant toute la durée du prêt. Ceux. à certaines dates de chaque mois, une personne dépose une somme d'argent spécifique jusqu'à ce que le prêt soit entièrement remboursé.

De plus, les intérêts du prêt sont déjà inclus dans le montant total versé à la banque.

Rente de classification

Les paiements de rente peuvent être divisés selon les types suivants :

1. Fixé. Les paiements qui ne changent pas ont un taux fixe quelles que soient les conditions externes.
2. Devise. La possibilité de modifier le montant du paiement en cas de baisse ou de hausse du taux de change.
3. indexé. Paiements en fonction du niveau, indicateur d'inflation. Pendant la durée du prêt, leur taille change souvent.
4. Variables. Rente, qui peut changer en fonction de l'état du système financier, instruments.

Faites attention! Les paiements fixes sont préférables pour tous les emprunteurs, car ils présentent peu de risques.

Avantages et inconvénients des versements de rente

Pour mieux comprendre le sujet, il est nécessaire d'étudier les principales caractéristiques de ce type de remboursements de prêts. Il a les avantages suivants :

• Établir un montant précis de paiement et la date de son paiement.
• Haute disponibilité pour les emprunteurs. Presque tout le monde peut demander une rente, quelle que soit sa situation financière.
• La possibilité de baisser le montant de la mensualité avec une augmentation de l'inflation.

Non sans inconvénients :

• Taux élevé. L'emprunteur paiera en trop une somme d'argent plus importante par rapport au paiement différentiel.
• Problèmes découlant de la volonté de rembourser la dette plus tôt que prévu.
• Pas de recalculs pour les paiements anticipés.

Quel est le remboursement du prêt ?

Le paiement de la rente comprend les éléments suivants :

• Intérêts payés par une personne lors du remboursement d'un prêt.
• Une partie du montant principal.

Par conséquent, le montant total des intérêts dépasse presque toujours le montant versé par l'emprunteur pour réduire la dette.

Formule de paiement de rente de base dans Excel

Comme mentionné ci-dessus, dans Microsoft Office Excel, vous pouvez travailler avec différents types de paiements pour les prêts et avances. La rente ne fait pas exception. En général, la formule avec laquelle vous pouvez calculer rapidement les cotisations de rente est la suivante :  

Important! Il est impossible d'ouvrir des parenthèses au dénominateur de cette expression pour la simplifier.

Les principales valeurs de la formule se déchiffrent comme suit :

• AP – paiement de rente (le nom est abrégé).
• O – la taille de la dette principale de l'emprunteur.
• PS - le taux d'intérêt proposé sur une base mensuelle par une banque particulière.
• C est le nombre de mois que dure le prêt.

Pour assimiler l'information, il suffit de donner quelques exemples d'utilisation de cette formule. Ils seront discutés plus loin.

Exemples d'utilisation de la fonction PMT dans Excel

On donne une condition simple du problème. Il est nécessaire de calculer le paiement mensuel du prêt si la banque propose un intérêt de 23% et que le montant total est de 25000 3 roubles. Le prêt durera XNUMX ans. Le problème est résolu selon l'algorithme :

1. Créez une feuille de calcul générale dans Excel basée sur les données sources.

2. Activez la fonction PMT et entrez ses arguments dans la case appropriée.
3. Dans le champ "Mise", saisissez la formule "B3/B5". Ce sera le taux d'intérêt sur le prêt.
4. Dans la ligne "Nper" écrivez la valeur sous la forme "B4*B5". Ce sera le nombre total de paiements pour toute la durée du prêt.
5. Remplissez le champ "PS". Ici, vous devez indiquer le montant initial prélevé à la banque, en écrivant la valeur «B2».

6. Assurez-vous qu'après avoir cliqué sur "OK" dans la table source, la valeur "Paiement mensuel" a été calculée.

Informations Complémentaires! Un nombre négatif indique que l'emprunteur dépense de l'argent.

Un exemple de calcul du montant du trop-perçu sur un prêt dans Excel

Dans ce problème, vous devez calculer le montant qu'une personne qui a contracté un prêt de 50000 roubles à un taux d'intérêt de 27% pendant 5 ans paiera en trop. Au total, l'emprunteur effectue 12 versements par an. La solution:

1. Compilez la table de données d'origine.

2. Du montant total des paiements, soustrayez le montant initial selon la formule «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Il doit être inséré dans la barre de formule en haut du menu principal du programme.
3. De ce fait, le montant des trop-perçus apparaîtra dans la dernière ligne de la plaque créée. L'emprunteur paiera en plus 41606 roubles.

La formule de calcul de la mensualité optimale du prêt dans Excel

Une tâche avec la condition suivante : le client a enregistré un compte bancaire de 200000 4 roubles avec possibilité de réapprovisionnement mensuel. Il est nécessaire de calculer le montant du paiement qu'une personne doit effectuer chaque mois, de sorte qu'après 2000000 ans, elle ait 11 roubles sur son compte. Le taux est de XNUMX %. La solution:

1. Créez une feuille de calcul basée sur les données d'origine.

2. Entrez la formule dans la ligne de saisie Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» et appuyez sur "Entrée" depuis le clavier. Les lettres seront différentes selon les cellules dans lesquelles le tableau est placé.
3. Vérifiez que le montant de la cotisation est automatiquement calculé dans la dernière ligne du tableau.

Faites attention! Ainsi, pour que le client accumule 4 roubles à un taux de 2000000% en 11 ans, il doit déposer 28188 roubles chaque mois. Le moins dans le montant indique que le client subit des pertes en donnant de l'argent à la banque.

Fonctionnalités d'utilisation de la fonction PMT dans Excel

En général, cette formule s'écrit comme suit : =PMT(taux ; nper ; ps ; [bs] ; [type]). La fonction a les fonctionnalités suivantes :

1. Lors du calcul des cotisations mensuelles, seul le taux annuel est pris en considération.
2. Lors de la spécification du taux d'intérêt, il est important de recalculer en fonction du nombre de versements par an.
3. Au lieu de l'argument "Nper" dans la formule, un nombre spécifique est indiqué. C'est le délai de paiement.

Calcul du paiement

En général, le paiement de la rente est calculé en deux étapes. Pour comprendre le sujet, chacune des étapes doit être considérée séparément. Cela sera discuté plus loin.

Etape 1 : calcul de la mensualité

Pour calculer dans Excel le montant que vous devez payer chaque mois sur un prêt à taux fixe, vous devez :

1. Compilez le tableau source et sélectionnez la cellule dans laquelle vous souhaitez afficher le résultat et cliquez sur le bouton "Insérer une fonction" en haut.

2. Dans la liste des fonctions, sélectionnez « PLT » et cliquez sur « OK ».

3. Dans la fenêtre suivante, définissez les arguments de la fonction en indiquant les lignes correspondantes dans la table compilée. À la fin de chaque ligne, vous devez cliquer sur l'icône, puis sélectionner la cellule souhaitée dans le tableau.

4. Lorsque tous les arguments sont remplis, la formule appropriée sera écrite dans la ligne de saisie des valeurs et le résultat du calcul avec un signe moins apparaîtra dans le champ du tableau «Paiement mensuel».

Important! Après avoir calculé le versement, il sera possible de calculer le montant que l'emprunteur paiera en trop pour toute la durée du prêt.

Étape 2 : détails du paiement

Le montant du trop-perçu peut être calculé mensuellement. En conséquence, une personne comprendra combien d'argent elle dépensera chaque mois pour un prêt. Le calcul détaillé est effectué comme suit :

1. Créez une feuille de calcul pour 24 mois.

2. Placez le curseur dans la première cellule du tableau et insérez la fonction « OSPLT ».

3. Remplissez les arguments de la fonction de la même manière.

4. Lorsque vous remplissez le champ "Période", vous devez vous référer au premier mois du tableau, en indiquant la cellule 1.

5. Vérifiez que la première cellule de la colonne «Paiement par l'organisme du prêt» est remplie.
6. Pour remplir toutes les lignes de la première colonne, vous devez étirer la cellule jusqu'à la fin du tableau

7. Sélectionnez la fonction « PRPLT » pour remplir la deuxième colonne du tableau.
8. Remplissez tous les arguments dans la fenêtre ouverte conformément à la capture d'écran ci-dessous.

9. Calculez le paiement mensuel total en additionnant les valeurs des deux colonnes précédentes.

10. Pour calculer le « Solde à payer », il faut ajouter le taux d'intérêt au versement sur le corps du prêt et l'étirer jusqu'au bout de la plaque pour remplir tous les mois du prêt.

Informations Complémentaires! Lors du calcul du reste, les signes dollar doivent être accrochés à la formule afin qu'elle ne bouge pas lorsqu'elle est étirée.

Calcul des versements d'annuités sur un prêt dans Excel

La fonction PMT est responsable du calcul de la rente dans Excel. Le principe de calcul en général consiste à effectuer les étapes suivantes :

1. Compilez la table de données d'origine.
2. Établissez un calendrier de remboursement de la dette pour chaque mois.
3. Sélectionnez la première cellule de la colonne "Paiements sur le prêt" et entrez la formule de calcul « PLT (3 $ B/12 ; 4 $ B$ ; 2 $ B$) ».
4. La valeur résultante est étirée pour toutes les colonnes de la plaque.

Calcul dans MS Excel du remboursement du montant principal de la dette

Les versements de rente doivent être effectués mensuellement en montants fixes. Et le taux d'intérêt ne change pas.

Calcul du solde du montant principal (avec BS=0, type=0)

Supposons qu'un prêt de 100000 10 roubles soit contracté pendant 9 ans à 1 %. Il faut calculer le montant de la dette principale au 3er mois de la XNUMXème année. La solution:

1. Compilez une fiche technique et calculez le paiement mensuel en utilisant la formule PV ci-dessus.
2. Calculez la part du paiement nécessaire pour rembourser une partie de la dette à l'aide de la formule «=-PMT-(PS-PS1)*élément=-PMT-(PS +PMT+PS*élément)».
3. Calculez le montant de la dette principale pour 120 périodes en utilisant une formule bien connue.
4. À l'aide de l'opérateur HPMT, trouvez le montant des intérêts payés pour le 25e mois.
5. Vérifiez le résultat.

Calcul du montant du principal qui a été payé entre deux périodes

Ce calcul est mieux fait d'une manière simple. Vous devez utiliser les formules suivantes pour calculer le montant dans l'intervalle de deux périodes :

• ="-BS(item; con_period; plt; [ps]; [type]) /(1+type *item)".
• = "+ BS(taux ; start_period-1 ; plt ; [ps] ; [type]) /IF(start_period =1 ; 1 ; 1+type *rate) ».

Faites attention! Les lettres entre parenthèses sont remplacées par des valeurs spécifiques.

Remboursement anticipé avec durée ou versement réduit

Si vous devez réduire la durée du prêt, vous devrez effectuer des calculs supplémentaires à l'aide de l'opérateur IF. Ainsi, il sera possible de contrôler le solde zéro, qui ne devrait pas être atteint avant la fin de la période de paiement.

Pour réduire les paiements, vous devez recalculer la cotisation pour chaque mois précédent.

Calculateur de prêt avec paiements irréguliers

Il existe plusieurs options de rente où l'emprunteur peut déposer des montants variables n'importe quel jour du mois. Dans une telle situation, le solde de la dette et les intérêts sont calculés pour chaque jour. En même temps, dans Excel, vous avez besoin de :

1. Entrez les jours du mois pour lesquels les paiements sont effectués et indiquez leur nombre.
2. Vérifiez les montants négatifs et positifs. Les négatifs sont préférés.
3. Comptez les jours entre deux dates auxquelles l'argent a été déposé.

Calcul du paiement périodique dans MS Excel. Dépôt à terme

Dans Excel, vous pouvez calculer rapidement le montant des paiements réguliers, à condition qu'un montant fixe ait déjà été accumulé. Cette action est effectuée à l'aide de la fonction PMT après la compilation de la table initiale.

Conclusion

Ainsi, les paiements de rente sont plus faciles, plus rapides et plus efficaces à calculer dans Excel. L'opérateur PMT est responsable de leur calcul. Des exemples plus détaillés peuvent être trouvés ci-dessus.