Cara Membuat Tabel Angsuran Pinjaman Bunga Efektif, Anuitas, dan Fixed

oleh | 9 Apr 2024 | Belajar Excel, Excel Elementary, Excel Intermediate, Tips Excel | 0 Komentar

Pinjaman adalah bagian penting dari keuangan pribadi atau bisnis. Memahami cara membuat tabel angsuran pinjaman dengan menggunakan Excel dapat sangat berguna untuk mengelola pembayaran pinjaman dengan efisien. Dalam artikel ini, kita akan membahas langkah-langkah untuk membuat tabel angsuran pinjaman yang mencakup bunga efektif, anuitas, dan skema pembayaran tetap (fixed). Mari kita mulai dengan langkah-langkahnya.

1. Mengatur Data Tabel Angsuran

Langkah pertama untuk membuat tabel angsuran pinjaman adalah mengatur data yang diperlukan. Anda memerlukan informasi seperti jumlah pinjaman, suku bunga tahunan, jumlah tenor (jumlah periode pembayaran), dan jenis skema pembayaran (anuitas atau tetap).

Dan berikut adalah contoh tabel yang akan kita buat:

Tabel Angsuran Pinjaman

Di sini untuk kolom “metode bunga” kita menggunakan dropdown list yang dibuat dengan Data Validation. Bagi yang belum tahu caranya, klik ribbon “Data” kemudian ambil “Data Validation” dan setting sebagai berikut:

Dropdown List Excel

2. Membuat Angsuran Bulan Ke

Hal pertama yang akan kita lakukan dalam membuat tabel angsuran pinjaman ini adalah membuat nomor untuk angsuran bulan di range B7:B24. Langkahnya adalah dengan menambahkan rumus Excel berikut di sel B7:

=IF(ROW(A1)>$G$3;"";ROW(A1))

Jadi kalau dibaca dengan bahasa kita maka artinya: jika baris A1 lebih besar dari G3 (tenor) maka kosongkan, jika tidak munculkan angka untuk baris A1 yang dalam hal ini akan menghasilkan angka 1. Copy dan paste rumus tersebut ke sel-sel yang ada di bawahnya. Dan berikut hasilnya:

Angsuran ke dengan Excel

3. Menghitung Saldo Pinjaman

Di kolom Saldo Pinjaman pada range C7:C24 kita bisa masukkan rumus Excel berikut:

=IF(B7="";"";IF(ISNUMBER(C6);G6;D3))

Artinya jika B7 (angsuran bulan ke) kosong maka kosongkan. Jika sel C6 berupa angka maka masukkan nilai di sel G6 (saldo akhir bulan) jika tidak keduanya maka masukkan nilai di D3 (pokok pinjaman).

Copy dan paste rumus di atas ke sel yang ada di bawahnya. Dan berikut adalah hasilnya:

Membuat Saldo Pinjaman dengan Excel

Seperti yang terlihat pada gambar, dari sel C8 sampai C24 masih “0” karena di kolom “Saldo Akhir Bulan” masih belum ada nilainya.

4. Menghitung Angsuran Per Bulan

Untuk mengisi angsuran per bulan pada tabel angsuran pinjaman berdasarkan metode bunga yang ada di sel G4 maka rumus yang harus diinput bisa mengakomodir ketiga metode bunga tersebut yakni: Efective, Anuitas dan Fixed. Untuk itu kita akan menggunakan fungsi IF bertingkat sebagai berikut:

=IFERROR(ABS(IF(B7="";"";IF($G$4="Efective";PMT($D$4;$G$3;$D$3);IF($G$4="Anuitas";(($D$3/$G$3)+($D$4C7));IF($G$4="Fixed";(($D$3/$G$3)+($D$4$D$3));0)))));"")

Seperti yang terlihat dari rumus di atas, fungsi IF bertingkat yang kita gunakan untuk mengakomodir ketiga metode bunga tersebut yakni: Efective, Anuitas dan Fixed. Copy dan paste rumus tersebut ke sel di bawahnya. Dan berikut hasilnya:

Mewnghitung Angsuran Pinjaman dengan Excel

Penjelasan Rumus

Rumus Excel di atas adalah rumus yang digunakan untuk menghitung angsuran pinjaman berdasarkan beberapa variabel seperti jenis skema pembayaran (bisa berupa bunga efektif, anuitas, atau pembayaran tetap), suku bunga per periode pembayaran, jumlah periode pembayaran, nilai pinjaman, dan jenis skema pembayaran pada sel G4.

Baca Juga  Mengenal Fungsi Isblank dan Countblank di Excel

