Perhitungan pembayaran anuitas di Excel

Sebelum mengambil pinjaman, akan menyenangkan untuk menghitung semua pembayaran di atasnya. Ini akan menyelamatkan peminjam di masa depan dari berbagai masalah dan kekecewaan yang tak terduga ketika ternyata kelebihan pembayaran terlalu besar. Bantuan pada perhitungan ini dapat alat program Excel. Mari cari tahu cara menghitung pembayaran anuitas pada pinjaman dalam program ini.

Perhitungan pembayaran

Pertama-tama, harus dikatakan bahwa ada dua jenis pembayaran kredit:

• Dibedakan;
• Anuitas.

Dengan skema yang berbeda, klien membawa bagian pembayaran yang sama setiap bulan pada badan pinjaman ditambah pembayaran bunga. Besarnya pembayaran bunga setiap bulan berkurang, karena badan pinjaman berkurang dari mana mereka dihitung. Dengan demikian, pembayaran bulanan secara keseluruhan juga berkurang.

Skema Ann Bibi menggunakan pendekatan yang sedikit berbeda. Klien membuat satu jumlah total pembayaran bulanan, yang terdiri dari pembayaran pada badan pinjaman dan pembayaran bunga. Awalnya, kontribusi bunga diberi nomor pada seluruh jumlah pinjaman, tetapi ketika tubuh berkurang, bunga berkurang dan bunga akrual. Tetapi jumlah total pembayaran tetap tidak berubah karena peningkatan bulanan dalam jumlah pembayaran oleh badan pinjaman. Dengan demikian, seiring waktu, proporsi minat dalam total pembayaran bulanan turun, dan berat proporsi tubuh tumbuh. Pada saat yang sama, pembayaran bulanan umum itu sendiri tidak berubah sepanjang jangka waktu kredit.

Hanya pada perhitungan pembayaran anuitas, kita akan berhenti. Terutama, ini relevan, karena sekarang sebagian besar bank menggunakan skema khusus ini. Ini nyaman bagi pelanggan, karena dalam hal ini jumlah total pembayaran tidak berubah, tetap tetap. Pelanggan selalu tahu berapa banyak yang perlu Anda bayar.

Langkah 1: Perhitungan kontribusi bulanan

Untuk menghitung kontribusi bulanan saat menggunakan rangkaian anuitas di Excele, ada fungsi khusus - PPT. Ini mengacu pada kategori operator keuangan. Formula fitur ini adalah sebagai berikut:

= Ppt (rate; Kper; ps; bs; ketik)

Seperti yang kita lihat, fungsi yang ditentukan memiliki jumlah argumen yang cukup besar. Benar, dua dari mereka yang terakhir tidak wajib.

Argumen "Tingkat" menunjukkan tingkat persentase untuk periode tertentu. Jika, misalnya, tingkat tahunan digunakan, tetapi pembayaran pinjaman dilakukan setiap bulan, maka tingkat tahunan harus dibagi menjadi 12 dan hasilnya digunakan sebagai argumen. Jika jenis pembayaran triwulanan diterapkan, maka dalam hal ini taruhan tahunan harus dibagi menjadi 4, dll.

"CP CP" berarti jumlah total periode pembayaran pinjaman. Artinya, jika pinjaman diambil selama satu tahun dengan pembayaran bulanan, maka jumlah periode dipertimbangkan 12, jika dua tahun, maka jumlah periode - 24. Jika pinjaman diambil selama dua tahun dengan pembayaran triwulanan, maka Jumlah periode adalah 8.

"PS" menunjukkan nilai sekarang pada saat ini. Berbicara dengan kata-kata sederhana, ini adalah jumlah total pinjaman pada awal pinjaman, yaitu jumlah yang Anda pinjam, tidak termasuk bunga dan pembayaran tambahan lainnya.

"BS" adalah biaya masa depan. Nilai ini akan menjadi badan pinjaman pada saat penyelesaian perjanjian pinjaman. Dalam kebanyakan kasus, argumen ini "0", karena peminjam pada akhir periode kredit harus sepenuhnya menetap dengan pemberi pinjaman. Argumen yang ditentukan tidak wajib. Karena itu, jika diturunkan, dianggap nol.

