Rumus untuk menghitung pembayaran anuitas di Excel

Pembayaran pinjaman lebih mudah dan cepat dihitung dengan Microsoft Office Excel. Lebih banyak waktu dihabiskan untuk perhitungan manual. Artikel ini akan fokus pada pembayaran anuitas, fitur perhitungannya, kelebihan dan kekurangannya.

Apa itu pembayaran anuitas?

Metode pembayaran bulanan pinjaman, di mana jumlah yang disetorkan tidak berubah selama seluruh periode pinjaman. Itu. pada tanggal tertentu setiap bulan, seseorang menyetor sejumlah uang tertentu sampai pinjaman dilunasi.

Apalagi bunga pinjaman sudah termasuk dalam jumlah total yang dibayarkan ke bank.

Anuitas klasifikasi

Pembayaran anuitas dapat dibagi menjadi beberapa jenis berikut:

1. Tetap. Pembayaran yang tidak berubah memiliki tingkat bunga tetap terlepas dari kondisi eksternal.
2. Mata uang. Kemampuan untuk mengubah jumlah pembayaran jika terjadi penurunan atau kenaikan nilai tukar.
3. diindeks. Pembayaran tergantung pada level, indikator inflasi. Selama masa pinjaman, ukurannya sering berubah.
4. Variabel. Anuitas, yang dapat berubah tergantung pada keadaan sistem keuangan, instrumen.

Perhatikan! Pembayaran tetap lebih disukai untuk semua peminjam, karena memiliki risiko kecil.

Keuntungan dan kerugian dari pembayaran anuitas

Untuk lebih memahami topik ini, perlu mempelajari fitur-fitur utama dari jenis pembayaran pinjaman ini. Ini memiliki keuntungan sebagai berikut:

• Menetapkan jumlah pembayaran tertentu dan tanggal pembayarannya.
• Ketersediaan tinggi untuk peminjam. Hampir setiap orang dapat mengajukan anuitas, terlepas dari situasi keuangan mereka.
• Kemungkinan menurunkan jumlah angsuran bulanan dengan peningkatan inflasi.

Bukan tanpa kekurangan:

• Tingkat tinggi. Peminjam akan membayar lebih jumlah uang yang lebih besar dibandingkan dengan pembayaran diferensial.
• Masalah timbul dari keinginan untuk melunasi utang lebih cepat dari jadwal.
• Tidak ada perhitungan ulang untuk pembayaran awal.

Apa pembayaran pinjaman?

Pembayaran anuitas memiliki komponen berikut:

• Bunga yang dibayarkan oleh seseorang ketika melunasi pinjaman.
• Bagian dari jumlah pokok.

Akibatnya, jumlah total bunga hampir selalu melebihi jumlah yang disumbangkan oleh peminjam untuk mengurangi utang.

Rumus Pembayaran Anuitas Dasar di Excel

Seperti disebutkan di atas, di Microsoft Office Excel Anda dapat bekerja dengan berbagai jenis pembayaran untuk pinjaman dan uang muka. Anuitas tidak terkecuali. Secara umum, rumus yang dapat digunakan untuk menghitung kontribusi anuitas dengan cepat adalah sebagai berikut:  

Penting! Tidak mungkin untuk membuka tanda kurung pada penyebut dari ekspresi ini untuk menyederhanakannya.

Nilai utama rumus diuraikan sebagai berikut:

• AP – pembayaran anuitas (namanya disingkat).
• O – besarnya utang pokok peminjam.
• PS – tingkat bunga yang diajukan setiap bulan oleh bank tertentu.
• C adalah jumlah bulan pinjaman berlangsung.

Untuk mengasimilasi informasi, cukup memberikan beberapa contoh penggunaan rumus ini. Mereka akan dibahas lebih lanjut.

Contoh penggunaan fungsi PMT di Excel

Kami memberikan kondisi sederhana dari masalah. Penting untuk menghitung pembayaran pinjaman bulanan jika bank mengajukan bunga 23%, dan jumlah totalnya adalah 25000 rubel. Pinjaman akan berlangsung selama 3 tahun. Masalahnya diselesaikan sesuai dengan algoritma:

1. Buat spreadsheet umum di Excel berdasarkan data sumber.

2. Aktifkan fungsi PMT dan masukkan argumen untuk itu di kotak yang sesuai.
3. Di kolom “Taruhan”, masukkan rumus “B3/B5”. Ini akan menjadi tingkat bunga pinjaman.
4. Pada baris “Nper” tulis nilai dalam bentuk “B4*B5”. Ini akan menjadi jumlah total pembayaran untuk seluruh jangka waktu pinjaman.
5. Isi kolom "PS". Di sini Anda perlu menunjukkan jumlah awal yang diambil dari bank, menulis nilai "B2".

