Kita boleh menggunakan formula PMT, IPMT, dan IF Excel untuk membuat jadual hutang. Pertama, kita perlu menyusun model dengan memasukkan beberapa andaian hutang. Dalam contoh ini, kita menganggap hutang adalah $ 5.000.000, jangka masa pembayaran menjadi 5 tahun, dan kadar faedah Kadar faedah Kadar faedah merujuk kepada jumlah yang dikenakan oleh pemberi pinjaman kepada peminjam untuk sebarang bentuk hutang yang diberikan, yang umumnya dinyatakan sebagai peratusan prinsipal. menjadi 4.5%.
1. Baki awal dalam jadual hutang kami sama dengan jumlah pinjaman $ 5 juta, jadi dalam sel E29 kami memasukkan = B25 untuk menghubungkannya dengan input andaian. Kemudian, kita boleh menggunakan formula PMT untuk mengira jumlah pembayaran untuk tempoh pertama = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Rumus mengira jumlah pembayaran menggunakan jumlah pinjaman, jangka waktu, dan kadar faedah yang dinyatakan di bahagian andaian.
2. Di sel E28, masukkan jangka masa yang kita ada, iaitu 1. Di sel E29, masukkan = E28 + 1 dan isi formula di sebelah kanan. Seterusnya, gunakan formula IPMT untuk mengetahui pembayaran faedah untuk tempoh pertama = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Pembayaran pokok adalah perbezaan antara jumlah pembayaran dan pembayaran bunga, iaitu = E30-E31 . Baki penutup adalah baki pembukaan ditambah dengan pembayaran pokok yang dibuat, iaitu = E29 + E32 . Baki pembukaan untuk tempoh 2 adalah baki penutup untuk tempoh 1, iaitu = E33 .
4. Salin semua formula dari sel E29 hingga E33 ke lajur seterusnya, kemudian salin semuanya ke kanan. Periksa sama ada baki penutup untuk tempoh 5 = 0 untuk memastikan formula dan nombor yang betul digunakan.
5. Perhatikan bahawa terdapat beberapa mesej ralat mulai dari tempoh 6 kerana baki pembukaannya adalah 0. Di sini kita dapat menggunakan fungsi IF untuk membersihkan kesalahan. Dalam sel E30, taip = JIKA (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Rumusnya menyatakan bahawa jika baki pembukaan kurang dari 0, maka jumlah nilai pembayaran akan ditunjukkan sebagai 0.
6. Dalam sel 31, taip = JIKA (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Rumus ini serupa dengan yang sebelumnya, yang menyatakan bahawa jika baki pembukaannya kurang dari 0, maka pembayaran faedah akan ditunjukkan sebagai 0.
7. Salin sel E30 dan E31, tekan SHIFT + anak panah kanan kemudian CTRL + R untuk mengisi kanan. Anda harus melihat bahawa semua mesej ralat sekarang ditunjukkan sebagai 0.
XNPV dan XIRR dengan fungsi DATE dan IF
Kita boleh mengira NPV dan IRR berdasarkan tarikh tertentu menggunakan fungsi Excel XNPV dan XIRR dengan fungsi DATE dan IF.
8. Pergi ke sel E6 dan masukkan = TARIKH (E5,12,31) untuk memaparkan tarikh. Salin ke kanan. Anda akan melihat # NILAI! mesej selepas 2021. Kami boleh memperbaikinya dengan menggunakan fungsi IFERROR = IFERROR (TARIKH (E5,12,31), ””) .
9. Sekarang kita boleh mula mengira NPV dan IRR. Pertama, kita perlu memasukkan jumlah aliran tunai percuma. Kami menganggap bahawa jumlah FCF dari tempoh 1 hingga 5 adalah -1,000, 500, 600, 700, 900. Dalam sel C37, kami akan memasukkan kadar potongan sebanyak 15%. Dalam sel B37, hitung NPV menggunakan formula XNPV = XNPV (C37, E35: I35, E6: I6) .
10. Dalam sel B38, hitung IRR menggunakan formula XIRR = XIRR (E35: I35, E6: I6) .
Menambah OFFSET ke XNPV dan XIRR
Kita boleh menukar ke formula XNPV dan XIRR untuk membuat formula yang lebih dinamik menggunakan fungsi OFFSET.
11. Dalam sel B42, ubah formula menjadi = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Rumusannya lebih dinamik kerana jika jumlah tempoh meningkat, maka tempoh aliran tunai percuma juga akan meningkat. Kami tidak perlu mengubah formula NPV jika jangka masa lebih lama. Untuk fungsi IRR, ubah ke = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Setelah menyesuaikan formula untuk bilangan tempoh, kita harus mengimbangi tarikhnya. Dalam sel B42, ubah formula menjadi = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Ini membolehkan formula NPV dan IRR mengambil jumlah aliran tunai percuma yang betul dengan perubahan jumlah tempoh.
Ringkasan Rumus Jadual Hutang Utama
- Formula PMT untuk mengira jumlah pembayaran hutang: = PMT (kadar faedah, bilangan syarat, nilai sekarang)
- Formula IPMT untuk mengira pembayaran faedah: = IPMT (kadar faedah, tempoh, jumlah syarat, nilai sekarang)
- Formula XNPV untuk mencari nilai sekarang bersih: = XNPV (kadar diskaun, aliran tunai percuma, tarikh)
- Formula XIRR untuk mencari kadar pulangan dalaman: = XIRR (aliran tunai percuma, tarikh)
- Formula OFFSET untuk mengira NPV dinamik: = XNPV (kadar diskaun, FCF pertama: OFFSET (FCF pertama, 0, # tempoh - 1), tarikh pertama: OFFSET (tarikh pertama, 0, # tempoh - 1))
- Formula OFFSET untuk mengira IRR dinamik: = XIRR (1st FCF: OFFSET (1st FCF, 0, # period - 1), tarikh 1: OFFSET (tarikh 1, 0, # tempoh - 1))
Sumber Lain
Terima kasih kerana membaca panduan Kewangan mengenai Jadual Hutang dengan formula PMT, IPMT dan IF. Untuk terus belajar dan memajukan kerjaya anda, sumber Kewangan berikut akan sangat membantu:
- Formula Excel Asas Rumus Excel Asas Menguasai formula Excel asas sangat penting bagi pemula untuk mahir dalam analisis kewangan. Microsoft Excel dianggap sebagai perisian standard industri dalam analisis data. Program spreadsheet Microsoft juga merupakan salah satu perisian yang paling disukai oleh bank pelaburan
- Amalan Terbaik Pemodelan Kewangan Amalan Terbaik Pemodelan Kewangan Artikel ini adalah untuk memberi maklumat pembaca mengenai amalan terbaik pemodelan kewangan dan panduan langkah demi langkah yang mudah diikuti untuk membina model kewangan.
- Senarai Fungsi Fungsi Excel Senarai fungsi Excel yang paling penting untuk penganalisis kewangan. Lembaran menipu ini merangkumi 100 fungsi yang sangat penting untuk diketahui sebagai penganalisis Excel
- Gambaran Keseluruhan Pintasan Excel Gambaran Keseluruhan Pintasan Excel Jalan pintas Excel adalah kaedah yang diabaikan untuk meningkatkan produktiviti dan kelajuan dalam Excel. Jalan pintas Excel menawarkan penganalisis kewangan alat yang hebat. Jalan pintas ini dapat melakukan banyak fungsi. semudah navigasi dalam hamparan untuk mengisi formula atau mengumpulkan data.
