Adakah anda tahu anda boleh menggunakan Excel untuk mengira pembayaran balik pinjaman anda? Artikel ini akan membimbing anda melalui semua langkah yang diperlukan untuk melakukannya. (Lihat juga: Kalkulator Gadai Janji: Bagaimana Mereka Bekerja .)

Menggunakan Excel, anda boleh mendapatkan pemahaman yang lebih baik tentang gadai janji anda dalam tiga langkah mudah. Langkah pertama ialah menentukan bayaran bulanan. Yang kedua adalah untuk mengetahui kadar faedah, dan yang ketiga adalah untuk mencari jadual pinjaman. Untuk berbuat demikian, anda boleh membina jadual dalam Excel yang akan memberitahu anda: Kadar yang paling rendah; pengiraan pinjaman untuk jangka masa; penguraian pinjaman, serta pelunasan dan perhitungan sewa bulanan.

Perhitungan Pinjaman untuk Sewa Bulanan

Pertama, mari kita lihat bagaimana untuk melaksanakan pengiraan pembayaran bulanan untuk gadai janji. Dalam erti kata lain, menggunakan kadar Iinterest tahunan, prinsipal dan tempoh, kita boleh menentukan amaun yang akan dibayar balik bulanan.

Formula, seperti yang ditunjukkan dalam tangkapan skrin di atas, ditulis seperti berikut:

= - PMT (kadar, panjang; present_value; [future_value]; [jenis])

Tanda minus di hadapan PMT adalah perlu, kerana formula mengembalikan nombor negatif. Tiga argumen pertama ialah kadar pinjaman, panjang pinjaman (jumlah tempoh) dan Principal yang dipinjam. Dua argumen terakhir adalah opsyenal, nilai sisa mungkir kepada 0, dibayar terlebih dahulu (untuk 1) atau pada akhir (untuk 0), juga pilihan.

Formula Excel yang digunakan untuk mengira bayaran bulanan pinjaman adalah:

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

Penjelasan: Bagi kadar yang kita gunakan tempoh kadar, iaitu kadar bulanan, bilangan tempoh (bulan di sini 120 selama 10 tahun didarabkan dengan 12 bulan) dan akhirnya kami menunjukkan ibu yang dipinjam. Pembayaran bulanan kami ialah $ 1, 161. 88 lebih dari 10 tahun.

Pengiraan Gadai Janji untuk Kadar Faedah

Kami telah melihat bagaimana untuk menetapkan pengiraan pembayaran bulanan untuk gadai janji. Tetapi kami mungkin mahu menetapkan bayaran bulanan maksimum yang kami mampu yang juga memaparkan jumlah tahun yang akan kami bayar. Atas sebab itu, kami ingin mengetahui kadar faedah tahunan yang sepadan.

Mengira Kadar Faedah untuk Pinjaman

Seperti yang ditunjukkan dalam tangkapan skrin di atas, kita mula-mula mengira kadar masa (bulanan dalam kes kita), dan kemudian kadar tahunan. Formula yang digunakan akan KADAR, seperti yang ditunjukkan dalam tangkapan skrin di atas, ia ditulis seperti berikut:

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

Tiga argumen pertama ialah panjang pinjaman (bilangan tempoh), dan bayaran bulanan untuk membayar balik pinjaman yang dipinjam. Tiga hujah yang terakhir adalah pilihan, dan nilai sisa mungkir kepada 0, hujah istilah untuk menguruskan kematangan terlebih dahulu (untuk 1) atau pada akhir (untuk 0) juga pilihan, dan akhirnya hujalan anggaran adalah pilihan, tetapi boleh memberikan anggaran awal kadar.

Formula Excel yang digunakan untuk mengira kadar pinjaman ialah:

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

Nota: data yang sepadan dalam bayaran bulanan mesti diberi tanda negatif. Ini sebabnya tanda minus sebelum formula. Tempoh kadar kami ialah 0. 294%.

Kami menggunakan formula = (1 + B5) ialah 12-1 ^ = (1 + 0. 294%) ^ 12-1 untuk mendapatkan kadar tahunan pinjaman kami untuk menjadi 3. 58 %. Dalam erti kata lain, untuk meminjam 120, 000 $ lebih 13 tahun untuk membayar 960 $ bulanan kita harus merundingkan pinjaman pada kadar maksimum 3. 58% tahunan.

Pengiraan Gadai janji untuk Panjang Pinjaman

Kami akan melihat bagaimana untuk mendapatkan panjang pinjaman apabila anda mengetahui kadar tahunan, prinsipal yang dipinjam dan bayaran bulanan yang akan dibayar balik. Dalam erti kata lain, berapa lama kita perlu membayar balik gadai janji $ 120,000 dengan kadar 3.10% dan pembayaran bulanan $ 1, 100?

Jumlah Pembayaran Balik untuk Pinjaman

Formula yang akan kita gunakan adalah NPER, seperti yang ditunjukkan dalam tangkapan skrin di atas, dan ia ditulis seperti berikut:

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

