84home  →86

85 2009年12月18日

年末調整用エクセル関数を作る

2012年12月11日改定版です。
平成25年1月1日から平成49年12月31日まで、復興特別所得税が加算されることとなったので、それに対応して、一部書き換えました。


2年ほど前の「さみだれ雑記78新定率法用エクセル関数を作る」が、当サイトでダントツアクセス一位だったりします。
う〜ん、そういうもんか、と今回年末調整用に作ったエクセル関数をアップしようか、と。


所得税源泉徴収簿 ってありますよね。それの

Hの「給与所得控除後の給与等の金額」。これ、平成21年分の「年末調整のしかた」では65ページから73ページ、全8ページの

平成21年分の年末調整のための給与所得控除後の給与等の金額の表

ってとこから探すわけですな。

まあ、私んとこは、今や新規に立ち上げることが出来ない由緒正しい(?)有限会社でありまして、しかも役員ばかりの100%同族会社でもあり、で、人数少ないです。で、まあたいした手間でもないんですが、まあ、いっちょ作ろうか、と。




 ♪





考え方は、平成21年分の「年末調整のしかた」の63ページ、「VI 電子計算機等による年末調整」に纏めてあります。(国税庁のページ

1)年調給与額を算出し、それを元に2)給与所得控除後の給与等の金額を出す、という2段構えです。




If 給与総額 < 1619000 Then

    年調給与額 = 給与総額

ElseIf 給与総額 < 1620000 Then

    年調給与額 = 給与総額 - (給与総額 - 1619000) Mod 1000

・・・こんな感じで続ければOK。


余りを出す関数modはワークシート上では 「 =mod(数値, 除数) 」のように使うのですが、VBAでは上のように「 数値 Mod 除数 」と並べます。





これも同じようにifで場合わけをしていけば、終わり。

注の2の端数切捨て、VBAには切り捨て関数はないようです。その場合、エクセルのワークシート関数を呼び出して使えます。

 控除後の給与額 = Application.RoundDown(年調給与額 * 0.9 - 1200000, 0)

こんな感じです。

完成版は以下のようです。「給与所得控除後の給与等の金額」は余りにも長いので「控除後の給与額」としました。



Function 控除後の給与額(給与総額 As Long) As Long


Dim 年調給与額 As Long

If 給与総額 < 1619000 Then

    年調給与額 = 給与総額

ElseIf 給与総額 < 1620000 Then

    年調給与額 = 給与総額 - (給与総額 - 1619000) Mod 1000
   
ElseIf 給与総額 < 1624000 Then

    年調給与額 = 給与総額 - (給与総額 - 1620000) Mod 2000

ElseIf 給与総額 < 6600000 Then

    年調給与額 = 給与総額 - (給与総額 - 1624000) Mod 4000

Else

    年調給与額 = 給与総額

End If


If 年調給与額 < 651000 Then

控除後の給与額 = 0

ElseIf 年調給与額 < 1619000 Then

控除後の給与額 = 年調給与額 - 650000

ElseIf 年調給与額 < 1620000 Then

控除後の給与額 = 年調給与額 * 0.6 - 2400

ElseIf 年調給与額 < 1622000 Then

控除後の給与額 = 年調給与額 * 0.6 - 2000

ElseIf 年調給与額 < 1624000 Then

控除後の給与額 = 年調給与額 * 0.6 - 1200


ElseIf 年調給与額 < 1628000 Then

控除後の給与額 = 年調給与額 * 0.6 - 400


ElseIf 年調給与額 < 1800000 Then

控除後の給与額 = 年調給与額 * 0.6


ElseIf 年調給与額 < 3600000 Then

控除後の給与額 = 年調給与額 * 0.7 - 180000


ElseIf 年調給与額 < 6600000 Then

控除後の給与額 = 年調給与額 * 0.8 - 540000

ElseIf 年調給与額 < 10000000 Then

控除後の給与額 = Application.RoundDown(年調給与額 * 0.9 - 1200000, 0)


ElseIf 年調給与額 <= 20000000 Then

控除後の給与額 = Application.RoundDown(年調給与額 * 0.95 - 1700000, 0)

Else

控除後の給与額 = 0

End If

End Function


2000万円を超えるものはは0表示にしました。



 ♪


次に、所得税源泉徴収簿の QRの部分です。






Qの1000円未満切捨ては、=ROUNDDOWN(W25-W34,-3) でOK。

Rの算出年税額を計算します。





この程度ならワークシート上にif文でもできます。

=IF(W37<=1950000,W37*5%,IF(W37<=3300000,W37*10%-97500,IF(W37<=6950000,W37*20%-427500,IF(W37<=9000000,W37*23%-63600,IF(W37<=16920000,W37*33%-1536000)))))

こんな感じ。(関数の入れ子(ネスト)は7までです。)


自作関数なら: 
 


Function 算出年税額(課税給与所得金額 As Long) As Long

If 課税給与所得金額 <= 1950000 Then

    算出年税額 = 課税給与所得金額 * 0.05

ElseIf 課税給与所得金額 <= 3300000 Then

    算出年税額 = 課税給与所得金額 * 0.1 - 97500
   
ElseIf 課税給与所得金額 <= 6950000 Then

    算出年税額 = 課税給与所得金額 * 0.2 - 427500

ElseIf 課税給与所得金額 <= 9000000 Then

    算出年税額 = 課税給与所得金額 * 0.23 - 636000

ElseIf 課税給与所得金額 <= 16920000 Then

    算出年税額 = 課税給与所得金額 * 0.33 - 1536000

Else: 課税給与所得金額 = 0

End If

End Function



ワークシートで複雑な if文作るより、簡単では?

といういとで、いっちょ毎月の源泉徴収額を返す関数も作成しちゃいましょう!


 ♪




国税庁の「平成25年分 源泉徴収税額表」(復興特別所得税が含まれたもの)の20ページに「月額表の甲欄を適用する給与等に対する源泉徴収税額の電算機計算の特例」というのがあります。(国税庁のページ









ということで、以下のようになります。

別表第二を、配偶者控除ありなら1+扶養親族の数を計算し、配偶者扶養人数として代入します。基礎控除は必ずあるのでプログラム上で追加しています。


Function 源泉徴収額(控除後の給与額 As Long, 配偶者扶養人数 As Long) As Long


Dim 給与所得控除の額 As Long
Dim 課税給与所得金額 As Long

If 控除後の給与額 < 135417 Then

給与所得控除の額 = 54167

ElseIf 控除後の給与額 < 150000 Then

給与所得控除の額 = 控除後の給与額 * 0.4


ElseIf 控除後の給与額 < 300000 Then

給与所得控除の額 = 控除後の給与額 * 0.3 + 15000

ElseIf 控除後の給与額 < 550000 Then

給与所得控除の額 = 控除後の給与額 * 0.2 + 45000

ElseIf 控除後の給与額 < 833334 Then

給与所得控除の額 = 控除後の給与額 * 0.1 + 100000

ElseIf 控除後の給与額 < 1250000 Then

給与所得控除の額 = 控除後の給与額 * 0.05 + 141667

Else

給与所得控除の額 = 204167

End If

給与所得控除の額 = Application.RoundUp(給与所得控除の額, 0)

 

'基礎控除として1を加える

課税給与所得金額 = 控除後の給与額 - 給与所得控除の額 - (配偶者扶養人数 + 1) * 31667

 

If 課税給与所得金額 < 0 Then

源泉徴収額 = 0

ElseIf 課税給与所得金額 < 162501 Then

源泉徴収額 = 課税給与所得金額 * 0.05105

ElseIf 課税給与所得金額 < 275001 Then

源泉徴収額 = 課税給与所得金額 * 0.1021 - 8296

ElseIf 課税給与所得金額 < 579167 Then

源泉徴収額 = 課税給与所得金額 * 0.2042 - 36374

ElseIf 課税給与所得金額 < 750001 Then

源泉徴収額 = 課税給与所得金額 * 0.23483 - 54113

ElseIf 課税給与所得金額 < 1500001 Then

源泉徴収額 = 課税給与所得金額 * 0.33693 - 130688

Else

源泉徴収額 = 課税給与所得金額 * 0.4084 - 237893

End If

源泉徴収額 = Application.WorksheetFunction.Round(源泉徴収額, -1)

End Function



一応見本として、gensen.zipダウンロード可能です。解凍すると源泉徴収(H25年以降).xlsになりますので、ご自由に試してください。エクセル2002で作成。他のヴァージョンでの動作確認はしていません。サポート等一切しません。自己責任でどうぞ。

私が現に使っているものは、所得税源泉徴収簿・給与所得の源泉徴収票などともリンクして使い勝手がいいのですが、ネット上でいただいた他の方のシートをカスタマイズして利用させていただいています。それをそのままアップするのは、道義的に問題だと思いますので、残念ながら実用性には乏しいほんの見本版です。あしからずご了承ください。



 ♪




エクセルの自作関数って言っても、if文ですむようなものなら、簡単です。

ついでに税金つながりで相続税






Function 相続税(cell1 As Currency) As Currency

If cell1 <= 0 Then
    相続税 = 0

ElseIf cell1 <= 10000000 Then
    相続税 = cell1 * 0.1

ElseIf cell1 <= 30000000 Then
    相続税 = cell1 * 0.15 - 500000

ElseIf cell1 <= 50000000 Then
    相続税 = cell1 * 0.2 - 2000000

ElseIf cell1 <= 100000000 Then
    相続税 = cell1 * 0.3 - 7000000

ElseIf cell1 <= 300000000 Then
    相続税 = cell1 * 0.4 - 17000000

Else

    相続税 = cell1 * 0.5 - 47000000

End If

End Function



ちょっと注意がいるのは、他の関数は

Function 算出年税額(課税給与所得金額 As Long) As Long などとlongを使いましたが、これは
Function 相続税(cell1 As Currency) As Currency       とcurrencyを使います。


データ型といって、定義せずに使ってもいいのですが、データ型を定義することによって、メモリ消費が抑えられ、VBAがデータ型を判別する手間を防ぐことでパフォーマンスも向上する、んだそうです。

で、まあ私としては一応律儀に定義しています。

数値を扱うデータ型には

integer     -32,768〜32,767までの小数点を含まない整数
long        -2,147,483,648〜2,147,483,647までの整数

とかありますが、相続税となると3億超え、とか・・・。long じゃ足らないリッチな方も世の中にはいらっしゃるようなので。

currency  -922,337,203,685,477.5808〜922,337,203,685,477.5807までの数値

の登場とあいなる次第です。ああ。

まあ、if文をメインにした自作関数は、ワークシート上で複雑な入れ子するより、頭こんがらがらなくて楽な面もありますので、皆様、ぜひ自力でご自分に必要な関数を作りましょう!

では良いお年を。


84home  →86