Argumen "Type" menentukan waktu perhitungan: di akhir atau pada awal periode. Dalam kasus pertama, dibutuhkan nilai "0", dan di yang kedua - "1". Sebagian besar lembaga perbankan menggunakan dengan tepat opsi dengan pembayaran pada akhir periode. Argumen ini juga opsional, dan jika dihilangkan, diyakini bahwa nol.

Sekarang saatnya untuk pindah ke contoh spesifik menghitung kontribusi bulanan menggunakan fungsi PL. Untuk menghitung, kami menggunakan tabel dengan data sumber, di mana suku bunga pinjaman (12%) diindikasikan, nilai pinjaman (500.000 rubel) dan periode pinjaman (24 bulan). Pada saat yang sama, pembayaran dilakukan setiap bulan pada akhir setiap periode.

1. Pilih elemen pada lembar di mana hasil hasil akan ditampilkan, dan klik ikon "Sisipkan Fungsi", ditempatkan di dekat rumus baris.



2. Jendela Wind Wizard diluncurkan. Dalam kategori "finansial" mengalokasikan nama "PLT" dan klik tombol "OK".



3. Setelah itu, membuka jendela argumen dari Operator PL.

   Di bidang "Tingkat", Anda harus memasukkan nilai persentase untuk periode tersebut. Ini dapat dilakukan secara manual, hanya menempatkan persentase, tetapi ditunjukkan dalam sel terpisah pada lembar, jadi kami akan memberikan tautan ke sana. Instal kursor di bidang, lalu klik pada sel yang sesuai. Tapi, seperti yang kita ingat, kita memiliki tingkat bunga tahunan di meja kita, dan periode pembayaran sama dengan bulan. Oleh karena itu, kami membagi taruhan tahunan, dan lebih tepatnya tautan ke sel di mana itu terkandung oleh angka 12, sesuai dengan jumlah bulan pada tahun tersebut. Divisi berjalan langsung di bidang jendela argumen.

   Di bidang CPer, pinjaman sudah diatur. Dia sama dengan 24 bulan. Anda dapat mendaftar di bidang angka 24 secara manual, tetapi kami, seperti pada kasus sebelumnya, tentukan tautan ke lokasi indikator ini di tabel sumber.

   Di lapangan "PS" menunjukkan nilai pinjaman awal. Hal ini sama dengan 500.000 rubel. Seperti dalam kasus-kasus sebelumnya, kita tentukan link ke elemen daun, yang berisi indikator ini.

   Di bidang "BS" menunjukkan besarnya pinjaman, setelah pembayaran penuh. Seperti yang Anda ingat, nilai ini hampir selalu nol. Instal di bidang ini jumlah "0". Meskipun argumen ini secara umum dapat dihilangkan.

   Di bidang "Type", kita tentukan di awal atau di akhir pembayaran bulan dibuat. Kami, seperti dalam kebanyakan kasus, itu diproduksi pada akhir bulan. Oleh karena itu, kami menetapkan jumlah "0". Seperti dalam kasus argumen sebelumnya, adalah mungkin untuk memasukkan apa pun ke bidang ini, maka program default akan menganggap bahwa itu adalah nol sama dengan itu.

   Setelah semua data dimasukkan, tekan tombol "OK".



4. Setelah itu, di dalam sel yang kita dialokasikan dalam paragraf pertama dari manual ini, hasil perhitungan ditampilkan. Seperti yang Anda lihat, besarnya pembayaran umum bulanan pinjaman adalah 23.536,74 rubel. Biarkan Anda bingung tanda "-" sebelum jumlah ini. Jadi Exile menunjukkan bahwa ini adalah aliran uang, yaitu, kerugian.



