Perhitungan Bunga Bank dengan Excel


Perhitungan Bunga Bank dengan Excel

Jika kita akan melakukan pinjaman di bank, kita akan disodori selembar kertas yang berisi tabel yang berisi jumlah pinjaman, masa pinjaman, dan besarnya angsuran yang harus di bayar tiap bulannya. Bahkan kadang kita tidak tahu sistem bunga apa yang diterapkan oleh bank tersebut, pakai bunga efektif ( effective rate ) atau bunga tetap ( flate rate ). Berapa yang harus kita bayar, jika kita menutup angsuran tersebut pada bulan ke – n ? Belum lagi jika kita melakukan perbandingan cicilan pada beberapa bank, misal lebih banyak mana cicilan yang harus kita bayar jika pada bank a menerapkan effektive rate 19 % / tahun dengan bank b yang menerapkan flate rate 12 % / tahun ?

Flate Rate - Bunga Bank dengan Excel

Perhitungan Bunga Bank dengan Excel

Pada flate rate, perhitungan cicilan relatif lebih mudah di banding dengan effective rate. Disini kita hanya melakukan perhitungan :

Cicilan = (pinjaman/jangka waktu dlm bulan) + ( pinjaman / bunga dlm bulan )

misal :

pinjaman (A1) = 10.000.000 (B1)
bunga (A2) = 12 % (B2) / tahun (C2)
Angsuran (A3) = 3 (B3) tahun (C3), maka
Cicilan = (10.000.000/ 3 / 12 ) + ( 10.000.000 * 12% / 12 ) = 377.777,78
Excel cell (B4) =(B1/B3/12)+(B1*B2/12)

Effective Rate - Bunga Bank dengan Excel

Pada perhitungan effective rate, perhitungan bunga tidak di hitung secara global seperti pada flate rate. Disini yang di hitung adalah sisa pokok ( sisa angsuran kita ) yang di kalikan bunga. Dikarenakan angsuran yang kita bayar tiap bulannya tetap, maka pada tiap-tiap bulan perbandingan antara pengurangan hutang pokok dan bunga berbeda. Jumlah pokok kita tiap bulan naik, dan bunga semakin menurun. Tidak seperti di flate rate yang pokok dan bunga setiap bulannya selalu tetap.

Memang perhitungan ini sangatlah rumit, tapi di excel kita mempunyai fungsi finansial ( financial function ) yaitu programming excel untuk finance :

PMT (rate, nper, pv, fv, type)

Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

Remarks

  • The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

misal :

pinjaman (A1) = 10.000.000 (B1)
bunga (A2) = 19 % (B2) / tahun (C2)
Angsuran (A3) = 3 (B3) tahun (C3), maka

Excel cell (B4) =PMT(B2/12,B3*12,-B1)
=PMT(19%/12, 3*12, -10.000.000)
= 366.560,20

Jadi, jika pinjaman kita 10.000.000 di angsur selama 3 tahun, antara flate rate yg menggunakan bunga 12%/ tahun dengan effective rate 19% / tahun, masih sedikit effective rate kan ??? Untuk simulasi pinjaman, bunga dan jangka angsuran lainnya silahkan disimulasi sendiri ya…

Lalu bagai mana jika kita ingin mengetahui jumlah pokok yang sudah terbayar, sisa angsuran pada bulan ke-n ???

Mari kita buat saja tabel dari excel :

Perhitungan Bunga Bank dengan Excel

