Cara Menghitung Rasio Kolektibilitas Pinjaman di Excel (RR, NPL, LAR, dan PAR)

Bagi koperasi simpan pinjam atau lembaga keuangan mikro, cara menghitung rasio kolektibilitas merupakan hal yang sangat penting untuk menilai sejauh mana pinjaman berjalan lancar dan seberapa besar risiko kredit yang dihadapi. Rasio kolektibilitas tidak hanya menggambarkan kondisi pembayaran pinjaman, tetapi juga menjadi indikator kesehatan keuangan koperasi secara keseluruhan.

Dalam praktiknya, ada empat rasio utama yang biasa digunakan dalam cara menghitung rasio kolektibilitas, yaitu:

1. RR (Repayment Rate)
2. NPL (Non Performing Loan)
3. LAR (Loan at Risk)
4. PAR (Portfolio at Risk)

Keempat rasio ini bisa dihitung secara otomatis di Microsoft Excel menggunakan fungsi-fungsi seperti SUM, SUMIFS, dan pembagian sederhana. Dengan sedikit logika, Anda bisa membangun dashboard analisis kolektibilitas pinjaman yang rapi dan mudah diperbarui setiap bulan.

Note: Artikel Cara Menghitung Rasio Kolektibilitas Pinjaman di Excel ini adalah lanjutan dari artikel sebelumnya yang berjudul: Cara Membuat Penilaian Kolektibilitas Pinjaman Secara Otomatis di Excel dan Rumus Kolektibilitas Pinjaman di Excel untuk Menghitung Rasio Kolektibilitas.

Contoh Tabel Dasar Penilaian Kolektibilitas

Berikut contoh data yang digunakan untuk menghitung rasio kolektibilitas pinjaman di Excel:

1. RR (Repayment Rate)

Repayment Rate (RR) adalah rasio yang menunjukkan seberapa besar persentase angsuran yang berhasil dibayar dibandingkan dengan total angsuran yang seharusnya dibayar.

Rumus Excel yang digunakan:

=SUM(H4:H18)/SUM(G4:G18)*100%

Penjelasan Rumus:

• SUM(H4:H18) → Menjumlahkan seluruh kolom Dibayar, yaitu total angsuran yang benar-benar diterima koperasi.
• SUM(G4:G18) → Menjumlahkan kolom Seharusnya, yaitu total angsuran yang seharusnya dibayar.
• Dibagi dan dikalikan 100% → Untuk mendapatkan nilai dalam bentuk persentase.

Hasil:

Jika total angsuran seharusnya adalah Rp11.900.000 dan total yang dibayar Rp8.250.000, maka:

RR = (8.250.000 / 11.900.000) x 100% = 69,5%

Artinya, tingkat pembayaran (Repayment Rate) koperasi adalah 69,5%. Nilai ini mengindikasikan masih ada keterlambatan pembayaran oleh sebagian anggota.

2. NPL (Non Performing Loan)

NPL menunjukkan persentase pinjaman yang sudah termasuk kategori Kurang Lancar, Diragukan, dan Macet dibandingkan dengan total pinjaman yang ada. Rasio ini sering disebut juga sebagai rasio pinjaman bermasalah.

Rumus Excel:

=((SUMIFS(D4:D18;F4:F18;"Kurang Lancar")
 + SUMIFS(D4:D18;F4:F18;"Diragukan")
 + SUMIFS(D4:D18;F4:F18;"Macet"))
 / SUM(D4:D18)) * 100%

Penjelasan Rumus:

• SUMIFS(D4:D18;F4:F18;”Kurang Lancar”) → Menjumlahkan pinjaman anggota dengan status Kurang Lancar.
• SUMIFS(D4:D18;F4:F18;”Diragukan”) → Menjumlahkan pinjaman dengan status Diragukan.
• SUMIFS(D4:D18;F4:F18;”Macet”) → Menjumlahkan pinjaman yang Macet.
• Semua hasil dijumlahkan, lalu dibagi total pinjaman (SUM(D4:D18)) dan dikalikan 100%.

Hasil:

Dari contoh tabel, hasilnya adalah 54,6%, artinya sekitar setengah dari total pinjaman berada dalam kondisi tidak lancar. Dalam praktik keuangan, rasio ini sebaiknya di bawah 5% untuk menunjukkan kondisi portofolio kredit yang sehat. Semakin tinggi NPL, semakin tinggi pula risiko gagal bayar yang dihadapi koperasi.

3. LAR (Loan at Risk)

Loan at Risk (LAR) adalah rasio yang lebih luas dibandingkan NPL, karena memasukkan kategori DPK ke dalam perhitungan. LAR menunjukkan total pinjaman yang berisiko atau berpotensi bermasalah, termasuk yang masih dalam perhatian khusus.

Rumus Excel:

