Jadwal Angsuran Otomatis

Assalammualaikum wrwb…

Pembaca yang budiman, kali ini saya mencoba membahas tentang cara pembuatan jadwal angsuran dengan menggunakan excel, hal ini bermula dari kejadian dikantor saya sendiri. Seluruh pegawai dikantor tempat saya bekerja bergabung didalam suatu lembaga koperasi yang dikelola oleh pengurus yang juga berasal dari karyawan dan kebetulan saya juga menjabat sebagai pengawas dikoperasi tersebut....

Permasalahannya bermula dari kebutuhan yang mendesak akan sebuah template atau kalkulator yang dapat menghitung pinjaman dari salah satu bank syariah di Indonesia  agar dapat diketahui berapa besar manfaat yang didapat dan berapa angsuran yang harus dibayar, serta seorang anggota dengan gaji sekian apakah bisa meminjam sejumlah pinjaman dari koperasi.

Template yang akan kita buat nantinya akan secara otomatis menghasilkan jadwal angsuran, sesuai pilihan atau isian yang telah kita tentukan didepan, antara lain :

1.      Penghasilan per bulan

2.      Limit (% dr Gaji) >> batasan maksimal nilai angsuran dihitung dari total gaji

3.      Nilai Pinjaman

4.      Lama pinjaman (Thn)

5.      Angsuran Sebelumnya >> besaran angsuran yang masih tersisa, jika ada

6.      Bunga per Tahun

7.      Bunga efektif atau flat

Dengan mengisi parameter tersebut diatas maka output jadwal angsuran akan ter-generate secara otomatis, berikut penampakannya.

Namun sebelum kita lanjutkan pembahasan ini, perlu saya garis bawahi bahwa bunga adalah riba dan riba hukumnya haram. Meskipun demikian perhitungan bagi hasil yang ada pada bank-bank syariah di Indonesia masih menggunakan cara yang sama sehingga masih dapat digunakan cara perhitungannya.

Langkah pertama

Silahkan download data kertas kerja untuk memulai bekerja disini.

Template perhitungan pinjaman tampilan awal

Didalam file master data diatas terdiri dari cell warna putih dan cell warna kuning, cell warna kuning berfungsi sebagai parameter untuk kita isi, sedangkan cell warna putih adalah cell yang berisikan fungsi-fungsi yang sebentar lagi akan kita bahas dan sifatnya tidak boleh di rubah-rubah untuk menjaga ke-konsistenan fungsi template ini.

Silahkan isi parameter-parameter yang ada pada cell berwarna kuning dengan sembarang angka, contohnya seperti gambar dibawah ini

Parameter awal

Pada gambar diatas untuk cell efektif atau flat sengaja tidak saya isi karena kita akan buat fitur data validation agar pilihan dapat dikerucutkan menjadi 2 pilihan saja yaitu Efektif dan Flat

Silahkan sorot cell A18 dan ketik “ EFEKTIF”

Silahkan sorot cell A19 dan ketik “ FLAT”

Link Efektif dan Flat

Kemudian sorot cell B10 , lalu pilih menu Data >> Data Validation

Data validation menu

Kemudian pada jendela Data validation, isi field Allow: dengan LIST dan field Source : =$A$18:$A$19

Jendela setting data validation

Langkah Kedua

Sorot cell B12 kemudian isi dengan fungsi =B7*12

Sorot cell B13 kemudian isi dengan fungsi =B4*B5

Sorot cell B14 kemudian isi dengan fungsi =G13

Sorot cell B15 kemudian isi dengan fungsi =B15/B14

 Hasilnya seperti dibawah ini

Langkah ke Tiga

Pada langkah ke tiga ini kita akan membuat jadwal angsuran mulai dari cell E13 s/d J72, kita hanya perlu membuat satu baris fungsi saja untuk setiap kolom, selanjutnya fungsi tersebut tinggal kita copy sampai kebawah cell J72.

Sorot cell E13 lalu ketik 1

Sorot cell F13 lalu ketik =B6

Sorot cell G13 lalu ketik =IF(E13<>"";H13+I13;"")

Sorot cell H13 lalu ketik

=IF(E13<>"";IF(LEFT($B$10;1)="E";-PPMT($B$9/12;E13;$B$13;$B$6);$B$6/$B$13);"")

Sorot cell I13 lalu ketik

=IF(E13<>"";IF(LEFT($B$10;1)="E";-IPMT($B$9/12;E13;$B$13;$B$6);($B$6*$B$9*$B$7)/$B$13);"")

Dan yang terakhir adalah sorot cell J13 kemudian ketik =IFERROR(F13-H13;"")

