Cara Membuat Rekap Angsuran Nasabah dan Perhitungan Sisa Pinjaman di Excel

Mengelola pinjaman bisa menjadi tugas yang rumit, terutama jika Anda memiliki banyak nasabah dengan jadwal angsuran yang berbeda-beda. Untungnya, dengan bantuan Microsoft Excel, Anda dapat membuat spreadsheet sederhana untuk membuat rekap angsuran nasabah dan menghitung sisa pinjaman secara otomatis. Dalam artikel ini, kita akan membahas langkah-langkah untuk membuat rekap angsuran dan menghitung sisa pinjaman dengan mudah menggunakan Excel.

Membuat Tabel Data

Langkah pertama membuat rekap angsuran nasabah adalah membuat tabel data yang akan digunakan untuk merekam informasi angsuran. Sebagai contoh sederhana, buatlah tabel Daftar Nasabah dengan beberapa kolom Nama nasabah, Tenor dan Plafond + Bunga Pinjaman. Pastikan untuk memberikan label yang jelas pada setiap kolom.

Dan tabel kedua yang berisi tentang Daftar Angsuran. Ini juga kita buat sederhana saja. Hanya berisi Tanggal Angsuran, Nama Nasabah dan Nominal Angsuran. Dan inilah penampakannya:

Merekam Data Angsuran

Isi tabel dengan data tanggal angsuran untuk setiap nasabah beserta nominal angsurannya. Ini akan membantu Anda melacak pembayaran yang telah diterima. Dan untuk menghindari kesalahan penulisan nama, di kolom Nama Nasabah sebaiknya kita buat dropdown list menggunakan Data Validation. Langkah-langkahnya adalah blok range C11:C22 kemudian klik: Data – Data Validation – Allow: List dan Source: =$B$4:$B$6 kemudian klik OK.

Dan berikut adalah hasilnya:

Menghitung Angsuran Otomatis

Untuk menghindari salah penginputan nominal, sebagai contoh kita gunakan rumus Excel INDEX MATCH. Ini memudahkan Anda untuk melihat berapa nominal angsuran yang harus dibayar oleh nasabah berdasarkan data dari tabel Daftar Nasabah. Rumus INDEX MATCH yang diinput di sel D11 adalah sebagai berikut:

=INDEX($D$4:$D$6;MATCH(C11;$B$4:$B$6;0))/INDEX($C$4:$C$6;MATCH(C11;$B$4:$B$6;0))

Dan hasilnya adalah seperti ini:

Penjelasan Rumus

Rumus Excel ini sederhananya merupakan pembagian dari hasil pencocokan pada dua INDEX-MATCH. Mari kita jabarkan setiap bagian dari rumus tersebut:

1. MATCH(C11;$B$4:$B$6;0): Bagian ini menggunakan fungsi MATCH untuk mencocokkan nilai yang terdapat di sel C11 dengan rentang data di kolom B (dari B4 hingga B6). Parameter ketiga, yaitu “0”, menunjukkan pencocokan persis atau pencocokan nilai tepat. Hasilnya adalah nomor baris di mana nilai di sel C11 cocok dengan data di kolom B.
2. INDEX($D$4:$D$6;MATCH(C11;$B$4:$B$6;0)): Bagian ini menggunakan fungsi INDEX untuk menentukan nilai di dalam rentang data kolom D (dari D4 hingga D6) berdasarkan hasil pencocokan yang diperoleh dari fungsi MATCH di langkah pertama. Ini berarti kita mengambil nilai dari kolom D yang berada di baris yang cocok dengan nilai di sel C11.
3. INDEX($C$4:$C$6;MATCH(C11;$B$4:$B$6;0)): Bagian ini mirip dengan bagian sebelumnya, tetapi kali ini kita mengambil nilai dari kolom C (dari C4 hingga C6) yang berada di baris yang sama yang sudah dicocokkan dengan nilai di sel C11.
4. INDEX($D$4:$D$6;MATCH(C11;$B$4:$B$6;0))/INDEX($C$4:$C$6;MATCH(C11;$B$4:$B$6;0)): Terakhir, kita membagi hasil dari langkah kedua dengan hasil dari langkah ketiga. Ini menghasilkan nilai pembagian antara dua nilai yang diperoleh dari pencocokan di kolom D dan C berdasarkan nilai di sel C11.

Dengan demikian, rumus ini sebenarnya menghitung rasio atau perbandingan antara dua nilai dari kolom D dan C berdasarkan pencocokan dengan nilai di sel C11. Pada dasarnya, rumus ini memberikan informasi tentang hubungan antara dua kolom tergantung pada nilai tertentu yang ditemukan di sel C11.

Membuat Rekap Angsuran Nasabah

Untuk membuat rekap angsuran nasabah ini kita akan menggunakan formula array. Harap diingat bahwa penanganan untuk membuat formula array adalah dengan cara mengakhiri penulisan rumus dengan menekan Ctrl+Shift dan Enter. Dan formula array yang kita input di sel H4 adalah sebagai berikut:

=IF(COLUMNS($H4:H4)<=(COUNTIF($C$11:$C$22;$G4));INDEX($D$11:$D$22;SMALL(IF($C$11:$C$22=$G4;ROW($C$11:$C$22)-ROW($C$11)+1);COLUMNS($H4:H4)));"")

Ctrl+Shift dan Enter. Jika sudah, tinggal drag rumus ke sel disamping dan sel di bawahnya. Maka hasilnya adalah sebagai berikut:

Penjelasan Rumus

Formula array Excel untuk membuat rekap angsuran nasabah ini merupakan kombinasi fungsi IF, COLUMNS, COUNTIF, INDEX, SMALL, IF, dan ROW yang digunakan untuk mendapatkan nilai dari seluruh rentang data yang memenuhi kriteria tertentu. Mari kita bahas langkah demi langkah:

1. COLUMNS($H4:H4): Fungsi COLUMNS digunakan untuk menghitung jumlah kolom dalam rentang yang ditentukan. Pada kasus ini, rumus berfungsi sebagai hitung kolom yang telah diisi di dalam rentang $H4:H4. Fungsi ini akan bertambah seiring dengan salin formula ke kanan.
2. COUNTIF($C$11:$C$22;$G4): Fungsi COUNTIF digunakan untuk menghitung berapa kali nilai yang terdapat di sel G4 muncul di dalam rentang $C$11:$C$22.
3. COLUMNS($H4:H4)<=(COUNTIF($C$11:$C$22;$G4)): Bagian ini membandingkan jumlah kolom yang telah diisi dengan hasil dari fungsi COUNTIF. Jika jumlah kolom yang telah diisi kurang dari atau sama dengan jumlah kemunculan nilai di sel G4 di rentang $C$11:$C$22, maka kondisi ini akan bernilai benar.
4. IF(...;INDEX($D$11:$D$22;SMALL(IF($C$11:$C$22=$G4;ROW($C$11:$C$22)-ROW($C$11)+1);COLUMNS($H4:H4)));""): Fungsi IF utama. Jika kondisi pada langkah sebelumnya benar, maka formula akan melanjutkan ke bagian IF yang pertama, jika tidak, formula akan mengembalikan nilai kosong (“”). a. IF($C$11:$C$22=$G4;ROW($C$11:$C$22)-ROW($C$11)+1): Fungsi IF di dalamnya memeriksa apakah nilai di rentang $C$11:$C$22 sama dengan nilai di sel G4. Jika benar, fungsi IF ini mengembalikan nomor baris relatif dari sel tersebut menggunakan fungsi ROW. b. SMALL(...;COLUMNS($H4:H4)): Fungsi SMALL digunakan untuk mengembalikan nilai terkecil dari array hasil fungsi IF sebelumnya. Argument kedua, yaitu COLUMNS($H4:H4), menunjukkan urutan nilai terkecil yang akan diambil. Seiring dengan salin formula ke kanan, urutan nilai ini akan bertambah. c. INDEX($D$11:$D$22;SMALL(...)): Fungsi INDEX digunakan untuk mengambil nilai dari rentang kolom D (dari D11 hingga D22) berdasarkan nomor baris yang diberikan oleh fungsi SMALL. Ini menghasilkan nilai dari sel D yang memenuhi kriteria. d. Jika kondisi awal pada langkah 3 benar, maka formula akan menghasilkan nilai yang sesuai. Jika tidak, formula akan menghasilkan nilai kosong (“”).

Dengan demikian, formula membuat rekap angsuran nasabah ini digunakan untuk mengekstrak nilai dari kolom D yang memenuhi kriteria tertentu pada kolom C, dengan batasan jumlah nilai yang diambil sebanyak jumlah kemunculan nilai di sel G4. Formulanya dirancang untuk digunakan secara horizontal sehingga dapat disalin ke kolom sebelah kanan untuk mendapatkan nilai yang sesuai dengan kriteria yang berbeda.

Memasukkan Plafond dan Bunga nasabah

Langkah berikutnya adalah mengambil nilai plafond dan bunganya dari tabel Daftar Nasabah. Rumus yang digunakan untuk keperluan ini di sel L4 adalah:

=IF(ISBLANK(G4);"";VLOOKUP(G4;$B$4:$D$6;3;0))

Dan hasilnya adalah sebagai berikut:

Penjelasan Rumus

Mari kita bahas setiap bagian dari rumus ini:

1. ISBLANK(G4): Fungsi ISBLANK digunakan untuk memeriksa apakah sel G4 kosong (tidak berisi nilai). Jika sel G4 kosong, maka fungsi ini mengembalikan nilai TRUE; jika tidak, mengembalikan nilai FALSE.
2. IF(ISBLANK(G4);"";VLOOKUP(G4;$B$4:$D$6;3;0)): Fungsi IF digunakan untuk membuat kondisi. Jika hasil dari fungsi ISBLANK(G4) adalah TRUE (artinya sel G4 kosong), maka rumus ini menghasilkan nilai kosong (“”); jika hasilnya adalah FALSE (artinya sel G4 tidak kosong), maka rumus ini melanjutkan ke bagian selanjutnya.
3. VLOOKUP(G4;$B$4:$D$6;3;0): Fungsi VLOOKUP digunakan untuk mencari nilai dalam suatu rentang dan mengembalikan nilai yang sesuai. Parameter pertama (G4) adalah nilai yang akan dicari, parameter kedua ($B$4:$D$6) adalah rentang di mana pencarian dilakukan, parameter ketiga (3) adalah kolom di dalam rentang yang akan mengembalikan nilai, dan parameter keempat (0) menunjukkan pencarian nilai yang persis.

Jadi, keseluruhan rumus ini bekerja sebagai berikut:

• Jika sel G4 kosong, maka rumus menghasilkan nilai kosong (“”).
• Jika sel G4 tidak kosong, maka rumus menggunakan fungsi VLOOKUP untuk mencari nilai yang sesuai dengan nilai di G4 dalam rentang $B$4:$D$6, dan mengembalikan nilai dari kolom ke-3 dalam rentang tersebut.

Dengan demikian, rumus ini digunakan untuk mengambil nilai dari kolom ke-3 (kolom terakhir) dalam rentang $B$4:$D$6 berdasarkan nilai yang terdapat di sel G4, dengan catatan bahwa rumus ini hanya berlaku jika sel G4 tidak kosong.

Menghitung Sisa Pinjaman

Setelah semua data mencukupi, barulah kemudian kita hitung sisa pinjaman plus bunga dari nasabah-nasabah tersebut. Langsung saja, di sel M4 rumus Excel yang digunakan adalah:

=IF(ISBLANK(G4);"";L4-SUM(H4:K4))

Dan setelah rumus di copy-paste ke sel M5 sampai M8 maka hasilnya adalah sebagai berikut:

Penjelasan Rumus

Mari kita bahas setiap bagian dari rumus ini:

1. ISBLANK(G4): Fungsi ISBLANK digunakan untuk memeriksa apakah sel G4 kosong (tidak berisi nilai). Jika sel G4 kosong, maka fungsi ini mengembalikan nilai TRUE; jika tidak, mengembalikan nilai FALSE.
2. IF(ISBLANK(G4);"";L4-SUM(H4:K4)): Fungsi IF digunakan untuk membuat kondisi. Jika hasil dari fungsi ISBLANK(G4) adalah TRUE (artinya sel G4 kosong), maka rumus ini menghasilkan nilai kosong (“”); jika hasilnya adalah FALSE (artinya sel G4 tidak kosong), maka rumus ini melanjutkan ke bagian selanjutnya.
3. L4-SUM(H4:K4): Bagian ini dijalankan jika sel G4 tidak kosong. Rumus ini menghitung selisih antara nilai di sel L4 dan hasil penjumlahan dari sel H4 hingga K4.

Jadi, keseluruhan rumus ini bekerja sebagai berikut:

• Jika sel G4 kosong, maka rumus menghasilkan nilai kosong (“”).
• Jika sel G4 tidak kosong, maka rumus menghitung selisih antara nilai di sel L4 dan hasil penjumlahan dari sel H4 hingga K4.

Rumus ini kemungkinan digunakan untuk menghitung selisih atau sisa antara nilai di sel L4 dan total dari beberapa sel lainnya (H4 hingga K4), tetapi hanya jika sel G4 tidak kosong.

Membuat Grafik Visual (Opsional)

Jika Anda ingin menyajikan data dengan cara yang lebih visual, Anda dapat membuat rekap angsuran nasabah dilengkapi grafik yang menunjukkan perkembangan pembayaran dan sisa pinjaman dari waktu ke waktu. Pilih jenis grafik yang sesuai, seperti grafik garis atau batang, untuk memberikan gambaran yang jelas.

Pembaruan Berkala

Agar spreadsheet Anda tetap akurat, pastikan setelah membuat rekap angsuran nasabah di Excel untuk memperbarui data angsuran dan sisa pinjaman secara berkala. Ini membantu Anda melacak pembayaran dan memberikan informasi terkini kepada nasabah.

Kesimpulan

Dengan membuat rekap angsuran nasabah dan menghitung sisa pinjaman di Excel, Anda dapat mengelola pinjaman nasabah dengan lebih efisien dan transparan. Spreadsheet ini tidak hanya mempermudah pelacakan pembayaran, tetapi juga memberikan gambaran yang jelas tentang sisa pinjaman nasabah. Dengan menggunakan Excel sebagai alat bantu, tugas manajemen pinjaman menjadi lebih mudah dan terorganisir. Semoga panduan ini membantu Anda dalam mengelola pinjaman dengan lebih baik.

Dan terakhir, bagi yang membutuhkan file Cara Membuat Rekap Angsuran Nasabah dan Perhitungan Sisa Pinjaman di Excel ini, silahkan download file Excel ini melalui tautan di bawah: