Η δημιουργία ενός προγράμματος απόσβεσης δανείων στο Excel μπορεί να είναι μια πολύτιμη δεξιότητα, η οποία σας επιτρέπει να απεικονίσετε και να διαχειριστείτε αποτελεσματικά τις αποπληρωμές του δανείου σας. Ένα πρόγραμμα απόσβεσης είναι ένας πίνακας που περιγράφει λεπτομερώς κάθε περιοδική πληρωμή για ένα αποσβεστικό δάνειο (συνήθως ένα στεγαστικό δάνειο ή δάνειο αυτοκινήτου). Αναλύει κάθε πληρωμή σε τόκους και κύρια στοιχεία, εμφανίζοντας το υπόλοιπο μετά από κάθε πληρωμή. Ας βουτήξουμε σε έναν οδηγό βήμα προς βήμα για να δημιουργήσουμε ένα τέτοιο χρονοδιάγραμμα στο Excel.

Τι είναι το πρόγραμμα απόσβεσης;

Δημιουργήστε ένα πρόγραμμα απόσβεσης στο Excel

Δημιουργήστε ένα πρόγραμμα απόσβεσης για μεταβλητό αριθμό περιόδων

Δημιουργήστε ένα πρόγραμμα απόσβεσης με επιπλέον πληρωμές

Δημιουργήστε ένα πρόγραμμα απόσβεσης (με επιπλέον πληρωμές) χρησιμοποιώντας το πρότυπο Excel

Τι είναι το πρόγραμμα απόσβεσης;

Ένα πρόγραμμα απόσβεσης είναι ένας λεπτομερής πίνακας που χρησιμοποιείται στους υπολογισμούς των δανείων που εμφανίζει τη διαδικασία αποπληρωμής ενός δανείου με την πάροδο του χρόνου. Τα προγράμματα απόσβεσης χρησιμοποιούνται συνήθως για δάνεια σταθερού επιτοκίου, όπως στεγαστικά δάνεια, δάνεια αυτοκινήτων και προσωπικά δάνεια, όπου το ποσό πληρωμής παραμένει σταθερό καθ' όλη τη διάρκεια του δανείου, αλλά η αναλογία πληρωμής προς τους τόκους έναντι του κεφαλαίου αλλάζει με την πάροδο του χρόνου.

Για να δημιουργήσετε ένα πρόγραμμα απόσβεσης δανείων στο Excel, πράγματι, οι ενσωματωμένες συναρτήσεις PMT, PPMT και IPMT είναι ζωτικής σημασίας. Ας καταλάβουμε τι κάνει κάθε συνάρτηση:

• Λειτουργία PMT: Αυτή η συνάρτηση χρησιμοποιείται για τον υπολογισμό της συνολικής πληρωμής ανά περίοδο για ένα δάνειο που βασίζεται σε σταθερές πληρωμές και σταθερό επιτόκιο.
• Λειτουργία IPMT: Αυτή η συνάρτηση υπολογίζει το ποσοστό τόκων μιας πληρωμής για μια δεδομένη περίοδο.
• Λειτουργία PPMT: Αυτή η συνάρτηση χρησιμοποιείται για τον υπολογισμό του κεφαλαίου μιας πληρωμής για μια συγκεκριμένη περίοδο.

Χρησιμοποιώντας αυτές τις συναρτήσεις στο Excel, μπορείτε να δημιουργήσετε ένα λεπτομερές πρόγραμμα απόσβεσης που δείχνει τους τόκους και τα κύρια στοιχεία κάθε πληρωμής, μαζί με το υπόλοιπο του δανείου μετά από κάθε πληρωμή.

Δημιουργήστε ένα πρόγραμμα απόσβεσης στο Excel

