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

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

My タックスノロジー

サラリーマン（給与所得者）の源泉徴収と年末調整

https://my-tax-nology.com/withholding-tax-and-year-end-adjustment-of-salaried-workers

今､会計事務所は色々と書類の提出期限が迫っている時期です（>

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

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

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

My タックスノロジー

Excel（エクセル）　源泉徴収税額表でVLOOKUP 関数を用いて源泉徴収税額を計算する...

https://my-tax-nology.com/how-to-calculate-withholding-tax-amount-using-vlookup-function-in-excel-withholding-tax-table

サラリーマンは毎月所得税を源泉徴収（給料から天引き）されています。源泉徴収税額は、源泉徴収される人の・社会保険料等控除後の給与等の金額・扶養親族等の数をもとに源泉徴収税額表を用いて計算します。源泉徴収税額表は、国税庁が発行するこのような↓紙の資料です。また､国税庁のHPでPDF版やExcel版をダウンロードすることもできます。平成29年分の月額表のExcel版をダウンロードして使います。ダウンロードした直後は、ファイルの拡張子が xls となっており、互換モードで開くので拡張子を xlsx にして保存し直します。今日は、...

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

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

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

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

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

1、賞与に対する源泉徴収税額の計算方法
2、賞与に対する源泉徴収税額を計算するシート設計
3、Excel VBA によるプログラミング
3、マクロ（Excel VBA）の動作確認
4、まとめ

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

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

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

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

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

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」としています。

Sub Gensen_Syouyo()
    
    Dim Zen_Kyuyo      '前月の給与等の総支給額（円）'
    Dim Zen_Syaho      '前月の社会保険料（円）'
    Dim Syoyo          '賞与の総支給額（円）'
    Dim Syoyo_Syaho    '賞与から控除される社会保険料（円）'
    Dim Fuyou          '扶養親族等の数（人）'
    Dim Syoyo_Ritsu    '賞与の金額に乗ずべき率'
    Dim Gensen         '源泉徴収税額（円）'
    
    Dim Zen_Kyuyo_1    '前月の社会保険料控除後の給与等の金額'
    Dim Syoyo_1        '社会保険料控除後の賞与の金額'
    
    Dim Kyuyo_Min      '範囲の下側の値　〇〇未満'
    Dim Kyuyo_Max      '範囲の上側の値　〇〇以上'
    
    Dim Min_Retsu      '範囲の下限が入った列番号'
    Dim Max_Retsu      '範囲の上限が入った列番号'
    
    
    Worksheets("操作画面").Activate     '「操作画面シート」をアクティブにする'
    
    
    Zen_Kyuyo = Cells(6, 4)        'セル「D6」'
    Zen_Syaho = Cells(7, 4)        'セル「D7」'
    Syoyo = Cells(8, 4)            'セル「D8」'
    Syoyo_Syaho = Cells(9, 4)      'セル「D9」'
    Fuyou = Cells(10, 4)           'セル「D10」'
    
    Zen_Kyuyo_1 = Zen_Kyuyo - Zen_Syaho     '前月の給与等の総支給額－前月の社会保険料'
    Syoyo_1 = Syoyo - Syoyo_Syaho           '前月の社会保険料控除後の給与等の金額－社会保険料控除後の賞与の金額'
        
    
    Select Case Fuyou   '扶養親族等の数は1～7の間で選択'
        
        Case 0   '扶養親族等の数　0人の場合'
            Min_Retsu = 4
            Max_Retsu = 5
            Kyuyo_Min = 68000
            Kyuyo_Max = 3548000
            
        Case 1   '扶養親族等の数　1人の場合'
            Min_Retsu = 6
            Max_Retsu = 7
            Kyuyo_Min = 94000
            Kyuyo_Max = 3580000
            
        Case 2   '扶養親族等の数　2人の場合'
            Min_Retsu = 8
            Max_Retsu = 9
            Kyuyo_Min = 133000
            Kyuyo_Max = 3611000
            
        Case 3   '扶養親族等の数　3人の場合'
            Min_Retsu = 10
            Max_Retsu = 11
            Kyuyo_Min = 171000
            Kyuyo_Max = 3643000
            
        Case 4   '扶養親族等の数　4人の場合'
            Min_Retsu = 12
            Max_Retsu = 13
            Kyuyo_Min = 210000
            Kyuyo_Max = 3675000
            
        Case 5   '扶養親族等の数　5人の場合'
            Min_Retsu = 14
            Max_Retsu = 15
            Kyuyo_Min = 243000
            Kyuyo_Max = 3706000
            
        Case 6   '扶養親族等の数　6人の場合'
            Min_Retsu = 16
            Max_Retsu = 17
            Kyuyo_Min = 275000
            Kyuyo_Max = 3738000
            
        Case Is >= 7   '扶養親族等の数　7人以上の場合'
            Min_Retsu = 18
            Max_Retsu = 19
            Kyuyo_Min = 308000
            Kyuyo_Max = 3770000
            
        Case Else
            
    End Select
    
    
    Worksheets("賞与").Activate     '「賞与シート」をアクティブにする'
    
    
    '「前月の社会保険料控除後の給与等の金額」が範囲の下側の値を下回る場合'
    If Zen_Kyuyo_1 < Kyuyo_Min Then
        Syoyo_Ritsu = 0
    Else
    End If
    
    
    '「前月の社会保険料控除後の給与等の金額」が範囲の上側の値以上の場合'
    If Zen_Kyuyo_1 >= Kyuyo_Max Then
        Syoyo_Ritsu = 0.45945
    Else
    End If
    
    
    '「前月の社会保険料控除後の給与等の金額」が通常の範囲にある場合'
    For i = 10 To 34
    
        If Zen_Kyuyo_1 >= Cells(i, Min_Retsu) * 1000 And Zen_Kyuyo_1 < Cells(i, Max_Retsu) * 1000 Then
            Syoyo_Ritsu = Cells(i, 2) / 100     '賞与の金額に乗ずべき率は B列（2列目）に並べられている'
            Exit For
        Else
        End If
    
    Next i
    
    Gensen = Int(Syoyo_1 * Syoyo_Ritsu)     '源泉徴収税額の計算結果、円未満は切り捨て'
    
    
    Worksheets("操作画面").Activate     '「操作画面シート」をアクティブにする'
    
    
    Cells(11, 4) = Syoyo_Ritsu
    Cells(12, 4) = Gensen
    
End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

Sub Gensen_Syouyo()

Dim Zen_Kyuyo '前月の給与等の総支給額（円）'

Dim Zen_Syaho '前月の社会保険料（円）'

Dim Syoyo '賞与の総支給額（円）'

Dim Syoyo_Syaho '賞与から控除される社会保険料（円）'

Dim Fuyou '扶養親族等の数（人）'

Dim Syoyo_Ritsu '賞与の金額に乗ずべき率'

Dim Gensen '源泉徴収税額（円）'

Dim Zen_Kyuyo_1 '前月の社会保険料控除後の給与等の金額'

Dim Syoyo_1 '社会保険料控除後の賞与の金額'

Dim Kyuyo_Min '範囲の下側の値　〇〇未満'

Dim Kyuyo_Max '範囲の上側の値　〇〇以上'

Dim Min_Retsu '範囲の下限が入った列番号'

Dim Max_Retsu '範囲の上限が入った列番号'

Worksheets("操作画面").Activate '「操作画面シート」をアクティブにする'

Zen_Kyuyo = Cells(6, 4) 'セル「D6」'

Zen_Syaho = Cells(7, 4) 'セル「D7」'

Syoyo = Cells(8, 4) 'セル「D8」'

Syoyo_Syaho = Cells(9, 4) 'セル「D9」'

Fuyou = Cells(10, 4) 'セル「D10」'

Zen_Kyuyo_1 = Zen_Kyuyo - Zen_Syaho '前月の給与等の総支給額－前月の社会保険料'

Syoyo_1 = Syoyo - Syoyo_Syaho '前月の社会保険料控除後の給与等の金額－社会保険料控除後の賞与の金額'

Select Case Fuyou '扶養親族等の数は1～7の間で選択'

Case 0 '扶養親族等の数　0人の場合'

Min_Retsu = 4

Max_Retsu = 5

Kyuyo_Min = 68000

Kyuyo_Max = 3548000

Case 1 '扶養親族等の数　1人の場合'

Min_Retsu = 6

Max_Retsu = 7

Kyuyo_Min = 94000

Kyuyo_Max = 3580000

Case 2 '扶養親族等の数　2人の場合'

Min_Retsu = 8

Max_Retsu = 9

Kyuyo_Min = 133000

Kyuyo_Max = 3611000

Case 3 '扶養親族等の数　3人の場合'

Min_Retsu = 10

Max_Retsu = 11

Kyuyo_Min = 171000

Kyuyo_Max = 3643000

Case 4 '扶養親族等の数　4人の場合'

Min_Retsu = 12

Max_Retsu = 13

Kyuyo_Min = 210000

Kyuyo_Max = 3675000

Case 5 '扶養親族等の数　5人の場合'

Min_Retsu = 14

Max_Retsu = 15

Kyuyo_Min = 243000

Kyuyo_Max = 3706000

Case 6 '扶養親族等の数　6人の場合'

Min_Retsu = 16

Max_Retsu = 17

Kyuyo_Min = 275000

Kyuyo_Max = 3738000

Case Is >= 7 '扶養親族等の数　7人以上の場合'

Min_Retsu = 18

Max_Retsu = 19

Kyuyo_Min = 308000

Kyuyo_Max = 3770000

Case Else

End Select

Worksheets("賞与").Activate '「賞与シート」をアクティブにする'

'「前月の社会保険料控除後の給与等の金額」が範囲の下側の値を下回る場合'

If Zen_Kyuyo_1 < Kyuyo_Min Then

Syoyo_Ritsu = 0

Else

End If

'「前月の社会保険料控除後の給与等の金額」が範囲の上側の値以上の場合'

If Zen_Kyuyo_1 >= Kyuyo_Max Then

Syoyo_Ritsu = 0.45945

Else

End If

'「前月の社会保険料控除後の給与等の金額」が通常の範囲にある場合'

For i = 10 To 34

If Zen_Kyuyo_1 >= Cells(i, Min_Retsu) * 1000 And Zen_Kyuyo_1 < Cells(i, Max_Retsu) * 1000 Then

Syoyo_Ritsu = Cells(i, 2) / 100 '賞与の金額に乗ずべき率は B列（2列目）に並べられている'

Exit For

Else

End If

Next i

Gensen = Int(Syoyo_1 * Syoyo_Ritsu) '源泉徴収税額の計算結果、円未満は切り捨て'

Worksheets("操作画面").Activate '「操作画面シート」をアクティブにする'

Cells(11, 4) = Syoyo_Ritsu

Cells(12, 4) = Gensen

End Sub

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

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）のコードについて詳しく説明しました↓

My タックスノロジー

マクロ（Excel VBA）を使って源泉徴収税額表から「賞与に対する源泉徴収税額」を計...

https://my-tax-nology.com/the-way-of-calculating-withholding-tax-on-bonus-by-excel-vba-part-2

国税庁の HP の「平成29年分　源泉徴収税額表」のページから「賞与に対する源泉徴収税額の算出率の表」の Excel ファイルをダウンロードすることができます。このダウンロードした Excelファイルを改良して、マクロ（Excel VBA）を使った「賞与に対する源泉徴収税額」の計算方法をこの記事の中で説明しました。さて、上記記事では VBA のコードを全て示しましたが、「コード内のどこでどういう処理をしているか」ということについての説明まではできなかったので、今日はそのあたりについて書きたいと思います。先回の記事と重複する...

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

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