Panduan Lengkap Membuat Tabel Angsuran Kredit di Excel

Dalam mengelola keuangan pribadi atau bisnis, pemantauan kredit sangat penting. Salah satu cara efektif untuk melakukannya adalah dengan membuat tabel angsuran kredit di Excel. Dengan menggunakan spreadsheet ini, Anda dapat dengan mudah menghitung dan memantau setiap pembayaran angsuran, sisa pinjaman, dan bunga yang dibayarkan.

Artikel ini akan membahas langkah-langkah rinci untuk membuat tabel angsuran kredit di Excel dengan rumus sederhana, sehingga Anda dapat mengelola keuangan Anda dengan lebih efisien.

Langkah-Langkah Membuat Tabel Angsuran Kredit di Excel

Langkah 1: Menentukan Parameter Kredit

Sebelum membuat tabel angsuran kredit di Excel, tentukan parameter kredit seperti jumlah pinjaman, tingkat bunga, dan jangka waktu pinjaman. Pastikan untuk menyesuaikan nilai-nilai ini sesuai dengan kebutuhan dan perjanjian kredit Anda seperti Suku bunga efektif per tahun, durasi kredit, plafond pinjaman dan tanggal kredit dicairkan.

Langkah 2: Membuat Tabel Angsuran

Buat tabel dengan kolom-kolom berikut untuk membuat tabel angsuran kredit di Excel:

• Nomor
• Tanggal Angsuran
• Saldo Awal
• Bunga
• Pokok
• Angsuran
• Sisa Pokok Pinjaman

Langkah 3: Mengisi Kolom Tanggal Angsuran

Gunakan rumus Excel untuk mengotomatisasi tanggal angsuran. Ini penting karena akan menjadi tanggal jatuh tempo sekaligus pengingat kita untuk jadwal angsuran. Rumus Excel di sel C8 adalah:

=DATE(YEAR($E$4);MONTH($E$4)+1;DAY(25))

Copy dan paste rumus di atas ke sel di bawahnya sampai dengan sel C19. Dan hasilnya adalah sebagai berikut:

Penjelasan Rumus

Mari kita bahas masing-masing bagian dari rumus ini:

1. YEAR($E$4): Bagian ini mengambil tahun dari sel yang terletak di sel E4. YEAR adalah fungsi Excel yang mengembalikan tahun dari tanggal yang diberikan. Dalam konteks ini, $E$4 menunjukkan bahwa sel E4 adalah referensi sel yang tetap, sehingga ketika rumus disalin ke sel lain, referensi sel tersebut tidak berubah.
2. MONTH($E$4)+1: Bagian ini mengambil bulan dari sel E4, kemudian menambahkan 1. MONTH adalah fungsi Excel yang mengembalikan bulan dari tanggal yang diberikan. Penambahan 1 dilakukan untuk mendapatkan bulan berikutnya. Sama seperti sebelumnya, $E$4 menunjukkan referensi sel yang tetap.
3. DAY(25): Bagian ini mengambil tanggal 25. DAY adalah fungsi Excel yang mengembalikan hari dari tanggal yang diberikan. Dalam rumus ini, kita secara khusus menetapkan tanggal 25.

Setelah mengambil tahun, bulan, dan tanggal, ketiganya digabungkan menggunakan fungsi DATE. Fungsi DATE mengembalikan nilai tanggal berdasarkan tahun, bulan, dan tanggal yang diberikan. Dalam hal ini, rumus menghasilkan tanggal yang terletak satu bulan setelah tanggal yang terdapat di sel E4, dengan tanggal tetap pada tanggal 25.

Jadi, secara keseluruhan, rumus ini berguna untuk menghasilkan tanggal yang merupakan satu bulan setelah tanggal yang terdapat di sel E4, dengan tanggal tetap pada tanggal 25.

Langkah 4: Menghitung Angsuran Pokok dan Bunga

Hitung angsuran pokok dan bunga dari pinjaman menggunakan rumus bunga bulanan yang sudah disepakati. Rumusnya adalah plafond pinjaman dibagi durasi kredit dikalikan dengan tingkat bunga bulanan. Dan rumus yang digunakan di sel G8 adalah sebagai berikut:

=-PMT($E$1/12;$E$2;$E$3)

Jika sudah, copy dan paste rumus di atas ke sel di bawahnya. Dan hasilnya adalah sebagai berikut:

Penjelasan Rumus

Mari kita bahas masing-masing bagian dari rumus ini:

