Pembayaran angsuran suatu pinjaman yang harus dibayar oleh peminjam dipengaruhi oleh beberapa faktor, di antaranya adalah pokok pinjaman, jangka waktu pinjaman, dan tingkat suku bunga
yang berlaku. Besamya bunga pinjaman yang harus dibayar oleh peminjam
dihitung berdasarkan sistem yang dipakai. Ada 3 sistem yang dipakai,
yaitu:
1. Bunga tetap (flat),
2. Bunga menurun (sliding)
3. Bunga efektif.
Ketiga sistem bunga tersebut dapat dicari dengan
menggunakan Excel. Untuk menghitung bunga dan angsuran pinjaman dengan
sistem bunga efektif dilakukan dengan menggunakan fungsi
finansial. Sementara untuk menghitung bunga dan angsuran pinjaman
dengan sistem bunga tetap dan bunga menurun dapat dilakukan dengan
membuat formula tersendiri.
Untuk lebih jelasnya berikut ini diuraikan tentang sistem bunga tersebut.
Perhitungan Bunga Tetap
Perhitungan bunga pinjaman dengan menggunakan constant payment mortgage
menghasilkan angsuran pinjaman yang tetap dari periode ke periode
(bulan). Di sini bunga dihitung tetap dari pokok pinjaman awal. Adapun
rumus untuk menghitung bunga tetap adalah sebagai berikut:
= (SALDO PINJAMAN AWAL x BUNGA PER TAHUN) /12
Perhitungan Bunga Menurun
Perhitungan bunga pinjaman dengan metode sliding atau model adjusted
rate mortgage akan menghasilkan bunga yang semakin menurun. Dengan
demikian angsuran pinjaman dari periode ke periode juga terus menurun.
Adapun rumus untuk menghitung bunga dengan sistem ini adalah sebagai
berikut:
Perhitungan Bunga Efektif
Perhitungan besamya angsuran pinjaman dengan perhitungan bunga efektif
adalah tetap (seperti sistem flat), tetapi cicilan pokok pinjaman
menaik dan bunga per bulan menurun (seperti sistem sliding). Perhitungan
angsuran pinjaman dengan metode ini berbeda dengan bunga tetap dan
menurun. Excel telah menyediakan fungsi untuk menghitung bunga pinjaman,
cicilan pokok pinjaman, pembayaran angsuran, bunga pinjaman kumulatif,
dan cicilan pokok pinjaman kumulatif.
Membuat Tabel Angsuran Pinjaman
Anda dapat membuat tabel angsuran pinjaman dengan berbagai sistem bunga menggunakan Excel.
Pembahasan materi berikut ini disertai dengan kasus dan penyelesaian.
Sebagi pendukung disertakan juga penyelesaian kasus yang tersimpan dalam
buku kerja Bab5 dan tersimpan dalam lembar kerja mulai KASUS 1, KASUS2,
dan seterusnya. Untuk berlatih telah tersedia lembar kerja tersendiri
yang telah disiapkan formatnya. Untuk KASUS 1, media berlatihnya adalah
lembar kerja LATIH1 yang terletak di samping lembar kerja jawab kasus
tersebut.
Untuk KASUS2, media berlatihnya adalah LATIH2. Demikian seterusnya.
Dalam pembahasan kasus-kasus berikut ini diasumsikan bahwa komputer program Excel telah aktif.
Tabel Angsuran Pinjaman Bunga Menurun
Sebuah lembaga keuangan yang sedang berkembang ingin membuat tabel angsuran pinjaman dari kredit yang diberikan kepada nasabah. Tabel yang diinginkan oleh koperasi tersebut diharapkan memuat informasi
antara lain tentang pokok pinjaman, cicilan pokok pinjaman, bunga,
angsuran per bulan, dan saldo pokok pinjaman. Dalam tahap pertama
koperasi tersebut ingin membuat tabel pinjaman dengan data seperti
berikut:
> Bunga pinjaman ditetapkan sebesar : 15%
> Jangka waktu pinjaman : 12 bulan
> Jumlah pokok pinjaman : 175.000.0000
> Tanggal pinjaman : 2 Februari 2006
Dari data tersebut buatlah tabel angsuran pinjaman dengan sistem bunga menurun.
Penyelesaian
Jawaban penyelesaian kasus tersebut tersimpan dalam buku kerja Bab5
pada lembar kerja KASUS 1 seperti terlihat pada gambar berikut:
|
!AB 1 • |
_ C |
D |
e |
.....F |
G |
H |
I |
~ |
|
2 I 3 I : 4 ; 5 j 6 : 7 ! 9 • |
|
ANGSURAN PINJAMAN PERHITUNGAN BUNGA MENURUN |
|
|
||||
|
Bunga Pinjaman PerTahun |
15% |
|
|
|
|
|||
|
Janaka Waktu Pinjaman |
12 hul<m |
|||||||
|
Pokok Pmiaman |
175.000,000 |
|||||||
|
Tanggal Pmiaman |
IFeh-2006 |
|||||||
|
HIM AN M |
l«l| AN |
POM IK PINJAMAN |
n< ii an l»< »h'»K PINJAMAN |
EM <N<jJV |
ANiiSIIFtAN j Pl« BULAN .pjNJflMSN |
|
||
|
10 : |
1 |
Feb 2006 |
175.000,000 |
14,583,333 |
2.1 87,500 |
16,770.833 |
1160.416.667 |
|
|
11 |
2 |
Mar 2006 |
160.416,667 |
14,583,333 |
2,005,208 |
16.588,542 |
1145.833.333 |
|
|
12 : |
3 |
Apr 2006 |
145,833,333 |
14,583,333 |
1.822,917 |
16,406,250 |
1131,250.000 |
|
|
13 > |
4 |
May 2006 |
131.250.000 |
14,583,333 |
1.640,625 |
16.223.958 |
1116,666.667 |
|
|
! 14 : |
5 |
Jun 2006 |
116.666,667 |
14,583,333 |
1.458,333 |
16,041.667 |
1102,083,333 |
|
|
! 15 |
6 |
Jul 2006 |
102.083,333 |
14.583,333 |
1.276,042 |
15,859,375 |
j 87.500,000 |
|
|
: 16 |
7 |
Aug 2006 |
87,500,000 |
14,583,333 |
1,093.750 |
15,677,083 |
! 72.916.667 |
|
|
' 17 |
8 |
Sep 2006 |
72.916,667 |
14,583,333 |
911.458 |
1 5,494.792 |
I 58,333.333 |
|
|
18 |
g |
Oct 2006 |
58.333,333 |
14.583,333 |
729,167 |
15,312.500 |
I 43,750.000 |
|
|
19 |
10 |
Nov 2006 |
43,750,000 |
14.583,333 |
546.875 |
15.130,208 |
! 29,166,667 |
|
|
: 20 : |
11 |
Dec 2006 |
29.166,667 |
14,583,333 |
364,583 |
14,947.917 |
! 14,583.333 |
|
|
21 |
12 |
Jan 2007 |
14,583,333 |
14,583,333 |
182,292 |
14,765,625 |
0 |
|
|
22 |
|
|
|
|
|
|
|
, |
|
< ► |
n\KASUS1/|« I |
|
|
|
|
|
>1 |
|
Keterangan:
> Bulan ke-1 (CIO). Gunakan fungsi IF dengan ketentuan jika Jangka Waktu Pinjam (F5) = 0, biarkan sel kosong. Jika tidak, isi dengan angka 1. Dengan demikian sel CIO diisi dengan fungsi sebagai berikut:
=IF(F5=0,"",1)
> Bulan ke-2 (Cll). Gunakan fungsi IF dengan ketentuan jika Bulan
ke-1 (CIO) kosong, maka isi sel tersebut kosong. Jika Jangka Waktu
Pinjam (F5) lebih besar atau sama dengan isian pada Bulan ke 1 (CIO),
maka sel Cll diisi dengan angka pada Bulan ke 1 (CIO) ditambah 1. Jika
tidak, alamat sel Cll dikosongkan. Dengan demikian sel Cll diisi dengan
fungsi sebagai berikut:
=IF(C 10=" ",IF(F$5>=C 10+1 ,C 10+1," "))
Untuk bulan selanjutnya, kopi fungsi tersebut ke bawah sampai sel C21 (bulan ke-12).
> Nama Bulan pertama (DIO). Gunakan fungsi IF dengan ketentuan jika
bulan ke-1 (CIO) kosong, maka sel tersebut dikosongkan. Jika tidak maka
isi dengan sel F7.
=IF(C10="","",F7)
> Nama Bulan ke-2 (DI 1) diisi jika alamat sel B11 tidak kosong. Gunakan fungsi EOMONTH, dengan bentuk penulisan fungsi:
=EOMONTH(start_date;months)
Di mana:
■ start date diisi tanggal pinjam (F7)
■ month diisi urutan pembayaran bulan sebelumnya
(CIO).
Dengan demikian sel DI 1 diisi dengan fungsi sebagai berikut: =IF(C 11=" "" ,EOMONTH(F$7,C 10))
Untuk bulan selanjutnya, kopi rumus tersebut ke bawah sampai sel D21.
> Pokok pinjaman Bulan ke-1 (E10) diisi jika alamat sel CIO tidak
kosong dengan ketentuan sama dengan Pokok Pinjaman pada alamat sel F6.
Dengan demikian sel E10 tersebut diisi dengan fungsi sebagai berikut:
=IF(C10="","",F6)
> Pokok Pinjaman Bulan ke-2 (Ell) diisi jika alamat sel Cl 1 tidak
kosong dan diisi dengan Saldo Pokok Pinjaman bulan sebelumnya (110).
Dengan demikian sel Ell diisi dengan fungsi sebagai berikut:
=IF(C11="","",I10)
Untuk pokok pinjaman bulan berikutnya, kopi rumus tersebut ke bawah sampai sel E21.
> Cicilan Pokok Pinjaman Bulan ke-1 (F10) diisi jika alamat sel CIO
tidak kosong. Rumus untuk mengisi berdasarkan perhitungan Pokok Pinjaman
(F6) dibagi Jangka Waktu Pinjam (F5). Dengan demikian sel F10 diisi
dengan fungsi sebagai berikut:
=IF(C10="","",F$6/F$5)
Untuk cicilan pokok pinjaman bulan selanjutnya, kopi fungsi tersebut ke bawah sampai sel F21.
> Bunga Pinjaman Bulan ke-1 (G10) diisi jika alamat sel CIO tidak
kosong dan diisi dengan hasil perkalian antara Pokok Pinjaman (E10)
dengan Bunga Pinjaman per Tahun (F4) dan selanjutnya hasilnya dibagi
dengan angka 12. Dengan demikian sel G10 diisi dengan fungsi sebagai
berikut:
=IF(C 1 o="","",(E 10*F$4)/12)
Untuk bunga pinjaman bulan selanjutnya, kopi rumus tersebut ke bawah sampai sel G21.
> Angsuran Per Bulan untuk Bulan ke-1 (H10) diisi jika alamat sel
CIO tidak kosong. Sel H10 diisi dengan hasil penjumlahan data Cicilan
Pokok Pinjaman (F10) dengan Bunga (G10). Dengan demikian sel ini diisi
dengan fungsi sebagai berikut:
=IF(C 10=""" ,F 10+G10)
Untuk angsuran bulan selanjutnya, kopi rumus tersebut ke bawah sampai sel H21.
> Saldo Pokok Pinjaman Bulan ke-1 (110) diisi jika alamat sel CIO
tidak kosong, diisi Pokok Pinjaman (F6) dikurangi dengan Cicilan Pokok
Pinjaman yang dimulai dari alamat sel F10. Dengan demikian sel 110 diisi
dengan fungsi sebagai berikut:
=IF(C10="","",F$6-SUM(F$10:F10))
Untuk saldo pokok pinjaman bulan selanjutnya, kopi rumus tersebut ke bawah sampai sel 121.
Cara pengerjaan seperti di atas adalah untuk membuat tabel dengan
format conditional sampai 12 bulan. Dengan langkah penyelesaian seperti
kasus tersebut, jika Anda menginginkan perubahan jumlah bulan di bawah
jumlah bulan yang Anda buat (dalam kasus ini 12 bulan), maka Excel
secara otomatis akan menampilkan tabel tersebut sesuai dengan jumlah
bulan yang Anda kehendaki. Misalnya Anda ingin mengubah periode pinjaman
menjadi 10 bulan, maka Anda tinggal mengganti isi sel yang terdapat
pada alamat sel F5 (jangka waktu pinjaman) menjadi angka 10. Hasilnya
akan nampak seperti terlihat pada gambar berikut:
|
AB 1 |
C D E |
............F...... |
G H I |
|
2 |
ANOGURAN PINJAMAN |
||
|
3 |
PERHITUNGAN BUNGA MENURUN |
||
|
4 : |
Bunga Pinjaman PerTahun |
15’« |
|
|
5 ; |
Jangka Waktu Pinjaman |
10 IhiUiii |
|
|
6 : |
Pokok Piniaman |
175.000.000 |
|
|
7 I |
Tanggal Pinjaman |
1 Felr-200<> |
|
|
9 |
BULAN POKOK KE ^ PINJAMAN |
CIOL.AM POKi PINJAMAN |
diims-a l ANIiSURAN 0^,^? BUNGA jppR pm Hfj I POKOK PFR BULAN pwJAM |