Membuat jadwal amortisasi pinjaman di Excel bisa menjadi keterampilan yang berharga, memungkinkan Anda memvisualisasikan dan mengelola pembayaran pinjaman Anda secara efektif. Jadwal amortisasi adalah tabel yang merinci setiap pembayaran berkala atas pinjaman amortisasi (biasanya hipotek atau pinjaman mobil). Ini memecah setiap pembayaran menjadi komponen bunga dan pokok, menunjukkan sisa saldo setelah setiap pembayaran. Mari selami panduan langkah demi langkah untuk membuat jadwal seperti itu di Excel.

Apa itu jadwal amortisasi?

Buat jadwal amortisasi di Excel

Buat jadwal amortisasi untuk beberapa periode yang bervariasi

Buat jadwal amortisasi dengan pembayaran ekstra

Buat jadwal amortisasi (dengan pembayaran tambahan) menggunakan templat Excel

Apa itu jadwal amortisasi?

Jadwal amortisasi adalah tabel rinci yang digunakan dalam perhitungan pinjaman yang menampilkan proses pelunasan pinjaman dari waktu ke waktu. Jadwal amortisasi biasanya digunakan untuk pinjaman dengan suku bunga tetap seperti hipotek, pinjaman mobil, dan pinjaman pribadi, di mana jumlah pembayaran tetap konstan sepanjang jangka waktu pinjaman, namun proporsi pembayaran terhadap bunga versus pokok berubah seiring waktu.

Untuk membuat jadwal amortisasi pinjaman di Excel, fungsi bawaan PMT, PPMT, dan IPMT sangat penting. Mari kita pahami fungsi masing-masing fungsi:

• Fungsi PMT: Fungsi ini digunakan untuk menghitung total pembayaran per periode pinjaman berdasarkan pembayaran konstan dan tingkat bunga konstan.
• Fungsi IPMT: Fungsi ini menghitung porsi bunga suatu pembayaran untuk periode tertentu.
• Fungsi PPMT: Fungsi ini digunakan untuk menghitung porsi pokok pembayaran pada periode tertentu.

Dengan menggunakan fungsi ini di Excel, Anda bisa membuat jadwal amortisasi terperinci yang memperlihatkan komponen bunga dan pokok setiap pembayaran, beserta sisa saldo pinjaman setelah setiap pembayaran.

Buat jadwal amortisasi di Excel

Di bagian ini, kami akan memperkenalkan dua metode berbeda untuk membuat jadwal amortisasi di Excel. Metode ini memenuhi preferensi pengguna dan tingkat keahlian yang berbeda-beda, sehingga memastikan bahwa siapa pun, terlepas dari kemahiran mereka dalam menggunakan Excel, dapat berhasil menyusun jadwal amortisasi yang terperinci dan akurat untuk pinjaman mereka.

Rumus menawarkan pemahaman yang lebih mendalam tentang penghitungan dasar dan memberikan fleksibilitas untuk menyesuaikan jadwal sesuai kebutuhan spesifik. Pendekatan ini ideal bagi mereka yang ingin mendapatkan pengalaman langsung dan pemahaman yang jelas tentang bagaimana setiap pembayaran dipecah menjadi komponen pokok dan bunga. Sekarang, mari kita uraikan proses pembuatan jadwal amortisasi di Excel langkah demi langkah:

⭐️ Langkah 1: Siapkan informasi pinjaman dan tabel amortisasi

1. Masukkan informasi pinjaman relatif, seperti tingkat bunga tahunan, jangka waktu pinjaman dalam beberapa tahun, jumlah pembayaran per tahun dan jumlah pinjaman ke dalam sel seperti gambar berikut yang ditampilkan:
   
2. Kemudian buat tabel amortisasi di Excel dengan label yang ditentukan, seperti Periode, Pembayaran, Bunga, Pokok, Sisa Saldo di sel A7:E7.
3. Pada kolom Periode, masukkan nomor periode. Misalnya jumlah pembayarannya adalah 24 bulan (2 tahun), jadi masukkan angka 1 sampai 24 pada kolom Periode. Lihat tangkapan layar:
   
