Γνωρίζατε ότι μπορείτε να χρησιμοποιήσετε το Excel για να υπολογίσετε τις αποπληρωμές δανείων σας; Αυτό το άρθρο θα σας καθοδηγήσει σε όλα τα απαραίτητα βήματα για να το κάνετε. (Δείτε επίσης: Υπολογιστές Στεγαστικών: Πώς Εργάζονται .)

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

Υπολογισμός Δανείου για Μηνιαίο Μίσθωμα

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

Ο τύπος, όπως φαίνεται στην παραπάνω εικόνα, γράφεται ως εξής:

- <->

= - PMT (τιμή, μήκος, τρέχουσα τιμή, μελλοντική τιμή, τύπος)

Το σημάδι μείον μπροστά από το PMT είναι απαραίτητο, καθώς ο τύπος επιστρέφει έναν αρνητικό αριθμό. Τα πρώτα τρία επιχειρήματα είναι το ποσοστό του δανείου, η διάρκεια του δανείου (αριθμός περιόδων) και ο κύριος δανεισμός. Τα δύο τελευταία επιχειρήματα είναι προαιρετικά, η προεπιλεγμένη υπολειπόμενη τιμή σε 0, πληρωτέα εκ των προτέρων (για 1) ή στο τέλος (για το 0), είναι επίσης προαιρετική.

Η μέθοδος Excel που χρησιμοποιείται για τον υπολογισμό της μηνιαίας πληρωμής του δανείου είναι:

= - PMT ((1 + B2) ^ (1/12) -1, B4 * 12, B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Επεξήγηση: Για τον ρυθμό που χρησιμοποιούμε την περίοδο του επιτοκίου, ο αριθμός των περιόδων (οι μήνες 120 εδώ και 10 χρόνια πολλαπλασιασμένοι με 12 μήνες) και τέλος, αναφέρουμε τον κύριο δανεισμό. Η μηνιαία πληρωμή μας θα είναι $ 1, 161. 88 σε διάστημα 10 ετών.

Υπολογισμός υποθηκών για επιτόκια

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

Υπολογισμός του επιτοκίου για ένα δάνειο

Όπως φαίνεται στο παραπάνω στιγμιότυπο οθόνης, υπολογίζουμε πρώτα τον ρυθμό περιόδου (μηνιαίως στην περίπτωσή μας) και στη συνέχεια τον ετήσιο ρυθμό. Ο τύπος που χρησιμοποιείται θα είναι RATE, όπως φαίνεται στο παραπάνω screenshot, είναι γραμμένος ως εξής:

= RATE (Nper; pmt; present_value; [future_value]; type)

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

Ο τύπος Excel που χρησιμοποιήθηκε για τον υπολογισμό του επιτοκίου δανεισμού είναι:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13, -960, 120000)

η μηνιαία πληρωμή πρέπει να έχει αρνητικό σημάδι. Αυτός είναι ο λόγος για το αρνητικό πρόσημο πριν από τη φόρμουλα. Η τιμή μας είναι 0,294%.

Χρησιμοποιούμε τον τύπο

= (1 + B5) είναι 12-1 ^ = (1 + 0,294%) ^ 12-1 %. Με άλλα λόγια, για να δανειστείτε 120.000 δολάρια σε 13 χρόνια για να πληρώσετε μηνιαίως 960 $, πρέπει να διαπραγματευτούμε ένα δάνειο με ετήσιο επιτόκιο 3. 58%. Υπολογισμός υποθηκών για το μήκος ενός δανείου

Θα δούμε τώρα πώς να πάρει το μήκος ενός δανείου όταν γνωρίζετε το ετήσιο επιτόκιο, τον κύριο δανεισμό και τη μηνιαία πληρωμή που πρόκειται να επιστραφεί. Με άλλα λόγια, πόσο καιρό θα χρειαστεί να εξοφλήσουμε μια υποθήκη $ 120.000 με επιτόκιο 3. 10% και μηνιαία πληρωμή $ 1, 100;

Ο αριθμός των αποπληρωμών για ένα δάνειο

Ο τύπος που θα χρησιμοποιήσουμε είναι NPER, όπως φαίνεται στο παραπάνω στιγμιότυπο οθόνης και είναι γραμμένος ως εξής:

= NPER (rate; pmt; current_value; [type])

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

= 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 περιόδους.

