Cara Membuat Jadwal Amortisasi di Excel: Langkah-langkah Dijelaskan

Jadwal amortisasi memberikan peta jalan keuangan yang jelas, menunjukkan bagaimana setiap pembayaran pinjaman terbagi antara bunga dan pokok hingga utang Anda lunas sepenuhnya.

Dengan fungsi bawaan Excel, Anda dapat membuat jadwal amortisasi terperinci untuk memvisualisasikan siklus hidup pinjaman, membuat keputusan keuangan yang tepat, dan bahkan mengeksplorasi opsi pelunasan lebih cepat.

Dalam artikel ini, Anda akan belajar cara membangun jadwal amortisasi yang komprehensif di Excel dari nol. Kami akan membahas fungsi penting seperti PMT(), IPMT(), dan PPMT(), memandu Anda melalui proses langkah demi langkah, serta mengeksplorasi skenario praktis untuk memperkaya perangkat perencanaan keuangan Anda.

Apa Sebenarnya Jadwal Amortisasi Itu?

Jadwal amortisasi adalah tabel lengkap pembayaran pinjaman berkala, yang menunjukkan jumlah pokok dan bunga yang menyusun setiap pembayaran hingga pinjaman dilunasi pada akhir jangka waktunya.

Saat Anda mengambil pinjaman, beberapa elemen utama menentukan bagaimana pembayaran Anda bekerja:

• Pokok: Jumlah awal yang dipinjam yang harus Anda kembalikan
• Bunga: Biaya meminjam uang, dihitung sebagai persentase dari pokok
• Periode pembayaran: Seberapa sering Anda membayar (bulanan, dua mingguan, dll.)
• Jangka waktu pinjaman: Total rentang waktu untuk melunasi pinjaman

Untuk sebagian besar pinjaman standar—seperti KPR, pinjaman mobil, dan pinjaman pribadi—pembayaran tetap konsisten sepanjang jangka waktu. Namun, yang berubah di setiap pembayaran adalah proporsi yang dialokasikan untuk bunga versus pokok. Pada tahap awal, persentase yang lebih besar dari setiap pembayaran menutup bunga. Seiring berjalannya waktu, lebih banyak dari setiap pembayaran akan mengurangi saldo pokok.

Tujuan utama membuat jadwal amortisasi adalah memberikan transparansi atas pinjaman Anda. Ini memungkinkan Anda melihat dengan tepat berapa banyak bunga yang akan dibayar seiring waktu, seberapa cepat Anda membangun ekuitas, dan bagaimana pembayaran tambahan dapat memengaruhi garis waktu pinjaman. Bagi bisnis, jadwal ini juga berguna untuk akuntansi dan perencanaan pajak.

Fungsi dan Rumus Excel yang Perlu Anda Ketahui

Excel tidak memiliki satu fungsi bawaan bernama "amortization". Sebagai gantinya, Anda biasanya menyusun jadwal amortisasi dengan fungsi keuangan spesifik yang bekerja bersama untuk menghitung berbagai aspek pembayaran pinjaman Anda. Berikut fungsi-fungsi kunci yang Anda perlukan:

Fungsi PMT()

Fungsi PMT() menghitung pembayaran untuk pinjaman berdasarkan pembayaran tetap dan suku bunga tetap:

=PMT(rate, nper, pv, [fv], [type])

• rate: Suku bunga per periode (suku bunga tahunan dibagi frekuensi pembayaran)

• nper: Jumlah total periode pembayaran

• pv: Nilai kini (jumlah pinjaman)

• [fv]: Nilai masa depan opsional (biasanya 0 untuk pinjaman yang dilunasi penuh)

• [type]: Opsional; 0 untuk pembayaran di akhir periode, 1 untuk awal periode

Untuk menghitung pembayaran bulanan KPR $250.000 dengan bunga tahunan 4,5% selama 30 tahun: 

=PMT(4.5%/12, 30*12, 250000)

Ini menghasilkan -$1.266,71, yang mewakili arus kas keluar bagi peminjam.