Σε αυτήν την ενότητα, θα εισαγάγουμε δύο διαφορετικές μεθόδους για τη δημιουργία ενός προγράμματος απόσβεσης στο Excel. Αυτές οι μέθοδοι καλύπτουν διαφορετικές προτιμήσεις χρηστών και επίπεδα δεξιοτήτων, διασφαλίζοντας ότι οποιοσδήποτε, ανεξάρτητα από την επάρκειά του στο Excel, μπορεί να δημιουργήσει με επιτυχία ένα λεπτομερές και ακριβές πρόγραμμα απόσβεσης για το δάνειό του.

Οι τύποι προσφέρουν βαθύτερη κατανόηση των υποκείμενων υπολογισμών και παρέχουν ευελιξία για την προσαρμογή του χρονοδιαγράμματος σύμφωνα με συγκεκριμένες απαιτήσεις. Αυτή η προσέγγιση είναι ιδανική για όσους επιθυμούν να έχουν μια πρακτική εμπειρία και μια σαφή εικόνα του τρόπου με τον οποίο κάθε πληρωμή αναλύεται σε στοιχεία κεφαλαίου και τόκου. Τώρα, ας αναλύσουμε τη διαδικασία δημιουργίας ενός προγράμματος απόσβεσης στο Excel βήμα προς βήμα:

⭐️ Βήμα 1: Ρυθμίστε τις πληροφορίες δανείου και τον πίνακα απόσβεσης

1. Εισαγάγετε τις σχετικές πληροφορίες δανείου, όπως ετήσιο επιτόκιο, διάρκεια δανείου σε έτη, αριθμό πληρωμών ανά έτος και ποσό δανείου στα κελιά, όπως εμφανίζεται το ακόλουθο στιγμιότυπο οθόνης:
   
2. Στη συνέχεια, δημιουργήστε έναν πίνακα απόσβεσης στο Excel με τις καθορισμένες ετικέτες, όπως Περίοδος, Πληρωμή, Τόκος, Κεφάλαιο, Υπόλοιπο στα κελιά A7:E7.
3. Στη στήλη Περίοδος, εισαγάγετε τους αριθμούς περιόδου. Για αυτό το παράδειγμα, ο συνολικός αριθμός πληρωμών είναι 24 μήνες (2 χρόνια), επομένως, θα εισαγάγετε τους αριθμούς 1 έως 24 στη στήλη Περίοδος. Δείτε στιγμιότυπο οθόνης:
   
4. Αφού ρυθμίσετε τον πίνακα με τις ετικέτες και τους αριθμούς περιόδου, μπορείτε να προχωρήσετε στην εισαγωγή τύπων και τιμών για τις στήλες Πληρωμή, Τόκοι, Κεφάλαιο και Υπόλοιπο με βάση τις ιδιαιτερότητες του δανείου σας.

⭐️ Βήμα 2: Υπολογίστε το συνολικό ποσό πληρωμής χρησιμοποιώντας τη συνάρτηση PMT

Η σύνταξη του PMT είναι:

Εισαγάγετε τον ακόλουθο τύπο στο κελί B7 και, στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω για να συμπληρώσετε αυτόν τον τύπο σε άλλα κελιά και θα δείτε ένα σταθερό ποσό πληρωμής για όλες τις περιόδους. Δείτε στιγμιότυπο οθόνης:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Βήμα 3: Υπολογίστε τους τόκους χρησιμοποιώντας τη λειτουργία IPMT

Σε αυτό το βήμα, θα υπολογίσετε τους τόκους για κάθε περίοδο πληρωμής χρησιμοποιώντας τη συνάρτηση IPMT του Excel.

Εισαγάγετε τον ακόλουθο τύπο στο κελί C7 και, στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω στη στήλη για να συμπληρώσετε αυτόν τον τύπο και να λάβετε το ενδιαφέρον για κάθε περίοδο.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Βήμα 4: Υπολογισμός κεφαλαίου χρησιμοποιώντας τη συνάρτηση PPMT