6. Pastikan bahwa setelah mengklik "OK" di tabel sumber, nilai "Pembayaran bulanan" dihitung.

Informasi tambahan! Angka negatif menunjukkan bahwa peminjam menghabiskan uang.

Contoh menghitung jumlah kelebihan pembayaran pinjaman di Excel

Dalam masalah ini, Anda perlu menghitung jumlah yang akan dibayar lebih oleh seseorang yang telah mengambil pinjaman 50000 rubel dengan tingkat bunga 27% selama 5 tahun. Secara total, peminjam melakukan 12 pembayaran per tahun. Larutan:

1. Kompilasi tabel data asli.

2. Dari jumlah total pembayaran, kurangi jumlah awal sesuai dengan rumus «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Itu harus dimasukkan ke dalam bilah rumus di bagian atas menu utama program.
3. Akibatnya, jumlah kelebihan pembayaran akan muncul di baris terakhir dari pelat yang dibuat. Peminjam akan membayar lebih 41606 rubel di atas.

Rumus untuk menghitung pembayaran pinjaman bulanan yang optimal di Excel

Tugas dengan kondisi berikut: klien telah mendaftarkan rekening bank untuk 200000 rubel dengan kemungkinan pengisian bulanan. Penting untuk menghitung jumlah pembayaran yang harus dilakukan seseorang setiap bulan, sehingga setelah 4 tahun ia memiliki 2000000 rubel di akunnya. Tarifnya adalah 11%. Larutan:

1. Buat spreadsheet berdasarkan data asli.

2. Masukkan rumus di baris input Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» dan tekan "Enter" dari keyboard. Huruf akan berbeda tergantung pada sel di mana tabel ditempatkan.
3. Periksa apakah jumlah kontribusi dihitung secara otomatis di baris terakhir tabel.

Perhatikan! Jadi, agar klien dapat mengumpulkan 4 rubel dengan laju 2000000% dalam 11 tahun, ia perlu menyetor 28188 rubel setiap bulan. Minus dalam jumlah menunjukkan bahwa klien mengalami kerugian dengan memberikan uang kepada bank.

Fitur menggunakan fungsi PMT di Excel

Secara umum, rumus ini ditulis sebagai berikut: =PMT(tingkat; nper; ps; [bs]; [jenis]). Fungsi ini memiliki fitur-fitur berikut:

1. Ketika kontribusi bulanan dihitung, hanya tarif tahunan yang dipertimbangkan.
2. Saat menentukan tingkat bunga, penting untuk menghitung ulang berdasarkan jumlah angsuran per tahun.
3. Alih-alih argumen "Nper" dalam rumus, nomor tertentu ditunjukkan. Ini adalah periode pembayaran.

Perhitungan pembayaran

Secara umum, pembayaran anuitas dihitung dalam dua tahap. Untuk memahami topik, setiap tahapan harus dipertimbangkan secara terpisah. Ini akan dibahas lebih lanjut.

Tahap 1: perhitungan angsuran bulanan

Untuk menghitung di Excel jumlah yang harus Anda bayar setiap bulan untuk pinjaman dengan tingkat bunga tetap, Anda harus:

1. Kompilasi tabel sumber dan pilih sel tempat Anda ingin menampilkan hasilnya dan klik tombol "Sisipkan fungsi" di atas.

2. Dalam daftar fungsi, pilih "PLT" dan klik "OK".

3. Di jendela berikutnya, atur argumen untuk fungsi, yang menunjukkan baris yang sesuai dalam tabel yang dikompilasi. Di akhir setiap baris, Anda perlu mengklik ikon, lalu pilih sel yang diinginkan dalam array.

4. Ketika semua argumen diisi, formula yang sesuai akan ditulis di baris untuk memasukkan nilai, dan hasil perhitungan dengan tanda minus akan muncul di bidang tabel "Pembayaran bulanan".

Penting! Setelah menghitung angsuran, dimungkinkan untuk menghitung jumlah yang akan dibayar lebih oleh peminjam untuk seluruh periode pinjaman.

Tahap 2: detail pembayaran

Jumlah kelebihan pembayaran dapat dihitung setiap bulan. Akibatnya, seseorang akan mengerti berapa banyak uang yang akan dia keluarkan untuk pinjaman setiap bulan. Perhitungan detail dilakukan sebagai berikut:

1. Buat spreadsheet selama 24 bulan.

2. Tempatkan kursor di sel pertama tabel dan masukkan fungsi "OSPLT".

3. Isi argumen fungsi dengan cara yang sama.

4. Saat mengisi bidang "Periode", Anda harus merujuk ke bulan pertama dalam tabel, yang menunjukkan sel 1.

5. Periksa apakah sel pertama di kolom “Pembayaran oleh badan pinjaman” terisi.
6. Untuk mengisi semua baris kolom pertama, Anda perlu meregangkan sel ke ujung tabel

7. Pilih fungsi “PRPLT” untuk mengisi kolom kedua tabel.
8. Isi semua argumen di jendela yang terbuka sesuai dengan tangkapan layar di bawah ini.

9. Hitung total pembayaran bulanan dengan menambahkan nilai pada dua kolom sebelumnya.

10. Untuk menghitung "Utang Saldo", Anda perlu menambahkan tingkat bunga ke pembayaran di badan pinjaman dan merentangkannya ke ujung pelat untuk mengisi semua bulan pinjaman.

Informasi tambahan! Saat menghitung sisanya, tanda dolar harus digantung pada rumus agar tidak keluar saat diregangkan.

Perhitungan pembayaran anuitas pinjaman di Excel

Fungsi PMT bertanggung jawab untuk menghitung anuitas di Excel. Prinsip perhitungan secara umum adalah melakukan langkah-langkah berikut:

1. Kompilasi tabel data asli.
2. Buat jadwal pembayaran utang untuk setiap bulan.
3. Pilih sel pertama di kolom "Pembayaran pinjaman" dan masukkan rumus perhitungan “PLT ($B3/12;$B$4;$B$2)”.
4. Nilai yang dihasilkan diregangkan untuk semua kolom pelat.

Perhitungan dalam MS Excel pembayaran kembali jumlah pokok hutang

Pembayaran anuitas harus dilakukan setiap bulan dalam jumlah tetap. Dan tingkat bunga tidak berubah.

Perhitungan saldo pokok (dengan BS=0, type=0)

Misalkan pinjaman 100000 rubel diambil selama 10 tahun pada 9%. Perlu untuk menghitung jumlah hutang pokok pada bulan ke-1 tahun ke-3. Larutan:

1. Susun lembar data dan hitung pembayaran bulanan menggunakan rumus PV di atas.
2. Hitung bagian pembayaran yang diperlukan untuk melunasi sebagian hutang menggunakan rumus «=-PMT-(PS-PS1)*barang=-PMT-(PS +PMT+PS*barang)».
3. Hitung jumlah hutang pokok selama 120 periode dengan menggunakan rumus yang terkenal.
4. Dengan menggunakan operator HPMT, temukan jumlah bunga yang dibayarkan untuk bulan ke-25.
5. Periksa hasil.

Menghitung jumlah pokok yang dibayarkan antara dua periode

Perhitungan ini paling baik dilakukan dengan cara yang sederhana. Anda perlu menggunakan rumus berikut untuk menghitung jumlah dalam interval untuk dua periode:

• =«-BS(item; con_period; plt; [ps]; [tipe]) /(1+tipe *item)».
• = “+ BS(tarif; start_period-1; plt; [ps]; [type]) /IF(start_period =1; 1; 1+type *rate)”.

Perhatikan! Huruf dalam tanda kurung diganti dengan nilai tertentu.

Pelunasan lebih awal dengan jangka waktu atau pembayaran yang dikurangi

Jika Anda perlu mengurangi jangka waktu pinjaman, Anda harus melakukan perhitungan tambahan menggunakan operator IF. Jadi akan mungkin untuk mengontrol saldo nol, yang tidak boleh dicapai sebelum akhir periode pembayaran.

Untuk mengurangi pembayaran, Anda perlu menghitung ulang kontribusi untuk setiap bulan sebelumnya.

Kalkulator pinjaman dengan pembayaran tidak teratur

Ada beberapa opsi anuitas di mana peminjam dapat menyetor jumlah variabel pada setiap hari dalam sebulan. Dalam situasi seperti itu, saldo utang dan bunga dihitung untuk setiap hari. Pada saat yang sama di Excel Anda perlu:

1. Masukkan hari-hari dalam bulan di mana pembayaran dilakukan, dan tunjukkan nomornya.
2. Periksa jumlah negatif dan positif. Yang negatif lebih disukai.
3. Hitung hari antara dua tanggal di mana uang disimpan.

Perhitungan pembayaran berkala di MS Excel. Deposito berjangka

Di Excel, Anda dapat dengan cepat menghitung jumlah pembayaran reguler, asalkan jumlah tetap telah terakumulasi. Tindakan ini dilakukan menggunakan fungsi PMT setelah tabel awal dikompilasi.

Kesimpulan

Dengan demikian, pembayaran anuitas lebih mudah, lebih cepat, dan lebih efisien untuk dihitung di Excel. Operator PMT bertanggung jawab atas perhitungannya. Contoh yang lebih rinci dapat ditemukan di atas.