Mari kita bahas bagian per bagian dari rumus membuat tabel angsuran pinjaman di atas:

  • =IFERROR(...): Ini adalah fungsi IFERROR yang digunakan untuk menangani kesalahan dalam perhitungan. Jika terjadi kesalahan dalam perhitungan rumus di dalamnya, fungsi IFERROR akan mengembalikan nilai kosong (“”), sehingga tidak muncul kesalahan dalam hasil perhitungan.
  • ABS(...): Ini adalah fungsi ABS yang digunakan untuk mengembalikan nilai absolut dari suatu angka. Ini digunakan di sini untuk memastikan bahwa hasil perhitungan angsuran pinjaman yang diperoleh selalu positif.
  • IF(B7="";"";...): Ini adalah fungsi IF yang digunakan untuk menentukan apakah sel B7 kosong atau tidak. Jika sel B7 kosong, maka hasil perhitungan rumus akan kosong (“”), jika tidak kosong, maka rumus akan melanjutkan perhitungan selanjutnya.
  • IF($G$4="Efective";PMT($D$4;$G$3;$D$3);...): Ini adalah bagian dari fungsi IF yang memeriksa apakah sel G4 berisi teks “Efective” (mungkin maksudnya “Effective”). Jika ya, maka rumus akan menggunakan fungsi PMT untuk menghitung angsuran berdasarkan suku bunga efektif ($D$4), jumlah periode pembayaran ($G$3), dan nilai pinjaman ($D$3).
  • IF($G$4="Anuitas";(($D$3/$G$3)+($D$4*C7));...): Bagian ini memeriksa apakah sel G4 berisi teks “Anuitas”. Jika ya, maka rumus akan menghitung angsuran berdasarkan skema anuitas, yaitu pembayaran tetap setiap bulan yang terdiri dari angsuran pokok ($D$3/$G$3) ditambah dengan bunga setiap bulan ($D$4*C7, dimana C7 adalah nomor periode pembayaran).
  • IF($G$4="Fixed";(($D$3/$G$3)+($D$4*$D$3));0): Bagian terakhir ini memeriksa apakah sel G4 berisi teks “Fixed”. Jika ya, maka rumus akan menghitung angsuran berdasarkan skema pembayaran tetap, yaitu pembayaran tetap setiap bulan yang terdiri dari angsuran pokok ($D$3/$G$3) ditambah dengan bunga tetap setiap bulan ($D$4*$D$3).

Jadi, rumus tersebut adalah cara untuk menghitung angsuran pinjaman dengan Excel berdasarkan jenis skema pembayaran yang dipilih (bisa berupa bunga efektif, anuitas, atau pembayaran tetap), suku bunga per periode pembayaran, jumlah periode pembayaran, dan nilai pinjaman.

5. Menghitung Bunga Efective, Anuitas dan Fixed

Sebelum mengisi rumusnya di sel E7 terlebih dahulu ingin kami jelaskan rumus untuk menghitung Bunga Efective, Anuitas dan Fixed ini.

Rumus untuk Bunga Efective

Bunga efektif adalah bunga yang dihitung berdasarkan nilai aktual dari pinjaman. Anda dapat menggunakan rumus Excel =FV(rate, nper, pmt) untuk menghitung bunga efektif pada setiap periode pembayaran.

Rumus untuk Bunga Anuitas

Untuk menghitung bunga anuitas, Anda dapat menggunakan rumus Excel seperti =PMT(rate, nper, pv), di mana:

  • rate adalah suku bunga per periode pembayaran,
  • nper adalah jumlah periode pembayaran, dan
  • pv adalah nilai pinjaman.

Rumus Bunga Angsuran Tetap (Fixed)

Jika Anda menggunakan skema pembayaran tetap, Anda dapat menggunakan rumus Excel sederhana seperti =pv/nper untuk menghitung angsuran tetap per bulan.

Rumus Excel yang Digunakan

Untuk rumus Excel yang bisa digunakan untuk menghitung Bunga Efective, Anuitas dan Fixed di sel E7 adalah sebagai berikut:

=IFERROR(ABS(IF(B7="";"";IF($G$4="Efective";IPMT($D$4;B7;$G$3;$D$3);IF($G$4="Anuitas";($D$4C7);IF($G$4="Fixed";($D$4$D$3);0)))));"")

Copy dan paste rumus tersebut di atas ke sel di bawahnya. Dan berikut hasilnya:

Menghitung Bunga di Excel

Penjelasan Rumus

Rumus Excel pada membuat tabel angsuran pinjaman di atas adalah rumus yang digunakan untuk menghitung bunga pinjaman berdasarkan jenis skema pembayaran yang dipilih (bisa berupa bunga efektif, anuitas, atau pembayaran tetap), suku bunga per periode pembayaran, jumlah periode pembayaran, nilai pinjaman, dan nomor periode pembayaran pada sel B7.

Baca Juga  Rumus XLOOKUP Excel untuk Mencari Data dengan Potongan Kata Kunci