Tiga argumen pertama ialah kadar tahunan pinjaman, bayaran bulanan yang diperlukan untuk membayar balik pinjaman, dan prinsipal yang dipinjam. Dua argumen terakhir adalah pilihan, nilai sisa mungkir kepada 0, istilah argumen yang perlu dibayar terlebih dahulu (untuk 1) atau pada akhir (untuk 0) juga pilihan. (1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Nota: data yang sepadan dalam pembayaran bulanan mesti diberi tanda negatif. Ini sebabnya kami mempunyai tanda tolak sebelum formula. Panjang bayaran adalah 127. 97 tempoh (bulan dalam kes kami).

Kami akan menggunakan formula = B5 / 12 = 127. 97/12 untuk bilangan tahun untuk menyelesaikan pembayaran balik pinjaman. Dalam erti kata lain, untuk meminjam $ 120, 000, dengan kadar tahunan 3.10% dan untuk membayar $ 1, 100 sebulan, kita perlu membayar balik kematangan selama 128 bulan atau 10 tahun dan 8 bulan.

Decomposing the Loan

Pembayaran pinjaman terdiri daripada dua perkara, prinsipal dan kepentingan. Faedah dikira untuk setiap tempoh, contohnya pembayaran balik bulanan selama 10 tahun, akan memberi kita 120 tempoh.

Tangkapan skrin di atas menunjukkan pecahan pinjaman (jumlah tempoh sama dengan 120), menggunakan formula PPMT dan IPMT. Argumen kedua formula adalah sama dan dibahagikan seperti berikut:

= - PPMT (kadar; num_period; panjang; prinsipal; [sisa]; [terma])

= - INTPER (kadar; num_period; panjang; prinsipal; [sisa]; [terma])

Argumen adalah sama seperti formula PMT yang dilihat pada bahagian pertama, kecuali untuk num_period yang ditambahkan untuk menunjukkan tempoh yang mana untuk memecahkan pinjaman, memberi prinsipal dan kepentingan untuknya. Mari kita ambil contoh:

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

Hasilnya adalah yang ditunjukkan dalam screenshot "Penguraian Pinjaman," selama tempoh dianalisis yang "1" , atau bulan pertama.Untuk yang ini, kami membayar $ 1161. 88, dipecah menjadi $ 856, 20 pokok dan $ 305. 68 minat.

Perhitungan Pinjaman Excel

Sekarang juga boleh mengira pembayaran prinsipal dan faedah untuk beberapa tempoh, seperti 12 bulan pertama atau 15 bulan pertama.

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

= - CUMIPMT (rate; length; principal; start_date; end_date; pokok dan istilah (yang wajib) yang telah kita lihat di bahagian pertama dengan formula PMT. Tetapi di sini, kita memerlukan start_date dan end_date arguments, juga. Yang pertama menunjukkan permulaan tempoh untuk dianalisis dan yang kedua akhir. Contohnya:

= - 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; 1; 12; 0)

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

yang ditunjukkan dalam tangkapan skrin "Cumul 1 tahun," maka tempoh yang dianalisa berkisar antara 1 hingga 12, dari tempoh pertama (bulan pertama) hingga ke 12 (bulan ke-12). Lebih setahun, kami akan membayar $ 10 419, 55 Prinsipal dan $ 3 522. 99 Faedah.

Pelunasan Pinjaman

Formula sebelum ini membolehkan kami membuat tempoh jadual kami mengikut tempoh, berapa banyak yang akan kami bayar bulanan dalam pokok dan faedah, dan berapa banyak yang perlu dibayar.

Buat Jadual Pinjaman dalam Excel

Untuk membuat jadual pinjaman, kami akan menggunakan formula berbeza yang dibincangkan di atas dan memperluaskannya ke dalam bilangan tempoh.

Dalam lajur pertama, masukkan "1" sebagai tempoh pertama, kemudian seret sel ke bawah. Dalam kes kami, kami memerlukan 120 tempoh sejak pembayaran pinjaman 10 tahun yang didarabkan sebanyak 12 bulan = 120.

Lajur kedua adalah jumlah bulanan yang perlu kita bayar setiap bulan, yang tetap sepanjang keseluruhan jadual pinjaman. Untuk mengira, masukkan formula berikut dalam sel tempoh pertama kami:

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

Lajur ketiga adalah prinsipal yang akan dibayar balik bulanan. Misalnya, untuk tempoh ke-40, kami akan membayar $ 945. 51 di prinsipal pada jumlah bulanan kami $ 1, 161. 88. Untuk mengira jumlah prinsipal yang ditebus, kami menggunakan formula berikut:

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

Lajur keempat adalah faedah, yang mana kita mengira prinsipal dibayar balik pada jumlah bulanan kami untuk mengetahui bagaimana (1 + 3, 10%) ^ (1/12) = - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ; 1; 10 * 12; 120000)

Lajur kelima mengandungi jumlah yang tinggal untuk bayar. Sebagai contoh, selepas pembayaran ke-40 kita perlu membayar $ 83, 994. 69 pada $ 120, 000. Formula adalah seperti berikut:

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

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; prinsipal di bawah tempoh hadapan dengan sel yang mengandungi pokok yang dipinjam. Tempoh ini mula berubah apabila kita menyalin dan mengheret sel ke bawah.Petikan di bawah menunjukkan bahawa pada akhir tempoh 120 pinjaman kami dibayar balik.