Formula untuk mengira pembayaran anuiti dalam Excel

Pembayaran pinjaman lebih mudah dan pantas untuk dikira dengan Microsoft Office Excel. Lebih banyak masa dihabiskan untuk pengiraan manual. Artikel ini akan memberi tumpuan kepada pembayaran anuiti, ciri pengiraannya, kelebihan dan kekurangannya.

Apakah bayaran anuiti

Kaedah pembayaran balik bulanan pinjaman, di mana jumlah yang didepositkan tidak berubah sepanjang tempoh pinjaman. Itu. pada tarikh tertentu setiap bulan, seseorang itu mendepositkan sejumlah wang tertentu sehingga pinjaman itu dibayar sepenuhnya.

Lebih-lebih lagi, faedah pinjaman sudah termasuk dalam jumlah yang dibayar kepada bank.

Anuiti klasifikasi

Pembayaran anuiti boleh dibahagikan kepada jenis berikut:

1. tetap. Bayaran yang tidak berubah mempunyai kadar tetap tanpa mengira keadaan luaran.
2. mata wang. Keupayaan untuk menukar jumlah pembayaran sekiranya berlaku kejatuhan atau kenaikan dalam kadar pertukaran.
3. diindeks. Bayaran bergantung pada tahap, penunjuk inflasi. Dalam tempoh pinjaman, saiz mereka sering berubah.
4. Pembolehubah. Anuiti, yang mungkin berubah bergantung pada keadaan sistem kewangan, instrumen.

Beri perhatian! Pembayaran tetap adalah lebih baik untuk semua peminjam, kerana mempunyai risiko yang kecil.

Kebaikan dan keburukan pembayaran anuiti

Untuk lebih memahami topik ini, adalah perlu untuk mengkaji ciri-ciri utama jenis pembayaran pinjaman ini. Ia mempunyai kelebihan berikut:

• Menetapkan jumlah pembayaran tertentu dan tarikh pembayarannya.
• Ketersediaan tinggi untuk peminjam. Hampir semua orang boleh memohon anuiti, tanpa mengira keadaan kewangan mereka.
• Kemungkinan menurunkan jumlah ansuran bulanan dengan peningkatan inflasi.

Bukan tanpa kelemahan:

• Kadar tinggi. Peminjam akan membayar lebih jumlah wang yang lebih besar berbanding dengan bayaran berbeza.
• Masalah yang timbul daripada keinginan untuk membayar hutang lebih awal daripada jadual.
• Tiada pengiraan semula untuk pembayaran awal.

Apakah bayaran pinjaman?

Pembayaran anuiti mempunyai komponen berikut:

• Faedah yang dibayar oleh seseorang apabila membayar pinjaman.
• Sebahagian daripada jumlah pokok.

Akibatnya, jumlah faedah hampir selalu melebihi jumlah yang disumbangkan oleh peminjam untuk mengurangkan hutang.

Formula Pembayaran Anuiti Asas dalam Excel

Seperti yang dinyatakan di atas, dalam Microsoft Office Excel anda boleh bekerja dengan pelbagai jenis pembayaran untuk pinjaman dan pendahuluan. Anuiti tidak terkecuali. Secara umum, formula yang anda boleh mengira dengan cepat sumbangan anuiti adalah seperti berikut:  

Penting! Adalah mustahil untuk membuka kurungan dalam penyebut ungkapan ini untuk memudahkannya.

Nilai utama formula diuraikan seperti berikut:

• AP – bayaran anuiti (nama disingkatkan).
• O – saiz hutang pokok peminjam.
• PS – kadar faedah yang dikemukakan setiap bulan oleh bank tertentu.
• C ialah bilangan bulan pinjaman itu bertahan.

Untuk mengasimilasikan maklumat, cukup untuk memberikan beberapa contoh penggunaan formula ini. Mereka akan dibincangkan lebih lanjut.

Contoh penggunaan fungsi PMT dalam Excel

Kami memberikan syarat mudah masalah itu. Ia adalah perlu untuk mengira bayaran pinjaman bulanan jika bank mengemukakan faedah sebanyak 23%, dan jumlah keseluruhan ialah 25000 rubel. Pinjaman akan berlangsung selama 3 tahun. Masalah diselesaikan mengikut algoritma:

1. Buat hamparan umum dalam Excel berdasarkan data sumber.

2. Aktifkan fungsi PMT dan masukkan argumen untuknya dalam kotak yang sesuai.
3. Dalam medan "Pertaruhan", masukkan formula "B3/B5". Ini akan menjadi kadar faedah ke atas pinjaman.
4. Dalam baris "Nper" tulis nilai dalam bentuk "B4*B5". Ini akan menjadi jumlah pembayaran untuk keseluruhan tempoh pinjaman.
5. Isikan medan "PS". Di sini anda perlu menunjukkan jumlah awal yang diambil dari bank, menulis nilai "B2".

