Fungsi excel fungs dengan contoh formula

Dalam tutorial ini, anda akan belajar: Apa maksud NPER dalam Excel? Bagaimana menggunakan fungsi NPER untuk mengira bilangan tempoh yang diperlukan untuk membayar pinjaman atau bilangan pelaburan yang diperlukan untuk mendapatkan jumlah sasaran? Dan banyak lagi!

Apabila membina dana korporat, penganalisis kewangan sering ingin mengetahui berapa lama masa yang diperlukan untuk mencapai korpus yang dikehendaki. Apabila memohon pinjaman, anda mungkin ingin mengetahui berapa banyak pembayaran yang diperlukan untuk membayarnya sepenuhnya. Untuk tugas -tugas tersebut, Excel menyediakan fungsi NPER, yang bermaksud "bilangan tempoh".

• Fungsi Excel NPER
• Formula asas di Excel
• Cara Mengira SHAP dalam Excel - Contoh Formula
  • Cari bilangan pembayaran berkala
  • Kirakan SPED berdasarkan nilai masa kini dan masa depan
  • Formula sejagat untuk mencari tempat di Excel
• Dapatkan SPED sebagai Integer
• Fungsi excel nper tidak berfungsi

Fungsi Excel NPER

NPER adalah fungsi kewangan Excel yang mengira bilangan tempoh pembayaran untuk pinjaman atau pelaburan berdasarkan pembayaran berkala yang sama dan kadar faedah yang berterusan.

Fungsi ini tersedia dalam semua versi Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 dan Excel 2007.

Sintaks adalah seperti berikut:

NPER (kadar, pmt, pv, [fv], [jenis])

Di mana:

• Kadar (diperlukan) - kadar faedah setiap tempoh. Jika anda membayar sekali setahun, nyatakan kadar faedah tahunan; Jika anda membayar setiap bulan, berikan kadar faedah bulanan, dll.
• PMT (diperlukan) - Jumlah yang dibayar setiap tempoh. Biasanya, ia termasuk prinsipal dan faedah, tetapi tiada cukai atau yuran.
• PV (Diperlukan) - Nilai pinjaman atau pelaburan semasa, iaitu berapa siri aliran tunai masa depan yang bernilai sekarang.
• FV (Pilihan) - Nilai masa depan pinjaman atau pelaburan selepas pembayaran terakhir. Jika ditinggalkan, lalai kepada 0.
• Jenis (Pilihan) - Menentukan apabila pembayaran akan dibayar:
  • 0 atau ditinggalkan (lalai) - Pada akhir tempoh perakaunan
  • 1 - Pada permulaan tempoh perakaunan

4 perkara yang perlu anda ketahui mengenai fungsi JPER

Untuk menggunakan fungsi Excel Nper dengan berkesan dalam lembaran kerja anda dan elakkan kesilapan yang sama, sila ingat perkara berikut:

1. Gunakan nombor positif untuk aliran masuk (apa -apa jumlah yang anda terima) dan nombor negatif untuk aliran keluar (apa -apa jumlah yang anda bayar).
2. Jika nilai sekarang ( PV ) adalah sifar atau ditinggalkan, nilai masa depan ( FV ) mesti dimasukkan.
3. Hujah kadar boleh dimasukkan sebagai peratusan atau nombor perpuluhan, misalnya 5% atau 0.05.
4. Pastikan kadar bekalan yang sepadan dengan tempoh . Sebagai contoh, jika pinjaman akan dibayar setiap bulan pada kadar faedah tahunan sebanyak 8%, maka gunakan 8%/12 atau 0.08/22 untuk hujah kadar .

Formula asas di Excel

Mengetahui teori itu, mari kita membina formula NPER dalam bentuk yang paling mudah untuk mendapatkan bilangan tempoh untuk membersihkan pinjaman berdasarkan data berikut:

• Kadar faedah tahunan ( kadar ): C2
• Pembayaran Tahunan ( PMT ): C3
• Jumlah Pinjaman ( PV ): C4

Meletakkan hujah -hujah bersama, kami mendapat formula mudah ini:

=NPER(C2, C3, C4)

Argumen pilihan [ FV ] dan [ jenis ] ditinggalkan kerana nilai masa depan tidak relevan dalam kes ini, dan pembayaran akan dibayar pada akhir tahun, yang merupakan jenis lalai.

Dengan mengandaikan pinjaman $ 10,000 diberikan kepada anda pada kadar faedah tahunan 8%, dan anda akan membayar $ 3,800 kepada bank setiap tahun, formula menunjukkan bahawa anda perlu membuat pembayaran 3 tahun untuk membayar balik jumlah pinjaman.

Sila ambil perhatian bahawa PMT adalah nombor negatif kerana ia adalah wang tunai yang keluar.

