Nous pouvons utiliser les formules PMT, IPMT et IF d'Excel pour créer un échéancier d'endettement. Premièrement, nous devons mettre en place le modèle en saisissant certaines hypothèses d'endettement. Dans cet exemple, nous supposons que la dette est de 5000000 $, la durée de paiement de 5 ans et le taux d'intérêt Taux d'intérêt Un taux d'intérêt fait référence au montant facturé par un prêteur à un emprunteur pour toute forme de dette donnée, généralement exprimée comme un pourcentage du principal. être de 4,5%.
1. Le solde d'ouverture de notre échéancier de la dette est égal au montant du prêt de 5 millions de dollars, donc dans la cellule E29, nous saisissons = B25 pour le lier à l'entrée d'hypothèse. Ensuite, nous pouvons utiliser la formule PMT pour calculer le paiement total pour la première période = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . La formule calcule le montant du paiement en utilisant le montant du prêt, la durée et le taux d'intérêt indiqués dans la section hypothèses.
2. Dans la cellule E28, entrez la période dans laquelle nous nous trouvons, qui est 1. Dans la cellule E29, entrez = E28 + 1 et remplissez la formule à droite. Ensuite, utilisez la formule IPMT pour connaître le paiement des intérêts pour la première période = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Le paiement du principal correspond à la différence entre le paiement total et le paiement des intérêts, soit = E30-E31 . Le solde de clôture est le solde d'ouverture plus le paiement principal effectué, soit = E29 + E32 . Le solde d'ouverture de la période 2 est le solde de clôture de la période 1, soit = E33 .
4. Copiez toutes les formules de la cellule E29 à E33 dans la colonne suivante, puis copiez tout à droite. Vérifiez si le solde de clôture pour la période 5 = 0 pour vous assurer que les formules et les nombres corrects sont utilisés.
5. Notez qu'il y a des messages d'erreur à partir de la période 6 car le solde d'ouverture est 0. Ici, nous pouvons utiliser la fonction IF pour nettoyer les erreurs. Dans la cellule E30, tapez = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . La formule indique que si le solde d'ouverture est inférieur à 0, la valeur totale du paiement sera affichée comme 0.
6. Dans la cellule 31, tapez = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Cette formule est similaire à la précédente, qui stipule que si le solde d'ouverture est inférieur à 0, le paiement des intérêts sera affiché comme 0.
7. Copiez la cellule E30 et E31, appuyez sur MAJ + flèche droite puis CTRL + R pour remplir à droite. Vous devriez voir que tous les messages d'erreur sont maintenant affichés comme 0.
XNPV et XIRR avec fonctions DATE et IF
Nous pouvons calculer la VAN et l'IRR en fonction de dates spécifiques en utilisant les fonctions Excel XNPV et XIRR avec les fonctions DATE et IF.
8. Allez dans la cellule E6 et entrez = DATE (E5,12,31) pour afficher la date. Copiez à droite. Vous verrez le #VALUE! message après 2021. Nous pouvons résoudre ce problème en utilisant la fonction IFERROR = IFERROR (DATE (E5,12,31), ””) .
9. Nous pouvons maintenant commencer à calculer la VAN et le TRI. Premièrement, nous devons saisir les montants des flux de trésorerie disponibles. Nous supposons que les montants FCF de la période 1 à 5 sont de -1 000, 500, 600, 700, 900. Dans la cellule C37, nous entrerons un taux d'actualisation de 15%. Dans la cellule B37, calculez la VAN à l'aide de la formule XNPV = XNPV (C37, E35: I35, E6: I6) .
10. Dans la cellule B38, calculez l'IRR à l'aide de la formule XIRR = XIRR (E35: I35, E6: I6) .
Ajout de OFFSET à XNPV et XIRR
Nous pouvons passer aux formules XNPV et XIRR pour créer des formules plus dynamiques en utilisant la fonction OFFSET.
11. Dans la cellule B42, modifiez la formule en = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . La formule est plus dynamique car si le nombre de périodes augmente, alors les périodes de cash flow libre augmenteront également. Nous n'avons pas besoin de changer la formule de la VAN si la période de prévision est plus longue. Pour la fonction IRR, changez-la en = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Après avoir ajusté la formule pour le nombre de périodes, nous devrions compenser les dates. Dans la cellule B42, remplacez la formule par = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Cela permet aux formules VAN et TRI de récupérer le bon nombre de flux de trésorerie disponibles avec le changement du nombre de périodes.
Résumé des principales formules d'échéancier de la dette
- Formule PMT pour le calcul du montant du remboursement de la dette: = PMT (taux d'intérêt, nombre de conditions, valeur actuelle)
- Formule IPMT pour le calcul du paiement des intérêts: = IPMT (taux d'intérêt, période, nombre de termes, valeur actuelle)
- Formule XNPV pour trouver la valeur actuelle nette: = XNPV (taux d'actualisation, flux de trésorerie disponibles, dates)
- Formule XIRR pour trouver le taux de rendement interne: = XIRR (free cash flows, dates)
- Formule OFFSET pour le calcul de la VAN dynamique: = XNPV (taux d'actualisation, 1er FCF: OFFSET (1er FCF, 0, # périodes - 1), 1ère date: OFFSET (1ère date, 0, # périodes - 1))
- Formule OFFSET pour le calcul de l'IRR dynamique: = XIRR (1er FCF: OFFSET (1er FCF, 0, # périodes - 1), 1ère date: OFFSET (1ère date, 0, # périodes - 1))
Autres ressources
Merci d'avoir lu le guide Finance sur le calendrier de la dette avec les formules PMT, IPMT et IF. Pour continuer à apprendre et faire progresser votre carrière, les ressources financières suivantes vous seront utiles:
- Formules Excel de base Formules Excel de base La maîtrise des formules Excel de base est essentielle pour que les débutants deviennent compétents en analyse financière. Microsoft Excel est considéré comme le logiciel standard de l'industrie en matière d'analyse de données. Le programme de feuilles de calcul de Microsoft se trouve également être l'un des logiciels les plus préférés des banquiers d'investissement
- Meilleures pratiques en matière de modélisation financière Meilleures pratiques en matière de modélisation financière Cet article vise à fournir aux lecteurs des informations sur les meilleures pratiques en matière de modélisation financière et un guide étape par étape facile à suivre pour créer un modèle financier.
- Liste des fonctions Excel Fonctions Liste des fonctions Excel les plus importantes pour les analystes financiers. Cette feuille de triche couvre des centaines de fonctions qu'il est essentiel de connaître en tant qu'analyste Excel
- Vue d'ensemble des raccourcis Excel Raccourcis Excel Vue d'ensemble Les raccourcis Excel sont une méthode négligée pour augmenter la productivité et la vitesse dans Excel. Les raccourcis Excel offrent à l'analyste financier un outil puissant. Ces raccourcis peuvent remplir de nombreuses fonctions. aussi simple que la navigation dans la feuille de calcul pour remplir des formules ou regrouper des données.