6. Pastikan bahawa selepas mengklik "OK" dalam jadual sumber, nilai "Bayaran bulanan" telah dikira.

Maklumat tambahan! Nombor negatif menunjukkan bahawa peminjam sedang membelanjakan wang.

Contoh pengiraan jumlah lebihan bayaran pinjaman dalam Excel

Dalam masalah ini, anda perlu mengira jumlah yang seseorang yang telah mengambil pinjaman sebanyak 50000 rubel pada kadar faedah 27% selama 5 tahun akan membayar lebih. Secara keseluruhan, peminjam membuat 12 pembayaran setahun. Penyelesaian:

1. Susun jadual data asal.

2. Daripada jumlah pembayaran, tolak jumlah awal mengikut formula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Ia mesti dimasukkan ke dalam bar formula di bahagian atas menu utama program.
3. Akibatnya, jumlah lebihan bayaran akan muncul dalam baris terakhir plat yang dibuat. Peminjam akan membayar lebih 41606 rubel di atas.

Formula untuk mengira bayaran pinjaman bulanan yang optimum dalam Excel

Tugas dengan syarat berikut: pelanggan telah mendaftarkan akaun bank untuk 200000 rubel dengan kemungkinan penambahan bulanan. Adalah perlu untuk mengira jumlah pembayaran yang mesti dibuat oleh seseorang setiap bulan, supaya selepas 4 tahun dia mempunyai 2000000 rubel dalam akaunnya. Kadarnya ialah 11%. Penyelesaian:

1. Buat hamparan berdasarkan data asal.

2. Masukkan formula dalam baris input Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» dan tekan "Enter" dari papan kekunci. Huruf akan berbeza bergantung pada sel di mana jadual diletakkan.
3. Semak bahawa jumlah sumbangan dikira secara automatik dalam baris terakhir jadual.

Beri perhatian! Oleh itu, untuk membolehkan pelanggan mengumpul 4 rubel pada kadar 2000000% dalam 11 tahun, dia perlu mendepositkan 28188 rubel setiap bulan. Tolak dalam jumlah menunjukkan bahawa pelanggan mengalami kerugian dengan memberi wang kepada bank.

Ciri menggunakan fungsi PMT dalam Excel

Secara umum, formula ini ditulis seperti berikut: =PMT(kadar; nper; ps; [bs]; [jenis]). Fungsi ini mempunyai ciri-ciri berikut:

1. Apabila caruman bulanan dikira, hanya kadar tahunan diambil kira.
2. Apabila menentukan kadar faedah, adalah penting untuk mengira semula berdasarkan bilangan ansuran setahun.
3. Daripada hujah "Nper" dalam formula, nombor tertentu ditunjukkan. Ini adalah tempoh pembayaran.

Pengiraan pembayaran

Secara amnya, bayaran anuiti dikira dalam dua peringkat. Untuk memahami topik, setiap peringkat mesti dipertimbangkan secara berasingan. Ini akan dibincangkan lebih lanjut.

Peringkat 1: pengiraan ansuran bulanan

Untuk mengira dalam Excel jumlah yang anda perlu bayar setiap bulan untuk pinjaman dengan kadar tetap, anda mesti:

1. Susun jadual sumber dan pilih sel di mana anda ingin memaparkan hasilnya dan klik pada butang "Sisipkan fungsi" di atas.

2. Dalam senarai fungsi, pilih "PLT" dan klik "OK".

3. Dalam tetingkap seterusnya, tetapkan hujah untuk fungsi, menunjukkan baris yang sepadan dalam jadual yang disusun. Pada penghujung setiap baris, anda perlu mengklik pada ikon, dan kemudian pilih sel yang dikehendaki dalam tatasusunan.

4. Apabila semua hujah diisi, formula yang sesuai akan ditulis dalam baris untuk memasukkan nilai, dan hasil pengiraan dengan tanda tolak akan muncul dalam medan jadual "Pembayaran bulanan".

Penting! Selepas mengira ansuran, adalah mungkin untuk mengira jumlah yang peminjam akan terlebih bayar untuk keseluruhan tempoh pinjaman.

Peringkat 2: butiran pembayaran

Jumlah lebihan bayaran boleh dikira setiap bulan. Akibatnya, seseorang akan memahami berapa banyak wang yang akan dibelanjakan untuk pinjaman setiap bulan. Pengiraan terperinci dilakukan seperti berikut:

1. Buat hamparan selama 24 bulan.

2. Letakkan kursor dalam sel pertama jadual dan masukkan fungsi "OSPLT".

3. Isikan argumen fungsi dengan cara yang sama.

4. Apabila mengisi medan "Tempoh", anda perlu merujuk pada bulan pertama dalam jadual, menunjukkan sel 1.

