Membuat Tabel Angsuran dan Denda Kredit Motor dengan Rumus Excel

oleh | 25 Mar 2024 | Belajar Excel, Excel Elementary, Excel Intermediate, Tips Excel | 0 Komentar

Pada dasarnya, kredit sepeda motor tipe flat adalah jenis kredit di mana jumlah angsuran bulanan tetap sepanjang masa kredit. Artinya, Anda membayar jumlah yang sama setiap bulan hingga lunas. Dalam mengelola kredit sepeda motor tipe flat, penting untuk membuat tabel angsuran dan denda kredit motor yang memudahkan pemantauan angsuran dan potensi denda yang mungkin timbul akibat keterlambatan pembayaran. Berikut adalah langkah-langkah detail dalam membuat tabel Angsuran menggunakan rumus denda dengan Excel:

Membuat Tabel Angsuran dan Denda Kredit Motor dengan Kolom yang Relevan

Untuk membuat tabel angsuran dan denda kredit motor, pertama-tama, buatlah tabel Excel dengan kolom-kolom yang sesuai. Kolom yang umumnya dibutuhkan meliputi:

  • Harga beli: Harga Motor dan Uang Muka
  • Tenor Kredit: waktu atau durasi kredit
  • Bunga Kredit: Bunga per tahun dari kredit tersebut
  • Total Angsuran: Jumlah total yang harus dibayarkan setiap bulan (angsuran pokok + bunga).
  • Jatuh Tempo: Tanggal Jatuh Tempo pembayaran kredit
  • Denda: Jumlah denda yang harus dibayarkan jika terjadi keterlambatan pembayaran.

Dan berikut adalah contoh tabel yang bisa digunakan sebagai referensi:

Membuat Tabel Angsuran dan Denda Kredit Motor dengan Rumus Excel

Menghitung Besaran Angsuran

Sebelum lebih jauh membuat tabel angsuran dan denda kredit motor, pertama yang harus kita lakukan adalah menghitung besaran angsuran kredit sepeda motor tersebut menggunakan rumus Excel sederhana. Dan rumus Excel yang bisa digunakan untuk menghitung angsuran bulanan ditambah bunga di sel G5 adalah sebagai berikut:

=((E4-E5)+((E4-E5)G4C5))/(C5*12)

Rumus Excel di atas adalah untuk menghitung angsuran dan bunga flat yang dikenakan pada umumnya untuk jenis kredit sepeda motor. Apa itu bunga flat? jumlah angsuran pokok dan bunga yang dikenakan tetap dan tidak berubah sampai kredit tersebut lunas.

Dan jika benar, maka hasilnya adalah sebagai berikut:

rumus Excel untuk menghitung besaran angsuran kredit motor

Penjelasan Rumus

Rumus Excel terkait membuat tabel angsuran dan denda kredit motor ini merupakan rumus untuk menghitung angsuran kredit. Mari kita jelaskan secara detail dan lengkap bagian per bagian dari rumus tersebut:

1. ((E4-E5)+((E4-E5)*G4*C5)): Bagian ini menghitung jumlah angsuran pokok dan bunga sebelum dibagi dengan jumlah bulan.

  • (E4-E5): Ini menghitung selisih antara jumlah pokok (harga Motor) dikurangi uang muka (E5). Hasilnya adalah jumlah pokok (belum termasuk bunga) yang akan diangsur tiap bulan.
  • ((E4-E5)*G4*C5): Ini menghitung jumlah bunga yang harus dibayar tiap bulannya.
    • (E4-E5): Ini adalah selisih jumlah pokok seperti yang dijelaskan di atas.
    • G4: Ini adalah suku bunga tahunan yang dinyatakan dalam bentuk desimal (misalnya, 0,07 untuk suku bunga 7%).
    • C5: Ini adalah durasi kredit atau tenor pembayaran angsuran.
Baca Juga  Memahami dan Menggunakan Rumus IFERROR di Microsoft Excel

2. (C5*12): Bagian ini menghitung jumlah bulan untuk pembayaran angsuran.

  • C5: Ini adalah durasi kredit atau tenor pembayaran angsuran.
  • *12: Ini mengalikan sisa saldo pokok dengan 12 untuk mendapatkan jumlah bulan, karena umumnya angsuran kredit dihitung per tahun.

