Tahukah Anda bahwa Anda dapat menggunakan Excel untuk menghitung pembayaran pinjaman Anda? Artikel ini akan memandu Anda melalui semua langkah yang diperlukan untuk melakukannya. Kalkulator KPR: Bagaimana Cara Kerjanya . Menggunakan Excel, Anda bisa mendapatkan pemahaman hipotek yang lebih baik dalam tiga langkah sederhana. Langkah pertama adalah menentukan pembayaran bulanan. Yang kedua adalah menemukan tingkat suku bunga, dan yang ketiga adalah menemukan jadwal pinjaman. Untuk melakukannya, Anda dapat membuat tabel di Excel yang akan memberi tahu Anda: Tingkat yang paling rendah; perhitungan pinjaman untuk durasi; membongkar pinjaman, serta amortisasi dan perhitungan sewa bulanan.

Perhitungan Pinjaman untuk Sewa Bulanan

Pertama, mari kita lihat bagaimana menerapkan perhitungan pembayaran bulanan untuk hipotek. Dengan kata lain, dengan menggunakan tingkat Iinterest tahunan, pokok dan durasi, kita dapat menentukan jumlah yang harus dibayar setiap bulan.

Rumusnya, seperti yang ditunjukkan pada gambar di atas, dituliskan sebagai berikut:

= - PMT (rate; length; present_value; [future_value]; [type])

Tanda minus di depan PMT diperlukan, karena rumus mengembalikan angka negatif. Tiga argumen pertama adalah tingkat pinjaman, panjang pinjaman (jumlah periode) dan pinjaman pokok. Dua argumen terakhir bersifat opsional, nilai residu default ke 0, dibayarkan di muka (untuk 1) atau pada akhir (untuk 0), juga opsional.

Rumus Excel yang digunakan untuk menghitung pembayaran pinjaman bulanan adalah:

= - PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Penjelasan: Untuk tingkat kita menggunakan periode suku bunga, yang merupakan tingkat bulanan, maka kita hitung jumlah periode (bulan disini 120 selama 10 tahun dikalikan dengan 12 bulan) dan akhirnya kita menunjukkan pinjaman pokoknya. Pembayaran bulanan kami akan menjadi $ 1, 161. 88 lebih dari 10 tahun.

Perhitungan Hipotek untuk Suku Bunga

Kita telah melihat bagaimana mengatur perhitungan pembayaran bulanan untuk hipotek. Tapi kami mungkin ingin menetapkan pembayaran bulanan maksimum yang kami mampu yang juga menampilkan jumlah tahun di mana kami harus membayarnya kembali. Oleh karena itu, kami ingin mengetahui tingkat bunga tahunan yang bersangkutan.

Menghitung Tingkat Bunga untuk Pinjaman

Seperti yang ditunjukkan pada gambar di atas, pertama-tama kita menghitung tingkat suku bunga (bulanan dalam kasus kami), dan kemudian tingkat tahunan. Rumus yang digunakan adalah RATE, seperti yang ditunjukkan pada gambar di atas, ditulis sebagai berikut:

= RATE (Nper; pmt; present_value; [future_value]; [type])

Tiga argumen pertama adalah panjang dari pinjaman (jumlah periode), dan pembayaran bulanan untuk membayar pokok pinjaman. Tiga argumen terakhir bersifat opsional, dan nilai residu default ke 0, argumen istilah untuk mengelola kematangan di awal (untuk 1) atau pada akhir (untuk 0) juga opsional, dan akhirnya argumen perkiraan bersifat opsional, namun dapat berikan perkiraan awal tarif.

Rumus Excel yang digunakan untuk menghitung suku bunga pinjaman adalah:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Catatan: data yang sesuai di pembayaran bulanan harus diberi tanda negatif. Inilah sebabnya mengapa tanda minus sebelum formula. Periode tarif kami adalah 0. 294%.

Kami menggunakan rumus

= (1 + B5) adalah 12-1 ^ = (1 + 0, 294%) ^ 12-1 untuk mendapatkan tingkat bunga pinjaman tahunan menjadi 3. 58 %. Dengan kata lain, meminjam 120.000 dolar lebih dari 13 tahun untuk membayar bulanan 960 $ kita harus menegosiasikan pinjaman pada tingkat maksimum tahunan 3. 58%. Perhitungan Hipotek untuk Panjang Pinjaman

Sekarang kita akan melihat bagaimana mendapatkan pinjaman ketika Anda mengetahui tingkat suku bunga tahunan, pinjaman pokok dan pembayaran bulanan yang akan dilunasi. Dengan kata lain, berapa lama kita perlu melunasi hipotek $ 120.000 dengan tingkat 3. 10% dan pembayaran bulanan sebesar $ 1, 100?

Jumlah Pembayaran untuk Pinjaman

Rumus yang akan kita gunakan adalah NPER, seperti yang ditunjukkan pada gambar di atas, dan ditulis sebagai berikut:

= NPER (rate; pmt; present_value; [future_value]; [tipe])

Tiga argumen pertama adalah tingkat bunga tahunan, pembayaran bulanan yang diperlukan untuk melunasi pinjaman, dan pinjaman terpinjam. Dua argumen terakhir bersifat opsional, nilai residu default ke 0, istilah argumen harus dibayar di muka (untuk 1) atau pada akhir (untuk 0) juga opsional.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Catatan: data yang sesuai dalam pembayaran bulanan harus diberi tanda negatif. Inilah sebabnya mengapa kita memiliki tanda minus sebelum formula. Panjang penggantian adalah 127. 97 periode (bulan dalam kasus kami).