4. Setelah Anda menyiapkan tabel dengan label dan nomor periode, Anda dapat melanjutkan dengan memasukkan rumus dan nilai untuk kolom Pembayaran, Bunga, Pokok, dan Saldo berdasarkan spesifikasi pinjaman Anda.

⭐️ Langkah 2: Hitung total jumlah pembayaran dengan menggunakan fungsi PMT

Sintaks PMT adalah:

Masukkan rumus berikut ke dalam sel B7, lalu seret gagang isian ke bawah untuk mengisi rumus ini ke sel lain, dan Anda akan melihat jumlah pembayaran konstan untuk semua periode. Lihat tangkapan layar:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Langkah 3: Hitung bunga dengan menggunakan fungsi IPMT

Pada langkah ini, Anda akan menghitung bunga untuk setiap periode pembayaran menggunakan fungsi IPMT Excel.

Masukkan rumus berikut ke dalam sel C7, lalu seret gagang isian ke bawah kolom untuk mengisi rumus ini dan dapatkan bunga untuk setiap periode.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Langkah 4: Hitung pokok dengan menggunakan fungsi PPMT

Setelah menghitung bunga setiap periode, langkah selanjutnya dalam membuat jadwal amortisasi adalah menghitung porsi pokok setiap pembayaran. Hal ini dilakukan dengan menggunakan fungsi PPMT, yang dirancang untuk menentukan bagian pokok pembayaran untuk periode tertentu, berdasarkan pembayaran konstan dan tingkat bunga konstan.

Sintaks IPMT adalah:

Sintaks dan parameter rumus PPMT sama dengan yang digunakan pada rumus IPMT yang telah dibahas sebelumnya.

Masukkan rumus berikut ke dalam sel D7, lalu seret gagang isian ke bawah kolom untuk mengisi pokok untuk setiap periode. Lihat tangkapan layar:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Langkah 5: Hitung sisa saldo

Setelah menghitung bunga dan pokok setiap pembayaran, langkah selanjutnya dalam jadwal amortisasi Anda adalah menghitung sisa saldo pinjaman setelah setiap pembayaran. Ini adalah bagian penting dari jadwal karena menunjukkan bagaimana saldo pinjaman menurun seiring waktu.

1. Di sel pertama kolom saldo Anda – E7, masukkan rumus berikut, artinya sisa saldo adalah jumlah pinjaman awal dikurangi porsi pokok pembayaran pertama:

   =B4-D7

   
2. Untuk periode kedua dan seluruh periode berikutnya, hitung sisa saldo dengan mengurangkan pembayaran pokok periode berjalan dari saldo periode sebelumnya. Silakan terapkan rumus berikut ke dalam sel E8:

   =E7-D8

    Note: Referensi ke sel keseimbangan harus bersifat relatif, sehingga diperbarui saat Anda menyeret rumus ke bawah.
   
3. Lalu seret gagang isian ke bawah ke kolom. Seperti yang Anda lihat, setiap sel akan secara otomatis menyesuaikan untuk menghitung sisa saldo berdasarkan pembayaran pokok yang diperbarui.
   

⭐️ Langkah 6: Buat ringkasan pinjaman

Setelah menyiapkan jadwal amortisasi terperinci, membuat ringkasan pinjaman dapat memberikan gambaran singkat tentang aspek-aspek utama pinjaman Anda. Ringkasan ini biasanya mencakup total biaya pinjaman, total bunga yang dibayarkan.

● Untuk menghitung total pembayaran:

=SUM(B7:B30)

● Untuk menghitung total bunga:

=SUM(C7:C30)

⭐️ Hasil:

Kini, jadwal amortisasi pinjaman yang sederhana namun komprehensif telah berhasil dibuat. lihat tangkapan layar:

Buat jadwal amortisasi untuk beberapa periode yang bervariasi

Pada contoh sebelumnya, kita membuat jadwal pembayaran pinjaman untuk jumlah pembayaran yang tetap. Pendekatan ini sempurna untuk menangani pinjaman atau hipotek tertentu yang persyaratannya tidak berubah.