Dengan menggabungkan kedua bagian ini, rumus tersebut menghitung jumlah angsuran yang harus dibayar setiap bulan berdasarkan sisa saldo pokok, suku bunga tahunan, dan pembayaran pokok yang dibuat pada bulan ini. Rumus tersebut memperhitungkan bagaimana pembayaran pokok dan bunga berkontribusi terhadap total angsuran bulanan.

Menghitung Denda Keterlambatan

Setelah mengetahui angsuran per bulannya (pokok dan bunganya) maka langkah berikutnya untuk membuat tabel angsuran dan denda kredit motor adalah menghitung denda keterlambatan untuk mengingatkan kita bahwa ketika pembayaran angsuran kredit sepeda motor kita lewat dari jatuh tempo maka akan dikenakan denda keterlambatan. Adapun besarnya denda keterlambatan dalam contoh ini adalah 0,05% per hari dihitung dari jumlah angsuran.

Dan ketentuan dendanya adalah sebagai berikut: Jika angsuran dibayar sebelum jatuh tempo atau tepat saat jatuh tempo maka tidak dikenakan denda. Sementara jika angsuran dibayarkan setelah melewati tanggal jatuh tempo maka akan dikenakan denda sebesar 0,05% per hari dihitung dari H+1 setelah tanggal jatuh tempo.

Dengan ketentuan tersebut di atas maka jika terjadi keterlambatan pembayaran, akan dikenakan denda. Untuk menghitung denda, Anda dapat menggunakan kombinasi fungsi IF Bertingkat dengan TODAY dengan rangkaian rumus denda Excel berikut di sel F10:

=IF(C10>TODAY();0;IF(D10="";$G$50,5%ABS(C10-C11);IF(C10<D10;$G$50,5%ABS(C10-D10);0)))

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

tabel denda keterlambatan kredit motor

Penjelasan Rumus

Rumus Excel ini digunakan untuk menghitung besaran denda keterlambatan pembayaran kredit. Mari kita jelaskan bagian per bagian dari rumus tersebut:

Baca Juga  Cara Membuat Rekapitulasi Omzet dan Analisis Pareto dengan Rumus Excel

1. IF(C10>TODAY();0; ... ): Bagian ini menguji apakah tanggal pembayaran (C10) lebih besar dari tanggal hari ini. Jika iya, maka denda akan dihitung sebagai 0.

  • C10: Ini adalah sel yang berisi tanggal pembayaran yang seharusnya dilakukan.
  • TODAY(): Ini adalah fungsi Excel yang mengembalikan tanggal hari ini.

2. IF(D10="";$G$5*0,5%*ABS(C10-C11); ... ): Bagian ini memeriksa apakah ada tanggal sebenarnya untuk pembayaran yang terlambat (D10). Jika tidak ada, maka denda dihitung berdasarkan selisih hari dari tanggal seharusnya (C10) dan tanggal aktual pembayaran (C11).

  • D10: Ini adalah sel yang berisi tanggal pembayaran sebenarnya (jika ada).
  • "": Ini adalah kondisi yang menguji apakah tidak ada tanggal pembayaran sebenarnya (sel kosong).
  • $G$5*0,5%*ABS(C10-C11): Ini adalah rumus untuk menghitung denda jika tidak ada tanggal pembayaran sebenarnya.
    • $G$5: Ini adalah sel yang berisi nilai pokok kredit atau jumlah utang.
    • 0,5%: Ini adalah persentase denda, dalam hal ini 0,5% dari nilai angsuran kredit.
    • ABS(C10-C11): Ini menghitung selisih absolut antara tanggal seharusnya dan tanggal pembayaran aktual.

3. IF(C10<D10;$G$5*0,5%*ABS(C10-D10);0): Bagian ini menghitung denda jika tanggal pembayaran sebenarnya (C11) lebih besar dari tanggal seharusnya (C10).

  • C10<D10: Ini adalah kondisi yang menguji apakah tanggal pembayaran sebenarnya lebih besar dari tanggal seharusnya.
  • $G$5*0,5%*ABS(C10-D10): Ini adalah rumus untuk menghitung denda jika tanggal pembayaran sebenarnya lebih besar dari tanggal seharusnya.
    • $G$5: Ini adalah sel yang berisi nilai pokok kredit atau jumlah utang.
    • 0,5%: Ini adalah persentase denda, dalam hal ini 0,5% dari nilai angsuran kredit.
    • ABS(C10-D10): Ini menghitung selisih absolut antara tanggal seharusnya dan tanggal pembayaran sebenarnya.