B6 =IF(A6<= ($B$3*12),PMT($B$2/12,$B$3*12,-$B$1),””)
B7 =IF(A7<= ($B$3*12),PMT($B$2/12,$B$3*12,-$B$1),””)
B185 =IF(A185<= ($B$3*12),PMT($B$2/12,$B$3*12,-$B$1),””)
C6 =IF(A6<= ($B$3*12),PPMT($B$2/12,$A6,$B$3*12,-$B$1),””)
C7 =IF(A7<= ($B$3*12),PPMT($B$2/12,$A7,$B$3*12,-$B$1),””)
C185 =IF(A185<= ($B$3*12),PPMT($B$2/12,$A185,$B$3*12,-$B$1),””)
D6 =IF(A6<=($B$3*12),IPMT($B$2/12,$A6,$B$3*12,-$B$1),””)
D7 =IF(A7<=($B$3*12),IPMT($B$2/12,$A7,$B$3*12,-$B$1),””)
D185 =IF(A185<=($B$3*12),IPMT($B$2/12,$A185,$B$3*12,-$B$1),””)
E6 =C6
E7 =IF(A7<=($B$3*12),E6+C7,””)
E185 =IF(A185<=($B$3*12),E184+C185,””)
F6 =IF($A6<=($B$3*12),$B$1-$E6,””)
F7 =IF($A7<=($B$3*12),$B$1-$E7,””)
F185 =IF($A185<=($B$3*12),$B$1-$E185,””)

Keterangan :

Untuk cell B8 sampai F184 silahkan di copy paste. Tabel ini berlaku sampai dengan 180 bulan, untuk angsuran yang lebih dari 180 bulan, silahkan ditambahkan lagi Excel Perhitungan bunga bank


Post Update :
Date : Friday, July 25, 2014 - Jumat, 25 Juli 2014
Posted in: internet



Related posts "Perhitungan Bunga Bank dengan Excel":


19 Comments on "Perhitungan Bunga Bank dengan Excel"

Trackback | Comments RSS Feed

  1. ratno says:

    saya minta simulasi perhitungan kpr terima kasih

    [Reply]

    Ricky Reply:

    Saya bisa memberi Anda file exel untuk perhitungan KPR, email me.

    [Reply]

  2. Adhirta Pradana says:

    mohon dikirimi soft copy perhitungan bunga bank
    terima kasih

    [Reply]

  3. Udin Nurtomo says:

    Terima Kasih infonya, Sangat membantu …………

    [Reply]

  4. Lia says:

    Makasih ya Mbk…Tabelnya sangat membantu….:)

    [Reply]

  5. yamin says:

    kalau ada programnya mohon kirim kr mail saya tks

    [Reply]

  6. iDho says:

    makasih banyak.. setelah ngublek2 karena rumusnya sempet ga mau2,,akhirnya mau juga,,tapi saya ada sedikit modifikasi rumusnya jadi tanda petik dua di belakang saya hilangkan,,soale ketika masih ada dua tanda petik itu ga mau terus formulanya

    tapi sungguh membantu,akhirnya dapat juga saya buat bikin kertas kerja perhitungan bunga pinjaman

    much thanks :)

    [Reply]

  7. Ronald says:

    trims berat atas infonya…. sangat bermanfaat tks

    [Reply]

  8. Irawan says:

    Trim’s…

    [Reply]

  9. Felix says:

    Dear Pak Ariefew

    Pak mohon dikirim soft copynya dong. Soalnya saya buat sendiri kok macet.

    Tq sebelumnya

    [Reply]

  10. Txh for your information.
    don;t forget visit my blog :)

    [Reply]

  11. idham says:

    kayaknya di pakai pada perhitungan pinjaman khusus perempuan pada program PNPN-MP… terima kasih atas informasinya.. sangat bermanfaat

    [Reply]

  12. gunawan says:

    artikel ini sangat membantu buat saya gan

    [Reply]

  13. syafril says:

    mohon bantuannya utk dpt mengirimkan soft copy dr excell tsb…terima kasih byk sblumnya

    [Reply]

  14. Mus Koto says:

    Salam kenal,
    Terima kasih sudah berbagi. Izinkan kami COPAS ! Smoga sukses slalu yaaa !

    [Reply]

    ariefew Reply:

    silahkan mas

    [Reply]

  15. bapak harsri says:

    hey bajingan tolong di upload file excel tersebut

    terimakasih

    [Reply]

  16. RE Soedrajat says:

    kenapa tidak bisa

    [Reply]

  17. Logan says:

    Bunga Anuitas ya itu??

    [Reply]

Post a Comment


7 + 8 =