Dari rumus diatas perlu saya jelaskan sedikit khususnya dikolom pokok dan kolom bunga bahwa saya menggunakan dua rumus perhitungan bunga, yang pertama adalah dengan cara efektif menggunakan fungsi PPMT dan IPMT, dan yang kedua menggunakan pembagian normal.

PPMT atau singkatan dari Principal payment mempunyai struktur fungsi sbb:

PPMT(rate, per, nper, pv, [fv], [type])

Dimana

Rate  adalah besaran bunga pertahun.

Per  adalah periode pembayaran, mulai dari 1 s/d nper (jika lama angsuran satu tahun)

Nper  adalah total jumlah pembayaran selama masa pinjaman, missal masa pinjaman satu tahun maka di isi dengan 12 jika dua tahun maka di isi dengan 24 (dengan asumsi pembayaran angsuran dilakukan satu kali setiap bulannya)

Pv  adalah nilai uang yang di pinjam

Fv  Optional

Type  Optional

IPMT atau singkatan dari Interest payment mempunyai struktur fungsi yang hampir sama dengan PPMT, yaitu:

IPMT(rate, per, nper, pv, [fv], [type])

Jika sudah mengerti silahkan perhatikan screenshoot kertas kerja saya dibawah ini, gambar dibawah ini akan tampak bila anda telah berhasil memaksukkan fungsi di cell E13 s/d I13 diatas.

Langkah ke Empat

Sorot cell E14 lalu ketik =IFERROR(IF(E13+1<=$B$13;E13+1;"");"")

Sorot cell F14 lalu ketik =J13

Sorot cell G14 lalu ketik =IF(E14<>"";H14+I14;"")

Sorot cell H14 lalu ketik

=IF(E14<>"";IF(LEFT($B$10;1)="E";-PPMT($B$9/12;E14;$B$13;$B$6);$B$6/$B$13);"")

Sorot cell I14 lalu ketik

=IF(E14<>"";IF(LEFT($B$10;1)="E";-IPMT($B$9/12;E14;$B$13;$B$6);($B$6*$B$9*$B$7)/$B$13);"")

Sorot cell J14 lalu ketik =IFERROR(F14-H14;"")

Jika sudah melakukan langkah diatas perhatikan screenshoot dibawah ini

Langkah ke Lima

Sorot cell E14 s/d J14 lalu klik kanan COPY dan PASTE ke bawah s/d cell E72, seperti ini hasilnya

Langkah ke Enam

Sorot Cell G3 pada field penilaian dan isikan dengan rumus =IF(B16<=B5;"DITERIMA";"TIDAK DITERIMA")

Sorot Cell G4 lalu ketik =SUM(H13:H200)

Sorot Cell G5 lalu ketik =SUM(I13:I200)

Sorot Cell G6 lalu ketik =G4+G5

Sorot Cell H4 lalu ketik

=IF($G$3="DITERIMA";"Penghasilan sebesar "&TEXT(B4;"0.00")&" mempunyai limit pembayaran sebesar "&TEXT(B14;"0.00")&" dan nilai ini masih lebih besar dari pada angsuran per bulan sebesar "&TEXT(B16;"0.0%")&" atau sebesar "&TEXT(B15;"0.00")&" per bulan.";$H$7)

Sorot Cell H7 lalu ketik

="Penghasilan sebesar "&TEXT(B4;"0.00")&" mempunyai limit pembayaran sebesar "&TEXT(B14;"0.00")&" dan nilai ini masih lebih kecil dari pada angsuran per bulan sebesar "&TEXT(B16;"0.0%")&" atau sebesar "&TEXT(B15;"0.00")&" per bulan."

UNtuk Cell H7 pastikan anda rubah fontnya menjadi warna putih agar tidak terlihat dan terbaca sedangkan H4 anda rubah fontnya menjadi warna biru agar eye catching

Untuk cell H4 dan H7 memang mempunyai rumus yang agak panjang, karena berfungsi sebagai komentator otomatis terkait apakah pinjaman diterima atau tidak.

Jika sudah seperti ini hasilnya.

Langkah ke Tujuh

Save dan backup hasil kerja anda, silahkan melakukan testing parameter pada field yang berwarna kuning untuk melihat apakah template kita berfungsi dengan baik. Saya rasa cukup sekian sampai disini, saya ucapkan selamat bagi anda yang berhasil menyelesaikannya.

Seperti biasa file yang sudah jadi dapat anda download diSINI

Bila ada pertanyaan silahkan comment, diwaktu luang akan saya sisihkan untuk menjawab perntanyaan2 saudara.

Wassalam…