Untuk penjelasan terperinci tentang fungsi PMT(), termasuk kesalahan umum dan contoh penggunaan lanjutan, Anda dapat merujuk ke panduan PMT() di Excel kami yang komprehensif.

Fungsi IPMT()

Fungsi IPMT() menghitung porsi bunga dari pembayaran tertentu:

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

• per: Periode pembayaran spesifik yang Anda hitung

Parameter per sangat penting karena menentukan jumlah bunga pada periode pembayaran mana yang Anda hitung—berbeda dengan fungsi PMT() yang memberikan jumlah pembayaran yang sama untuk semua periode, hasil IPMT() bervariasi secara signifikan tergantung periode yang ditentukan karena porsi bunga menurun sepanjang jangka pinjaman.

Ini menunjukkan dengan tepat berapa banyak dari suatu pembayaran yang dialokasikan untuk bunga.

Fungsi PPMT()

Fungsi PPMT() menghitung porsi pokok dari pembayaran tertentu:

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

Serupa dengan IPMT(), jumlah pokok bervariasi menurut periode, dengan pembayaran awal memiliki porsi pokok yang lebih kecil dan pembayaran akhir lebih besar. Ini menunjukkan berapa banyak dari suatu pembayaran yang mengurangi saldo pinjaman Anda.

Rumus kustom

Untuk setiap periode pembayaran, Anda perlu melacak:

Saldo tersisa: Jumlah pinjaman yang masih harus dibayar setelah setiap pembayaran

Remaining balance = Previous balance - Current principal payment

Akumulasi pokok: Total pokok yang telah dibayar hingga saat ini

Cumulative principal = Previous cumulative principal + current principal payment

Akumulasi bunga: Total bunga yang telah dibayar hingga saat ini

Formula: Previous cumulative interest + current interest payment

Fungsi Round()

Bahkan kesalahan pembulatan kecil dapat terakumulasi seiring waktu dalam jadwal amortisasi:

=ROUND(value, 2)

Ini memastikan semua nilai moneter dibulatkan hingga dua desimal terdekat.

Referensi absolut vs. relatif

Saat menyusun tabel amortisasi, Anda perlu:

• Menggunakan referensi absolut ($) untuk nilai tetap seperti suku bunga dan jumlah pinjaman

• Menggunakan referensi relatif untuk nilai yang berubah setiap periode

Rumus berikut, misalnya, menggunakan referensi absolut untuk suku bunga, jangka waktu, dan jumlah pinjaman, tetapi referensi relatif untuk periode pembayaran:

=PPMT($B$2/12, A10, $B$3*12, $B$1)

Panduan Langkah demi Langkah Membuat Jadwal Amortisasi

Sekarang setelah Anda memahami fungsi kunci, mari buat jadwal amortisasi dari awal. Panduan ini akan membuat tabel pembayaran pinjaman lengkap yang melacak setiap pembayaran sepanjang masa pinjaman Anda.

1. Memasukkan data pinjaman

Masukkan detail pinjaman Anda di sel input khusus seperti ditunjukkan pada contoh di bawah:

• Jumlah pinjaman: $250.000 (sel B1)
• Suku bunga tahunan: 4,5% (sel B2)
• Jangka waktu pinjaman: 30 tahun (sel B3)
• Frekuensi pembayaran: Bulanan (sel B4)

Menyetel parameter input pinjaman. Gambar oleh Penulis. 

2. Menghitung pembayaran bulanan

Di sel B5, hitung pembayaran bulanan menggunakan fungsi PMT: =PMT(B2/12, B3*12, B1)

Rumus ini menghasilkan -$1.266,71, yang mewakili pembayaran bulanan Anda. Tanda negatif menunjukkan ini adalah pembayaran (arus kas keluar).

Menyetel header kolom dan rumus. Gambar oleh Penulis.

3. Membuat baris pertama jadwal

Selanjutnya, kita dapat membuat header berikut dan mengisi baris data pertama (baris 7 dan 8):  

