Πρόγραμμα χρεών με τύπους PMT, IPMT & IF - Οδηγός και παραδείγματα

Μπορούμε να χρησιμοποιήσουμε τους τύπους PMT, IPMT και IF του Excel για να δημιουργήσουμε ένα πρόγραμμα χρεών. Πρώτον, πρέπει να ρυθμίσουμε το μοντέλο εισάγοντας κάποιες παραδοχές χρέους. Σε αυτό το παράδειγμα, υποθέτουμε ότι το χρέος είναι 5.000.000 $, ο όρος πληρωμής είναι 5 χρόνια και το επιτόκιο Επιτόκιο Το επιτόκιο αναφέρεται στο ποσό που χρεώνει ο δανειστής σε δανειολήπτη για οποιαδήποτε μορφή δανεισμού, γενικά εκφραζόμενη ως ποσοστό του κεφαλαίου. να είναι 4,5%.

1. Το εναρκτήριο υπόλοιπο στο πρόγραμμα χρέους μας είναι ίσο με το ποσό δανείου των 5 εκατομμυρίων δολαρίων, οπότε στο κελί E29 εισάγουμε = B25 για να το συνδέσουμε με την είσοδο της υπόθεσης. Στη συνέχεια, μπορούμε να χρησιμοποιήσουμε τον τύπο PMT για να υπολογίσουμε τη συνολική πληρωμή για την πρώτη περίοδο = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Ο τύπος υπολογίζει το ποσό πληρωμής χρησιμοποιώντας το ποσό δανείου, τον όρο και το επιτόκιο που αναφέρονται στην ενότητα υπόθεσης.

Χρονοδιάγραμμα χρεών

2. Στο κελί E28, εισαγάγετε την περίοδο στην οποία βρισκόμαστε, δηλαδή 1. Στο κελί E29, εισαγάγετε = E28 + 1 και συμπληρώστε τον τύπο προς τα δεξιά. Στη συνέχεια, χρησιμοποιήστε τον τύπο IPMT για να μάθετε την πληρωμή τόκων για την πρώτη περίοδο = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .

3. Η κύρια πληρωμή είναι η διαφορά μεταξύ της συνολικής πληρωμής και της πληρωμής τόκων, που είναι = E30-E31 . Το υπόλοιπο κλεισίματος είναι το υπόλοιπο ανοίγματος συν η κύρια πληρωμή που πραγματοποιείται, δηλαδή = E29 + E32 . Το υπόλοιπο έναρξης για την περίοδο 2 είναι το υπόλοιπο κλεισίματος για την περίοδο 1, που είναι = E33 .

4. Αντιγράψτε όλους τους τύπους από το κελί E29 έως E33 στην επόμενη στήλη και, στη συνέχεια, αντιγράψτε τα πάντα προς τα δεξιά. Ελέγξτε εάν το υπόλοιπο κλεισίματος για την περίοδο 5 = 0 για να βεβαιωθείτε ότι χρησιμοποιούνται σωστοί τύποι και αριθμοί.

5. Παρατηρήστε ότι υπάρχουν μερικά μηνύματα σφάλματος που ξεκινούν από την περίοδο 6, επειδή το υπόλοιπο ανοίγματος είναι 0. Εδώ μπορούμε να χρησιμοποιήσουμε τη λειτουργία IF για να καθαρίσουμε τα σφάλματα. Στο κελί E30, πληκτρολογήστε = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Ο τύπος δηλώνει ότι εάν το εναρκτήριο υπόλοιπο είναι μικρότερο από 0, τότε η συνολική αξία πληρωμής θα εμφανίζεται ως 0.

6. Στο κελί 31, πληκτρολογήστε = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Αυτός ο τύπος είναι παρόμοιος με τον προηγούμενο, ο οποίος δηλώνει ότι εάν το υπόλοιπο ανοίγματος είναι μικρότερο από 0, τότε η πληρωμή τόκων θα εμφανίζεται ως 0.

7. Αντιγράψτε τα κελιά E30 και E31, πατήστε SHIFT + δεξί βέλος και στη συνέχεια CTRL + R για να γεμίσετε δεξιά. Θα πρέπει να δείτε ότι όλα τα μηνύματα σφάλματος εμφανίζονται τώρα ως 0.

XNPV και XIRR με συναρτήσεις DATE και IF

Μπορούμε να υπολογίσουμε το NPV και το IRR με βάση συγκεκριμένες ημερομηνίες χρησιμοποιώντας τις συναρτήσεις Excel XNPV και XIRR με τις συναρτήσεις DATE και IF.

8. Μεταβείτε στο κελί E6 και εισαγάγετε = DATE (E5,12,31) για να εμφανιστεί η ημερομηνία. Αντιγράψτε προς τα δεξιά. Θα δείτε την # ΑΞΙΑ! μήνυμα μετά το 2021. Μπορούμε να το διορθώσουμε χρησιμοποιώντας τη συνάρτηση IFERROR = IFERROR (ΗΜΕΡΟΜΗΝΙΑ (E5,12,31), ””) .

9. Τώρα μπορούμε να αρχίσουμε να υπολογίζουμε το NPV και το IRR. Κατ 'αρχάς, πρέπει να εισαγάγουμε τα δωρεάν ποσά ταμειακών ροών. Υποθέτουμε ότι τα ποσά FCF από την περίοδο 1 έως 5 είναι -1.000, 500, 600, 700, 900. Στο κελί C37, θα εισάγουμε προεξοφλητικό επιτόκιο 15%. Στο κελί B37, υπολογίστε το NPV χρησιμοποιώντας τον τύπο XNPV = XNPV (C37, E35: I35, E6: I6) .

