Cara Mudah Membuat Tabel Cicilan KPR Rumah Dengan Rumus Dalam Microsoft Excel - webpediax

Membuat Tabel Cicilan KPR Dalam Excel - Dalam beberapa artikel sebelumnya saya membahas seputar penghitungan angsuran KPR rumah.

Angsuran KPR rumah ini sebenarnya dalam Excel bisa dihitung dengan sangat mudah menggunakan rumus PMT.

Ada dua artikel berbeda yang membahas tentang angsuran KPR dengan rumus dalam Excel ini, pertama angsuran untuk KPR konvensional dan yang kedua angsuran KPR syariah.

Untuk dapat membedakan kedua jenis angsuran ini silahkan baca dan pelajari dalam artikelnya masing - masing.

Dalam artikel ini saya masih akan membahas seputar hal yang berkaitan dengan angsuran KPR rumah.

Dalam pembahasan ini kita akan belajar cara membuat tabel angsuran KPR rumah dengan menggunakan rumus Excel IPMT dan PPMT.

Apa perbedaan rumus Excel IPMT, PPMT dengan rumus yang sebelumnya kita gunakan yaitu PMT ?

Untuk lebih detailnya silahkan lanjutkan membaca artikel dalam pembahasan kita kali ini.

Cara Membuat Tabel Cicilan KPR Rumah Dalam Excel

Seperti yang kita ketahui bahwa ada dua metode dalam penghitungan angsuran KPR ini.

Metode yang pertama adalaha penghitungan dengan bunga anuitas dan yang kedua adalah penghitungan dengan bunga tetap.

Perbedaan yang paling mendasar pada kedua metode ini terletak pada besarnya bunga dan pokok yang dibayarkan setiap bulan.

Pada metode bunga tetap besarnya jumlah pokok dan bunga yang dibayarkan setiap bulan akan tetap.

Sedangkan pada penghitungan dengan metode anuitas besarnya bunga dan pokok yang dibayarkan setiap bulan akan terus berubah.

Pada awal periode jumlah bunga akan jauh lebih besar sedangkan jumlah pokoknya akan lebih kecil.

Pada akhir periode akan terjadi sebaliknya yaitu jumlah pokok akan jauh lebih besar dibandingkan jumlah bunganya.

Dengan kata lain pada metode anuitas ini pihak Bank akan menarik bunga yang besar pada awal periode dan akan menarik nilai pokok yang besar diakhir periode.

Pembuatan tabel angsuran KPR yang akan kita bahas akan menggunakan metode yang pertama yaitu bunga anuitas.

Dalam pembuatan tabel bunga anuitas ini memang penghitungannya jauh lebih sulit jika dibandingkan dengan metode bunga tetap.

Jika ingin membuat tabel angsuran KPR dengan metode bunga tetap sangatlah mudah karena nilai pokok dan nilai bunganya setiap periode akan saa jumlahnya.

Sedangkan pada metode bunga anuitas jumlah pokok dan jumlah bunganya akan terus berubah seperti yang dijelaskan diatas.

Karena alasan tersebut diataslah saya akan menggunakan bantuan dua rumus Excel yaitu IPMT dan rumus Excel PPMT.

Selanjutnya apa perbedaan rumus Excel IPMT dan PPMT yang akan kita gunakan dalam pembuatan tabel angsuran KPR ?

1. Rumus Excel Untuk Membuat Tabel Cicilan KPR Rumah

Dalam pembuatan tabel angsuran KPR ini kita akan memisahkan nilai pokok dan juga bunga dari total angsuran yang akan kita bayarkan.

Disinilah peran dari rumus Excel IPMT dan PPMT yang akan bisa menghitung berapa jumlah bunga dan juga berapa jumlah pokok dari setiap periode yang akan kita bayarkan.

Secara rinci berikut ini adalah fungsi dari kedua rumus Excel tersebut :

• Rumus Excel PPMT : rumus Excel ini akan kita gunakan untuk menghitung jumlah angsuran pokok pada periode tertentu
• Rumus Excel IPMT : rumus Excel ini akan kita gunakan untuk menghitung jumlan bunga dari total angsuran

Jadi perbedaan yang paling mendasar antara rumus Excel PMT, PPMT dan IPMT adalah jumlah yang dihasilkan rumus Excel PPMT dan IPMT jika dijumlahkan akan menjadi nilai angsuran yang bisa dihitung dengan rumus Excel PMT.

Jika kita hanya ingin mengetahui jumlah angsurannya saja silahkan gunakan rumus Excel PMT.

Sedangkan jika ingin mengetahui berapa jumlah pokok serta berapa jumlah bunga dari angsuran tersebut silahkan gunakan rumus Excel PPMT dan IPMT.

Untuk syntax dasar serta argumen dari kedua rumus Excel tersebut diatas tidak akan saya bahas dalam artikel ini dan jika ingin mempelajarinya silahkan baca dalam kategori Fungsi Keuangan.

2. Contoh Membuat Tabel Cicilan KPR Rumah Dalam Excel

Setelah mengetahui rumus apa saja yang akan kita gunakan dalam membuat tabel angsuran KPR selanjutnya mari kita membahas contoh pembuatan tabel angsuran KPR ini.

Dalam proses pembuatan tabel angsuran KPR akan muncul juga besaran jumlah angsuran setiap bulannya dengan mengunakan rumus PMT.

Saya tidak akan menjelaskan secara rinci penghitungan angsuran KPR ini karena sudah dijelaskan dalam artikel sebelumnya.

Untuk contoh pembuatan tabel angsuran KPR dalam Excel silahkan perhatikan gambar berikut ini :

Dalam gambar tersebut saya telah membuat sebuah tabel angsuran KPR dengan data - data awal sebagai berikut :