Μετά τον υπολογισμό του τόκου για κάθε περίοδο, το επόμενο βήμα για τη δημιουργία ενός προγράμματος απόσβεσης είναι ο υπολογισμός του κεφαλαίου της κάθε πληρωμής. Αυτό γίνεται χρησιμοποιώντας τη συνάρτηση PPMT, η οποία έχει σχεδιαστεί για να προσδιορίζει το κύριο μέρος μιας πληρωμής για μια συγκεκριμένη περίοδο, με βάση σταθερές πληρωμές και σταθερό επιτόκιο.

Η σύνταξη του IPMT είναι:

Η σύνταξη και οι παράμετροι για τον τύπο PPMT είναι πανομοιότυπες με εκείνες που χρησιμοποιούνται στον τύπο IPMT που συζητήθηκε προηγουμένως.

Εισαγάγετε τον ακόλουθο τύπο στο κελί D7 και, στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω στη στήλη για να συμπληρώσετε την κύρια για κάθε περίοδο. Δείτε στιγμιότυπο οθόνης:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Βήμα 5: Υπολογίστε το υπόλοιπο που απομένει

Αφού υπολογίσετε τόσο τους τόκους όσο και το κεφάλαιο κάθε πληρωμής, το επόμενο βήμα στο πρόγραμμα αποσβέσεών σας είναι να υπολογίσετε το υπόλοιπο του δανείου μετά από κάθε πληρωμή. Αυτό είναι ένα κρίσιμο μέρος του χρονοδιαγράμματος, καθώς δείχνει πώς μειώνεται το υπόλοιπο του δανείου με την πάροδο του χρόνου.

1. Στο πρώτο κελί της στήλης του υπολοίπου σας – E7, εισαγάγετε τον ακόλουθο τύπο, που σημαίνει ότι το υπόλοιπο θα είναι το αρχικό ποσό του δανείου μείον το κεφάλαιο της πρώτης πληρωμής:

   =B4-D7

   
2. Για τη δεύτερη και όλες τις επόμενες περιόδους, υπολογίστε το υπόλοιπο αφαιρώντας το κεφάλαιο της τρέχουσας περιόδου από το υπόλοιπο της προηγούμενης περιόδου. Εφαρμόστε τον ακόλουθο τύπο στο κελί E8:

   =E7-D8

    Note: Η αναφορά στο κελί ισορροπίας πρέπει να είναι σχετική, επομένως ενημερώνεται καθώς σύρετε τον τύπο προς τα κάτω.
   
3. Και μετά σύρετε τη λαβή πλήρωσης προς τα κάτω στη στήλη. Όπως μπορείτε να δείτε, κάθε κελί θα προσαρμόζεται αυτόματα για τον υπολογισμό του υπολοίπου με βάση τις ενημερωμένες πληρωμές κεφαλαίου.
   

⭐️ Βήμα 6: Κάντε μια περίληψη δανείου

Αφού ρυθμίσετε το λεπτομερές πρόγραμμα απόσβεσης, η δημιουργία μιας σύνοψης δανείου μπορεί να παρέχει μια γρήγορη επισκόπηση των βασικών πτυχών του δανείου σας. Αυτή η περίληψη θα περιλαμβάνει συνήθως το συνολικό κόστος του δανείου, τους συνολικούς τόκους που καταβλήθηκαν.

● Για τον υπολογισμό των συνολικών πληρωμών:

=SUM(B7:B30)

● Για τον υπολογισμό του συνολικού τόκου:

=SUM(C7:C30)

⭐️ Αποτέλεσμα:

Τώρα, ένα απλό αλλά ολοκληρωμένο πρόγραμμα απόσβεσης δανείων έχει δημιουργηθεί με επιτυχία. δείτε στιγμιότυπο οθόνης:

Δημιουργήστε ένα πρόγραμμα απόσβεσης για μεταβλητό αριθμό περιόδων