Kita akan menggunakan rumus = B5 / 12 = 127. 97/12 untuk jumlah tahun untuk menyelesaikan pelunasan pinjaman. Dengan kata lain, untuk meminjam $ 120.000, dengan tingkat tahunan 3. 10% dan untuk membayar $ 1, 100 bulanan, kita harus melunasi jatuh tempo untuk 128 bulan atau 10 tahun dan 8 bulan.

Menguraikan Pinjaman

Pembayaran pinjaman terdiri dari dua hal, pokok dan bunga. Bunga dihitung untuk setiap periode, misalnya pembayaran bulanan lebih dari 10 tahun, akan memberi kita 120 periode.

Tangkapan layar di atas menunjukkan rincian pinjaman (total periode sama dengan 120), dengan menggunakan formula PPMT dan IPMT. Argumen dari kedua formula itu sama dan dipecah sebagai berikut:

= - PPMT (tingkat; num_period; length; principal; [residual]; [terme])

= - INTPER (rate; num_period; Panjang argumen, sama seperti rumus PMT yang terlihat pada bagian pertama, kecuali num_period yang ditambahkan untuk menunjukkan periode dimana untuk memecah pinjaman, memberikan pokok dan bunga untuk itu. Mari kita ambil contoh:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12 ) (1 + 3), (1 + 3), (1 + 3, 10). %) (1/12) -1; 1; 10 * 12; 120000)

Hasilnya adalah yang ditunjukkan pada tangkapan layar "Dekomposisi Pinjaman," selama periode yang dianalisis yaitu "1," jadi periode pertama , atau bulan pertama.Untuk yang satu ini, kita bayar $ 1161. 88, dipecah menjadi $ 856, 20 pokok dan $ 305. Bunga.

Perhitungan Pinjaman Excel

Sekarang juga mungkin untuk menghitung pembayaran pokok dan bunga untuk beberapa periode, seperti 12 bulan pertama atau 15 bulan pertama.

= - CUMPRINC (level; length; principal; start_date; end_date; type)

= - CUMIPMT (rate; length; principal; start_date; end_date; type)

Kami menemukan argumen, tingkat, panjang, pokok dan istilah (yang wajib) yang telah kita lihat pada bagian pertama dengan rumus PMT. Tapi di sini, kita membutuhkan argumen start_date dan end_date juga. Yang pertama menunjukkan awal periode yang akan dianalisis dan yang kedua berakhir. Mari kita ambil contoh:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

= - CUMIPMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3 , (12, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

Hasilnya adalah yang ditunjukkan pada tangkapan layar "Cumul 1st year," jadi periode dianalisis berkisar antara 1 sampai 12, dari periode pertama (bulan pertama) sampai tanggal dua belas (12 bulan). Lebih dari setahun, kami akan membayar $ 10 419, 55 Prinsipal dan $ 3 522. 99 Bunga.

Amortisasi Pinjaman

Rumus sebelumnya memungkinkan kami membuat jadwal periode berdasarkan periode, berapa banyak yang akan kami bayar setiap bulan pokoknya dan bunga, dan berapa banyak yang harus dibayar.

Buat Jadwal Pinjaman di Excel

Untuk membuat jadwal pinjaman, kami akan menggunakan formula yang berbeda yang dibahas di atas dan memperbanyaknya selama jumlah periode.

Pada kolom periode pertama, masukkan "1" sebagai periode pertama, lalu seret sel ke bawah. Dalam kasus kami, kami membutuhkan 120 periode sejak pembayaran pinjaman 10 tahun dikalikan dengan 12 bulan = 120.

Kolom kedua adalah jumlah bulanan yang harus kami bayar setiap bulan, yang konstan selama keseluruhan jadwal pinjaman. Untuk menghitungnya, masukkan rumus berikut di sel periode pertama kami:

= - PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12 ) -1; 10 * 12; 120000)

Kolom ketiga adalah kepala sekolah yang akan dilunasi setiap bulan. Misalnya, untuk periode ke-40, kita akan membayar $ 945. 51 secara prinsipal dengan jumlah keseluruhan bulanan sebesar $ 1, 161. 88. Untuk menghitung jumlah pokok yang ditebus, kami menggunakan rumus berikut:

= - PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Kolom keempat adalah bunga, yang mana kami menghitung pokok pembayaran pada jumlah bulanan kami untuk mengetahui bagaimana Banyak bunga yang harus dibayar, dengan menggunakan rumus:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12) ; 1; 10 * 12; 120000)

Kolom kelima berisi jumlah yang harus dibayar. Misalnya, setelah pembayaran ke-40 kita harus membayar $ 83, 994. 69 pada $ 120.000. Rumusnya adalah sebagai berikut:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1, A18; 0)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

Rumus menggunakan kombinasi dari kepala sekolah di bawah masa depan dengan sel yang berisi pinjaman pokok. Periode ini mulai berubah saat kita menyalin dan menyeret sel ke bawah.Tangkapan layar di bawah menunjukkan bahwa pada akhir 120 periode pinjaman kami dilunasi.