Namun, jika Anda ingin membuat jadwal amortisasi fleksibel yang dapat digunakan berulang kali untuk pinjaman dengan periode yang berbeda-beda, sehingga memungkinkan Anda mengubah jumlah pembayaran sesuai kebutuhan untuk skenario pinjaman yang berbeda, Anda harus mengikuti metode yang lebih detail.

⭐️ Langkah 1: Siapkan informasi pinjaman dan tabel amortisasi

1. Masukkan informasi pinjaman relatif, seperti tingkat bunga tahunan, jangka waktu pinjaman dalam beberapa tahun, jumlah pembayaran per tahun dan jumlah pinjaman ke dalam sel seperti gambar berikut yang ditampilkan:
   
2. Kemudian buat tabel amortisasi di Excel dengan label yang ditentukan, seperti Periode, Pembayaran, Bunga, Pokok, Sisa Saldo di sel A7:E7.
3. Di kolom Periode, masukkan jumlah pembayaran tertinggi yang mungkin Anda pertimbangkan untuk pinjaman apa pun, misalnya, isikan angka yang berkisar antara 1 hingga 360. Angka ini dapat mencakup pinjaman standar 30 tahun jika Anda melakukan pembayaran bulanan.
   

⭐️ Langkah 2: Ubah rumus pembayaran, bunga dan pokok dengan fungsi IF

Masukkan rumus berikut di sel yang sesuai, lalu seret gagang isian untuk memperluas rumus ini hingga jumlah maksimum periode pembayaran yang Anda tetapkan.

● Rumus pembayaran:

Biasanya, Anda menggunakan fungsi PMT untuk menghitung pembayaran. Untuk memasukkan pernyataan IF, rumus sintaksnya adalah:

Jadi rumusnya begini:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Rumus bunga:

Rumus sintaksnya adalah:

Jadi rumusnya begini:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Rumusan prinsip:

Rumus sintaksnya adalah:

Jadi rumusnya begini:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Langkah 3: Sesuaikan rumus sisa saldo

Untuk sisa saldo, biasanya Anda dapat mengurangi pokok dari saldo sebelumnya. Dengan pernyataan IF, ubah menjadi:

● Sel saldo pertama:(E7)

=B4-D7

● Sel keseimbangan kedua:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Langkah 4: Buat ringkasan pinjaman

Setelah Anda mengatur jadwal amortisasi dengan rumus yang telah dimodifikasi, langkah selanjutnya adalah membuat ringkasan pinjaman.

● Untuk menghitung total pembayaran:

=SUM(B7:B366)

● Untuk menghitung total bunga:

=SUM(C7:C366)

⭐️ Hasil:

Sekarang, Anda memiliki jadwal amortisasi yang komprehensif dan dinamis di Excel, lengkap dengan ringkasan pinjaman terperinci. Setiap kali Anda menyesuaikan jangka waktu periode pembayaran, seluruh jadwal amortisasi akan diperbarui secara otomatis untuk mencerminkan perubahan ini. Lihat demonya di bawah ini:

Buat jadwal amortisasi dengan pembayaran ekstra

Dengan melakukan pembayaran tambahan di luar jadwal yang dijadwalkan, pinjaman dapat dilunasi lebih cepat. Jadwal amortisasi yang mencakup pembayaran tambahan, ketika dibuat di Excel, menunjukkan bagaimana pembayaran tambahan ini dapat mempercepat pembayaran pinjaman dan menurunkan total bunga yang dibayarkan. Inilah cara Anda mengaturnya:

⭐️ Langkah 1: Siapkan informasi pinjaman dan tabel amortisasi

1. Masukkan informasi pinjaman relatif, seperti tingkat bunga tahunan, jangka waktu pinjaman dalam beberapa tahun, jumlah pembayaran per tahun, jumlah pinjaman dan pembayaran tambahan ke dalam sel seperti gambar berikut yang ditampilkan:
   