Στο προηγούμενο παράδειγμα, δημιουργούμε ένα χρονοδιάγραμμα αποπληρωμής δανείου για έναν σταθερό αριθμό πληρωμών. Αυτή η προσέγγιση είναι ιδανική για το χειρισμό ενός συγκεκριμένου δανείου ή υποθήκης όπου οι όροι δεν αλλάζουν.

Ωστόσο, εάν θέλετε να δημιουργήσετε ένα ευέλικτο πρόγραμμα απόσβεσης που μπορεί να χρησιμοποιηθεί επανειλημμένα για δάνεια με διαφορετικές περιόδους, επιτρέποντάς σας να τροποποιήσετε τον αριθμό των πληρωμών όπως απαιτείται για διαφορετικά σενάρια δανείων, θα πρέπει να ακολουθήσετε μια πιο λεπτομερή μέθοδο.

⭐️ Βήμα 1: Ρυθμίστε τις πληροφορίες δανείου και τον πίνακα απόσβεσης

1. Εισαγάγετε τις σχετικές πληροφορίες δανείου, όπως ετήσιο επιτόκιο, διάρκεια δανείου σε έτη, αριθμό πληρωμών ανά έτος και ποσό δανείου στα κελιά, όπως εμφανίζεται το ακόλουθο στιγμιότυπο οθόνης:
   
2. Στη συνέχεια, δημιουργήστε έναν πίνακα απόσβεσης στο Excel με τις καθορισμένες ετικέτες, όπως Περίοδος, Πληρωμή, Τόκος, Κεφάλαιο, Υπόλοιπο στα κελιά A7:E7.
3. Στη στήλη Περίοδος, εισαγάγετε τον υψηλότερο αριθμό πληρωμών που μπορείτε να εξετάσετε για οποιοδήποτε δάνειο, για παράδειγμα, συμπληρώστε τους αριθμούς που κυμαίνονται από το 1 έως το 360. Αυτό μπορεί να καλύψει ένα τυπικό δάνειο 30 ετών, εάν κάνετε μηνιαίες πληρωμές.
   

⭐️ Βήμα 2: Τροποποιήστε τους τύπους πληρωμής, τόκων και βασικών τύπων με τη συνάρτηση IF

Εισαγάγετε τους ακόλουθους τύπους στα αντίστοιχα κελιά και, στη συνέχεια, σύρετε τη λαβή πλήρωσης για να επεκτείνετε αυτούς τους τύπους στον μέγιστο αριθμό περιόδων πληρωμής που έχετε ορίσει.

● Τύπος πληρωμής:

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

Οπότε οι τύποι είναι οι εξής:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Τύπος τόκου:

Ο συντακτικός τύπος είναι:

Οπότε οι τύποι είναι οι εξής:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Βασικός τύπος:

Ο συντακτικός τύπος είναι:

Οπότε οι τύποι είναι οι εξής:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Βήμα 3: Προσαρμόστε τον τύπο υπολοίπου που απομένει

Για το υπόλοιπο, μπορείτε συνήθως να αφαιρέσετε το κεφάλαιο από το προηγούμενο υπόλοιπο. Με μια δήλωση IF, τροποποιήστε την ως εξής:

● Το πρώτο κελί ισορροπίας:(E7)

=B4-D7

● Το δεύτερο κελί ισορροπίας:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Βήμα 4: Κάντε μια περίληψη δανείου

Αφού ρυθμίσετε το πρόγραμμα απόσβεσης με τους τροποποιημένους τύπους, το επόμενο βήμα είναι να δημιουργήσετε μια περίληψη δανείου.

● Για τον υπολογισμό των συνολικών πληρωμών:

=SUM(B7:B366)

● Για τον υπολογισμό του συνολικού τόκου:

=SUM(C7:C366)

⭐️ Αποτέλεσμα:

Τώρα, έχετε ένα ολοκληρωμένο και δυναμικό πρόγραμμα απόσβεσης στο Excel, πλήρες με μια λεπτομερή περίληψη δανείου. Κάθε φορά που προσαρμόζετε τη διάρκεια της περιόδου πληρωμής, ολόκληρο το πρόγραμμα απόσβεσης θα ενημερώνεται αυτόματα για να αντικατοπτρίζει αυτές τις αλλαγές. Δείτε το demo παρακάτω:

Δημιουργήστε ένα πρόγραμμα απόσβεσης με επιπλέον πληρωμές

Κάνοντας πρόσθετες πληρωμές πέρα ​​από τις προγραμματισμένες, ένα δάνειο μπορεί να αποπληρωθεί πιο γρήγορα. Ένα χρονοδιάγραμμα απόσβεσης που ενσωματώνει επιπλέον πληρωμές, όταν δημιουργείται στο Excel, δείχνει πώς αυτές οι πρόσθετες πληρωμές μπορούν να επιταχύνουν την αποπληρωμή του δανείου και να μειώσουν τους συνολικούς τόκους που καταβάλλονται. Δείτε πώς μπορείτε να το ρυθμίσετε:

⭐️ Βήμα 1: Ρυθμίστε τις πληροφορίες δανείου και τον πίνακα απόσβεσης

1. Εισαγάγετε τις σχετικές πληροφορίες δανείου, όπως ετήσιο επιτόκιο, διάρκεια δανείου σε έτη, αριθμό πληρωμών ανά έτος, ποσό δανείου και επιπλέον πληρωμή στα κελιά, όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης:
   
2. Στη συνέχεια, υπολογίστε την προγραμματισμένη πληρωμή.
   Εκτός από τα κελιά εισόδου, χρειάζεται ένα άλλο προκαθορισμένο κελί για τους επόμενους υπολογισμούς μας - το προγραμματισμένο ποσό πληρωμής. Αυτό είναι το κανονικό ποσό πληρωμής ενός δανείου με την προϋπόθεση ότι δεν πραγματοποιούνται πρόσθετες πληρωμές. Εφαρμόστε τον ακόλουθο τύπο στο κελί B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Στη συνέχεια, δημιουργήστε έναν πίνακα απόσβεσης στο Excel:
   • Ορίστε τις καθορισμένες ετικέτες, όπως Περίοδος, Προγραμματισμός πληρωμής, Επιπλέον πληρωμή, Συνολική πληρωμή, Τόκοι, Κεφάλαιο, Υπόλοιπο στα κελιά A8:G8;
   • Στη στήλη Περίοδος, εισαγάγετε τον μεγαλύτερο αριθμό πληρωμών που μπορείτε να εξετάσετε για οποιοδήποτε δάνειο. Για παράδειγμα, συμπληρώστε τους αριθμούς που κυμαίνονται από το 0 έως το 360. Αυτό μπορεί να καλύψει ένα τυπικό δάνειο 30 ετών εάν κάνετε μηνιαίες πληρωμές.
   • Για την Περίοδο 0 (σειρά 9 στην περίπτωσή μας), ανακτήστε την τιμή Υπόλοιπο με αυτόν τον τύπο = B4, που αντιστοιχεί στο αρχικό ποσό του δανείου. Όλα τα άλλα κελιά σε αυτήν τη σειρά πρέπει να μείνουν κενά.
   • 

⭐️ Βήμα 2: Δημιουργήστε τους τύπους για το πρόγραμμα αποσβέσεων με επιπλέον πληρωμές

Εισαγάγετε έναν προς έναν τους παρακάτω τύπους στα αντίστοιχα κελιά. Για να βελτιώσουμε τη διαχείριση σφαλμάτων, ενσωματώνουμε αυτόν και όλους τους μελλοντικούς τύπους στη συνάρτηση IFERROR. Αυτή η προσέγγιση βοηθά στην αποφυγή πολλαπλών πιθανών σφαλμάτων που θα μπορούσαν να προκύψουν εάν κάποιο από τα κελιά εισόδου παραμείνει κενό ή διατηρεί εσφαλμένες τιμές.