5. Untuk menghitung jumlah total pembayaran untuk jangka waktu pinjaman keseluruhan, dengan mempertimbangkan pembayaran tubuh pinjaman dan bunga bulanan, bukan kalikan jumlah pembayaran bulanan (23.536,74 rubel) untuk jumlah bulan (24 bulan ). Seperti yang Anda lihat, jumlah total pembayaran untuk jangka waktu pinjaman keseluruhan dalam kasus kami 564.881,67 rubel.



6. Sekarang Anda dapat menghitung jumlah kelebihan pembayaran pinjaman. Untuk melakukan hal ini, perlu untuk mengambil dari jumlah total pembayaran pinjaman, termasuk bunga dan tubuh pinjaman, jumlah awal diklaim. Tapi kita ingat bahwa yang pertama dari nilai-nilai ini sudah dengan tanda "-". Oleh karena itu, dalam khusus, kasus kami ternyata bahwa mereka harus dilipat. Seperti yang kita lihat, total kelebihan pembayaran pinjaman selama seluruh periode itu 64.881,67 rubel.

Pelajaran: Master Fungsi di Excel

Tahap 2: Rincian Pembayaran

Dan sekarang, dengan bantuan operator Excel lainnya, kita membuat detail bulanan pembayaran untuk melihat berapa banyak dalam bulan tertentu kita membayar melalui tubuh pinjaman, dan berapa jumlah bunga. Untuk tujuan ini, pandai besi dalam tabel pengasingan, yang kita akan mengisi data. Garis-garis tabel ini akan bertanggung jawab untuk periode yang sesuai, yaitu bulan. Mengingat bahwa periode pinjaman adalah 24 bulan, jumlah baris juga akan sesuai. Kolom menunjukkan tubuh pinjaman, pembayaran bunga, pembayaran bulanan total, yang merupakan jumlah dari dua kolom sebelumnya, serta sisanya untuk membayar.

1. Untuk menentukan jumlah pembayaran oleh badan pinjaman, gunakan fungsi OSP, yang hanya ditujukan untuk tujuan-tujuan ini. Kami mendirikan kursor di sel, yang terletak di garis "1" dan di kolom "pembayaran oleh badan pinjaman". Klik pada tombol "Tempel Fungsi".



2. Pergi ke Master of Functions. Dalam kategori "keuangan", kami perhatikan nama "osplt" dan klik tombol "OK".



3. Argumen argumen operator OSP dimulai. Ini memiliki sintaks berikut:

   = ISPult (Tingkat; periode; Kper; PS; BS)

   Seperti yang dapat kita lihat, argumen fitur ini hampir sepenuhnya bertepatan dengan argumen operator PLT, hanya alih-alih argumen opsional "Type" menambahkan argumen wajib "periode". Ini menunjukkan jumlah periode pembayaran, dan dalam kasus khusus kami pada jumlah bulan.

   Isi argumen argumen fungsi OSR yang sudah akrab oleh kami dengan data yang sama, yang digunakan untuk fungsi PL. Hanya mengingat fakta bahwa di masa depan, menyalin rumus akan digunakan melalui penanda pengisian, Anda perlu melakukan semua tautan di bidang absolut sehingga mereka tidak berubah. Ini membutuhkan tanda dolar sebelum setiap nilai koordinat vertikal dan horizontal. Tetapi lebih mudah untuk melakukannya, cukup memilih koordinat dan mengklik tombol fungsi F4. Tanda dolar akan ditempatkan di tempat yang tepat secara otomatis. Kami juga tidak lupa bahwa taruhan tahunan harus dibagi menjadi 12.



4. Tetapi kami memiliki argumen baru lain, yang bukan dari fungsi PL. Argumen ini "periode". Di bidang yang sesuai, atur referensi ke sel pertama kolom "Periode". Elemen lembar ini berisi angka "1", yang menunjukkan jumlah bulan pertama pinjaman. Tetapi tidak seperti bidang sebelumnya, kami meninggalkan relatif tautan di bidang yang ditentukan, dan tidak mutlak dari itu.

   Setelah semua data tentang yang kami bicarakan di atas diperkenalkan, tekan tombol "OK".



5. Setelah itu, di dalam sel, yang sebelumnya kami alokasikan, jumlah pembayaran oleh badan pinjaman untuk bulan pertama akan muncul. Ini akan menjadi 18536,74 rubel.