2. Kemudian, hitung pembayaran yang dijadwalkan.
   Selain sel masukan, sel lain yang telah ditentukan sebelumnya diperlukan untuk penghitungan selanjutnya - jumlah pembayaran yang dijadwalkan. Ini adalah jumlah pembayaran rutin pinjaman dengan asumsi tidak ada pembayaran tambahan yang dilakukan. Silakan terapkan rumus berikut ke sel B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Kemudian, buat tabel amortisasi di Excel:
   • Atur label yang ditentukan, seperti Periode, Jadwal Pembayaran, Pembayaran Tambahan, Total Pembayaran, Bunga, Pokok, Sisa Saldo di sel A8:G8;
   • Di kolom Periode, masukkan jumlah pembayaran tertinggi yang mungkin Anda pertimbangkan untuk pinjaman apa pun. Misalnya, isikan angka mulai dari 0 hingga 360. Angka ini dapat mencakup pinjaman standar 30 tahun jika Anda melakukan pembayaran bulanan;
   • Untuk Periode 0 (baris 9 dalam kasus kami), ambil nilai Saldo dengan rumus ini = B4, yang sesuai dengan jumlah pinjaman awal. Semua sel lain di baris ini harus dikosongkan.
   • 

⭐️ Langkah 2: Buat rumus jadwal amortisasi dengan pembayaran ekstra

Masukkan rumus berikut di sel yang sesuai satu per satu. Untuk meningkatkan penanganan kesalahan, kami memasukkan rumus ini dan semua rumus yang akan datang ke dalam fungsi IFERROR. Pendekatan ini membantu menghindari beberapa potensi kesalahan yang mungkin timbul jika salah satu sel masukan dibiarkan kosong atau memiliki nilai yang salah.

● Hitung pembayaran terjadwal:

Masukkan rumus berikut ke dalam sel B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Hitung pembayaran tambahan:

Masukkan rumus berikut ke dalam sel C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Hitung total pembayaran:

Masukkan rumus berikut ke dalam sel D10:

=IFERROR(B10+C10, "")

● Hitung pokoknya:

Masukkan rumus berikut ke dalam sel E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Hitung bunganya:

Masukkan rumus berikut ke dalam sel F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Hitung sisa saldo

Masukkan rumus berikut ke dalam sel G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Setelah Anda menyelesaikan setiap rumus, pilih rentang sel B10:G10, dan gunakan gagang isian untuk menyeret dan memperluas rumus ini ke seluruh rangkaian periode pembayaran. Untuk periode mana pun yang tidak digunakan, sel akan menampilkan angka 0. Lihat tangkapan layar:

⭐️ Langkah 3: Buat ringkasan pinjaman

● Dapatkan jumlah pembayaran yang dijadwalkan:

=B2:B3

● Dapatkan jumlah pembayaran aktual:

=COUNTIF(D10:D369,">"&0)

● Dapatkan total pembayaran ekstra:

=SUM(C10:C369)

● Dapatkan bunga total:

=SUM(F10:F369)

⭐️ Hasil:

Dengan mengikuti langkah-langkah ini, Anda membuat jadwal amortisasi dinamis di Excel yang memperhitungkan pembayaran tambahan.

Buat jadwal amortisasi dengan menggunakan template Excel

Membuat jadwal amortisasi di Excel menggunakan templat adalah metode yang mudah dan efisien waktu. Excel menyediakan templat bawaan yang secara otomatis menghitung bunga, pokok, dan sisa saldo setiap pembayaran. Berikut cara membuat jadwal amortisasi menggunakan template Excel:

1. Klik File > New, di kotak pencarian, ketik jadwal amortisasi, dan tekan Enter kunci. Kemudian, pilih template yang paling sesuai dengan kebutuhan Anda dengan mengkliknya. Misalnya, di sini saya akan memilih Template kalkulator pinjaman sederhana. Lihat tangkapan layar:
   
2. Setelah Anda memilih templat, klik membuat tombol untuk membukanya sebagai buku kerja baru.
3. Kemudian, masukkan detail pinjaman Anda, template akan secara otomatis menghitung dan mengisi jadwal berdasarkan masukan Anda.
4. Terakhir, simpan buku kerja jadwal amortisasi baru Anda.