Kita dapat menggunakan rumus PMT, IPMT, dan IF Excel untuk membuat jadwal hutang. Pertama, kita perlu menyiapkan model dengan memasukkan beberapa asumsi utang. Dalam contoh ini, kami mengasumsikan hutang menjadi $ 5.000.000, jangka waktu pembayaran menjadi 5 tahun, dan tingkat bunga Suku Bunga Tingkat bunga mengacu pada jumlah yang dibebankan oleh pemberi pinjaman kepada peminjam untuk segala bentuk hutang yang diberikan, umumnya dinyatakan sebagai persentase dari kepala sekolah. menjadi 4,5%.
1. Saldo awal dalam jadwal hutang kita sama dengan jumlah pinjaman $ 5 juta, jadi di sel E29 kita masukkan = B25 untuk menghubungkannya ke input asumsi. Kemudian, kita dapat menggunakan rumus PMT untuk menghitung total pembayaran untuk periode pertama = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Rumus menghitung jumlah pembayaran menggunakan jumlah pinjaman, jangka waktu, dan tingkat bunga yang dinyatakan di bagian asumsi.
2. Di sel E28 masukan periode kita berada 1. Di sel E29 masukkan = E28 + 1 dan isi rumus di sebelah kanan. Selanjutnya gunakan rumus IPMT untuk mengetahui pembayaran bunga periode pertama = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Pembayaran pokok adalah selisih antara total pembayaran dan pembayaran bunga, yaitu = E30-E31 . Saldo penutupan adalah saldo awal ditambah pembayaran pokok yang dilakukan, yaitu = E29 + E32 . Saldo awal periode 2 adalah saldo akhir periode 1, yaitu = E33 .
4. Salin semua rumus dari sel E29 ke E33 ke kolom berikutnya, lalu salin semuanya ke kanan. Periksa apakah saldo penutup untuk periode 5 = 0 untuk memastikan rumus dan angka yang digunakan benar.
5. Perhatikan bahwa ada beberapa pesan kesalahan mulai dari periode 6 karena saldo awal adalah 0. Disini kita dapat menggunakan fungsi IF untuk membersihkan kesalahan. Di sel E30, ketik = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Rumusnya menyatakan bahwa jika saldo awal kurang dari 0, maka total nilai pembayaran akan ditampilkan sebagai 0.
6. Dalam sel 31, ketik = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Rumus ini mirip dengan rumus sebelumnya, yang menyatakan bahwa jika saldo awal kurang dari 0, maka pembayaran bunga akan ditampilkan sebagai 0.
7. Copy sel E30 dan E31, tekan SHIFT + panah kanan lalu CTRL + R untuk mengisi kanan. Anda harus melihat bahwa semua pesan kesalahan sekarang ditampilkan sebagai 0.
XNPV sebuah XIRR dengan fungsi DATE dan IF
Kami dapat menghitung NPV dan IRR berdasarkan tanggal tertentu menggunakan fungsi Excel XNPV dan XIRR dengan fungsi DATE dan IF.
8. Pergi ke sel E6 dan masukkan = DATE (E5,12,31) untuk menampilkan tanggal. Salin ke kanan. Anda akan melihat kesalahan #VALUE! pesan setelah 2021. Kita bisa memperbaikinya dengan menggunakan fungsi IFERROR = IFERROR (DATE (E5,12,31), ””) .
9. Sekarang kita bisa mulai menghitung NPV dan IRR. Pertama, kita perlu memasukkan jumlah arus kas bebas. Kita asumsikan bahwa jumlah FCF dari periode 1 sampai 5 adalah -1.000, 500, 600, 700, 900. Di sel C37, kita akan memasukkan tingkat diskon 15%. Di sel B37, hitung NPV menggunakan rumus XNPV = XNPV (C37, E35: I35, E6: I6) .
10. Di sel B38, hitung IRR menggunakan rumus XIRR = XIRR (E35: I35, E6: I6) .
Menambahkan OFFSET ke XNPV dan XIRR
Kita dapat mengubah ke rumus XNPV dan XIRR untuk membuat rumus yang lebih dinamis menggunakan fungsi OFFSET.
11. Di sel B42, ubah rumusnya menjadi = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Rumusnya lebih dinamis karena jika jumlah periode bertambah, maka periode arus kas bebas juga akan bertambah. Kami tidak perlu mengubah rumus NPV jika periode perkiraan lebih lama. Untuk fungsi IRR, ubah ke = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Setelah menyesuaikan rumus untuk jumlah periode, kita harus mengimbangi tanggal. Di sel B42, ubah rumusnya menjadi = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Hal ini memungkinkan rumus NPV dan IRR untuk mengambil jumlah arus kas bebas yang tepat dengan perubahan jumlah periode.
Ringkasan Rumus Jadwal Utang Utama
- Rumus PMT untuk menghitung jumlah pembayaran hutang: = PMT (suku bunga, jumlah istilah, nilai sekarang)
- Rumus IPMT untuk menghitung pembayaran bunga: = IPMT (suku bunga, periode, jumlah istilah, nilai sekarang)
- Rumus XNPV untuk menemukan nilai sekarang bersih: = XNPV (tingkat diskonto, arus kas bebas, tanggal)
- Rumus XIRR untuk menemukan tingkat pengembalian internal: = XIRR (arus kas bebas, tanggal)
- Rumus OFFSET untuk menghitung NPV dinamis: = XNPV (tingkat diskonto, FCF pertama: OFFSET (FCF pertama, 0, # periode - 1), tanggal 1: OFFSET (tanggal 1, 0, # periode - 1))
- Rumus OFFSET untuk menghitung IRR dinamis: = XIRR (FCF 1: OFFSET (FCF 1, 0, # periode - 1), tanggal 1: OFFSET (tanggal 1, 0, # periode - 1))
Sumber Daya Lainnya
Terima kasih telah membaca panduan Keuangan tentang Jadwal Hutang dengan formula PMT, IPMT dan IF. Untuk terus belajar dan memajukan karier Anda, sumber daya Keuangan berikut akan membantu:
- Rumus Excel Dasar Rumus Excel Dasar Menguasai rumus dasar Excel sangat penting bagi pemula untuk mahir dalam analisis keuangan. Microsoft Excel dianggap sebagai perangkat lunak standar industri dalam analisis data. Program spreadsheet Microsoft juga merupakan salah satu perangkat lunak yang paling disukai oleh bankir investasi
- Praktik Terbaik Pemodelan Keuangan Praktik Terbaik Pemodelan Keuangan Artikel ini adalah untuk memberikan informasi kepada pembaca tentang praktik terbaik pemodelan keuangan dan panduan langkah demi langkah yang mudah diikuti untuk membangun model keuangan.
- Daftar Fungsi Fungsi Excel Daftar fungsi Excel terpenting bagi analis keuangan. Cheat sheet ini mencakup 100 fungsi yang penting untuk diketahui sebagai analis Excel
- Gambaran Umum Pintasan Excel Pintasan Excel Ikhtisar Pintasan Excel adalah metode yang diabaikan untuk meningkatkan produktivitas dan kecepatan dalam Excel. Pintasan Excel menawarkan alat yang ampuh kepada analis keuangan. Pintasan ini dapat melakukan banyak fungsi. sesederhana navigasi dalam spreadsheet untuk mengisi rumus atau mengelompokkan data.