Jika anda memasukkan pembayaran berkala sebagai nombor positif , maka letakkan tanda tolak sebelum hujah PMT terus dalam formula:

=NPER(C2, -C3, C4)

Cara Menggunakan Fungsi NPER dalam Contoh Formula Excel -

Di bawah ini, anda akan menemui beberapa contoh formula Excel NPER yang menunjukkan cara mengira bilangan tempoh pembayaran untuk senario yang berbeza.

Kirakan bilangan pembayaran berkala untuk pinjaman

Kebanyakan gadai janji dan pinjaman jangka panjang yang lain dibayar dalam ansuran bulanan. Sesetengah yang lain dibayar setiap suku tahun atau setiap setengah tahun. Persoalannya - bagaimana anda dapati jumlah pembayaran berkala yang diperlukan untuk membayar balik jumlah pinjaman?

Kuncinya adalah untuk menukar kadar faedah tahunan kepada kadar berkala . Untuk ini, hanya membahagikan kadar tahunan dengan bilangan tempoh setahun:

Bayaran Bulanan: Kadar = Kadar Faedah Tahunan / 12

Pembayaran suku tahunan: kadar = kadar faedah tahunan / 4

Pembayaran Semiannual: Kadar = Kadar Faedah Tahunan / 2

Mari kita anggap anda meminjam $ 10,000 pada kadar faedah tahunan 8%, dengan pembayaran bulanan sebanyak $ 500.

Kami mulakan dengan memasukkan data dalam sel yang sepadan:

• Kadar Faedah Tahunan (C2): 8%
• Bayaran Bulanan (C3): -500
• Jumlah Pinjaman (C4): 10,000

Untuk mendapatkan nilai yang betul untuk hujah kadar , bahagikan C2 dengan 12. Dan anda akan tampil dengan formula berikut untuk mengira bilangan pembayaran bulanan pinjaman:

=NPER(C2/12, C3, C4)

Hasilnya menunjukkan bahawa ia akan mengambil masa 22 bulan (atau 1 tahun dan 10 bulan) untuk membayar pinjaman:

Jika anda membuat pembayaran suku tahunan , gunakan C2/4 untuk kadar untuk memaksa SPUR untuk mengembalikan tempoh di pihak:

=NPER(C2/4, C3, C4)

Sebagai alternatif, anda boleh memasukkan bilangan tempoh setiap tahun dalam sel berasingan (C5) dan membahagikan kadar faedah tahunan (C2) oleh sel itu:

=NPER(C2/C5, C3, C4)

Kirakan SPED berdasarkan nilai masa kini dan masa depan

Apabila melabur atau menjimatkan wang, anda umumnya tahu berapa banyak yang anda ingin peroleh pada akhir tempoh. Dalam kes ini, anda boleh memasukkan nilai masa depan yang dikehendaki ( FV ) dalam formula.

Katakan anda ingin melabur $ 1,000 pada kadar faedah tahunan sebanyak 3% dan membuat sumbangan tambahan sebanyak $ 500 pada akhir setiap bulan. Matlamat anda adalah untuk mencapai $ 10,000.

Untuk mencari bilangan pelaburan bulanan, masukkan data berikut dalam sel yang telah ditetapkan:

• Kadar Faedah Tahunan (C2): 3%
• Bayaran Bulanan (C3): -500
• Nilai sekarang (C4): -1,000
• Nilai Masa Depan (C5): 10,000
• Tempoh setahun (C6): 12

Masukkan formula ini di C8:

=NPER(C2/C6, C3, C4, C5)

Dan anda akan melihat bahawa ia memerlukan 18 pelaburan bulanan untuk mencapai jumlah sasaran:

Sila perhatikan bahawa:

• Untuk kadar , kami membahagikan kadar faedah tahunan dengan bilangan tempoh setiap tahun (bulan dalam kes kami) untuk mendapatkan fungsi NPER untuk mengembalikan tempoh dalam bulan.
• Kedua -dua PMT dan PV adalah nombor negatif kerana mereka mewakili aliran keluar.
• Adalah diandaikan bahawa pembayaran akan dibayar pada akhir tempoh, jadi hujah jenis ditinggalkan.

Formula sejagat untuk mencari tempat di Excel

Mengendalikan kes -kes tertentu adalah mudah, bukan? Bagaimana pula dengan membina formula umum yang melakukan pekerjaan yang sempurna untuk mengira SPEL dalam Excel sama ada anda meminjam atau menyimpan dan tidak kira berapa kerap anda membuat pembayaran? Untuk ini, kami akan menggunakan fungsi NPER dalam bentuk penuh dan memperuntukkan sel -sel untuk semua argumen, termasuk pilihan. Dalam kes kita, itu akan:

• Kadar faedah tahunan ( kadar ) - C2
• Pembayaran Berkala ( PMT ) - C3
• Nilai sekarang ( PV ) - C4
• Nilai Masa Depan ( FV ) - C5
• Apabila pembayaran dibayar ( jenis ) - C6
• Tempoh setahun - C7

Katakan anda ingin melabur awal $ 10,000 pada kadar faedah tahunan sebanyak 5%, dan kemudian membuat pembayaran tambahan sebanyak $ 1,000 pada permulaan setiap suku tahun, yang bertujuan untuk mencapai $ 50,000. Berapa banyak bayaran suku tahunan yang perlu anda buat?

Untuk mendapatkan kadar berkala yang betul, kami membahagikan kadar faedah tahunan (C2) dengan bilangan tempoh setahun (C7). Untuk hujah -hujah yang lain, hanya membekalkan rujukan sel yang sepadan:

=NPER(C2/C7, C3, C4, C5, C6)

Petua:

• Untuk meninggalkan kurang ruang untuk kesilapan, anda boleh membuat senarai drop-down yang hanya membolehkan 0 dan 1 nilai untuk hujah jenis .
• Biasanya, apabila anda meminjam wang, anda membuat pembayaran pada akhir tempoh untuk membiarkan faedah terkumpul ( jenis ditetapkan kepada 0). Apabila cuba menyelamatkan, anda berminat untuk mendapatkan wang secepat mungkin supaya anda akan mengumpulkan faedah, itulah sebabnya anda membayar pada permulaan tempoh ( jenis ditetapkan kepada 1).

Cara mendapatkan fungsi fung untuk mengembalikan nombor keseluruhan

Dalam contoh di atas, bilangan tempoh yang dikira dipaparkan sebagai integer kerana sel formula diformat untuk tidak menunjukkan tempat perpuluhan. Jika anda memformat sel untuk menunjukkan satu atau lebih tempat perpuluhan, anda akan melihat bahawa hasil dari NPER sebenarnya adalah nombor perpuluhan:

Walau bagaimanapun, dalam kehidupan sebenar, tidak ada tempoh perpuluhan. Dalam situasi sedemikian, pembayaran terakhir hanya akan lebih rendah daripada dalam tempoh sebelumnya.

Untuk mendapatkan bilangan tempoh yang diperlukan yang betul, anda boleh membungkus formula NPER anda dalam fungsi Roundup yang melengkapkan nilai ke atas ke bilangan digit yang ditentukan. Oleh kerana kita mahu hasilnya menjadi integer, kita menetapkan argumen num_digit kepada sifar:

Roundup (nper (), 0)

Dengan kadar faedah tahunan dalam C2, jumlah pembayaran dalam C3 dan jumlah pinjaman di C4, formula mengambil borang ini:

=ROUNDUP(NPER(C2, C3, C4), 0)

Seperti yang anda lihat, bilangan tempoh penuh adalah 4, dan bukan 3 kerana anda mungkin berfikir melihat tangkapan skrin di atas. Anda hanya perlu membayar jumlah yang sangat kecil pada tahun ^ke -4.

Fungsi excel nper tidak berfungsi

Jika formula NPER anda mengembalikan kesilapan atau hasil yang salah, kemungkinan besar akan menjadi salah satu daripada yang berikut.

#Num! ralat

Berlaku jika nilai masa depan yang ditentukan ( FV ) tidak dapat dicapai dengan kadar faedah ( kadar ) dan pembayaran ( PMT ) yang diberikan. Untuk mendapatkan hasil yang sah, cuba meningkatkan kadar faedah berkala dan/atau jumlah pembayaran.

#Value! ralat

Berlaku jika ada argumen yang tidak angka. Untuk menetapkan ralat, pastikan tiada nombor yang digunakan dalam formula diformat sebagai teks. Untuk maklumat lanjut, sila lihat cara menukar teks ke nombor.

Hasil fungsi NPER adalah nombor negatif

Biasanya, masalah ini berlaku apabila pembayaran keluar diwakili oleh nombor positif. Sebagai contoh, apabila mengira tempoh pembayaran untuk pinjaman, membekalkan jumlah pinjaman ( PV ) sebagai nombor positif dan pembayaran berkala ( PMT ) sebagai nombor negatif. Apabila melabur atau menjimatkan wang, pelaburan awal ( PV ) dan pembayaran berkala ( PMT ) sepatutnya negatif dan nilai masa depan ( FV ) positif.

Begitulah caranya untuk mencari tempat di Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh formula NPER di Excel (fail .xlsx)

Atas ialah kandungan terperinci Fungsi excel fungs dengan contoh formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!