Mari kita bahas bagian per bagian dari rumus tersebut:

  • =IFERROR(...): Ini adalah fungsi IFERROR yang digunakan untuk menangani kesalahan dalam perhitungan. Jika terjadi kesalahan dalam perhitungan rumus di dalamnya, fungsi IFERROR akan mengembalikan nilai kosong (“”), sehingga tidak muncul kesalahan dalam hasil perhitungan.
  • ABS(...): Ini adalah fungsi ABS yang digunakan untuk mengembalikan nilai absolut dari suatu angka. Ini digunakan di sini untuk memastikan bahwa hasil perhitungan bunga yang diperoleh selalu positif.
  • IF(B7="";"";...): Ini adalah fungsi IF yang digunakan untuk menentukan apakah sel B7 kosong atau tidak. Jika sel B7 kosong, maka hasil perhitungan rumus akan kosong (“”), jika tidak kosong, maka rumus akan melanjutkan perhitungan selanjutnya.
  • IF($G$4="Efective";IPMT($D$4;B7;$G$3;$D$3);...): Ini adalah bagian dari fungsi IF yang memeriksa apakah sel G4 berisi teks “Efective” (mungkin maksudnya “Effective”). Jika ya, maka rumus akan menggunakan fungsi IPMT untuk menghitung bunga berdasarkan suku bunga efektif ($D$4), nomor periode pembayaran (B7), jumlah periode pembayaran ($G$3), dan nilai pinjaman ($D$3).
  • IF($G$4="Anuitas";($D$4*C7);...): Bagian ini memeriksa apakah sel G4 berisi teks “Anuitas”. Jika ya, maka rumus akan menghitung bunga berdasarkan skema anuitas, yaitu bunga tetap setiap bulan ($D$4) dikalikan dengan nomor periode pembayaran (C7).
  • IF($G$4="Fixed";($D$4*$D$3);0): Bagian terakhir ini memeriksa apakah sel G4 berisi teks “Fixed”. Jika ya, maka rumus akan menghitung bunga berdasarkan skema pembayaran tetap, yaitu bunga tetap setiap bulan ($D$4) dikalikan dengan nilai pinjaman ($D$3).

Jadi, rumus tersebut adalah cara untuk menghitung bunga pinjaman dengan Excel berdasarkan jenis skema pembayaran yang dipilih (bisa berupa bunga efektif, anuitas, atau pembayaran tetap), suku bunga per periode pembayaran, jumlah periode pembayaran, dan nilai pinjaman.

7. Menghitung Cicilan Pokok Pinjaman

Masih menggunakan fungsi IF bertingkat, untuk menghitung cicilan pokok pinjaman di sel F7 ini adalah sebagai berikut:

=IFERROR(ABS(IF(B7="";"";IF($G$4="Efective";PPMT($D$4;B7;$G$3;$D$3);IF($G$4="Anuitas";($D$3/$G$3);IF($G$4="Fixed";($D$3/$G$3))))));"")

Copy dan paste rumusnya ke sel yang ada di bawahnya dan berikut hasilnya:

menghitung cicilan pokok dengan excel

Penjelasan Rumus

Rumus Excel di atas adalah rumus yang digunakan untuk menghitung angsuran pokok pinjaman berdasarkan jenis skema pembayaran yang dipilih (bisa berupa bunga efektif, anuitas, atau pembayaran tetap), suku bunga per periode pembayaran, jumlah periode pembayaran, dan nilai pinjaman, serta nomor periode pembayaran pada sel B7.

Mari kita bahas bagian per bagian dari rumus dari cara membuat tabel angsuran pinjaman tersebut:

  • =IFERROR(...): Ini adalah fungsi IFERROR yang digunakan untuk menangani kesalahan dalam perhitungan. Jika terjadi kesalahan dalam perhitungan rumus di dalamnya, fungsi IFERROR akan mengembalikan nilai kosong (“”), sehingga tidak muncul kesalahan dalam hasil perhitungan.
  • ABS(...): Ini adalah fungsi ABS yang digunakan untuk mengembalikan nilai absolut dari suatu angka. Ini digunakan di sini untuk memastikan bahwa hasil perhitungan angsuran pokok yang diperoleh selalu positif.
  • IF(B7="";"";...): Ini adalah fungsi IF yang digunakan untuk menentukan apakah sel B7 kosong atau tidak. Jika sel B7 kosong, maka hasil perhitungan rumus akan kosong (“”), jika tidak kosong, maka rumus akan melanjutkan perhitungan selanjutnya.
  • IF($G$4="Efective";PPMT($D$4;B7;$G$3;$D$3);...): Ini adalah bagian dari fungsi IF yang memeriksa apakah sel G4 berisi teks “Efective” (mungkin maksudnya “Effective”). Jika ya, maka rumus akan menggunakan fungsi PPMT untuk menghitung angsuran pokok berdasarkan suku bunga efektif ($D$4), nomor periode pembayaran (B7), jumlah periode pembayaran ($G$3), dan nilai pinjaman ($D$3).
  • IF($G$4="Anuitas";($D$3/$G$3);...): Bagian ini memeriksa apakah sel G4 berisi teks “Anuitas”. Jika ya, maka rumus akan menghitung angsuran pokok berdasarkan skema anuitas, yaitu nilai pinjaman ($D$3) dibagi dengan jumlah periode pembayaran ($G$3).
  • IF($G$4="Fixed";($D$3/$G$3);0): Bagian terakhir ini memeriksa apakah sel G4 berisi teks “Fixed”. Jika ya, maka rumus akan menghitung angsuran pokok berdasarkan skema pembayaran tetap, yaitu nilai pinjaman ($D$3) dibagi dengan jumlah periode pembayaran ($G$3).
