Cara Menghitung Sliding Rate, Flat Rate, dan Effective Rate dengan Excel

Bunga kredit adalah kewajiban pihak peminjam kepada bank atas penyediaan uang oleh bank berdasarkan persetujuan atau kesepakatan pinjam-meminjam antara peminjam dan bank. Perhitungan bunga kredit harus dilakukan secara cermat untuk mengurangi dan menghindari risiko yang tidak diinginkan.

Bunga kredit adalah salah satu jenis pendapatan bank. Namun demikian, selain memberikan kontribusi bagi pendapatan bank, bunga kredit juga memiliki risiko yang tinggi, oleh karena itu perlu dikelola dengan baik.

Misalnya berkaitan dengan pencatatan dan perhitungan bunga kredit bank.

Bagaimana cara menghitung bunga kredit? Ikuti proses dan langkah sederhana-nya berikut ini …

 

01: Cara Menghitung Sliding Rate dengan Microsoft Excel

Jenis Bunga Kredit

Ada 3 jenis  bunga kredit yaitu:

  1. Sliding Rate
  2. Flat Rate
  3. Effective Rate

Bagaimana perhitungan bunga kredit sliding rateflat rate. floating rate?

Mari dibahas satu-per-satu ya…

 

Proses Menghitung Sliding Rate 

Yang pertama kita bahas cara menghitung bunga kredit sliding.

Untuk memudahkan dalam memahami cara ini,  saya menggunakan Excel sebagai alat untuk menghitung bunga sliding.

Perhatikan contoh berikut ini:

Sebagai ilustrasi dalam contoh ini digunakan data-data sebagai berikut:

  • Bunga pinjaman = 0,24%
  • Jangka waktu = 12 bulan
  • Pokok pinjaman = Rp 30.000.000

Jumlah pokok pinjaman cicilan pokok, cicilan bunga, angsuran total per bulan, dan saldo pokok pinjaman tiap bulan adalah:

Tabel: Perhitungan kredit bank sliding rate

Proses dan langkah-langkah perhitungan tabel di atas adalah sebagai berikut:

 

 #1: Kolom B : “Bulan ke”.

Untuk mengisikan nomor urut di kolom ini, ada beberapa cara yang bisa digunakan antara lain:

  • Input angka 1 pada B6 dan angka 2 di B7 serta angka 3 di B8.
  • Kemudian ketiga angka tersebut diblok, selanjutnya kursor pada kanan bawah blok dan tarik sampai ke B17
  • Perhatikan gambar berikut ini:
Membuat Periode Kredit

 

#2: Kolom C : “Bulan”

Untuk mengisi kolom ini, caranya sebagai berikut:

  • Pada baris C6, ketikan 1 Januari 2020
  • Baris C7, ketikan 1 Februari 2020
  • Pada baris C8, ketikan 1 Maret 2020
  • Kemudian diblok isian tersebut, letakkan kursor pada kanan bawah blok itu. Selanjutnya di tarik hingga ke C17.
  • Hasilnya adalah seperti gambar berikut ini:
Membuat Urutan Bulan Excel

 

#3: Kolom D: “Pokok Pinjaman”

Untuk mengisi kolom ‘pokok pinjaman’ caranya adalah:

  • Pada sel Excel D6, ketikan tanda “+” atau “=” kemudian arahkan kursor ke cell E3, selanjutnya klik enter.
  • Cell D7, masukkan persamaan “D6-E6” kemudian enter.
  • Pada cell D8, masukkan rumus persamaan “D7-E7” kemudian enter.
  • Blok dari D6 sampai dengan D7, kursor ada di kanan bawah blok, kemudian ditarik sampai D17.

Perhatikan gambar di bawah ini:

Menghitung pokok pinjaman

 

#4: Kolom E: “Cicilan Pokok”

Untuk melengkapi kolom E “Cicilan Pokok” ini langkah-langkahnya adalah:

  • Ketik di sel Excel E6 “=E$3/E$2” kemudian enter.
  • Hasilnya diblok dan letakkan kursor pada kanan bawah, selanjutnya drag sampai dengan sel E17
  • Selalu diingat ya, kotak kursor tetap berada di tempat yang kita inginkan, misalnya E6

Untuk lebih jelasnya, perhatikan gambar berikut ini:

Tabel Perhitungan Cicilan Pokok

 

#5: Kolom F: “Cicilan Bunga”

Untuk mengisi kolom “Cicilan Bunga” ini, proses dan langkahnya adalah:

  • Pada cell Excel F6 “=D6*(E$1/E$2)” kemudian enter.
  • Hasilnya kita blok dan kursor di kanan bawah F6, kemudian kita drag sama dengan F17.

Perhatikan gambar berikut ini:

Rumus Excel Untuk Menghitung Cicilan Bunga

 