6. Kemudian, seperti yang disebutkan di atas, kita harus menyalin rumus ini ke sel kolom yang tersisa menggunakan penanda pengisian. Untuk melakukan ini, atur kursor ke sudut kanan bawah sel, yang berisi rumus. Kursor dikonversi ke salib, yang disebut penanda pengisian. Klik tombol kiri mouse dan tarik ke ujung meja.



7. Akibatnya, semua kolom sel diisi. Sekarang kami memiliki bagan untuk membayar pinjaman setiap bulan. Seperti disebutkan di atas, jumlah pembayaran pada artikel ini meningkat setiap periode baru.



8. Sekarang kita perlu melakukan perhitungan pembayaran bulanan berdasarkan bunga. Untuk keperluan ini, kita akan menggunakan operator PRT. Kami mengalokasikan sel kosong pertama di kolom "Persentase Pembayaran". Klik pada tombol "Tempel Fungsi".



9. Dalam fungsi master fungsi dalam kategori "keuangan", kami memproduksi nama-nama NAMP. Lakukan klik pada tombol "OK".



10. Jendela argumen dari fungsi TRP dimulai. Sintaksnya terlihat seperti ini:

    = PRT (tarif; periode; cpu; ps; bs)

    Seperti yang bisa kita lihat, argumen fungsi ini benar-benar identik dengan elemen-elemen serupa dari operator OSP. Oleh karena itu, cukup masukkan data yang sama ke jendela yang kami masukkan di jendela argumen sebelumnya. Kita tidak lupa bahwa referensi di bidang "periode" harus relatif, dan di semua bidang lain koordinat harus dibawa ke bentuk absolut. Setelah itu, klik tombol "OK".



11. Kemudian hasil penghitungan jumlah pembayaran untuk bunga pinjaman untuk bulan pertama ditampilkan di sel yang sesuai.



12. Menerapkan penanda pengisian, buat penyalinan rumus ke dalam elemen kolom yang tersisa, dengan cara ini menerima jadwal bulanan untuk persentase untuk pinjaman. Seperti yang bisa kita lihat, seperti yang dikatakan sebelumnya, dari bulan ke bulan nilai jenis pembayaran ini berkurang.



13. Sekarang kita harus menghitung pembayaran bulanan secara keseluruhan. Untuk perhitungan ini, seseorang tidak boleh menggunakan operator apa pun, karena Anda dapat menggunakan formula aritmatika sederhana. Kami melipat konten sel pada bulan pertama kolom "pembayaran oleh badan pinjaman" dan "minat penuh". Untuk melakukan ini, atur tanda "=" ke dalam sel kosong pertama kolom "Total pembayaran bulanan". Kemudian klik pada dua elemen di atas dengan mengatur tanda "+" di antara mereka. Klik pada tombol Enter.



14. Selanjutnya, menggunakan penanda pengisian, seperti pada kasus sebelumnya, isi kolom data. Seperti yang bisa kita lihat, di seluruh aksi kontrak, jumlah total pembayaran bulanan, yang mencakup pembayaran oleh badan pinjaman dan pembayaran bunga, akan menjadi 23536,74 rubel. Sebenarnya, kami sudah menghitung indikator ini sebelum menggunakan PPT. Tetapi dalam hal ini disajikan lebih jelas, tepatnya sebagai jumlah pembayaran oleh tubuh pinjaman dan bunga.



15. Sekarang Anda perlu menambahkan data ke kolom, di mana saldo jumlah pinjaman ditampilkan setiap bulan, yang masih diperlukan untuk membayar. Di sel pertama kolom "saldo untuk membayar" perhitungan akan menjadi yang termudah. Kita perlu diambil dari magnitudo pinjaman awal, yang ditentukan dalam tabel dengan data primer, pembayaran oleh badan pinjaman untuk bulan pertama di tabel yang dihitung. Tapi, mengingat fakta bahwa salah satu angka yang sudah kita ikuti dengan tanda "-", maka mereka tidak boleh dibawa pergi, tetapi untuk melipat. Kami membuatnya dan klik pada tombol Enter.



16. Tetapi perhitungan saldo untuk membayar setelah bulan kedua dan selanjutnya akan menjadi lebih rumit. Untuk melakukan ini, kita perlu mengambil dari tubuh pinjaman ke awal pinjaman jumlah total pembayaran oleh badan pinjaman untuk periode sebelumnya. Instal tanda "=" di sel kedua kolom "istana untuk membayar". Selanjutnya, tentukan tautan ke sel, yang berisi jumlah pinjaman awal. Kami membuatnya absolut, menyoroti dan menekan tombol F4. Kemudian kita meletakkan tanda "+", karena kita memiliki makna kedua dan sangat negatif. Setelah itu, klik tombol "Sisipkan Fungsi".



17. Master of Functions diluncurkan, di mana Anda harus pindah ke kategori "Matematika". Di sana kami mengalokasikan prasasti "jumlah" dan tekan tombol "OK".



18. Jendela argumen memulai argumen fungsi. Operator yang ditentukan berfungsi untuk merangkum data dalam sel-sel yang perlu kita lakukan dalam kolom "Pembayaran oleh Badan Pinjaman". Ini memiliki sintaks berikut:

    = Jumlah (Number1; Number2; ...)

    Sebagai argumen, referensi sel di mana angka terkandung. Kami mengatur kursor di bidang "Number1". Kemudian pinkan tombol kiri mouse dan pilih dua sel pertama dari kolom bodi kredit pada lembar. Di lapangan, seperti yang kita lihat, tautan ke rentang muncul. Ini terdiri dari dua bagian yang dipisahkan oleh usus besar: referensi ke kisaran pertama kisaran dan pada yang terakhir. Agar dapat menyalin rumus yang ditentukan di masa depan dengan penanda pengisian, kami membuat tautan pertama ke rentang absolut. Kami menyorotnya dan mengklik tombol fungsi F4. Bagian kedua dari referensi dan meninggalkan relatif. Sekarang, ketika menggunakan penanda pengisian, kisaran pertama kisaran akan diperbaiki, dan yang terakhir akan membentang saat bergerak ke bawah. Ini perlu bagi kita untuk memenuhi tujuan. Selanjutnya, klik tombol "OK".



19. Jadi, hasil dari keseimbangan hutang kredit setelah bulan kedua dibuang ke dalam sel. Sekarang, dimulai dengan sel ini, kami membuat menyalin rumus ke dalam elemen kolom kosong menggunakan penanda pengisian.



20. Perhitungan bulanan sisa-sisa untuk membayar pinjaman dibuat untuk seluruh periode kredit. Sebagaimana mestinya, pada akhir tenggat waktu, jumlah ini nol.

Dengan demikian, kami tidak hanya menghitung pembayaran pinjaman, tetapi terorganisir semacam kalkulator kredit. Yang akan bertindak pada skema anuitas. Jika di tabel sumber kami, misalnya, mengubah jumlah pinjaman dan tingkat bunga tahunan, kemudian di tabel akhir akan ada perhitungan ulang data otomatis. Oleh karena itu, dapat digunakan tidak hanya sekali untuk kasus tertentu, tetapi berlaku dalam berbagai situasi untuk menghitung opsi kredit pada skema anuitas.

Pelajaran: Fungsi keuangan di Excel

Seperti yang Anda lihat, menggunakan program Excel di rumah, Anda dapat dengan mudah menghitung pembayaran pinjaman bulanan secara keseluruhan pada skema anuitas, menggunakan operator PL untuk tujuan ini. Selain itu, dengan bantuan fungsi OSR dan PRT, dimungkinkan untuk menghitung jumlah pembayaran oleh badan pinjaman dan persentase untuk periode yang ditentukan. Menerapkan semua fungsi bagasi ini bersama-sama, adalah mungkin untuk membuat kalkulator kredit yang kuat yang dapat digunakan lebih dari sekali untuk menghitung pembayaran anuitas.