Jadi, keseluruhan rumus tersebut menguji berbagai kondisi terkait dengan tanggal pembayaran kredit dan menghitung denda keterlambatan berdasarkan persentase tertentu dari nilai pokok kredit.

Menghitung Jumlah Kredit (Pokok + Bunga)

Terakhir dari membuat tabel angsuran dan denda kredit motor ini, kita akan menghitung berapa sih total pembayaran yang harus kita keluarkan sampai kredit sepeda motor itu lunas dengan bunga 7% per tahun dan tenor 1 tahun. Untuk keperluan menghitung total pembayaran yang harus kita keluarkan sampai kredit sepeda motor itu lunas dengan bunga 7% per tahun dan tenor 1 tahun adalah sebagai berikut:

=(E4-E5)+(E4-E5)*G4  

Dan jika ditulis dengan benar maka hasilnya sebagai berikut:

Baca Juga  Mengungkap Rahasia Penggunaan Fungsi SUMSQ di Excel
tabel sisa pinjaman angsuran kredit motor

Penjelasan Rumus

Rumus Excel ini adalah rumus untuk menghitung total angsuran kredit berdasarkan harga motor (E4), dikurangi uang muka (E5), dan ditambah suku bunga tahunan (G4). Mari kita jelaskan bagian per bagian dari rumus tersebut:

1. (E4-E5): Bagian ini menghitung selisih antara jumlah pokok kredit (harga sepeda motor) di sel E4 dan uang muka yang disetor di sel E5. Hasilnya adalah jumlah pokok yang akan diangsur pada bulan ini.

  • E4: Ini adalah sel yang berisi harga sepeda motor.
  • E5: Ini adalah sel yang berisi pembayaran uang muka.

2. (E4-E5)*G4: Bagian ini menghitung jumlah bunga yang harus dibayar.

  • (E4-E5): Ini adalah selisih jumlah pokok seperti yang dijelaskan di atas.
  • G4: Ini adalah suku bunga tahunan yang dinyatakan dalam bentuk desimal (misalnya, 0,07 untuk suku bunga 7%).

Jadi, keseluruhan rumus tersebut menghitung total angsuran kredit sebagai jumlah dari pembayaran pokok dan jumlah bunga yang harus dibayar yang dihitung berdasarkan suku bunga tahunan.

Menghitung Sisa Pinjaman

Dan karena kita sudah mengetahui total angsuran dan denda keterlambatannya, maka langkah berikutnya adalah menghitung sisa pinjaman tiap bulannya. Ini bisa dilakukan dengan dua metode yaitu apakah menyertakan denda sebagai sisa pinjaman atau mengabaikannya. Dan berikut adalah hasilnya ketika kita memilih untuk mengabaikan dendanya:

Hasil akhir Membuat Tabel Angsuran dan Denda Kredit Motor dengan Rumus Excel

Penutup

Pastikan untuk mengupdate data secara berkala sesuai dengan pembayaran yang dilakukan agar tabel tetap akurat. Anda juga dapat menambahkan kolom-kolom tambahan sesuai kebutuhan.

Dengan membuat tabel angsuran dan denda kredit motor dengan rumus Excel yang rapi dan terstruktur, Anda dapat memantau dengan lebih baik pembayaran kredit motor tipe flat dan menghindari denda akibat keterlambatan pembayaran. Selamat mengelola keuangan Anda dengan baik!

Dan bagi yang membutuhkan file contoh Membuat Tabel Angsuran dan Denda Kredit Motor dengan Rumus Excel ini, file Excel bisa diunduh melalui tautan di bawah ini:

Gambar ini memiliki atribut alt yang kosong; nama berkasnya adalah download-1024x272.jpg

Terakhir, jika membutuhkan tutorial Membuat Tabel Angsuran dan Denda Kredit Motor dengan Rumus Excel secara visual, bisa ditonton videonya di Youtube Channel: @DepotExcel

0 Komentar

Trackbacks/Pingbacks

  1. Cara Mudah Sembunyikan Baris Otomatis di Excel untuk Setiap Pinjaman Lunas - Depot Excel - […] Sebagai contoh, berikut adalah tabel data yang berisi Nama Nasabah, Plafond Pinjaman, Tanggal Jatuh Tempo dan Keterangan Pinjaman. […]

Kirim Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Join Our Newsletter