#6: Kolom G: “Angsuran Total Per Bulan”

Cara mengisi kolom “Angsuran Total Per Bulan” langkah-langkahnya adalah seperti ini:

  • Pada sel G6 ketik “=E6+F6”, selanjutnya enter.
  • Kemudian kursor di kanan bawah sel G6, selanjutnya drag sampai dengan sel G17

Dan hasilnya seperti gambar di bawah ini:

Rumus Excel Menghitung Angsuran Total Per Bulan

 

#7: Kolom H: “Saldo Pinjaman”

Cara mengisi kolom H, “Saldo Pinjaman” prosesnya adalah seperti ini:

  • Pada sel H6 ketik “=D6-E6” kemudian enter.
  • Letakkan kursor pada kanan bawah sel H6, kemudian drag sampai dengan sel H17.

Maka hasilnya adalah sebagai berikut:

Proses Perhitungan Saldo Pinjaman

 

02: Cara Menghitung Bunga Kredit Flat dengan Excel

Proses Perhitungan Flat Rate

Untuk membantu dalam memahami proses dan langkah untuk menghitung bunga kredit flat dengan Excel, saya sajikan contoh berikut ini:

  • Bunga pinjaman = 0,24
  • Jangka Waktu = 12
  • Pokok Pinjaman Kredit = Rp 000.000

Dari data-data di atas, maka kita dapat menghitung jumlah pokok pinjaman, angsuran pokok, angsuran bunga, total angsuran per bulan, dan saldo pokok pinjaman sebagai berikut:

Tabel Perhitungan Bunga Kredit flat rate

Keterangan:

Proses dan langkah perhitungan bunga kredit flat seperti pada penghitungan bunga kredit sliding, untuk kolom #1, #2, #3, #4, #6, dan #7 sama dengan cara di atas.

Khusus untuk langkah ke-5 penghitungan “angsuran bunga” adalah sebagai berikut:

  • Pada cell F “Cicilan Bunga” ketik “=D$7*(E$2/E$3)”, kemudian enter
  • Hasil dari perhitungan itu selanjutnya diblok. Selanjutnya letakkan kursor pada kanan bawah sel F7, kemudian drag atau tarik sampai dengan sel F17.

 

Dan hasilnya adalah seperti di bawah ini:

Perhitungan cicilan bunga flat rate

Jadi, dari perhitungan bunga kredit  pada tabel Excel di atas, kita bsa melihat bahwa cicilan bunga flat rate jumlah sama sepanjang waktu kredit, yaitu Rp 600.000.

 

03: Cara Menghitung Bunga Kredit Efektif dengan Excel

Proses Perhitungan Effective Rate 

Untuk menghitung bunga kredit efektif, kita bisa menggunakan fungsi-fungsi keuangan di Microsoft Excel.

  • Menentukan angsuran pokok menggunakan fungsi financial Excel PPMT.
  • Untuk menentukan angsuran bunga, kita gunakan fungsi IPMT.
  • Sedangkan untuk menentukan angsuran total per periode (bulan) kita bisa gunakan fungsi PMT.

Untuk lebih jelasnya, perhatikan contoh menggunakan data-data berikut ini:

  • Bunga pinjaman = 0,24
  • Jangka waktu = 12 bulan
  • Pokok pinjaman = Rp 30.000.000
  • Tanggal pinjaman = 01 Desember 2019

***

Dari contoh data-data di atas, kita dapat menghitung angsuran pokok kredit, angsuran bunga kredit, dan angsuran total dengan langkah-langkah sebagai berikut:

#1: Mengisi Kolom Pokok Pinjaman

Untuk menentukan nomor urut “bulan ke” dan “bulan, tgl, tahun”caranya sama seperti langkah yang dilakukan sebelumnya dalam menghitung bunga flat dan sliding.

  • Untuk mengisi sel C11, ketik “=D5” kemudian enter.
  • Hasilnya adalah sebagai berikut:
Tabel Perhitungan Bunga Kredit Efektif – Pokok Pinjaman

 

#2: Menghitung angsuran pokok (kolom D “cicilan pokok”).

Langkah-langkah untuk menentukan cicilan pokok adalah sebagai berikut:

  • Gunakan fungsi Financial Excel, PPMT
  • Input contoh data-data di atas sebagai berikut:
Tabel Perhitungan Bunga Kredit dengan fungsi PPMT Excel

Keterangan:

  • Rate = Tingkat suku bunga
  • Per = Angsuran ke..
  • Nper = Periode kredit
  • PV = Nilai sekarang kredit

 

Setelah data-data diinput, hasilnya adalah seperti di bawah ini:

Tabel hasil perhitungan angsuran pokok kredit dengan fungsi PPMT

Untuk menghitung angsuran pokok bulan Februari 2020 sampai bulan Desember 2020, caranya dengan meng-copy sel D11.

