Ajaib.co.id – Tahukah kamu bahwa kamu bisa mengelola jadwal loan repayment, menentukan cicilan setiap bulan, total suku bunga, dan jadwal pinjaman sendiri menggunakan rumus Microsoft Excel. Bagaimana cara atur jadwal loan repayment ini?
Loan Repayment atau pembayaran pinjaman merupakan langkah membayar kembali uang yang sebelumnya dipinjam dari pemberi pinjaman.
Biasanya loan repayment dilakukan melalui serangkaian pembayaran berkala yang mencakup biaya yang sudah ditentukan tiap pembayaran ditambah suku bunga.
Namun tahukah kamu mengelola jadwal loan repayment bisa dilakukan menggunakan rumus di aplikasi Microsoft Excel?
Di artikel ini kamu akan menemukan panduan secara bertahap bagaimana cara atur jadwal loan repayment menggunakan rumus Microsoft Excel untuk menentukan berapa yang harus dibayar tiap bulan, total suku bunga, dan jadwal pinjaman secara komprehensif yang nantinya akan memudahkan mengelola pinjaman secara efektif dan efisien.
Cara Atur Jadwal Loan Repayment
Memahami Hipotek
Hipotek (mortgage) skema pinjaman atau kredit jangka panjang. Peminjaman tersebut biasanya digunakan untuk pembelian berbiaya besar dan tidak bisa dilakukan secara tunai. Dengan menggunakan rumus Microsoft Excel, kamu bisa memahami perhitungan hipotek lebih mudah hanya dengan tiga langkah.
Langkah pertama yaitu menentukan pembayaran setiap bulannya, langkah kedua menghitung total suku bunga, dan langkah ketiga adalah menentukan kapan jadwal pembayaran bulannya.
Kamu bisa menggunakan fitur tabel pada Microsoft Excel yang akan menampilkan tingkat suku bunga, perhitungan pinjaman selama jangka waktu peminjaman, pemaparan pinjaman secara rinci, dan pembayaran bulanan.
Menghitung Pembayaran Bulanan
Untuk menghitung pembayaran bulanan hipotek, kamu bisa menggunakan tingkat bunga tahunan, biaya pokok, dan jangka waktu untuk menentukan berapa julah yang harus dibayar setiap bulan.
Untuk rumusnya, seperti yang ditampilkan di tangkapan layar di atas, ditulis sebagai berikut:
=-PMT(rate;length;present_value;[future_value];[type])
Tanda minus di depan PMT dibutuhkan untuk sebagai rumus mengembalikan angka negatif. Tiga elemen pertama adalah tingkat pinjaman, jangka waktu pinjaman (jumlah periode), dan pokok pinjaman. Sementara dua elemen terakhir adalah opsional, yaitu nilai sisa awal diatur ke nol; dibayar di muka atau di akhir.
Sementara rumus Excel yang digunakan untuk menghitung pembayaran bulanan pinjaman adalah:
=-PMT((1+B2)^(1/12)-1;B4*12;B3) = PMT((1+3,10%)^(1/12)-1;10*12;120000)
Penjelasan: Untuk periode, kami menggunakan hitungan bulan, kemudian kami menghitung jumlah periode (120 untuk 10 tahun dikalikan 12 bulan) dan, akhirnya, kami menemukan jumlah pembayaran bulanan kami adalah $1.161 atau sekitar Rp17.211.825 selama 10 tahun.
Menghitung Suku Bunga Tahunan
Kamu telah mengetahui cara mengatur perhitungan pembayaran hipotek bulanan, tapi mungkin kamu ingin menetapkan berapa pembayaran bulan maksimal yang kamu mampu bayar dan juga menampilkan jumlah tahun yang dibutuhkan untuk membayar pinjaman. Untuk mengetahuinya, kamu perlu menentukan suku bunga tahunan yang sesuai.
Seperti yang ditampilkan pada tangkapan layar, pertama-tama kamu harus menghitung periode yang dipakai (misalnya: bulanan), dan kemudian tahunan. Rumus yang akan digunakan adalah Rate, ditulis sebagai berikut:
= RATE (Nper; pmt; present_value; [future_value]; [type])
Tiga elemen pertama adalah jangka waktu pinjaman (periode), pembayaran setiap bulannya, dan pokok pinjaman. Sementara, tiga elemen terakhir adalah opsional, nilai sisa diatur ke nol: elemen untuk mengelola jatuh tempo di awal atau di akhir juga opsional. Yang akhirnya akan menemukan estimasi awal dari nilai tukar.
Sementara rumus Excel yang digunakan untuk menghitung tingkat pinjaman adalah:
=RATE(12*B4;-B2;B3) = RATE(12*13;-960;120000)
Perlu dicatat bahwa data terkait dalam pembayaran bulanan harus diberi tanda negatif. Inilah mengapa selalu ada tanda minus sebelum rumus. Dengan periode nilai tukar adalah 0.294%.
Kami menggunakan rumus = (1 + B5) adalah 12-1 ^ = (1 + 0.294%) ^ 12-1 untuk mengetahui tingkat pinjaman tahunan, yaitu sekitar 3.58%. Dengan kata lain, untuk meminjam $120.000 atau sekitar Rp1,778,760,000 selama 13 tahun untuk membayar $960 setiap bulan sekitar Rp14,230,080, kamu harus menegosiasikan pinjaman maksimal tahunan di angka 3.58%.
Menentukan Panjang Pinjaman
Kali ini kamu berada di tahap bagaimana menentukan periode pinjaman ketika kamu sudah mengetahui tingkat tahunan, biaya pokok pinjaman, dan cicilan bulanan. Dengan kata lain, berapa kamu perlu membayar hipotek sebesar $120.000 atau sekitar Rp1,778,760,000 dengan suku bunga 3.10% dan cicilan bulanan sebesar $1100 atau setara dengan Rp16,305,300?
Rumus yang akan digunakan adalah NPER, ditulis sebagai berikut:
= NPER (rate; pmt; present_value; [future_value]; [type])
Tiga elemen pertama adalah tingkat tahunan pinjaman, cicilan bulanan yang diperlukan untuk membayar kembali pinjaman, dan pokok pinjaman. Dua elemen terakhir adalah opsional, nilai sisa diatur nol. elemen untuk mengelola jatuh tempo di awal atau di akhir juga opsional.
=NPER((1+B2)^(1/12)-1;-B4;B3) = NPER((1+3,10%)^(1/12)-1;-1100;120000)
Untuk catatan, data terkait dalam pembayaran bulanan harus diberi tanda negatif. Ini sebabnya harus menggunakan tanda minus sebelum rumus. Panjang penggantian adalah 127,97 periode (periode bulan).
Kami menggunakan rumus = B5 / 12 = 127,97 / 12 untuk jumlah tahun dalam menyelesaikan pembayaran pinjaman. Dengan kata lain, untuk meminjam $120.000 atau sekitar Rp1,778,760,000, dengan suku bunga 3,10% dan membayar $1100 atau setara dengan Rp16,305,300 setiap bulan, kamu harus membayar jatuh tempo selama 128 bulan atau 10 tahun dan 8 bulan.
Merinci Pinjaman
Pembayaran pinjaman terdiri dari biaya pokok pinjaman dan suku bunga yang dihitung untuk setiap periode, misalnya pembayaran cicilan selama 10 tahun akan memberikan kamu 120 periode.
Tabel di atas menunjukkan rincian peminjaman (total periode sama dengan 120) menggunakan rumus PPMP dan IPMT. Elemen dari kedua rumus tersebut sama seperti yang dituliskan berikut ini:
=-PPMT(rate;num_period;length;principal;[residual];[term])
Elemennya sama seperti rumus PMT, kecuali di bagian “num_aktu” yang ditambahkan untuk menampilkan akhir periode untuk menguraikan pinjaman (biaya pokok dan suku bunga). Berikut contohnya:
=-PPMT((1+B2)^(1/12)-1;1;B4*12;B3) = PPMT((1+3,10%)^(1/12)-1;1;10*12;120000)
Hasilnya ditunjukkan pada tangkapan layar di atas “loan decompositon” atau rincian pinjaman selama periode yang dianalisis, yaitu satu yang artinya periode pertama dan bulan pertama. Kamu harus membayar $1161 atau sekitar Rp17,220,532, dengan rincian biaya pokok $856 atau sekitar Rp12.696.620 dan suku bunga $305 atau setara dengan Rp4.523.912.
Membuat Jadwal Pembayaran Pinjaman
Untuk membuat jadwal pembayaran pinjaman, kamu harus menggunakan beberapa formula yang sudah digunakan di atas dan melebarkannya selama beberapa periode.
Pada kolom pertama masukkan “!” sebagai periode pertama pembayaran, lalu seret sel ke bawah. Di kasus ini, kamu membutuhkan 120 periode mengingat pembayaran pinjaman 10 tahun dikalikan 12 bulan menjadi 120 periode.
Kolom kedua adalah jumlah bulan yang harus kita bayar setiap bulan secara teratur. Untuk menghitung jumlahnya, masukkan rumus berikut ke dalam sel periode pertama:
= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Kolom ketiga ada cicilan pokok bulanan yang harus dibayar setiap bulan. Misalnya, pada periode pembayaran ke-40, kamu akan membayar cicilan pokok bulanan sebesar $945 atau sekitar Rp14,026,162 pada jumlah total bulanan sebesar $1161 atau sekitar Rp17,220,532.
Untuk menghitung jumlah cicilan pokok bulanan yang ditebus, gunakan rumus berikut:
=-PPMT(TP;A18;$B$4*12;$B$3) =-PPMT((1+3,10%)^(1/12);1;10*12;120000)
Kolom keempat adalah suku bunga, yang menggunakan rumus menghitung cicilan pokok bulanan untuk menemukan berapa banyak bunga yang harus dibayar:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
Kolom kelima berisi jumlah keseluruhan yang harus dibayar. Sebagai contoh, setelah pembayaran ke-40, kamu harus membayar $83.994 atau sekitar Rp1.245.337.041 dari total keseluruhan pinjaman $120.000 atau sekitar Rp1,778,760,000.
Rumusnya adalah sebagai berikut:
=$B$3+CUMPRINC(TP;$B$4*12;$B$3;1;A18;0)
Rumus tersebut menggunakan kombinasi pokok pada periode di depan sel yang berisi jumlah pokok pinjaman. Periode ini akan berubah ketika kamu menyalin dan menyeret sel ke bawah. Tabel di bawah ini menunjukkan bahwa pada akhir 120 periode pinjaman kamu telah dilunasi.
Itulah cara atur jadwal loan repayment dengan Excel. Yuk dicoba!
