Rumus Ms Excel untuk mempermudah penghitungan PPh

RUMUS MS EXCEL TARIF PASAL 17 WP OP/PPH 21

kopikan rumus ini pada cell tempat dimana anda akan menghitung PPh 21 (sel x19 adalah nilai PKP)

=IF(X19<=25000000,X19*5%,IF(X19<=50000000,25000000*5%+(X19-25000000)*10%,IF(X19<=100000000,25000000*5%+(50000000-25000000)*10%+(X19-50000000)*15%,IF(X19<=200000000,25000000*5%+(50000000-25000000)*10%+(100000000-50000000)*15%+(X19-100000000)*25%,25000000*5%+(50000000-25000000)*10%+(100000000-50000000)*15%+(200000000-100000000)*25%+(X19-200000000)*35%))))

Rumus tarif progresif di Module

apabila anda lebih ingin hasil yang lebih pro, buka vb editor excel (tekan Alt+F11) dan insert new module, dan kopikan rumus ini di script editornya.lalu simpan dan isikan rumus “=progresif(PKP) “(cell dimana nilai PKP ada) lalu enter.

Function progresif(pkp)
Select Case pkp
Case Is <= 25000000
progresif = pkp * (5 / 100)
Case Is <= 50000000
progresif = 1250000 + ((pkp – 25000000) * 10 / 100)
Case Is <= 100000000
progresif = 3750000 + ((pkp – 50000000) * 15 / 100)
Case Is <= 200000000
progresif = 11250000 + ((pkp – 100000000) * 25 / 100)
Case Is > 200000000
progresif = 36250000 + ((pkp – 200000000) * 35 / 100)
End Select
End Function

Rumus terbilang

Ini adalah rumus terbilang, cara nya sama dengan rumus progresif diatas. “=terbilang(nilai)”

Function Terbilang(Nilai)
If Nilai = “0″ Then
Terbilang = “- N I H I L -”
Else
Snil = Format(Str(Nilai), “000000000″)
JUTA = Mid(Snil, 1, 3)
RIBU = Mid(Snil, 4, 3)
SATU = Mid(Snil, 7, 3)

If JUTA = “000″ Then
JUT = “”
Else
UCAP = Ucapan(JUTA)
JUT = UCAP + “Juta “
End If

If RIBU = “000″ Then
RIB = “”
Else
UCAP = Ucapan(RIBU)
RIB = UCAP + “Ribu “
End If

If SATU = “000″ Then
SAT = “”
Else
UCAP = Ucapan(SATU)
SAT = UCAP
End If
Terbilang = “( ” + JUT + RIB + SAT + “Rupiah )”
End If
End Function

Function Ucapan(Bilang)
RATUSAN = Left(Bilang, 1)
PULUHAN = Mid(Bilang, 2, 1)
SATUAN = Right(Bilang, 1)

Select Case RATUSAN
Case “0″
SRATUS = “”
Case “”
SRATUS = “”
Case “1″
SRATUS = “Seratus “
Case “2″
SRATUS = “Dua Ratus “
Case “3″
SRATUS = “Tiga Ratus “
Case “4″
SRATUS = “Empat Ratus “
Case “5″
SRATUS = “Lima Ratus “
Case “6″
SRATUS = “Enam Ratus “
Case “7″
SRATUS = “Tujuh Ratus “
Case “8″
SRATUS = “Delapan Ratus “
Case “9″
SRATUS = “Sembilan Ratus “
End Select

Select Case PULUHAN
Case “0″
SPULUH = “”
Case “”
SPULUH = “”
Case “1″
SPULUH = “”
Case “2″
SPULUH = “Dua Puluh “
Case “3″
SPULUH = “Tiga Puluh “
Case “4″
SPULUH = “Empat Puluh “
Case “5″
SPULUH = “Lima Puluh “
Case “6″
SPULUH = “Enam Puluh “
Case “7″
SPULUH = “Tujuh Puluh “
Case “8″
SPULUH = “Delapan Puluh “
Case “9″
SPULUH = “Sembilan Puluh “
End Select

If PULUHAN = “1″ Then
Select Case SATUAN
Case “0″
SSATU = “Sepuluh “
Case “1″
SSATU = “Sebelas “
Case “2″
SSATU = “Dua Belas “
Case “3″
SSATU = “Tiga Belas “
Case “4″
SSATU = “Empat Belas “
Case “5″
SSATU = “Lima Belas “
Case “6″
SSATU = “Enam Belas “
Case “7″
SSATU = “Tujuh Belas “
Case “8″
SSATU = “Delapan Belas “
Case “9″
SSATU = “Sembilan Belas “
End Select

Else
Select Case SATUAN
Case “0″
SSATU = “”
Case “”
SSATU = “”
Case “1″
SSATU = “Satu “
Case “2″
SSATU = “Dua “
Case “3″
SSATU = “Tiga “
Case “4″
SSATU = “Empat “
Case “5″
SSATU = “Lima “
Case “6″
SSATU = “Enam “
Case “7″
SSATU = “Tujuh “
Case “8″
SSATU = “Delapan “
Case “9″
SSATU = “Sembilan “
End Select
End If
Ucapan = SRATUS + SPULUH + SSATU
End Function

About these ads

3 Tanggapan

  1. mas, macro excel seperti ini cara belajarnya gimana ya? Ada website atau milis khusunya gak?

    terima kasih

  2. ada tKSIR KE PULUH RIBUAN

  3. Wah lumayan nih dapat sesuatu yang baru…. Terima kasih infonya

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Logout / Ubah )

Twitter picture

You are commenting using your Twitter account. Logout / Ubah )

Facebook photo

You are commenting using your Facebook account. Logout / Ubah )

Google+ photo

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

Ikuti

Get every new post delivered to your Inbox.

%d bloggers like this: