スポンサーリンク

マクロ(Excel VBA)を使って源泉徴収税額表から「賞与に対する源泉徴収税額」を計算する 〜その1〜


法人や個人事業主などの源泉徴収義務者は、従業員への給料の支払いの際に所得税を源泉徴収して、翌月10日までに税務署(国)に納付しなければなりません。

なお、従業員の数が常時10人未満の場合は「納期特例」の届出をすることにより、7月10日と1月20日の年に2回、それぞれ半年分の源泉所得税をまとめて納付するようにすることができます。
今年ももうすぐ、7月10日(月)ですね。

さて、月々の給料から差し引かれる源泉徴収税額は、「給与所得の源泉徴収税額表(月額表)」から金額を計算することができます。

以前に、Excel で Vlookup 関数を用いて源泉徴収税額を計算する方法を説明しました。

源泉徴収義務者は月々の給料だけではなく、賞与を支給する際にも源泉徴収をすることになりますが、その場合における源泉徴収税額も、計算方法が定められています。

国税庁の HP の「平成29年分 源泉徴収税額表」のページから「 賞与に対する源泉徴収税額の算出率の表」の Excel ファイルをダウンロードすることができます。

今日は、ダウンロードした「 賞与に対する源泉徴収税額の算出率の表」の Excel ファイルを改良して、マクロ(Excel VBA)を使った「賞与に対する源泉徴収税額」の計算方法を説明したいと思います。

なお、源泉徴収税額表の見方については Youtube に動画もアップしておりますので、よろしければどうぞ!

チャンネル登録は、こちらからお願い致します。

スポンサーリンク

1、賞与に対する源泉徴収税額の計算方法

「賞与に対する源泉徴収」については国税庁の HP で確認することができます。

通常の場合の計算方法は次のように示されています↓
————————————-
(1)前月の給与から社会保険料等を差し引きます。
(2)上記(1)の金額と扶養親族等の数を「賞与に対する源泉徴収税額の算出率の表」に当てはめて税率(賞与の金額に乗ずべき率)を求めます。
(3)(賞与から社会保険料等を差し引いた金額) × 上記(2)の税率

この(3)の金額が、賞与から源泉徴収する税額になります。
————————————-

上記の流れを示しますと、まず「賞与の金額に乗ずべき率」を「賞与に対する源泉徴収税額の算出率の表」から次のXYを基に引っ張り出します(扶養控除等申告書の提出がある従業員の場合、甲欄を用います )。

X、扶養親族等の数
Y、前月の社会保険料控除後の給与等の金額


賞与に対する源泉徴収税額 = 社会保険料控除後の賞与の金額 × 賞与の金額に乗ずべき率

この一連の流れをマクロ(Excel VBA)を使って行いたいと思います。

2、賞与に対する源泉徴収税額を計算するシート設計

「賞与に対する源泉徴収税額の算出率の表」の Excel ファイルをダウンロード

まず、国税庁の HP の「平成29年分 源泉徴収税額表」のページから「賞与に対する源泉徴収税額の算出率の表」の Excel ファイルをダウンロードします。

ダウンロードすると Excel ファイルが「.xls」の拡張子で開くので、メニューバーの「ファイル」から「名前を付けて保存」を選択して、マクロを使うことができる拡張子「.xlsm」を選択して保存します。

なお、シートが「改プレビュー」(印刷した際に表示される部分を分かりやすくする設定)となっているので、メニューバーの「表示」から「標準」を選択して普通の表示にしましょう。

その方が見やすいと思います(そのままでも後の計算には影響ありませんので、お好みでということで)。

操作画面の作成

次に、計算のための「操作画面」を作成します。

シートを新たに挿入して名前を「操作画面」とします。

この状態で、このファイルの中には「賞与シート」と「操作画面シート」の2つが存在することになります。

次に「操作画面シート」の中を作ります。

扶養控除等申告書の提出がある従業員の場合、「賞与に対する源泉徴収税額の算出率の表」の甲欄を用います。

「賞与に対する源泉徴収税額」を計算する上で必要な入力値として、以下の5つがあります。

a、前月の給与等の総支給額(円)
b、前月の社会保険料(円)
c、賞与の総支給額(円)
d、賞与から控除される社会保険料(円)
e、扶養親族等の数(人)

上記のa、b、cの入力値を基に「賞与に対する源泉徴収税額の算出率の表」から

f、賞与の金額に乗ずべき率

を引っ張り出した後、c、d.fを基に「源泉徴収税額(円)」を計算します。

以下のように、入力が必要な部分を色付けした表にしました↓

これに、さらにマクロ(Excel VBA)を実行するボタンを設置します。

メニューバーの「挿入」から「図形」で四角を選択して、適当なクリックしやすい位置に配置しておきましょう。

四角形の色は水色にして、テキスト(図形を選択した状態でキーボード入力)で「実行」と書いておきます。

「実行」ボタンにはマクロを埋め込みます。
VBA のコードを作成した後にやるべき設定になりますが、やり方を先に説明しておきまます。

図形(四角形)を選択して、「右クリック」⇒「マクロの登録」で埋め込むマクロを選択します。
(選択すべきマクロ名が「Gensen_Syouyo」の場合、それを選択して OK を押します)

5つの入力値を入力した後に「実行」ボタンをクリックすればマクロが実行するシート設計 とします。

データの入力規制

さて、次に5つの入力値の入力規制を設定します。

【a、前月の給与等の総支給額(円) c、賞与の総支給額(円)】

セル「D6」と「D8」を同時に選択(Ctrlキーを押しながらクリック)して、メニューバーの「データ」から「データの入力規制」を選択します。

a、前月の給与等の総支給額(円) c、賞与の総支給額(円)両方とも0より大きい整数でなければなりません。

「設定」を選択して、

・「入力値の種類」を「整数」
・「データ」を「次の値より大きい」
・「最小値」を「0」

として、0より大きい整数に設定します。

また、「エラーメッセージ」を選択して「スタイル」を「停止」にしておきます。
こうすることで、誤入力(文字を入力してしまうなど)した際には訂正するまで入力を完了できなくなります。

「日本語入力」を選択して「オフ(英語モード)」を選択しておきます。
こうすることで、このセルの上を選択した際には「ひらがな入力」が「直接入力」に切り替わります。

【b、前月の社会保険料(円)】

次に、前月の社会保険料の入力規制を設定について説明します。

b、前月の社会保険料(円)は a、前月の給与等の総支給額(円)よりも小さい金額でなければなりません。

セル「D7」のデータの入力規制として、セル「D6」未満の整数に設定します。

「エラーメッセージ」は、先ほどと同様に「スタイル」を「停止」にしておき、「日本語入力」は「オフ(英語モード)」を選択しておきます。

【d、賞与から控除される社会保険料(円)】

同様に、d、賞与から控除される社会保険料(円)は c、賞与の総支給額(円) よりも小さい金額でなければなりません。

セル「D9」のデータの入力規制として、セル「D8」未満の整数に設定します。

「エラーメッセージ」は、先ほどと同様に「スタイル」を「停止」にしておき、「日本語入力」は「オフ(英語モード)」を選択しておきます。

【e、扶養親族等の数(人)】

扶養親族等の数(セル「D10」)は、0以上の整数に設定します。

「エラーメッセージ」は、先ほどと同様に「スタイル」を「停止」にしておきおき、「日本語入力」は「オフ(英語モード)」を選択しておきます。

カンマ、小数点以下の桁数

なお、入力値、出力値ともにほとんどが金額なので3桁ごとにカンマで区切って表示させるようにしておきましょう。
セル「D6~D12」を選択した状態で、ショートカットキーで「Ctrl+Shift+1」を押せば3桁ごとのカンマ区切りで表示されます。

また、セル「D11」に「賞与の金額に乗ずべき率」を出力させますが、%(0~100)を割合(0~1)に変換する(100で割る)ので、小数点以下も表示させたいです。