Harga rumah : 600.000.000
Uang Muka : 180.000.000
Jumlah Pinjaman : 420.000.000
Bunga / tahun : 9%
Tenor (Bulan) : 12
Angsuran / Bulan : 36.729.620

Silahkan perhatikan tabel diatas meskipun jumlah angsurannya tetap setiap bulan tetapi jumlah pokok dan bunga akan berubah setiap bulan.

Jumlah pokok akan cenderung naik sedangkan jumlah bunga akan sebaliknya yaitu menurun ke akhir periode.

Sekali lagi saya sampaikan bahwa saya tidak akan menjelaskan bagaimana cara menghitung jumlah angsuran per bulannya dengan menggunakan rumus Excel PMT karena sudah dibahas dalam artikel sebelumnya.

Langkah yang pertama untuk membuat tabel angsuran KPR ini adalah membuat kolom angsuran dan diisi dengan seluruh periode angsuran dan dalam contoh diatas selama 1 tahun dimulai dari 0 sampai dengan 12.

Selanjutnya kita akan buat kolom untuk jumlah pokok, bunga, angsuran serta kolom sisa pokok.

Untuk mengisi kolom jumlah pokok atau dalam gambar diatas adalah kolom D kita akan gunakan rumus PPMT sebagai berikut :


=PPMT($H$3/12;B11;$H$4;-$D$5)


Rumus Excel PPMT tersebut diatas akan menghitung jumlah nilai pokok dari angsuran yang kita bayarkan setiap periode.

Untuk bulan yang ke-1 jumlah nilai pokok yang kita bayarkan akan terlihat seperti dalam cell D11 yaitu 33.579.620.

Untuk cell lainnya silahkan copy pastekan rumus tersebut diatas dari mulai cell D12 sampai dengan cell D22.

Langkah selanjutnya adalah kita akan menghitung jumlah nilai bunga dari angsuran yang kita bayarkan.

Dalam gambar diatas penghitunga bunga ini terdapat dalam kolom E dengan rumus sebagai berikut :


=IPMT($H$3/12;B11;$H$4;-$D$5)


Seperti yang terlihat dalam gambar diatas bahwa pada periode yang pertama bunga yang kita bayarkan adalah sebesar 3.150.000.

Untuk cell selanjutnya dalam kolom E ini silahkan copy pastekan rumus diatas sampai dengan cell E22.

Dalam menghitung pokok serta bunga ini ada 4 cell atau 4 angka yang kita gunakan yaitu sebagai berikut :

Bunga / Tahun : cell H3
Angsuran ke / periode : kolom B
Tenor ( bulan ) : cell H4
Jumlah pinjaman : cell D5

Untuk penerapan detail semua cell tersebut diatas kedalam rumus Excel PPMT dan IPMT silahkan pelajari dalam artikel yang secara khusus membahas kedua rumus tersebut.

Langkah selanjutnya kita akan menghitung total angsuran KPR dari jumlah pokok dan juga bunga.

Sebenarnya kolom ini hanya akan memastikan saja bahwa jumlah pokok dengan bunga akan sama dengan jumlah angsuran.

Rumus yang digunakan sanga mudah karena hanya menjumlahkan kolom D dengan kolom E saja yaitu sebagai berikut :


=E11+D11


Seperti yang terlihat dalam gambar diatas bahwa seluruh periode jumlah dari pokok dan bunga selalu sama dengan jumlah angsuran dalam cell H5.

Langkah yang terakhir adalah kita akan menghitung sisa pokok hutang atas pinjaman KPR yang sudah kita bayar setiap periode.

Dalam contoh diatas sisa pokok ini terdapat dalam kolom H dan rumus yang digunakan adalah sebagai berikut :


=H10-D11


Rumus tersebut akan mengurangi jumlah pokok hutang bulan sebelumnya dengan jumlah pokok hutang yang kita bayarkan pada periode berjalan.

Hasil dari rumus - rumus tersebut diatas akan terlihat seperti dalam gambar yaitu pada akhir periode atau bulan ke-12 sisa pokok pinjaman adalah 0 karena seluruh hutang kita sudah dilunasi kepada pihak Bank.

Pembuatan tabel angsuran KPR ini sebenarnya bukan hanya digunakan sebagai control kita terhadap jumlah yang dibayarkan saja.

Karena selain itu ada hal lain yang menurut saya sangat membutuhkan penghitungan dari tabel angsuran KPR ini.

Hal penting tersebut adalah pada saat kita akan melunasi pinjaman KPR sebelum berakhirnya jangka waktu kredit atau tenor yang sudah ditetapkan.

Pelunasan ini dalam KPR lebih dikenal dengan sebutan percepatan pelunasan hutang KPR.

Tentu saja untuk percepatan pelunasan KPR ini melibatkan angka sisa hutang pokok serta angka bunga berjalan pada periode yang akan kita lunasi.

Penghitungan percepatan pelunasan hutang KPR ini sudah saya bahas dalam artikel sebelumnya dalm silahkan untuk dipelajari.

Seperti yang terlihat dalam contoh gambar serta uraian yang saya jelaskan diatas bahwa pembuatan tabel angsuran KPR dengan rumus Excel ini sangat mudah.

Bagian terpenting yang harus kita kuasai adalah pemahaman terhadap rumus Excel PPMT dan IPMT yang kita gunakan dalam pembuatan tabel angsuran KPR tersebut.

Jika sudah menguasai kedua rumus Excel tersebut maka pembuatan tabel angsuran tidak akan menemukan kendala apapun.

Itulah pembahasan kita kali ini tentang membuat tabel angsuran KPR dengan rumus PPMT dan IPMT dalam Excel, semoga artikel ini bermanfaat untuk semua pembaca.