• Periode: Masukkan 1 di sel A8

• Jumlah Pembayaran: =$B$5 di sel B8 (merujuk pembayaran bulanan yang dihitung)

• Pembayaran Bunga: =IPMT($B$2/12, A8, $B$3*12, $B$1) di sel C8

• Pembayaran Pokok: =PPMT($B$2/12, A8, $B$3*12, $B$1) di sel D8

• Saldo Tersisa: =$B$1+D8 di sel E8 (pinjaman awal plus pembayaran pokok, yang berfungsi karena D8 menghasilkan nilai negatif, secara efektif mengurangkan jumlah pokok dari pinjaman)

• Akumulasi Pokok: =D8 di sel F8 (sama dengan pembayaran pokok untuk baris pertama)

• Akumulasi Bunga: =C8 di sel G8 (sama dengan pembayaran bunga untuk baris pertama)

Menampilkan perincian pembayaran pertama. Gambar oleh Penulis.

4. Menyelesaikan jadwal

Untuk baris kedua (baris 9):

• Periode: =A8+1 di sel A9 (bertambah 1)

• Jumlah Pembayaran: Salin rumus dari B8 ke B9

• Pembayaran Bunga: =IPMT($B$2/12, A9, $B$3*12, $B$1) di sel C9

• Pembayaran Pokok: =PPMT($B$2/12, A9, $B$3*12, $B$1) di sel D9

• Saldo Tersisa: =E8+D9 di sel E9 (saldo sebelumnya plus pokok saat ini)

• Akumulasi Pokok: =F8+D9 di sel F9 (total sebelumnya plus pokok saat ini)

• Akumulasi Bunga: =G8+C9 di sel G9 (total sebelumnya plus bunga saat ini)

Perhatikan bahwa pada fungsi IPMT() dan PPMT(), A9 digunakan sebagai referensi relatif (tanpa tanda dolar), sehingga saat Anda menyalin rumus ke bawah, rumus akan otomatis diperbarui menjadi A10, A11, dst., menghitung periode yang benar setiap kali.

Menyetel rumus yang dapat disalin ke bawah. Gambar oleh Penulis.

Beberapa pembayaran pertama dalam jadwal amortisasi. Gambar oleh Penulis.

Pilih semua sel pada baris 9 dan seret ke bawah hingga baris 367 (untuk pinjaman bulanan 30 tahun dengan 360 pembayaran).

Seiring berjalannya jangka pinjaman, lebih banyak dari setiap pembayaran dialokasikan ke pokok daripada bunga. Gambar oleh Penulis.

5. Memverifikasi pembayaran terakhir

Dalam jadwal amortisasi yang disusun dengan benar, pembayaran terakhir harus membuat saldo tersisa menjadi tepat nol, dan akumulasi pokok harus sama dengan jumlah pinjaman awal.

Pembayaran terakhir membuat saldo tersisa menjadi tepat nol. Gambar oleh Penulis.

Seperti yang terlihat pada baris terakhir, saldo tersisa adalah $0,00 (disorot), dan akumulasi pokok sama dengan $250.000, yang cocok dengan jumlah pinjaman awal. Ini mengonfirmasi bahwa jadwal amortisasi kita akurat.

Saat memverifikasi saldo akhir, menerapkan fungsi ROUND() pada perhitungan kunci dapat bermanfaat. Perbedaan desimal kecil karena presisi perhitungan Excel mungkin menyebabkan saldo akhir menunjukkan jumlah sisa kecil alih-alih tepat nol. Menggunakan =ROUND(value, 2) memastikan semua nilai moneter konsisten dengan standar pelaporan keuangan yang umum.

Hal Tambahan yang Perlu Anda Pertimbangkan

Jadwal standar adalah titik awal. Pertimbangkan hal-hal ini untuk membuat model Anda lebih fleksibel:

Mengelola variasi pembayaran

