Formula Excel Menghitung Angsuran Pinjaman


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
Dari gambar tersebut terlihat bahwa Excel menampilkannya sesuai dengan jumlah bulan atau lama pinjaman (dalam hal ini 10 bulan). Jadi apakah anda sudah bisa Menghitung Angsuran Pinjaman

1 komentar:

  1. Membuat daftar angsuran dengan program - POS Finance Tools SL2Download Software Untuk Kredit Barang & Finance POSFT - Sudah support : Anuitas, Flat, Sliding, Floating. Lengkap dengan grafik. Lengkap dengan petunjuk penggunaan dan VIDEO TUTORIAL.

    BalasHapus