Comment préparer un calendrier d'amortissement dans Excel?
1. Téléchargez le plan d'amortissement de Microsoft.
2. Ouvrez le modèle dans Excel.
3. Remplissez vos données de prêt dans la section "ENTRER LES VALEURS".
4. Tapez le nom de la banque comme "NOM DU PRÊTEUR".
5. Enregistrez la feuille de calcul en tant que fichier Excel.
Un tableau d'amortissement montre l'intérêt appliqué à un prêt à taux fixe et la façon dont le principal est réduit par les paiements.
Un tableau d'amortissement montre l'intérêt appliqué à un prêt à taux fixe et la façon dont le principal est réduit par les paiements. Il montre également le calendrier détaillé de tous les paiements afin que vous puissiez voir combien va vers le principal et combien est payé pour les frais d'intérêt. Ce guide vous apprend à créer votre propre plan d'amortissement dans Microsoft Excel.
Méthode 1 sur 2: création manuelle d'un plan d'amortissement
- 1Ouvrez une nouvelle feuille de calcul dans Microsoft Excel.
- 2Créez des étiquettes dans la colonne A. Créez des étiquettes pour vos données dans la première colonne pour garder les choses organisées. Voici ce que vous devez mettre dans chaque cellule:.
- A1: Loan Amount
- A2: Interest Rate
- A3: Months
- A4: Payments
- 3Entrez les informations relatives à votre prêt dans la colonne B. Remplissez les cellules B1-B3 avec des informations sur votre prêt. Laissez B4 (la cellule à côté de l'étiquette Paiements) vide.
- La valeur «Mois» doit être le nombre total de mois dans la durée du prêt. Par exemple, si vous avez un prêt de 2 ans, saisissez 24.
- La valeur du «taux d'intérêt» doit être un pourcentage (par exemple, 8,2%).
- 4Calculez votre paiement dans la cellule b4. Pour ce faire, cliquez sur la cellule B4, puis tapez la formule suivante dans la barre formule (FX) en haut de la feuille, puis appuyez sur ↵ Enterou ⏎ Return: =ROUND(PMT($B1,50€/12,$B2,20€,-$B7,50€), 2).
- Les signes dollar dans la formule sont des références absolues pour s'assurer que la formule cherchera toujours ces cellules spécifiques, même si elle est copiée ailleurs dans la feuille de calcul.
- Le taux d'intérêt du prêt doit être divisé par 12, puisqu'il s'agit d'un taux annuel calculé mensuellement.
- Par exemple, si votre prêt est de 112000€ avec un intérêt de 6% pendant 30 ans (360 mois), votre paiement de prêt s'élèvera à 670€
- 5Créez des en-têtes de colonne dans la ligne 7. Vous allez ajouter des données supplémentaires à la feuille, ce qui nécessite une deuxième zone de graphique. Entrez les étiquettes suivantes dans les cellules:
- A7: Period
- B7: Beginning Balance
- C7: Payment
- J7: Principal
- E7: Interest
- F7: Cumulative Principal
- G7: Cumulative Interest
- H7: Ending Balance.
Le taux d'intérêt du prêt doit être divisé par 12, puisqu'il s'agit d'un taux annuel calculé mensuellement. - 6Remplissez la colonne période. Cette colonne contiendra vos dates de paiement. Voici ce qu'il faut faire:
- Tapez le mois et l'année du premier paiement du prêt dans la cellule A8. Vous devrez peut-être formater la colonne pour afficher correctement le mois et l'année.
- Cliquez une fois sur la cellule pour la sélectionner.
- Faites glisser vers le bas à partir du centre de la cellule sélectionnée pour couvrir toutes les cellules via A367. Si cela ne permet pas à toutes les cellules de refléter les dates de paiement mensuelles correctes, cliquez sur la petite icône avec un éclair dessus dans le coin inférieur droit de la cellule inférieure et assurez-vous que l' option Mois dernier est sélectionnée.
- 7Remplissez les autres entrées dans les cellules b8 à h8.
- Le solde d'ouverture de votre prêt dans la cellule B8.
- Dans la cellule C8, tapez =$B3€ et appuyez sur Entrée ou Retour.
- Dans la cellule E8, créez une formule pour calculer le montant des intérêts du prêt sur le solde d'ouverture pour cette période. La formule ressemblera à =ROUND($B8*($B1,50€/12), 2). Le signe dollar unique crée une référence relative. La formule recherchera la cellule appropriée dans la colonne B.
- Dans la cellule D8, soustrayez le montant des intérêts sur le prêt dans la cellule E8 du paiement total dans C8. Utilisez des références relatives pour que cette cellule se copie correctement. La formule ressemblera à =$C8-$E8.
- Dans la cellule H8, créez une formule pour soustraire la partie principale du paiement du solde de début pour cette période. La formule ressemblera à =$B8-$D8.
- 8Continuez la planification en créant les entrées de b9 à h9.
- La cellule B9 doit inclure une référence relative au solde de clôture de la période précédente. Tapez =$H8 B9 et appuyez sur Entrée ou Retour.
- Copiez les cellules C8, D8 et E8 et collez-les dans C9, D9 et E9 (respectivement)
- Copiez H8 et collez-le dans H9. C'est là que la référence relative devient utile.
- Dans la cellule F9, créez une formule pour tabuler le principal payé cumulé. La formule se présente comme suit: =$D9+$F8.
- Entrez la formule d'intérêt cumulatif dans G9 comme ceci: =$E9+$G8.
- 9Mettez en surbrillance les cellules b9 à h9. Lorsque vous placez le curseur de la souris sur la partie inférieure droite de la zone en surbrillance, le curseur se transforme en réticule.
- 10Faites glisser le réticule jusqu'à la ligne 367. Cela remplit toutes les cellules de la ligne 367 avec le plan d'amortissement.
- Si cela semble drôle, cliquez sur la petite icône ressemblant à une feuille de calcul dans le coin inférieur droit de la cellule finale et sélectionnez Copier les cellules.
Méthode 2 sur 2: utilisation d'un modèle Excel
- 1Accédez à https://templates.office.com/en-us/loan-amortization-schedule-tm03986974. Il s'agit d'un modèle de calendrier d'amortissement gratuit et téléchargeable qui facilite le calcul du total des intérêts et des paiements totaux. Il inclut même la possibilité d'ajouter des paiements supplémentaires.
- 2Cliquez sur Télécharger. Cela enregistre le modèle sur votre ordinateur au format de modèle Excel (XLTX).
Le calendrier de cet article a été conçu pour un montant de paiement fixe avec un intérêt fixe et des délais de paiement fixes. - 3Double-cliquez sur le fichier téléchargé. Il s'appelle tf03986974.xltx et vous le trouverez généralement dans votre dossier Téléchargements. Cela ouvre le modèle dans Microsoft Excel.
- Les données du modèle sont là à titre d'exemple - vous pourrez ajouter vos propres données.
- Si vous y êtes invité, cliquez sur Activer la modification afin de pouvoir apporter des modifications au classeur.
- 4Tapez le montant du prêt dans la cellule "montant du prêt". Il se trouve dans la section «ENTRER DES VALEURS» près du coin supérieur gauche de la feuille. Pour le saisir, cliquez simplement sur la valeur existante (3730€) et saisissez votre propre montant.
- Lorsque vous appuyez sur ⏎ Returnou ↵ Enter(ou cliquez sur une autre cellule), les montants dans le reste de la feuille sont recalculés. Cela se produira chaque fois que vous modifiez une valeur dans cette section.
- 5Entrez votre taux d'intérêt annuel. Cela va dans la cellule " Taux d'intérêt annuel ".
- 6Saisissez la durée de votre prêt (en années). Cela entre dans la cellule «Durée du prêt en années».
- 7Saisissez le nombre de paiements que vous effectuez par an. Par exemple, si vous effectuez des paiements une fois par mois, saisissez 12 la cellule "Nombre de paiements par an".
- 8Entrez la date de début du prêt. Cela va dans la cellule "Date de début du prêt".
- 9Entrez une valeur pour «paiements supplémentaires facultatifs». Si vous payez au-delà du montant minimum dû sur votre prêt à chaque période de paie, entrez ce montant supplémentaire dans cette cellule. Sinon, remplacez la valeur par défaut par 0 (zéro).
Il s'agit d'un modèle de calendrier d'amortissement gratuit et téléchargeable qui facilite le calcul du total des intérêts et des paiements totaux. - 10Entrez le nom de l'émetteur du prêt. La valeur par défaut du champ «NOM DU PRÊTEUR» est «Woodgrove Bank». Remplacez-le par le nom de votre banque pour votre propre référence.
- 11Enregistrez la feuille de calcul en tant que nouveau fichier Excel. Voici comment:
- Cliquez sur le menu Fichier en haut à gauche et sélectionnez Enregistrer sous.
- Sélectionnez un emplacement sur votre ordinateur ou dans le cloud où vous souhaitez stocker votre programme.
- Entrez un nom pour le fichier. Si le type de fichier n'est pas déjà défini sur «Classeur Excel (*.xlsx)», sélectionnez cette option dans le menu déroulant (sous le nom de fichier) maintenant.
- Cliquez sur Enregistrer.
- Si vous ne recevez pas un solde final de 0€, assurez-vous d'avoir utilisé les références absolues et relatives comme indiqué et que les cellules ont été copiées correctement.
- Vous pouvez maintenant faire défiler jusqu'à n'importe quelle période pendant le paiement du prêt pour voir combien du paiement est appliqué au principal, combien est facturé en tant qu'intérêts sur le prêt et combien de principal et d'intérêts vous avez payés à ce jour.
- Cela ne fonctionnera que pour les prêts immobiliers calculés mensuellement. Si le prêt est un prêt automobile ou un prêt composé quotidiennement, cela ne donnera que des estimations approximatives des intérêts payés.
- L'ordinateur
- Microsoft Excel
- Détails du prêt
Lisez aussi: Comment protéger votre idée d'application?
Questions et réponses
- Puis-je calculer le nombre de mois avec un remboursement prédéterminé?Oui. Vous pouvez calculer le nombre de mois restant sur un prêt en exécutant une fonction "NPER". Utilisez la formule suivante: (solde du prêt, taux d'intérêt et paiement) = NPER (taux / 12, paiement, solde du prêt).
- Comment modifier la formule des paiements trimestriels plutôt que des paiements mensuels?Partout où il y a un champ rempli avec la formule = ROUND (...), remplacez l'entrée «12» par un «4». Exemple: dans la cellule E8, créez une formule pour calculer le montant des intérêts du prêt sur le solde d'ouverture pour cette période. La formule ressemblera à "= ROUND ($ B8 * ($ B1,50€ / 4), 2)"
- Le solde final est proche de zéro, mais pas exactement nul. Toutes les cellules sont correctes. Est-ce un problème d'arrondi?Tant que c'est à moins d'un centime pour chaque mois de la période, ça va, c'est à dire 30 ans = 2,70€
- Comment puis-je construire une table d'amortissement sans intérêt?Utilisez le calendrier ci-dessus, mais mettez l'intérêt à zéro (0)%.
- Comment puis-je changer la devise?La devise dépend du format de la cellule contenant des montants en dollars. Faites un clic droit sur la cellule que vous souhaitez modifier et cliquez sur "Format". Ensuite, choisissez "Devise" et entrez les codes appropriés pour la devise que vous choisissez.
- Que se passe-t-il si les montants des paiements varient et les dates de paiements varient, les intérêts peuvent-ils être calculés quotidiennement jusqu'à la date du paiement et au montant précis du paiement réel?Oui, mais c'est beaucoup plus de travail. Le calendrier de cet article a été conçu pour un montant de paiement fixe avec un intérêt fixe et des délais de paiement fixes. Vous pouvez modifier la formule de n'importe quelle cellule pour tenir compte des nouvelles informations. Accédez à la première modification du paiement, des intérêts ou de la période. Sur cette ligne, modifiez la formule en conséquence, puis copiez-la dans le reste de la planification.
- Comment puis-je ajouter un calcul pour les impôts fonciers amortis?Ce tableau vous permet de faire varier le taux d'intérêt, le principal et la durée du prêt. En règle générale, les impôts fonciers et les frais d'assurance sont séquestrés (ce qui signifie que les portions mensuelles sont incluses avec chaque paiement de prêt), de sorte qu'ils ne varient pas. Cependant, si vous souhaitez voir le paiement mensuel total, y compris les montants bloqués, vous pouvez inclure quelques lignes en haut du tableau. Dans la cellule A5, ajoutez l'étiquette «Paiement d'entiercement mensuel» et dans la cellule A6, ajoutez l'étiquette «Paiement mensuel total». La cellule B5 contiendrait le montant de votre taxe foncière. La cellule B6 serait la formule = B4 + B5.
- F9 et G9 ne devraient-ils pas être un total des principes et intérêts du mois précédent et de ce mois-ci?Oui, ça devrait.
- Comment ajouter une colonne pour les paiements de principe supplémentaires?Ajoutez une nouvelle colonne I et nommez-la «Principal supplémentaire». Chaque mois où vous effectuez un paiement de principal supplémentaire, inscrivez le montant du principal supplémentaire dans cette nouvelle colonne I. Ensuite, modifiez la formule de la cellule E8 de «= B8-D8» à «B8-D8-I8». Enfin, copiez la formule de E8 dans chaque cellule en dessous, comme vous l'avez fait lorsque vous avez copié la première ligne dans chaque cellule jusqu'à environ 370. Sans rien dans la colonne I, la formule agit exactement comme la formule d'origine.
- Comment faire un prêt à paiement hebdomadaire?Vous devez changer les périodes. Essayez ceci: remplacez «Mois» dans la cellule A3 par «Périodes». Modifiez la formule «Paiements» dans la cellule B4, en changeant le «12» (qui représente les mois) en «52» qui représente le nombre de semaines dans une année. La nouvelle formule ressemblera à ceci: "= ROUND (PMT ($ B1,50€ / 52, $ B2,20€, - $ B60€), 2)." Vous devrez également modifier vos règles à partir de la cellule A9 pour ajouter 7 jours au lieu de 1 mois. J'utiliserais cette formule dans la cellule A9: "= DATE (ANNEE (A8), MOIS (A8), JOUR (A8) +7." Enfin, copiez votre nouvelle formule dans le reste de l'horaire afin que toutes les périodes soient corriger.
Les commentaires (16)
- Facile à suivre, des instructions simples qui ont fonctionné! Il a certainement battu les "téléchargements" et les feuilles protégées par des mots de passe. Il va sur mon lecteur flash pour référence permanente.
- Très utile! Je ne maîtrise pas Excel et cela m'a beaucoup aidé! Merci!
- Wow, c'est fantastique. J'ai pu amortir
- Étapes spécifiques et détaillées que je pourrais copier et coller directement dans ma propre feuille de calcul. Très facile!
- C'était très utile, car j'avais des paiements mensuels irréguliers. Lorsque le calendrier d'amortissement a été terminé, j'ai simplement changé les paiements au montant mensuel correct.
- J'ai suivi les instructions sur un achat de matériel lourd de 42 mois 126000€ a très bien fonctionné!!
- Très complet et facile à suivre. Je n'ai eu aucun problème à recréer mon échéancier d'intérêt fixe de 98 mois et à mettre à jour les remboursements du principal au besoin.
- Beaucoup de travail a été fait. Superbe oeuvre exceptionnelle. Processus étape par étape. Merci beaucoup.
- Exactement les informations dont j'avais besoin. Les graphiques accompagnés d'une explication étape par étape sont extrêmement utiles.
- Article clair et descriptif étape par étape. La seule chose sur laquelle j'insisterais davantage serait la section "Mois" vers le début. Les mois doivent être multipliés par 12, quel que soit le nombre d'années choisies. Grande aide, merci.
- C'est le meilleur article que j'ai jamais lu sur le guide - il était facile à suivre et était précis pour ce qu'il avait dit qu'il ferait. Merci - excellent message.
- Cela a été très utile pour préparer un tableau d'amortissement pour une vente et un prêt avec 2 taux d'intérêt. Merci!
- Je travaille dans un cabinet de CPA et j'ai dû faire un plan d'amortissement rapide pour un client hors d'Excel. J'en ai fait un il y a quelque temps, mais c'était rapide et pertinent, car j'ai évidemment perdu le contact avec le temps. Merci beaucoup, cela a été très utile.
- J'avais besoin d'un calendrier de prêt et je n'en ai pas trouvé. Je voulais donc j'ai utilisé le vôtre et fabriqué le mien. Merci.
- Étapes faciles à suivre!
- Ma connaissance d'Excel est très basique, mais votre étape par étape a été écrite avec des instructions claires et simples. Je l'ai suivi et j'ai terminé mon emploi du temps en quelques minutes. Wahoo!
En parallèle