Το παραπάνω screenshot δείχνει την κατανομή ενός δανείου (συνολική περίοδος ίσο με 120), χρησιμοποιώντας τους τύπους PPMT και IPMT. Τα επιχειρήματα των δύο τύπων είναι τα ίδια και κατανέμονται ως εξής:

= - PPMT (ποσοστό · num_period · μήκος · κύριο · [υπολειπόμενο] · [terme])

= - INTPER (ποσοστό; μήκος [principal] [residual] · [terme])

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

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT (1 + 3, 10% ) - 1, 1, 10 * 12, 120000)

= - INTPER ((1 + Β2) Το αποτέλεσμα είναι αυτό που παρουσιάζεται στο στιγμιότυπο "Αποσύνθεση δανείου", κατά την αναλυθείσα περίοδο η οποία είναι "1", έτσι ώστε η πρώτη περίοδος , ή τον πρώτο μήνα.Για αυτό, πληρώνουμε 1161 δολάρια. 88, κατανεμημένα σε $ 856, 20 κύρια και 305 δολάρια. 68 τόκους.

Υπολογισμός δανείου Excel

Τώρα είναι επίσης δυνατό να υπολογίσετε την επιστροφή κεφαλαίου και τόκων για αρκετές περιόδους, όπως τους πρώτους 12 μήνες ή τους πρώτους 15 μήνες.

= - CUMPRINC (ποσοστό, μήκος, κύριος, start_date, end_date, τύπος)

= CUMIPMT (ποσοστό, μήκος, κύριος, start_date, end_date, τύπος)

το κεφάλαιο και τον όρο (που είναι υποχρεωτικά) που είδαμε ήδη στο πρώτο μέρος με τον τύπο PMT. Αλλά εδώ, χρειαζόμαστε επίσης τα arguments start_date και end_date. Το πρώτο δείχνει την αρχή της περιόδου που πρέπει να αναλυθεί και η δεύτερη το τέλος. Ας πάρουμε ένα παράδειγμα:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0) 10%) ^ (1/12) -1 · 10 * 12 · 120000 · 1 · 12 · 0)

= - CUMIPMT (1 + Β2) ^ (1/12) -1 Β4 * 12 Β3 · 1 · 12 · 0)

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1, 10 * 12, 120000, 1, 12, 0)

ένα που εμφανίζεται στο στιγμιότυπο οθόνης "Cumul 1st year," έτσι οι αναλυόμενες περίοδοι κυμαίνονται από 1 έως 12, της πρώτης περιόδου (πρώτος μήνας) έως το δωδέκατο (12ος μήνας). Πάνω από ένα χρόνο, θα πληρώναμε 10 419 δολάρια, 55 βασικά και 3 522. 99 δολάρια.

Η απόσβεση του δανείου

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

Δημιουργία Προγράμματος Δανείου στο Excel

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

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

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

= - PMT (TP-1; B4 * 12; B3) = -PMT (1 + 3, 10% ) -1, 10 * 12, 120000)

Η τρίτη στήλη είναι ο βασικός τίτλος που θα εξοφληθεί κάθε μήνα. Για παράδειγμα, για την 40η περίοδο, θα πληρώσουμε 945 δολάρια. 51 για το μηνιαίο συνολικό ποσό των $ 1, 161. 88. Για να υπολογίσουμε το βασικό ποσό που εξαργυρώσαμε, χρησιμοποιούμε τον ακόλουθο τύπο:

= - PPMT (TP · A18 · $ B $ 4 * 12; $ B $ 3) = Το τέταρτο εδάφιο είναι το επιτόκιο για το οποίο υπολογίζουμε το αρχικό ποσό που επιστρέφεται στο μηνιαίο ποσό μας για να ανακαλύψουμε πώς (1 + 3, 10%) ^ (1/12) Η τιμή του εισοδήματος υπολογίζεται με βάση τον τύπο:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) · 1 · 10 * 12 · 120000)

Η πέμπτη στήλη περιέχει το ποσό που απομένει να πληρώσει. Για παράδειγμα, μετά την 40η πληρωμή θα πρέπει να πληρώσουμε $ 83, 994. 69 για $ 120, 000. Ο τύπος έχει ως εξής:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1 · A18 · 0)

= 120000 + CUMPRINC ((1 + 3, 10%) (1/12) · 10 * 12 · 120000 · 1 · 1 · 0)

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