● Υπολογίστε την προγραμματισμένη πληρωμή:

Εισαγάγετε τον ακόλουθο τύπο στο κελί B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Υπολογίστε την επιπλέον πληρωμή:

Εισαγάγετε τον ακόλουθο τύπο στο κελί C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Υπολογίστε τη συνολική πληρωμή:

Εισαγάγετε τον ακόλουθο τύπο στο κελί D10:

=IFERROR(B10+C10, "")

● Υπολογίστε την κύρια:

Εισαγάγετε τον ακόλουθο τύπο στο κελί E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Υπολογίστε τους τόκους:

Εισαγάγετε τον ακόλουθο τύπο στο κελί F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Υπολογίστε το υπόλοιπο που απομένει

Εισαγάγετε τον ακόλουθο τύπο στο κελί G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Αφού ολοκληρώσετε κάθε έναν από τους τύπους, επιλέξτε το εύρος κελιών B10:G10 και χρησιμοποιήστε τη λαβή πλήρωσης για να σύρετε και να επεκτείνετε αυτούς τους τύπους προς τα κάτω σε ολόκληρο το σύνολο των περιόδων πληρωμής. Για οποιεσδήποτε περιόδους που δεν χρησιμοποιούνται, τα κελιά θα δείχνουν 0. Δείτε στιγμιότυπο οθόνης:

⭐️ Βήμα 3: Κάντε μια περίληψη δανείου

● Λάβετε προγραμματισμένο αριθμό πληρωμών:

=B2:B3

● Λάβετε τον πραγματικό αριθμό πληρωμών:

=COUNTIF(D10:D369,">"&0)

● Λάβετε συνολικές επιπλέον πληρωμές:

=SUM(C10:C369)

● Λάβετε συνολικό ενδιαφέρον:

=SUM(F10:F369)

⭐️ Αποτέλεσμα:

Ακολουθώντας αυτά τα βήματα, δημιουργείτε ένα δυναμικό πρόγραμμα απόσβεσης στο Excel που λαμβάνει υπόψη τις επιπλέον πληρωμές.

Δημιουργήστε ένα πρόγραμμα απόσβεσης χρησιμοποιώντας το πρότυπο Excel

Η δημιουργία ενός προγράμματος απόσβεσης στο Excel χρησιμοποιώντας ένα πρότυπο είναι μια απλή και χρονικά αποδοτική μέθοδος. Το Excel παρέχει ενσωματωμένα πρότυπα που υπολογίζουν αυτόματα τους τόκους, το κεφάλαιο και το υπόλοιπο κάθε πληρωμής. Δείτε πώς μπορείτε να δημιουργήσετε ένα πρόγραμμα απόσβεσης χρησιμοποιώντας ένα πρότυπο Excel:

1. Πατήστε Αρχεία > Νέα, στο πλαίσιο αναζήτησης, πληκτρολογήστε χρονοδιάγραμμα απόσβεσης, και πατήστε εισάγετε κλειδί. Στη συνέχεια, επιλέξτε το πρότυπο που ταιριάζει καλύτερα στις ανάγκες σας κάνοντας κλικ σε αυτό. Για παράδειγμα, εδώ, θα επιλέξω πρότυπο Απλή αριθμομηχανή δανείου. Δείτε στιγμιότυπο οθόνης:
   
2. Αφού επιλέξετε ένα πρότυπο, κάντε κλικ στο Δημιουργία κουμπί για να το ανοίξετε ως νέο βιβλίο εργασίας.
3. Στη συνέχεια, εισαγάγετε τα δικά σας στοιχεία δανείου, το πρότυπο θα πρέπει να υπολογίζει αυτόματα και να συμπληρώνει το χρονοδιάγραμμα με βάση τις εισροές σας.
4. Επιτέλους, αποθηκεύστε το νέο βιβλίο εργασίας του προγράμματος απόσβεσης.