=(SUMIFS(D4:D18;F4:F18;"DPK") +
SUMIFS(D4:D18;F4:F18;"Kurang Lancar") +
SUMIFS(D4:D18;F4:F18;"Diragukan") +
SUMIFS(D4:D18;F4:F18;"Macet")) / SUM(D4:D18) * 100%

Penjelasan Rumus:

• Menjumlahkan seluruh nilai pinjaman dengan status DPK, Kurang Lancar, Diragukan, dan Macet.
• Lalu dibagi dengan total pinjaman untuk mengetahui persentasenya.

Hasil:

Hasil perhitungan dari data di atas menghasilkan 75,6%, artinya 75,6% dari total pinjaman masuk kategori berisiko. Nilai ini menunjukkan bahwa hanya sebagian kecil portofolio yang benar-benar lancar.

Dalam cara menghitung rasio kolektibilitas, LAR penting karena memberi gambaran lebih awal terhadap potensi kredit bermasalah sebelum benar-benar jatuh ke kategori macet.

4. PAR (Portfolio at Risk)

Portfolio at Risk (PAR) menunjukkan persentase dari total pinjaman yang menunggak lebih dari 30 hari. Rasio ini digunakan secara internasional untuk menilai kesehatan portofolio pinjaman lembaga keuangan mikro.

Rumus Excel:

=SUMIFS(D4:D18;E4:E18;">30")/SUM(D4:D18)*100%

Penjelasan Rumus:

• SUMIFS(D4:D18;E4:E18;”>30″) → Menjumlahkan seluruh pinjaman dengan Tunggakan (Hari) lebih dari 30.
• SUM(D4:D18) → Total keseluruhan pinjaman.
• Hasilnya dikalikan 100% agar tampil dalam bentuk persentase.

Hasil:

Berdasarkan contoh tabel, PAR = 75,6%, yang berarti 75,6% dari total portofolio pinjaman memiliki tunggakan lebih dari 30 hari. Dalam praktik sehat, rasio PAR sebaiknya di bawah 10%.

Mengapa Penting Memahami Cara Menghitung Rasio Kolektibilitas?

Ada beberapa alasan mengapa koperasi harus memahami dan rutin melakukan cara menghitung rasio kolektibilitas, di antaranya:

1. Mengetahui tingkat kelancaran pembayaran.
   Rasio RR memberi gambaran sejauh mana anggota disiplin membayar angsuran.
2. Mendeteksi risiko sejak dini.
   Melalui LAR dan PAR, koperasi bisa mengetahui pinjaman mana yang mulai berisiko sehingga dapat diambil tindakan penagihan lebih awal.
3. Menentukan cadangan kerugian pinjaman.
   Rasio NPL menjadi dasar dalam menentukan berapa banyak dana yang harus disiapkan untuk menutup potensi kerugian.
4. Meningkatkan profesionalitas laporan keuangan.
   Dengan menggunakan Excel dan rumus otomatis, laporan kolektibilitas menjadi lebih akurat, konsisten, dan cepat disusun.

Tips Otomatisasi dan Visualisasi di Excel

Agar analisis cara menghitung rasio kolektibilitas semakin efektif, Anda bisa menambahkan beberapa fitur pendukung:

• Gunakan Conditional Formatting untuk memberi warna otomatis pada status kolektibilitas (misalnya merah untuk macet, kuning untuk DPK).
• Gunakan Chart (diagram batang/pie) untuk menampilkan perbandingan RR, NPL, LAR, dan PAR.
• Gunakan Pivot Table untuk menghitung rasio berdasarkan cabang, wilayah, atau petugas kredit.
• Buat Dashboard Interaktif agar pimpinan koperasi bisa memantau perkembangan kolektibilitas dari bulan ke bulan.

Kesimpulan

Melalui panduan ini, Anda telah memahami cara menghitung rasio kolektibilitas pinjaman secara otomatis di Excel menggunakan empat indikator utama:

1. RR (Repayment Rate) → Mengukur tingkat pembayaran angsuran.
2. NPL (Non Performing Loan) → Mengukur pinjaman bermasalah.
3. LAR (Loan at Risk) → Mengukur pinjaman yang berisiko.
4. PAR (Portfolio at Risk) → Mengukur pinjaman dengan tunggakan lebih dari 30 hari.

Dengan menggunakan rumus-rumus sederhana seperti SUM, SUMIFS, dan operasi pembagian, Anda bisa mendapatkan analisis kolektibilitas yang akurat, efisien, dan siap digunakan untuk pengambilan keputusan manajerial.

Jadi, mulailah menerapkan cara menghitung rasio kolektibilitas di koperasi Anda menggunakan Excel agar pengelolaan pinjaman lebih transparan, profesional, dan mudah dipantau dari waktu ke waktu.

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Cara Menghitung Rasio Kolektibilitas Pinjaman di Excel ini. File Excel bisa didownload melalui tautan berikut:

Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!

—