1. PMT: Ini adalah fungsi dalam Excel yang digunakan untuk menghitung pembayaran periodik untuk suatu investasi atau pinjaman berdasarkan suku bunga tetap dan periode pembayaran yang konstan.
2. $E$1/12: Bagian ini mengambil suku bunga tahunan yang terletak di sel E1, dan dibagi dengan 12. Suku bunga yang diukur secara tahunan dibagi dengan 12 untuk mendapatkan suku bunga bulanan. Tanda dollar ($) digunakan untuk membuat referensi tetap ke sel E1, sehingga ketika rumus disalin ke sel lain, referensi sel tersebut tidak berubah.
3. $E$2: Bagian ini merujuk pada jumlah periode pembayaran atau jumlah angsuran. Sel E2 dalam contoh adalah untuk 12 bulan.
4. $E$3: Bagian ini merujuk pada nilai pinjaman awal yang akan dihitung pembayarannya. Sel E3 berisi plafond pinjaman sebesar Rp 20.000.000.

Dengan menggabungkan semua bagian, rumus ini menghitung pembayaran periodik yang harus dilakukan untuk suatu pinjaman dengan suku bunga tetap, periode pembayaran yang tetap, dan jumlah pinjaman tertentu.

Langkah 5: Menghitung Bunga Efektif

Untuk menghitung bunga efektif ini diperlukan sisa saldo pinjaman karena bunga efektif berarti besaran bunga akan mengikuti sisa pinjaman yang ada. Tambahkan sisa pinjaman pokok dan angsuran pokok per bulannya untuk memudahkan menghitung bunga efektif per bulannya.

Dan berikut adalah rumus Excel untuk menghitung bunga efektif per bulannya di sel E8:

=$D8*($E$1/12)

Copy dan paste rumus di atas ke sel di bawahnya. Dan berikut adalah hasilnya:

Penjelasan rumus

Mari kita bahas masing-masing bagian dari rumus ini:

1. $D8: Bagian ini merujuk pada nilai yang terletak di sel D8. Tanda dollar ($) digunakan untuk membuat referensi tetap ke kolom D, sehingga ketika rumus disalin ke sel lain secara horizontal, hanya nomor barisnya yang berubah, sedangkan referensi kolomnya tetap.
2. *: Ini adalah operator perkalian dalam Excel. Dalam rumus ini, digunakan untuk mengalikan nilai yang terletak di sel D8 dengan nilai suku bunga bulanan.
3. ($E$1/12): Bagian ini merujuk pada suku bunga tahunan yang terletak di sel E1, dan dibagi dengan 12. Suku bunga yang diukur secara tahunan dibagi dengan 12 untuk mendapatkan suku bunga bulanan. Tanda dollar ($) digunakan untuk membuat referensi tetap ke sel E1, sehingga ketika rumus disalin ke sel lain, referensi sel tersebut tidak berubah.

Jadi, secara keseluruhan, rumus ini mengalikan nilai yang terletak di sel D8 dengan suku bunga bulanan yang diambil dari sel E1/12. Hasilnya adalah nilai yang mewakili bunga bulanan dari suatu nilai tertentu.

Langkah 6: Menghitung Sisa Pinjaman

Kurangkan angsuran pokok dari sisa pinjaman bulan sebelumnya untuk mendapatkan sisa pinjaman baru di range H8:H19. Rumus di sel H8 sendiri cukup sederhana yakni: =D8-F8 lalu copy-paste ke sel di bawahnya, dan inilah hasilnya:

Langkah 7: Mengisi Saldo Awal

Untuk saldo awal, ada sedikit perlakuan berbeda. Untuk sel D8 mengacu ke sel E3 sementara sisanya mengacu ke range “Sisa Pokok Pinjaman.” Dan berikut adalah penampakan hasil dari membuat tabel angsuran kredit di Excel:

Langkah 8: Mengisi Kolom Angsuran Pokok

Untuk kolom angsuran pokok, cukup gunakan rumus yang mengacu pada kolom “Angsuran” dikurangi dengan kolom “Bunga.” Jadi, di sel F8 masukkan rumus: =G8-E8 dan hasilnya adalah sebagai berikut:

Langkah 9: Menambahkan Grafik (Opsional)

Jika Anda menyukai visualisasi, tambahkan grafik yang memperlihatkan perkembangan angsuran, bunga, dan sisa pinjaman sepanjang waktu setelah membuat tabel angsuran kredit di Excel.

Langkah 10: Memantau Pembayaran dan Membuat Perubahan

Gunakan tabel untuk memantau setiap pembayaran angsuran. Jika ada perubahan dalam tingkat bunga atau perubahan lainnya, ubah parameter di tabel untuk melihat dampaknya secara instan.

Kesimpulan

Dengan mengikuti langkah-langkah di atas, Anda dapat membuat tabel angsuran kredit di Excel dengan mudah. Tabel ini akan menjadi alat yang sangat berguna untuk merencanakan keuangan Anda, memantau pembayaran, dan memahami lebih baik bagaimana pembayaran kredit Anda dikelola. Dengan visualisasi yang jelas, Anda dapat mengambil keputusan keuangan yang lebih bijaksana dan mengontrol keuangan Anda dengan lebih efektif.

Terakhir, barangkali ada yang membutuhkan file contoh membuat tabel angsuran kredit di Excel di atas, file Excel bisa diunduh melalui tautan di bawah ini: