Taxed Income Function: Case Indonesia

Base on UU PPh pasal 21 in Indonesia, every working citizen have to pay income tax with progressive calculation tariff. The detail of this UU PPh will not discuss in here. What i will try to discuss is how to implemented it into sql server function dan store the tariff into tabel.

Even the progressive calculation tariff may stay for along time, it may possible that the rate may change or the placement. Real case is, the change of PPh 21 before 2008 change from 5 placement into 4 placement on 2009. You can imagine how you have to change all your calculation in your salary system if you use "fix code" method in your calculation system.

This is my idea to make the function and store the tariff into table. I Hope it will help you to efficient your job.

Here is some step you need to consider:
1. The function is for progressive tariff policy
2. There is a table to store the rate. it will be explain later.
3. The calculation is for Taxed Income, i will used PKP as the variable.

Firstly, we create the table:

CREATE TABLE [dbo].[Master_Tarif_Pajak](
[Tahun] [char](4) NOT NULL,
[Tingkat] [int] NOT NULL,
[Lower] [decimal](18, 0) NULL,
[Upper] [decimal](18, 0) NULL,
[Pajak] [decimal](5, 2) NULL,
CONSTRAINT [PK_Master_Tarif_Pajak] PRIMARY KEY CLUSTERED
(
[Tahun] ASC,
[Tingkat] ASC
)ON [PRIMARY]
) ON [PRIMARY]

The Requirement is :
Entry the record from the first placement to the fifth and the last one the upper we leave it Zero
Your data my looks like this :

Tahun Tingkat Lower Upper Pajak
2008 1 0 25000000 0.05
2008 2 25000000 50000000 0.10
2008 3 50000000 100000000 0.15
2008 4 100000000 200000000 0.25
2008 5 200000000 0 0.35
2009 1 0 50000000 0.05
2009 2 50000000 250000000 0.15
2009 3 250000000 500000000 0.25
2009 4 500000000 0 0.30

(9 row(s) affected)

Next step is the function :

/*
created : 29-08-2008 by wihendro

*/
create function dbo.UDF_Calculate_Tax
(
@pkp money , @tahun char(4)
)
returns money
as
begin
declare @tingkat int , @pajak money, @lower money, @upper money, @pajak1thn money
, @terhitung money, @denda money

set @tingkat = 1
set @pajak1thn = 0
set @terhitung = 0

while exists( select tingkat from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat
)
begin
select @lower = [lower], @upper = [upper] , @pajak = pajak
from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat


if @pkp > @upper
begin
if @upper = 0
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung)*@pajak)
break
end
else if @lower is not null
begin
if (@terhitung + ( @upper - @terhitung )) <= @pkp begin set @pajak1thn = @pajak1thn + ((@upper - @terhitung)*@pajak) set @terhitung = @terhitung + (@upper - @terhitung ) end else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
end
else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung) * @pajak )
break
end
set @tingkat = @tingkat + 1
set @lower = null
set @upper = null
end

return @pajak1thn
end

Example:

select Pajak = dbo.UDF_Calculate_Tax(200000000,'2008' )

Result :
Pajak
---------------------
36250000.00

(1 row(s) affected)

So, if next year, the role change into 4 placement ( see the sample data that i have created for you ), you only have to fill the tariff to the table and in your only have to send the value 2008 as the year. More effectively if you make an entry modul for the tariff table. So prepare your income tax calculation program to overcome the new UU Pph next year.

Note: You need to entry the new tariff every new begging year. Just don't forget to maintain the table ok.

Go to try it.
Good Luck.