10. Στο κελί B38, υπολογίστε το IRR χρησιμοποιώντας τον τύπο XIRR = XIRR (E35: I35, E6: I6) .

Προσθήκη OFFSET σε XNPV και XIRR

Μπορούμε να αλλάξουμε στους τύπους XNPV και XIRR για να κάνουμε πιο δυναμικούς τύπους χρησιμοποιώντας τη συνάρτηση OFFSET.

11. Στο κελί B42, αλλάξτε τον τύπο σε = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Ο τύπος είναι πιο δυναμικός γιατί αν αυξηθεί ο αριθμός των περιόδων, τότε οι περίοδοι της ελεύθερης ταμειακής ροής θα αυξηθούν επίσης. Δεν χρειάζεται να αλλάξουμε τον τύπο NPV εάν η περίοδος πρόβλεψης είναι μεγαλύτερη. Για τη συνάρτηση IRR, αλλάξτε την σε = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .

12. Αφού προσαρμόσουμε τον τύπο για τον αριθμό των περιόδων, θα πρέπει να αντισταθμίσουμε τις ημερομηνίες. Στο κελί B42, αλλάξτε τον τύπο σε = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Αυτό επιτρέπει στους τύπους NPV και IRR να λαμβάνουν τον σωστό αριθμό ελεύθερων ταμειακών ροών με την αλλαγή στον αριθμό περιόδων.

Σύνοψη των βασικών τύπων χρονοδιαγράμματος χρέους

  • Τύπος PMT για τον υπολογισμό του ποσού πληρωμής χρέους: = PMT (επιτόκιο, αριθμός όρων, παρούσα αξία)
  • Τύπος IPMT για τον υπολογισμό της πληρωμής τόκων: = IPMT (επιτόκιο, περίοδος, αριθμός όρων, παρούσα αξία)
  • XNPV τύπος για την εύρεση της καθαρής παρούσας αξίας: = XNPV (προεξοφλητικό επιτόκιο, δωρεάν ταμειακές ροές, ημερομηνίες)
  • Τύπος XIRR για εύρεση του εσωτερικού ποσοστού απόδοσης: = XIRR (δωρεάν ταμειακές ροές, ημερομηνίες)
  • Τύπος OFFSET για τον υπολογισμό δυναμικού NPV: = XNPV (προεξοφλητικό επιτόκιο, 1ο FCF: OFFSET (1ο FCF, 0, # περίοδοι - 1), 1η ημερομηνία: OFFSET (1η ημερομηνία, 0, # περίοδοι - 1)
  • Τύπος OFFSET για τον υπολογισμό δυναμικού IRR: = XIRR (1ο FCF: OFFSET (1ο FCF, 0, # περίοδοι - 1), 1η ημερομηνία: OFFSET (1η ημερομηνία, 0, # περίοδοι - 1))

Άλλοι πόροι

Σας ευχαριστούμε που διαβάσατε τον οδηγό Finance σχετικά με το Χρέος με τους τύπους PMT, IPMT και IF. Για να συνεχίσετε να μαθαίνετε και να προωθείτε την καριέρα σας, οι ακόλουθοι πόροι χρηματοδότησης θα είναι χρήσιμοι:

  • Βασικοί τύποι Excel Βασικοί τύποι Excel Η εξάσκηση βασικών τύπων Excel είναι ζωτικής σημασίας για τους αρχάριους να είναι ικανοί στην οικονομική ανάλυση. Το Microsoft Excel θεωρείται το βιομηχανικό πρότυπο λογισμικού στην ανάλυση δεδομένων. Το πρόγραμμα υπολογιστικών φύλλων της Microsoft τυχαίνει επίσης να είναι ένα από τα πιο προτιμώμενα λογισμικά από επενδυτές τραπεζίτες
  • Βέλτιστες Πρακτικές Χρηματοοικονομικής Μοντελοποίησης Βέλτιστες Πρακτικές Χρηματοοικονομικής Μοντελοποίησης Αυτό το άρθρο είναι να παρέχει στους αναγνώστες πληροφορίες σχετικά με τις βέλτιστες πρακτικές χρηματοοικονομικής μοντελοποίησης και έναν εύκολο να ακολουθηθεί, βήμα προς βήμα οδηγό για τη δημιουργία ενός οικονομικού μοντέλου.
  • Λίστα λειτουργιών του Excel Λειτουργίες Λίστα των πιο σημαντικών συναρτήσεων του Excel για οικονομικούς αναλυτές. Αυτό το φύλλο εξαπάτησης καλύπτει 100 δευτερόλεπτα συναρτήσεων που είναι πολύ σημαντικό να γνωρίζουμε ως αναλυτής του Excel
  • Επισκόπηση των συντομεύσεων του Excel Συντομεύσεις του Excel Επισκόπηση Οι συντομεύσεις του Excel είναι μια μέθοδος που παραβλέπεται για την αύξηση της παραγωγικότητας και της ταχύτητας στο Excel. Οι συντομεύσεις Excel προσφέρουν στον οικονομικό αναλυτή ένα ισχυρό εργαλείο. Αυτές οι συντομεύσεις μπορούν να εκτελέσουν πολλές λειτουργίες. τόσο απλή όσο η πλοήγηση στο υπολογιστικό φύλλο για τη συμπλήρωση τύπων ή ομαδοποίησης δεδομένων.

Πρόσφατες δημοσιεύσεις