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

credit : primarycons.wordpress.com