le tutoriel montre comment construire un calendrier D’amortissement dans Excel pour détailler les paiements périodiques sur un prêt amorti ou une hypothèque.
Un amortissement de prêt est juste une façon élégante de définir un prêt est remboursé en versements échelonnés pendant toute la durée du prêt.
fondamentalement, tous les prêts s’amortissent d’une manière ou d’une autre. Par exemple, un prêt entièrement amorti pour 24 mois aura 24 paiements mensuels égaux., Chaque paiement applique une certaine somme au capital et une autre aux intérêts. Pour détailler chaque paiement sur un prêt, vous pouvez établir un calendrier d’amortissement du prêt.
un calendrier d’amortissement est un tableau qui répertorie les paiements périodiques sur un prêt ou une hypothèque au fil du temps, décompose chaque paiement en principal et en intérêts, et montre le solde restant après chaque paiement.,
- Comment faire pour créer un tableau d’amortissement de prêt dans Excel
- tableau d’Amortissement pour un nombre variable de périodes
- tableau d’amortissement de Prêt avec des paiements supplémentaires
- Excel amortissement modèle
Comment faire pour créer un tableau d’amortissement de prêt dans Excel
Pour construire un prêt ou une hypothèque tableau d’amortissement sous Excel, nous aurons besoin d’utiliser les fonctions suivantes:
- PMT fonction calcule le montant total des paiements périodiques. Ce montant reste constant pendant toute la durée du prêt.,
- fonction PPMT-obtient la partie principale de chaque paiement qui va vers le principal du prêt, c’est-à-dire le montant que vous avez emprunté. Ce montant augmente pour les paiements ultérieurs.
- fonction IPMT-trouve la partie intérêt de chaque paiement qui va vers les intérêts. Ce montant diminue à chaque paiement.
passons maintenant au processus étape par étape.,
configurer le tableau d’amortissement
pour commencer, définissez les cellules d’entrée dans lesquelles vous allez entrer les composantes connues d’un prêt:
- C2 – taux d’intérêt annuel
- C3 – Durée du prêt en années
- C4 – nombre de paiements par an
- C5 – montant du prêt
La prochaine chose que vous paiement, intérêt, principal, solde) dans A7:E7., Dans la colonne période, entrez une série de nombres égaux au nombre total de paiements (1 – 24 dans cet exemple):
avec tous les composants connus en place, passons aux formules d’amortissement des prêts partiels les plus intéressantes.
calculer le montant total du paiement (formule PMT)
Le montant du paiement est calculé avec la fonction PMT(rate, nper, pv,,).
pour gérer correctement les différentes fréquences de paiement (telles que hebdomadaire, mensuelle, trimestrielle, etc.,), vous devez être cohérent avec les valeurs fournies pour les arguments rate et nper:
- Rate – divisez le taux d’intérêt annuel par le nombre de périodes de paiement par an (C C C 2/4 C C 4).
- Nper – multiplier le nombre d’années par le nombre de périodes de paiement par année (C C C 3*4 C 4 4).
- pour l’argument pv, entrez le montant du prêt (C C C 5).
- les arguments fv et type peuvent être omis car leurs valeurs par défaut fonctionnent très bien pour nous (le solde après le dernier paiement est censé être 0; les paiements sont effectués à la fin de chaque période).,
en rassemblant les arguments ci-dessus, nous obtenons cette formule:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
veuillez faire attention, que nous utilisons des références de cellules absolues car cette formule doit être copiée dans les cellules ci-dessous sans aucun changement.,
entrez la formule PMT dans B8, faites-la glisser dans la colonne, et vous verrez un montant de paiement constant pour toutes les périodes:
calculer les intérêts (formule IPMT)
pour trouver la partie intérêt de chaque paiement périodique, utilisez la fonction IPMT(rate, per, NPER, pv,,):
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
tous les arguments sont les mêmes que dans la formule PMT, à l’exception de l’argument per qui spécifie la période de paiement., Cet argument est fourni en tant que référence de cellule relative (A8) car il est censé changer en fonction de la position relative d’une ligne sur laquelle la formule est copiée.,
cette formule passe à C8, puis vous la Copiez dans autant de cellules que nécessaire:
trouver le principal (formule PPMT)
pour calculer la partie principale de chaque paiement périodique, utilisez cette formule PPMT:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
la syntaxe et les arguments sont exactement les mêmes que dans la formule ipmt discutée ci-dessus:
cette formule va à la colonne d, en commençant par D8:
Obtenir le solde
Pour calculer le solde restant pour chaque période, nous allons utiliser deux formules différentes.
pour trouver le solde après le premier paiement en E8, additionnez le montant du prêt (C5) et le capital de la première période (D8):
=C5+D8
parce que le montant du prêt est un nombre positif et le principal est un nombre négatif, ce dernier est en fait soustrait du premier.,
pour la deuxième période et toutes les périodes suivantes, additionnez le solde précédent et le principal de cette période:
=E8+D9
La formule ci-dessus va à E9, puis vous la Copiez dans la colonne. En raison de l’utilisation de références de cellules relatives, la formule s’ajuste correctement pour chaque ligne.
C’est elle! Notre calendrier d’amortissement de prêt mensuel est fait:
astuce: retourner les paiements sous forme de nombres positifs
comme un prêt est payé sur votre compte bancaire, les fonctions Excel renvoient le paiement, les intérêts et le principal sous forme de nombres négatifs., Par défaut, ces valeurs sont surlignées en rouge et entre parenthèses comme vous pouvez le voir dans l’image ci-dessus.
Si vous préférez avoir tous les résultats sous forme de nombres positifs, mettez un signe moins avant les fonctions PMT, IPMT et PPMT.
pour les formules de solde, utilisez la soustraction au lieu de l’addition comme indiqué dans la capture d’écran ci-dessous:
calendrier D’amortissement pour un nombre variable de périodes
dans l’exemple ci-dessus, nous avons construit un calendrier d’amortissement, Cette rapide solution fonctionne bien pour un prêt ou une hypothèque.
Si vous cherchez à créer un calendrier d’amortissement réutilisable avec un nombre variable de périodes, vous devrez adopter une approche plus complète décrite ci-dessous.
entrez le nombre maximum de périodes
Dans la colonne Période, insérer le nombre maximal de paiements que vous allez permettre à tout emprunt, disons, de 1 à 360. Vous pouvez tirer parti de la fonction de remplissage automatique D’Excel pour saisir une série de chiffres plus rapidement.,
utilisez les déclarations IF dans les formules d’amortissement
comme vous avez maintenant beaucoup de nombres de période excessifs, vous devez en quelque sorte limiter les calculs au nombre réel de paiements pour un prêt particulier. Cela peut être fait en enveloppant chaque formule dans une instruction IF. Le test logique de L’instruction IF vérifie si le numéro de période de la ligne actuelle est inférieur ou égal au nombre total de paiements. Si le test logique est TRUE, la fonction correspondante est calculée; si FALSE, une chaîne vide est renvoyée.,
en supposant que la période 1 est dans la ligne 8, Entrez les formules suivantes dans les cellules correspondantes, puis copiez-les dans toute la table.,
Paiement (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
l’Intérêt (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Principal (D8):
=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Équilibre:
Pour la Période 1 (E8), la formule est la même que dans l’exemple précédent:
=C5+D8
Pour la Période 2 (E9) et toutes les périodes ultérieures, la formule prend cette forme:
=IF(A9<=$C$3*$C$4, E8+D9, "")
le résultat est Que vous avez correctement calculé le calendrier d’amortissement et un tas de lignes vides à la période de chiffres après que le prêt est remboursé.,
Masquer les numéros de périodes supplémentaires
Si vous pouvez vivre avec un tas de numéros de période superflus affichés après le dernier paiement, vous pouvez considérer le travail effectué et ignorer cette étape. Si vous recherchez la perfection, masquez toutes les périodes inutilisées en créant une règle de mise en forme conditionnelle qui définit la couleur de police sur blanc pour toutes les lignes après le dernier paiement.,
pour cela, sélectionnez Toutes les lignes de données si votre tableau d’amortissement (A8:E367 dans notre cas) et cliquez sur Onglet Accueil>mise en forme conditionnelle>nouvelle règle
dans la case correspondante, entrez la formule ci-dessous qui vérifie si le numéro de période dans la colonne A est supérieur au nombre total de paiements:
=$A8>$C$3*$C$4
Après cela, cliquez sur le bouton Format… et de choisir le blanc, couleur de police. Fait!,
faire un résumé de prêt
pour voir le résumé de votre prêt en un coup d’œil, ajoutez quelques formules supplémentaires en haut de votre calendrier d’amortissement.
le Total des paiements (F2):
=-SUM(B8:B367)
Total des intérêts (F3):
=-SUM(C8:C367)
Si vous avez des paiements des nombres positifs, retirez le signe de l’formules ci-dessus.
C’est elle! Notre calendrier d’amortissement des prêts est terminé et bon pour aller!,
télécharger le calendrier d’amortissement du prêt pour Excel
comment faire un calendrier d’amortissement du prêt avec des paiements supplémentaires dans Excel
Les calendriers d’amortissement discutés dans les exemples précédents sont faciles à créer et à suivre (espérons-le :). Cependant, ils laissent de côté une fonctionnalité utile qui intéresse de nombreux payeurs de prêts: des paiements supplémentaires pour rembourser un prêt plus rapidement. Dans cet exemple, nous verrons comment créer un calendrier d’amortissement du prêt avec des paiements supplémentaires.
Définir les cellules d’entrée
Comme d’habitude, à commencer par la mise en place des cellules d’entrée., Dans ce cas, nommons ces cellules comme écrit ci – dessous pour faciliter la lecture de nos formules:
- InterestRate – C2 (taux d’intérêt annuel)
- LoanTerm – C3 (Durée du prêt en années)
- PaymentsPerYear – C4 (nombre de paiements par an)
- LoanAmount – C5 (montant total du prêt)
- ExtraPayment – C6 (paiement supplémentaire par période)
calculer un paiement planifié
en dehors des cellules D’entrée, une cellule prédéfinie supplémentaire est requise pour nos calculs ultérieurs-le montant du paiement planifié, C’est-à-dire, le montant à payer sur un prêt si aucun paiement supplémentaire n’est effectué. Ce montant est calculé avec la formule suivante:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
veuillez noter que nous mettons un signe moins avant la fonction PMT pour avoir le résultat comme un nombre positif. Pour éviter les erreurs dans le cas où certaines cellules d’entrée sont vides, nous enfermons la formule PMT dans la fonction IFERROR.
entrez cette formule dans une cellule (G2 dans notre cas) et nommez cette cellule ScheduledPayment.,
configurez le tableau d’amortissement
créez un tableau d’amortissement du prêt avec les en-têtes indiqués dans la capture d’écran ci-dessous. Dans la colonne période, entrez une série de nombres commençant par zéro (vous pouvez masquer la ligne période 0 plus tard si nécessaire).
Si vous souhaitez créer un calendrier d’amortissement réutilisable, entrez le nombre maximal de périodes de paiement possibles (de 0 à 360 dans cet exemple).
pour la période 0 (Ligne 9 dans notre cas), tirez la valeur du solde, qui est égale au montant du prêt initial., Toutes les autres cellules de cette ligne reste vide:
la Formule dans G9:
=LoanAmount
4. Construire des formules pour le calendrier d’amortissement avec des paiements supplémentaires
c’est un élément clé de notre travail. Parce que les fonctions intégrées D’Excel ne prévoient pas de paiements supplémentaires, nous devrons faire tous les calculs par nous-mêmes.
entrez les formules suivantes dans la ligne 10 (période 1), puis copiez-les pour toutes les périodes restantes.
paiement planifié (B10):
Si le montant du paiement planifié (appelé cellule G2) est inférieur ou égal au solde restant (G9), utilisez le paiement planifié. Sinon, ajoutez le solde restant et les intérêts du mois précédent.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
par précaution supplémentaire, nous enveloppons cette formule et toutes les formules suivantes dans la fonction IFERROR., Cela évitera un tas d’erreurs diverses si certaines cellules d’entrée sont vides ou contiennent des valeurs non valides.
paiement supplémentaire (C10):
utilisez une formule IF avec la logique suivante:
Si le montant de L’Extrapaiement (appelé cellule C6) est inférieur à la différence entre le solde restant et le capital de cette période (G9-E10), retournez L’Extrapaiement; sinon, utilisez la différence.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
paiement Total (D10)
ajoutez simplement le paiement prévu (B10) et le paiement supplémentaire (C10) pour la période en cours:
=IFERROR(B10+C10, "")
Principal (E10)
si le paiement prévu pour une période donnée est supérieur à zéro, retournez une plus petite des deux valeurs: paiement prévu moins intérêts (B10-F10) ou le solde restant (G9); sinon retourner zéro.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
Veuillez noter que le directeur ne comprend la partie de la date prévue de paiement (pas le paiement supplémentaire!) qui va vers le principal du prêt.,
intérêt (F10)
Si le paiement prévu pour une période donnée est supérieur à zéro, divisez le taux d’intérêt annuel (cellule C2) par le nombre de paiements par année (cellule C4) et multipliez le résultat par le solde restant après la période précédente; sinon, retournez 0.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
Solde (G10)
Si le solde restant (G9) est supérieure à zéro, de soustraire la partie principale du paiement (E10) et le paiement supplémentaire (C10) à partir du solde restant après la période précédente (G9); sinon retourner 0.,
=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")
Si tout est fait correctement, votre calendrier d’amortissement du prêt à ce stade devrait ressembler à ceci:
Masquer les périodes supplémentaires
configurez une règle de mise en forme conditionnelle pour masquer les valeurs dans les périodes inutilisées, comme expliqué dans cette astuce.,paiements par année:
=LoanTerm*PaymentsPerYear
nombre réel de paiements:
compter les cellules dans la colonne paiement Total supérieures à zéro, en commençant par la période 1:
=COUNTIF(D10:D369,">"&0)
total des paiements supplémentaires:
additionner les cellules dans la colonne paiement supplémentaire, en commençant par la période 1:
=SUM(C10:C369)
total des intérêts:
additionnez les cellules dans la colonne des intérêts, en commençant par la période 1:
=SUM(F10:F369)
en option, masquez la ligne de la période 0, et votre calendrier d’amortissement du prêt avec des paiements supplémentaires est terminé!, La capture d’écran ci-dessous montre le résultat final:
télécharger le calendrier d’amortissement du prêt avec des paiements supplémentaires
calendrier D’amortissement modèle Excel
pour créer un calendrier d’amortissement de prêt de premier ordre en un rien de temps, utilisez les modèles intégrés Il suffit d’aller dans le fichier >Nouveau, tapez » calendrier d’amortissement »dans la zone de recherche et choisissez le modèle que vous aimez, par exemple, celui-ci avec des paiements supplémentaires:
c’est ainsi que vous créez un calendrier d’amortissement de prêt ou, Je vous remercie d’avoir lu et j’espère vous voir sur notre blog la semaine prochaine!
téléchargements
le Calendrier d’Amortissement de l’Échantillon Classeur
- Comment calculer les intérêts composés dans Excel
- Comment trouver TCAM (taux de croissance annuel composé) dans Excel
- le Calcul du pourcentage dans Excel avec la formule exemples