Προγραμματισμός επιστροφών δανείων με τύπους Excel
Η αποπληρωμή δανείου είναι η πράξη αποπληρωμής χρημάτων που έχουν προηγουμένως δανειστεί από δανειστή, συνήθως μέσω μιας σειράς περιοδικών πληρωμών που περιλαμβάνουν κεφάλαιο συν τόκους. Γνωρίζατε ότι μπορείτε να χρησιμοποιήσετε το πρόγραμμα λογισμικού Excel για να υπολογίσετε τις αποπληρωμές δανείων σας;
Αυτό το άρθρο είναι ένας οδηγός βήμα προς βήμα για τη δημιουργία υπολογισμών δανείου.
Βασικές τακτικές
- Χρησιμοποιήστε το Excel για να πάρετε μια λαβή για την υποθήκη σας, καθορίζοντας τη μηνιαία πληρωμή σας, το επιτόκιο σας και το χρονοδιάγραμμα των δανείων σας.
- Μπορείτε να πάρετε μια πιο εμπεριστατωμένη ματιά στην κατανομή του δανείου με το Excel και να δημιουργήσετε ένα πρόγραμμα αποπληρωμής που λειτουργεί για σας.
- Υπάρχουν υπολογισμοί για κάθε βήμα που μπορείτε να τσιμπήσετε για να καλύψετε τις συγκεκριμένες ανάγκες σας.
- Η κατάργηση και η εξέταση του δανείου σας βήμα-βήμα μπορεί να κάνει τη διαδικασία αποπληρωμής να αισθάνεται λιγότερο συντριπτική και πιο εύχρηστη.
Κατανόηση της υποθήκης σας
Χρησιμοποιώντας το Excel, μπορείτε να έχετε καλύτερη κατανόηση της υποθήκης σας σε τρία απλά βήματα. Το πρώτο βήμα καθορίζει τη μηνιαία πληρωμή. Το δεύτερο βήμα υπολογίζει το επιτόκιο και το τρίτο βήμα καθορίζει το χρονοδιάγραμμα δανεισμού.
Μπορείτε να δημιουργήσετε έναν πίνακα στο Excel που θα σας ενημερώσει για το επιτόκιο, τον υπολογισμό του δανείου για τη διάρκεια του δανείου, την αποσύνθεση του δανείου, την απόσβεση και τη μηνιαία πληρωμή.
Υπολογίστε τη μηνιαία πληρωμή
Πρώτον, μπορείτε να υπολογίσετε τη μηνιαία πληρωμή για μια υποθήκη. Χρησιμοποιώντας το ετήσιο επιτόκιο, τον κύριο και τη διάρκεια, μπορούμε να καθορίσουμε το ποσό που πρέπει να επιστραφεί μηνιαίως.
Ο τύπος, όπως φαίνεται στο παραπάνω στιγμιότυπο οθόνης, γράφεται ως εξής:
= -PMT (ποσοστό, μήκος, τρέχουσα τιμή, μελλοντική τιμή, τύπος)
Το σύμβολο μείον μπροστά από το PMT είναι απαραίτητο καθώς ο τύπος επιστρέφει έναν αρνητικό αριθμό. Τα πρώτα τρία επιχειρήματα είναι το ποσοστό του δανείου, το μήκος του δανείου (αριθμός περιόδων) και το κεφάλαιο που δανείστηκε. Τα δύο τελευταία επιχειρήματα είναι προαιρετικά, η υπολειμματική τιμή είναι μηδενική. πληρωτέα εκ των προτέρων (για ένα) ή στο τέλος (για μηδέν), είναι επίσης προαιρετική.
Ο τύπος Excel που χρησιμοποιείται για τον υπολογισμό της μηνιαίας πληρωμής του δανείου είναι:
= -PMT ((1 + Β2) ^ (1/12) -1 · Β4 * 12 · Β3) = PMT (1 + 3, 10%) (1/12) -1 · 10 * 12 · 120000)
Επεξήγηση: Για το επιτόκιο χρησιμοποιούμε το μηνιαίο επιτόκιο (περίοδος επιτοκίου), τότε υπολογίζουμε τον αριθμό των περιόδων (120 για 10 χρόνια πολλαπλασιασμένους επί 12 μήνες) και, τέλος, αναφέρουμε τον κύριο δανεισμό. Η μηνιαία πληρωμή μας θα είναι $ 1.161, 88 σε διάστημα 10 ετών.
Υπολογίστε το ετήσιο επιτόκιο
Έχουμε δει πώς να ρυθμίσετε τον υπολογισμό μιας μηνιαίας πληρωμής για μια υποθήκη. Αλλά ίσως να θέλουμε να ορίσουμε μια μέγιστη μηνιαία πληρωμή που μπορούμε να αντέξουμε, η οποία να δείχνει επίσης τον αριθμό των ετών κατά τα οποία θα έπρεπε να επιστρέψουμε το δάνειο. Για το λόγο αυτό, θα θέλαμε να γνωρίζουμε το αντίστοιχο ετήσιο επιτόκιο.
Όπως φαίνεται στο παραπάνω στιγμιότυπο οθόνης, υπολογίζουμε πρώτα τον ρυθμό περιόδου (μηνιαίως, στην περίπτωσή μας) και στη συνέχεια τον ετήσιο ρυθμό. Ο τύπος που χρησιμοποιείται θα είναι RATE, όπως φαίνεται στο παραπάνω στιγμιότυπο οθόνης. Είναι γραμμένο ως εξής:
= RATE (Nper; pmt, present_value, [future_value], [type])
Τα τρία πρώτα επιχειρήματα είναι η διάρκεια του δανείου (αριθμός περιόδων), η μηνιαία πληρωμή για την αποπληρωμή του δανείου και το κεφάλαιο που δανείστηκε. Τα τελευταία τρία επιχειρήματα είναι προαιρετικά και η υπολειμματική τιμή είναι μηδενική. ο όρος όρο για τη διαχείριση της προθεσμίας λήξης εκ των προτέρων (για ένα) ή στο τέλος (για το μηδέν) είναι επίσης προαιρετικός. Τέλος, το επιχείρημα της εκτίμησης είναι προαιρετικό, αλλά μπορεί να δώσει μια αρχική εκτίμηση του επιτοκίου.
Ο τύπος Excel που χρησιμοποιείται για τον υπολογισμό του επιτοκίου δανεισμού είναι:
= ΚΩΔΙΚΟΣ (12 * Β4, -Β2, Β3) = ΚΩΔΙΚΟΣ (12 * 13, -960, 120000)
Σημείωση: τα αντίστοιχα δεδομένα στη μηνιαία πληρωμή πρέπει να έχουν αρνητικό σημάδι. Αυτός είναι ο λόγος για τον οποίο υπάρχει ένα σημάδι μείον πριν από τη φόρμουλα. Η περίοδος του ποσοστού είναι 0, 294%.
Χρησιμοποιούμε τον τύπο = (1 + B5) είναι 12-1 ^ = (1 + 0, 294%) ^ 12-1 για να λάβουμε το ετήσιο επιτόκιο του δανείου μας, το οποίο είναι 3, 58%. Με άλλα λόγια, για να δανειστείτε 120.000 δολάρια για 13 χρόνια για να πληρώσετε 960 δολάρια μηνιαίως, θα πρέπει να διαπραγματευτούμε ένα δάνειο με ετήσιο μέγιστο επιτόκιο 3, 58%.
Η χρήση του Excel είναι ένας πολύ καλός τρόπος για να παρακολουθείτε τι χρωστάτε και να καταλήξετε σε ένα χρονοδιάγραμμα για την αποπληρωμή που ελαχιστοποιεί τις αμοιβές που μπορεί να καταλήξετε.
Προσδιορισμός του μήκους ενός δανείου
Θα δούμε τώρα πώς να καθορίσετε τη διάρκεια ενός δανείου όταν γνωρίζετε την ετήσια ισοτιμία, τον κύριο δανεισμό και τη μηνιαία πληρωμή που πρόκειται να επιστραφεί. Με άλλα λόγια, πόσο καιρό θα χρειαστεί να εξοφλήσουμε μια υποθήκη αξίας 120.000 δολαρίων με επιτόκιο 3.10% και μηνιαία πληρωμή $ 1.100 ">
Ο τύπος που θα χρησιμοποιήσουμε είναι NPER, όπως φαίνεται στο παραπάνω στιγμιότυπο οθόνης και είναι γραμμένος ως εξής:
= NPER (ποσοστό, pmt, present_value, [future_value], [type])
Τα τρία πρώτα επιχειρήματα είναι ο ετήσιος ρυθμός του δανείου, η μηνιαία πληρωμή που απαιτείται για την αποπληρωμή του δανείου και το κεφάλαιο που δανείστηκε. Τα δύο τελευταία επιχειρήματα είναι προαιρετικά, η υπολειπόμενη τιμή είναι μηδενική. Ο όρος "προκαταβολή" πληρωτέα εκ των προτέρων (για ένα) ή στο τέλος (για το μηδέν) είναι επίσης προαιρετική.
= NPER ((1 + Β2) ^ (1/12) -1, -Β4, Β3) = NPER ((1 + 3, 10%
Σημείωση: τα αντίστοιχα δεδομένα στη μηνιαία πληρωμή πρέπει να έχουν αρνητικό σημάδι. Αυτός είναι ο λόγος που έχουμε ένα αρνητικό πρόσημο πριν από τη φόρμουλα. Το ποσό αποζημίωσης ανέρχεται σε 127, 97 περιόδους (μήνες στην περίπτωσή μας).
Θα χρησιμοποιήσουμε τον τύπο = B5 / 12 = 127.97 / 12 για τον αριθμό των ετών για να ολοκληρωθεί η αποπληρωμή του δανείου. Με άλλα λόγια, για να δανειστείτε 120.000 δολάρια, με ετήσιο επιτόκιο 3.10% και να πληρώσετε 1.100 δολάρια μηνιαίως, θα πρέπει να εξοφλήσουμε τις διάρκειες για 128 μήνες ή 10 χρόνια και 8 μήνες.
Αποσύνθεση του δανείου
Η πληρωμή του δανείου αποτελείται από το κεφάλαιο και τους τόκους. Οι τόκοι υπολογίζονται για κάθε περίοδο - για παράδειγμα, οι μηνιαίες αποπληρωμές σε διάστημα 10 ετών θα μας δώσουν 120 περιόδους.
Ο ανωτέρω πίνακας δείχνει την κατανομή του δανείου (συνολική περίοδος ίση με 120) χρησιμοποιώντας τους τύπους PPMT και IPMT. Τα επιχειρήματα των δύο τύπων είναι τα ίδια και κατανέμονται ως εξής:
= -PPMT (ποσοστό, αριθμός_περίοδος, μήκος, κύριο, [υπόλοιπο], [όρος])
Τα επιχειρήματα είναι τα ίδια όπως και για τον τύπο PMT που ήδη παρατηρήθηκε, εκτός από την "num_period", η οποία προστίθεται για να δείξει την περίοδο κατά την οποία θα κατανεμηθεί το δάνειο λαμβάνοντας υπόψη το κεφάλαιο και το ενδιαφέρον. Ακολουθεί ένα παράδειγμα:
= -PPMT ((1 + Β2) ^ (1/12) -1 · 1 · Β4 * 12 · Β3) = ΡΡΜΤ (1 + 3, 10% 12, 120000)
Το αποτέλεσμα εμφανίζεται στο στιγμιότυπο οθόνης "Αποσύνθεση δανείου" κατά τη διάρκεια της περιόδου που αναλύθηκε, η οποία είναι "μία". δηλαδή την πρώτη περίοδο ή τον πρώτο μήνα. Καταβάλλουμε 1.161, 88 δολάρια που κατανέμονται σε κεφάλαιο 856, 20 δολ. Και τόκο 305, 68 δολαρίων.
Υπολογισμός δανείου στο Excel
Είναι επίσης δυνατό να υπολογίσετε την επιστροφή του κεφαλαίου και των τόκων για αρκετές περιόδους, όπως τους πρώτους 12 μήνες ή τους πρώτους 15 μήνες.
= -CUMPRINC (ποσοστό, μήκος, αρχικό, start_date, end_date, τύπος)
Βρίσκουμε τα επιχειρήματα, το ρυθμό, το μήκος, τον κύριο και τον όρο (που είναι υποχρεωτικά) που είδαμε ήδη στο πρώτο μέρος με τον τύπο PMT. Αλλά εδώ, χρειαζόμαστε επίσης τα επιχειρήματα "start_date" και "end_date". Το "start_date" υποδηλώνει την αρχή της περιόδου που πρόκειται να αναλυθεί και η "end_date" υποδεικνύει το τέλος της προς ανάλυση περιόδου.
Ακολουθεί ένα παράδειγμα:
= -CUMPRINC ((1 + Β2) ^ (1/12) -1 · Β4 * 12 · Β3 · 1 · 12 · 0)
Το αποτέλεσμα εμφανίζεται στο στιγμιότυπο "Cumul 1st year", έτσι ώστε οι αναλυόμενες περίοδοι κυμαίνονται από το ένα έως το 12 της πρώτης περιόδου (πρώτος μήνας) έως το δωδέκατο (12ος) μήνα. Πάνω από ένα χρόνο, θα πληρώναμε 10.419, 55 δολάρια σε κεφάλαιο και 3.522.99 δολάρια σε τόκους.
Αποσβέσεις του δανείου
Οι προηγούμενες φόρμουλες μας επιτρέπουν να δημιουργούμε την περίοδο προγραμματισμού ανά περίοδο, να γνωρίζουμε πόσο θα πληρώνουμε μηνιαίως σε κεφάλαιο και ενδιαφέρον και να γνωρίζουμε πόσα χρήματα πρέπει να πληρώσετε.
Δημιουργία Προγράμματος Δανείου
Για να δημιουργήσετε ένα χρονοδιάγραμμα δανείων, θα χρησιμοποιήσουμε τους διάφορους τύπους που αναφέρθηκαν παραπάνω και θα τα επεκτείνουμε στον αριθμό των περιόδων.
Στη στήλη της πρώτης περιόδου, εισάγετε "1" ως την πρώτη περίοδο και, στη συνέχεια, σύρετε το κελί κάτω. Στην περίπτωσή μας, χρειαζόμαστε 120 περιόδους, δεδομένου ότι μια πληρωμή δανείου διάρκειας 10 ετών πολλαπλασιασμένη με 12 μήνες ισούται με 120.
Η δεύτερη στήλη είναι το μηνιαίο ποσό που πρέπει να πληρώσουμε κάθε μήνα - το οποίο είναι σταθερό για όλο το χρονοδιάγραμμα δανεισμού. Για να υπολογίσετε το ποσό, εισαγάγετε τον ακόλουθο τύπο στο κελί της πρώτης περιόδου:
= -ΡΜΤ (ΤΡ-1 · Β4 * 12 · Β3) = -ΡΜΤ ((1 + 3, 10%) ^ (1/12) -1 · 10 * 12 · 120000)
Η τρίτη στήλη είναι ο κύριος λογαριασμός που θα εξοφληθεί κάθε μήνα. Για παράδειγμα, για την 40η περίοδο, θα πληρώσουμε 945, 51 δολάρια κατ 'αρχήν για το συνολικό μας μηνιαίο ποσό των 1.161, 88 δολαρίων.
Για να υπολογίσουμε το βασικό ποσό που εξαργυρώσαμε, χρησιμοποιούμε τον ακόλουθο τύπο:
= -PPMT (1, 3, 10%), (1/12), 1, 10 * 12, 120000)
Η τέταρτη στήλη είναι το επιτόκιο, για το οποίο χρησιμοποιούμε τον τύπο για να υπολογίσουμε την επιστροφή του αρχικού κεφαλαίου στο μηνιαίο μας ποσό, για να ανακαλύψουμε το ύψος των τόκων που πρέπει να καταβληθούν:
= -INTPER (TP · A18 · $ B $ 4 * 12 · $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12) · 1 · 10 * 12 · 120000)
Η πέμπτη στήλη περιέχει το ποσό που απομένει να πληρώσει. Για παράδειγμα, μετά την 40η πληρωμή, θα πρέπει να πληρώσουμε $ 83.994, 69 με $ 120.000.
Ο τύπος έχει ως εξής:
= $ B $ 3 + CUMPRINC (TP · $ B $ 4 * 12 · $ B $ 3 · 1 · A18 · 0)
Ο τύπος χρησιμοποιεί έναν συνδυασμό κεφαλαίου σε μια περίοδο μπροστά από το κελί που περιέχει τον κύριο δανεισμό. Αυτή η περίοδος αρχίζει να αλλάζει όταν αντιγράφετε και σύρετε το κελί κάτω. Ο παρακάτω πίνακας δείχνει ότι στο τέλος 120 περιόδων το δάνειο μας επιστρέφεται.