5. Semak bahawa sel pertama dalam lajur "Pembayaran mengikut isi kandungan pinjaman" telah diisi.
6. Untuk mengisi semua baris lajur pertama, anda perlu meregangkan sel ke hujung jadual

7. Pilih fungsi "PRPLT" untuk mengisi lajur kedua jadual.
8. Isikan semua hujah dalam tetingkap yang dibuka mengikut tangkapan skrin di bawah.

9. Kira jumlah bayaran bulanan dengan menambah nilai dalam dua lajur sebelumnya.

10. Untuk mengira "Baki yang perlu dibayar", anda perlu menambah kadar faedah pada bayaran pada badan pinjaman dan meregangkannya ke hujung plat untuk mengisi semua bulan pinjaman.

Maklumat tambahan! Apabila mengira baki, tanda dolar mesti digantung pada formula supaya ia tidak bergerak apabila diregangkan.

Pengiraan bayaran anuiti atas pinjaman dalam Excel

Fungsi PMT bertanggungjawab untuk mengira anuiti dalam Excel. Prinsip pengiraan secara umum adalah melakukan langkah-langkah berikut:

1. Susun jadual data asal.
2. Bina jadual pembayaran balik hutang untuk setiap bulan.
3. Pilih sel pertama dalam lajur "Pembayaran atas pinjaman" dan masukkan formula pengiraan “PLT ($B3/12;$B$4;$B$2)”.
4. Nilai yang terhasil diregangkan untuk semua lajur plat.

Pengiraan dalam MS Excel pembayaran balik amaun pokok hutang

Bayaran anuiti mesti dibuat setiap bulan dalam jumlah tetap. Dan kadar faedah tidak berubah.

Pengiraan baki amaun pokok (dengan BS=0, jenis=0)

Katakan bahawa pinjaman 100000 rubel diambil selama 10 tahun pada 9%. Ia adalah perlu untuk mengira jumlah hutang pokok pada bulan pertama tahun ke-1. Penyelesaian:

1. Susun lembaran data dan hitung bayaran bulanan menggunakan formula PV di atas.
2. Kira bahagian pembayaran yang diperlukan untuk membayar sebahagian daripada hutang menggunakan formula «=-PMT-(PS-PS1)*item=-PMT-(PS +PMT+PS*item)».
3. Kira jumlah hutang pokok untuk 120 tempoh menggunakan formula yang terkenal.
4. Menggunakan pengendali HPMT cari jumlah faedah yang dibayar untuk bulan ke-25.
5. Semak keputusan.

Mengira jumlah prinsipal yang telah dibayar antara dua tempoh

Pengiraan ini paling baik dilakukan dengan cara yang mudah. Anda perlu menggunakan formula berikut untuk mengira jumlah dalam selang untuk dua tempoh:

• =«-BS(item; con_period; plt; [ps]; [type]) /(1+type *item)».
• = “+ BS(kadar; tempoh_mula-1; plt; [ps]; [jenis]) /IF(tempoh_mula =1; 1; 1+jenis *kadar)”.

Beri perhatian! Huruf dalam kurungan digantikan dengan nilai tertentu.

Pembayaran balik awal dengan tempoh atau bayaran yang dikurangkan

Jika anda perlu mengurangkan tempoh pinjaman, anda perlu melakukan pengiraan tambahan menggunakan pengendali IF. Oleh itu, adalah mungkin untuk mengawal baki sifar, yang tidak sepatutnya dicapai sebelum tamat tempoh pembayaran.

Untuk mengurangkan bayaran, anda perlu mengira semula sumbangan untuk setiap bulan sebelumnya.

Kalkulator pinjaman dengan bayaran tidak tetap

Terdapat beberapa pilihan anuiti di mana peminjam boleh mendepositkan jumlah berubah pada mana-mana hari dalam bulan tersebut. Dalam keadaan sedemikian, baki hutang dan faedah dikira untuk setiap hari. Pada masa yang sama dalam Excel anda memerlukan:

1. Masukkan hari dalam bulan yang pembayaran dibuat, dan nyatakan nombor mereka.
2. Semak jumlah negatif dan positif. Yang negatif lebih diutamakan.
3. Kira hari antara dua tarikh di mana wang didepositkan.

Pengiraan pembayaran berkala dalam MS Excel. Deposit bertempoh

Dalam Excel, anda boleh mengira jumlah pembayaran biasa dengan cepat, dengan syarat jumlah tetap telah terkumpul. Tindakan ini dilakukan menggunakan fungsi PMT selepas jadual awal telah disusun.

Kesimpulan

Oleh itu, pembayaran anuiti adalah lebih mudah, cepat dan lebih cekap untuk dikira dalam Excel. Pengendali PMT bertanggungjawab untuk pengiraan mereka. Contoh yang lebih terperinci boleh didapati di atas.