Jadwal amortisasi menjadi lebih bernilai saat disesuaikan untuk mengeksplorasi berbagai skenario pembayaran. Untuk memodelkan pembayaran ekstra, buat sel input tambahan untuk pembayaran tambahan dan modifikasi rumus pembayaran pokok Anda untuk memasukkan jumlah tersebut. Pendekatan ini membantu memvisualisasikan bagaimana pembayaran tambahan mengurangi jangka pinjaman dan total bunga yang dibayar. Untuk pinjaman dengan pembayaran balon atau suku bunga variabel, pertimbangkan membuat bagian terpisah dalam jadwal yang mencerminkan perubahan suku pada titik tertentu. Anda dapat menggunakan Scenario Manager di Excel untuk membandingkan lingkungan suku yang berbeda dan dampaknya terhadap total biaya Anda.

Meningkatkan keterbacaan dan analisis

Pemformatan yang tepat mengubah jadwal amortisasi Anda dari tabel dasar menjadi alat keuangan yang informatif. Terapkan format mata uang dengan jumlah desimal yang konsisten pada semua nilai moneter, dan gunakan pemformatan bersyarat untuk menyoroti tonggak penting—seperti saat Anda melunasi setengah pokok atau mencapai ambang ekuitas tertentu. Penting juga untuk memahami bagaimana metode perhitungan bunga yang berbeda memengaruhi pembayaran Anda. Perhitungan harian (umum pada pinjaman mahasiswa) menghitung bunga berdasarkan tahun 365 hari, sementara banyak KPR menggunakan metode akrual bulanan. Mendokumentasikan metode mana yang digunakan jadwal Anda memastikan akurasi saat membandingkan dengan mutasi pinjaman aktual.

Menyesuaikan untuk jenis pinjaman spesifik

Berbagai pinjaman memiliki karakteristik unik yang mungkin memerlukan penyesuaian pada templat amortisasi dasar Anda. Untuk KPR dengan rekening escrow, tambahkan kolom untuk melacak komponen asuransi dan pajak bersamaan dengan pokok dan bunga. Untuk pinjaman dengan amortisasi negatif (di mana pembayaran tidak menutup bunga yang telah terjadi), modifikasi perhitungan saldo tersisa Anda untuk menambahkan bunga yang belum dibayar ke pokok. Opsi waktu pembayaran pada fungsi keuangan Excel (parameter type) memungkinkan Anda memodelkan pembayaran di awal atau akhir periode, yang sangat berguna untuk sewa guna dan beberapa pinjaman komersial di mana konvensi waktu berbeda dari pinjaman konsumen standar.

Kesimpulan

Membuat jadwal amortisasi di Excel memberi Anda wawasan berharga tentang pinjaman melebihi apa yang biasanya disediakan pemberi pinjaman. Dengan membangun jadwal sendiri, Anda dapat melihat dengan tepat bagaimana setiap pembayaran memengaruhi saldo, memahami biaya sebenarnya dari meminjam seiring waktu, dan membuat keputusan yang tepat tentang opsi pelunasan dipercepat. Keterampilan yang dikembangkan melalui proses ini—menggunakan fungsi keuangan Excel, membuat rumus dinamis, dan merancang tabel informatif—jauh melampaui analisis pinjaman dan dapat diterapkan pada banyak aspek perencanaan keuangan pribadi dan bisnis.

Jika Anda ingin melangkah lebih jauh dari amortisasi dan membangun model keuangan yang lebih lengkap—termasuk memproyeksikan pendapatan, mengevaluasi investasi, dan menjalankan skenario what-if—lihat kursus Financial Modeling in Excel kami. Ini adalah langkah berikut yang tepat untuk memperkuat keterampilan spreadsheet Anda untuk analisis keuangan. Dan ingat, semakin nyaman Anda dengan perhitungan keuangan ini, semakin siap Anda untuk mengambil kendali masa depan keuangan Anda, baik Anda mengelola utang pribadi, merencanakan investasi bisnis, atau memberi nasihat kepada klien tentang opsi keuangan mereka.