Kemudian paste ke sel D12 hingga sel 22.

 

#3: Menghitung angsuran bunga (kolom E “cicilan bunga”)

Langkah-langkah untuk menghitung angsuran bunga adalah sebagai berikut:

  • Letakkan kursor pada E11, kemudian pilih menu “Formula”, lalu klik fungsi Financial. Selanjutnya pilih dan klik IPMT. Perhatikan gambar berikut:
Rumus Excel untuk menghitung angsuran bunga kredit

 

  • Masukkan contoh data-data di atas seperti berikut:
Perhitungan cicilan bunga kredit dengan fungsi IPMT Excel

 

  • Setelah semua data di-input, hasilnya adalah:
Tabel hasil perhitungan cicilan bunga kredit
  • Selanjutnya, untuk menghitung angsuran bunga bulan Februari 2020 sampai bulan Desember 2020, kita tinggal meng-copy sel E11, kemudian paste sel E12 sampai sel E22.

 

#4: Menghitung angsuran total per bulan (kolom F )

Untuk menghitung angsuran total per periode, langkah-langkahnya adalah:

  • Buka fungsi Financial Excel PMT, kemudian input data-data seperti berikut ini:
Perhitungan angsuran total kredit dengan fungsi PMT

 

  • Hasilnya adalah:
Tabel Hasil Perhitungan angsuran total kredit
  • Untuk menghitung angsuran total bulan Februari 2020 sampai bulan Desember 2020, caranya dengan meng-copy sel F11. Kemudian paste ke sel F12 sampai sel F22.

 

#5: Menghitung jumlah saldo pokok per bulan

Proses untuk menghitung jumlah saldo per bulan (kolom G) caranya adalah:

  • Letakkan kursor di sel G11, ketik “=C11-D11” kemudian enter.
  • Hasilnya adalah:.
Hasil perhitungan saldo pokok pinjaman
  • Copy sel G11 dengan meng-klik Ctrl + C. Selanjutnya Paste di cell G12 sampai G22. Caranya letakkan kursor di sel G12, kemudian diblok sampai sel G22. Kemudian klik Ctrl + V.

 

#6: Menghitung jumlah pokok pinjaman bulan Februari 2020

Jumlah pokok pinjaman bulan Februari 2020 sampai dengan Desember 2020 bisa dihitung dengan langkah-langkah sebagai berikut:

  • Letakkan kursor di sel C12, ketik “=C11-D11”, kemudian enter.
  • Dan hasilnya adalah:
Hasil perhitungan pokok pinjaman

***

Setelah semua proses dan langkah-langkah di atas dilakukan, hasil selengkapnya adalah:

Tabel Perhitungan effective Rate

 

04: Konversi dari Bunga Kredit Efektif ke Flat

Cara Menghitung Konversi Bunga

Untuk menghitung konversi dari sistem bunga efektif ke bunga flat bisa dihitung sebagai berikut:

= (Total Angsuran Per Bulan – Pokok Pinjaman Bulan Januari 2020) : Pokok Pinjaman Bulan Januari 2020

= (Rp 34.041.455 – Rp 30.000.000) : Rp 30.000.000
= Rp  4.041.455 : Rp 30.000.000
= 13%

Pada konversi ini tidak dibagi dengan Nper (periode kredit), karena kredit berjangka waktu satu tahun.

Jika Nper (periode kredit) lebih dari satu tahun maka hasilnya dibagi Nper (periode kredit) dulu.

***

Untuk contoh kasus di atas, akad kredit terjadi tanggal 31 Desember 2020, sehingga angsuran dimulai setiap akhir periode atau 1 Januari 2020.

Pada kotak function arguments, kita tidak mengisi type, berarti dianggap angsuran di belakang dengan tipe 0.

Tapi bila angsuran di muka setiap awal bulan maka kita input 1 pada kotak type.

Kredit dengan tipe 1 umumnya terjadi pada kredit barang, misalnya perhitungan bunga kredit mobil (misalnya KKB Bank BRI), perhitungan bunga kredit motor, dan perhitungan bunga kredit HP.

 

Kesimpulan

Secara umum ada tiga jenis bunga kredit, yaitu sliding rate, flat rate, dan effective rate. Untuk menghitung tiga jenis bunga kredit tersebut bisa dilakukan dengan Microsoft Excel.

Cara ini menurut kami relatif mudah dan bisa dilakukan oleh sebagian besar pemilik komputer desktop, laptop, notebook dan telepon genggam.

Demikian yang dapat saya bagikan tentang cara perhitungan bunga kredit bank dengan Microsoft Excel.

Semoga bermanfaat. Terima kasih.

Profesional lulusan ekonomi yang menekuni ERP (SAP), Accounting Software, Business Analyst dan berbagi pengalaman pekerjaan Finance & Accounting.