Baca Juga  Panduan Lengkap: Membuat Surat Kelulusan di Word dengan Mail Merge ke Excel

Jadi, rumus tersebut adalah cara untuk menghitung angsuran pokok pinjaman dengan Excel berdasarkan jenis skema pembayaran yang dipilih (bisa berupa bunga efektif, anuitas, atau pembayaran tetap), suku bunga per periode pembayaran, jumlah periode pembayaran, dan nilai pinjaman.

8. Menghitung Saldo Akhir Bulan

Dan langkah terakhir adalah menghitung saldo akhir bulannya. Rumus Excel untuk menghitung saldo akhir bulan di sel G7 ini adalah sebagai berikut:

=IFERROR(ABS(IF(B7="";"";C7-F7));"")

Copy dan paste rumus tersebut ke sel yang ada di bawahnya. Dan hasilnya adalah sebagai berikut:

menghitung saldo akhir pinjaman dengan excel

Penjelasan Rumus

Rumus Excel di atas adalah rumus yang digunakan untuk menghitung saldo akhir pinjaman berdasarkan nilai angsuran yang telah dibayarkan (C7) dan nilai pokok pinjaman yang telah dibayar (F7). Mari kita jelaskan bagian per bagian dari rumus tersebut:

  • =IFERROR(...): Ini adalah fungsi IFERROR yang digunakan untuk menangani kesalahan dalam perhitungan. Jika terjadi kesalahan dalam perhitungan rumus di dalamnya, fungsi IFERROR akan mengembalikan nilai kosong (“”), sehingga tidak muncul kesalahan dalam hasil perhitungan.
  • ABS(...): Ini adalah fungsi ABS yang digunakan untuk mengembalikan nilai absolut dari suatu angka. Ini digunakan di sini untuk memastikan bahwa hasil perhitungan saldo akhir pinjaman yang diperoleh selalu positif.
  • IF(B7="";"";C7-F7): Ini adalah bagian dari fungsi IF yang digunakan untuk menentukan apakah sel B7 kosong atau tidak. Jika sel B7 kosong, maka hasil perhitungan rumus akan kosong (“”), jika tidak kosong, maka rumus akan menghitung saldo akhir pinjaman dengan mengurangkan nilai angsuran yang telah dibayarkan (C7) dengan nilai pokok pinjaman yang telah dibayar (F7).

Jadi, rumus tersebut adalah cara untuk menghitung saldo akhir pinjaman dengan Excel berdasarkan nilai angsuran yang telah dibayarkan dan nilai pokok pinjaman yang telah dibayar. Rumus ini berguna untuk memantau sisa saldo pinjaman setelah melakukan pembayaran angsuran dan bunga.

Kesimpulan dalam Membuat Tabel Angsuran Pinjaman

Membuat tabel angsuran pinjaman dengan Excel memungkinkan Anda untuk mengelola pembayaran pinjaman dengan lebih efisien dan memahami bagaimana pembayaran tersebut dipengaruhi oleh bunga efektif, anuitas, atau skema pembayaran tetap. Dengan mengikuti langkah-langkah membuat tabel angsuran pinjaman di atas, Anda dapat membuat tabel angsuran yang informatif dan berguna untuk keputusan keuangan Anda.

Pranala Luar

File Excel Membuat Tabel Angsuran Pinjaman untuk Bunga Efektif, Anuitas, dan Fixed dengan Rumus Excel ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkah cara membuat tabel angsuran pinjaman secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!

0 Komentar

Trackbacks/Pingbacks

  1. Membuat Tabel Angsuran dan Denda Kredit Motor dengan Rumus Excel - Depot Excel - […] Rumus Excel terkait membuat tabel angsuran dan denda kredit motor ini merupakan rumus untuk menghitung angsuran kredit. Mari kita…

Kirim Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Join Our Newsletter