後でマクロの動作確認の際にやるべきことなので(何も出力されていない状態だとやれない)少し順番が前後しますが、先に説明しておきます。

セル「D11」を選択した状態で、メニューバーの「ホーム」から以下のアイコンをクリックして桁数を調整します。

計算結果の表示を見ながら、小数点以下第6位くらいまで表示するようにしましょう。

3、Excel VBA によるプログラミング

さて、ここまででシート設計は完了しました。いよいよ、VBAによるプログラミングです。

VBEを開いて(Atl + F11)、標準モジュールを挿入しコードを書き込みます。

さて、源泉徴収税額(円)を計算するコードの全てを以下に示します。

マクロ名は「Gensen_Syouyo」としています。

コードの詳細な説明についても書ければ書きたかったのですが、この記事に書くと量が多くなるので別記事にしたいと思います。

3、マクロ(Excel VBA)の動作確認

上記のマクロの動作確認をしてみます。

何条件か試してみましょう。

条件1

a、前月の給与等の総支給額(円) 60,000
b、前月の社会保険料(円) 0
c、賞与の総支給額(円) 120,000
d、賞与から控除される社会保険料(円) 0
e、扶養親族等の数(人) 0

この条件で実行すると以下のようになります。

扶養親族等の数が0人で、「前月の社会保険料控除後の給与等の金額」が 60,000(=60,000ー0 )の場合は

●「賞与の金額に乗ずべき率」は 0(0%)
●「源泉徴収税額(円)」は 0(=(120,000ー0) × 0)

となるので妥当です。

賞与の金額に乗ずべき率の抽出↓

条件2

a、前月の給与等の総支給額(円) 425,000
b、前月の社会保険料(円) 59,500
c、賞与の総支給額(円) 1,275,000
d、賞与から控除される社会保険料(円) 178,500
e、扶養親族等の数(人) 4

この条件で実行すると以下のようになります。

扶養親族等の数が4人で、「前月の社会保険料控除後の給与等の金額」が 365,500(=425,000ー59,500 )の場合は

●「賞与の金額に乗ずべき率」は 0.04084(4.084 %)
●「源泉徴収税額(円)」は 44,781(=(1,275,000ー178,500) × 0.04084 )

となるので妥当です。

賞与の金額に乗ずべき率の抽出↓

条件3

a、前月の給与等の総支給額(円) 2,500,000
b、前月の社会保険料(円) 350,000
c、賞与の総支給額(円) 5,000,000
d、賞与から控除される社会保険料(円) 700,000
e、扶養親族等の数(人) 7

この条件で実行すると以下のようになります。

扶養親族等の数が7人で、「前月の社会保険料控除後の給与等の金額」が 2,150,000(=2,500,000 ー350,000 )の場合は

●「賞与の金額に乗ずべき率」は 0.38798(38.798 %)
●「源泉徴収税額(円)」は 1,668,314(=(5,000,000ー700,000) × 0.38798 )

となるので妥当です。

賞与の金額に乗ずべき率の抽出↓

4、まとめ

マクロ(Excel VBA)を使って源泉徴収税額表から「賞与に対する源泉徴収税額」を計算する方法について、説明しました。

VBA のコードについては、別記事で詳細説明をしたいと思います。

源泉徴収税額表は紙の資料もあります(これを使っている人の方が多いのかも分かりません)。

目で数値を追って「賞与の金額に乗ずべき率」を拾い出して、電卓を使って「賞与に対する源泉徴収税額」を計算することもできますが、数値がたくさん並んでいるので見ていて疲れますし、読み間違えなどのミスが発生する可能性も否めません。

VBA を使った場合。正しいコードを一度作りさえすれば、以後同じ計算をさせる際には「入力値を入れて、実行ボタンを押す」だけで計算結果が出てきます。

今回のようなケースで積極的に使うことをお勧めします。

なお、続きの記事で上に示したマクロ(Excel VBA)のコードについて詳しく説明しました↓

なお、源泉徴収税額表の見方については Youtube に動画もアップしておりますので、よろしければどうぞ!

チャンネル登録は、こちらからお願い致します。