会社が、年末調整で各従業員の年調年税額を求める過程をエクセルの関数で行った場合どういう流れになるか・・・
前回で、給与所得控除後の給与等の計算までが終わりました。
今回は、最終的に従業員が支払う所得税である「年調年税額」を算出したいと思います。
所得控除
「給与所得控除」を行って算出された「給与等」の金額から、さらに、「所得控除」ができます。
よく見聞きする「所得控除」の種類としては、以下のようなものがあります。
- 基礎控除
- 社会保険料控除
- 扶養控除
- 配偶者控除
- 寡婦控除
- 生命保険料控除
- 住宅借入金等特別控除
ここでは、各所得控除の計算は割愛します。
控除できる金額を以下のように一覧にして、その合計を算出するにとどめます。
「基礎控除」と「社会保険料控除」の2つだけが控除できる場合、それらの数字を入力して、単純に「SUM関数」で足し算してみました。
課税給与所得金額
さて、それでは次に「課税給与所得金額」を算出してみましょう。
「課税給与所得金額」とは、実際に所得税が課せられる給与の金額、ということです。
今回、「支払い給与総額」が「2,139,500円」として計算を始めたわけですが、そこから「給与所得控除」を計算した結果、「1,315,200円」になり、前項の「所得控除として計算された470,000円」を差し引いて、「845,200円」となり、最終的に端数を切り捨てて、「845,000円」となったのです。
この「845,000円」が「課税給与所得金額」というわけですね。
給与総額は「2,139,500円」あるけれども、「845,000円」分だけ所得税の計算対象として考えるよ、ということになるのです。
それでは、計算の流れを見てみましょう。
セルC33には、セルC18の値を表示します。
つまり、「=C18」と入力します。
セルC34には、セルC29の値を表示します。
こちらは、「=C29」と入力します。
セルC35には、「=C33-C34」と引き算の式を入力します。
セルC36に表示したい「最終的な課税給与所得金額」は、セルC35で算出された「課税給与所得金額」の「1,000円未満を切り捨てる」ことで求められます。
数値の切り捨ては、おなじみの「ROUNDDOWN関数」を使うことになります。
「ROUNDDOWN関数」は、最初の引数に切り捨てする元の数値を指定し、2番目の引数にどの部分を切り捨てるかを数値で指定します。
つまり、「課税給与所得金額」を算出したセル「C35」を最初の引数に指定し、整数部分の切り捨ての場合、マイナスで数値を指定します。
この場合、1,000円未満の3桁を切り捨てるので「-3」となります。
「845,200円」の「200円」部分が切り捨てられ、「課税給与所得金額」は「845,000円」と計算された、というわけですね。
年調所得税額の算出
さて、「年調所得税額」の算出に移りますが、前回、「給与所得控除後の給与等の金額」でも触れた”あの数式”が再度登場します。
そう、「配列数式」ですね。
今回、計算する「年調所得税額」も、あらかじめ提示された数字を元に最終的に支払う所得税を計算する必要があります。
前項までで、所得税の計算対象となる給与金額は、「845,000円」と算出されました。
そして、上の図のように「年調所得税額」を計算するために与えられた表をエクセル上に作成しました。
セル「C41」に、「845,000円」に対する年調所得税額が自動計算されるように、数式を挿入してみましょう。
{=IF((C40>F41:F45)*(C40<=G41:G45),(C40*H41:H45)-I41:I45)}
{}は、実際に入力しません。
数式を入力し終わったら、キーボードの「Ctrlキー+Shiftキー」を押しながら「Enterキー」を押せば、「配列数式である」という目印となる{}が自動で挿入されます。
算出したセル「C40」の「課税給与所得金額」が、F列の数値を超え、なおかつG列の数値以下で該当する行があれば、その該当する行番号のH列の「税率」を掛けて、さらに該当する行番号のI列の「控除額」を差し引きます。
前回の配列数式の説明でも書きましたが、配列数式内で、「AND関数」と「OR」関数を使う事はできません。
したがって、論理積であるAND条件を作成するときは、(C40>F41:F45)*(C40<=G41:G45)のように、「*(掛け算)」で処理します。
では、実際の計算の流れを見てみましょう。
セル「C40」には、これまでの計算で算出された「845,000円」が表示されています。
「IF((C40>F41:F45)*(C40<=G41:G45)～」の部分を見ると、「845,000円」は表のF列では「-1を超える」に該当し、表のG列では「1,950,000以下」に該当します。
つまり、該当する行は、「41行目(表の1行目)」となります。
IF関数で該当したので、真の場合の計算式である(C40*H41:H45)-I41:I45)の部分が実行されます。
41行目のH列の税率は「5%」、I列の控除額は「0円」なので、「845,000円×5%－0円」という計算式となり、結果としてセル「C41」に、「42,250円」という答えが表示されます。
配列数式は、どうしても特殊な使い方になります。
このような表中において、条件に該当する行の計算をスッキリした数式で処理したい場合には、配列数式がある、ということを思い出してみてください。
年調年税額の算出
さぁ、いよいよ最後の「年調年税額」の計算となります。
この計算を終えると、従業員の方が支払う「年間の所得税」が算出されます。
この計算は、「年調所得税額」に「102.1%」を掛けるだけです。
会社員の場合、会社ですべて行ってくれるこの「年末調整」という作業での最後の「102.1%」が何を意味しているかは理解できないかもしれませんが、これは「東日本大震災」の復興特別所得税となっているのです。
先程セル「C41」に「年調所得税額」である「42,250円」が算出されました。
それをセル「C47」に「=C41」と数式を入力して表示します。
そして、セル「C48」に「年調年税額」を計算するようにします。
「年調年税額」は、「年調所得税額」に「102.1%」を掛けてそれを100円未満で切り捨てます。
つまり、セル「C48」に、「=ROUNDDOWN(C47*102.1%,-2)」と「ROUNDDOWN関数」を使って切り捨てします。
100円未満、つまり10円の位と1円の位の2つを切り捨てるので、「2番目の引数」に「-2」と指定します。
最後にまとめ
これまで全4回で作成してきた計算式がすべて完成すると、「支払い給与総額」が「2,139,500円」の従業員であれば、「年調年税額」は、「43,100円」と簡単に表示されるようになりました。
今回は、関数や数式をメインに順番に見てきましたが、システマチックにするのであれば、エクセルVBAを使ってもいいかもしれませんし、実際にエクセルVBAで作成した年末調整のためのファイルを公開している税理士も結構いらっしゃるようです。
個人で小さくやっている会社であってもエクセル1つあれば、年末調整だって関数だけでこれくらいまで簡素化して計算できるようになります。
計算途中で与えられた表の数値などは、その年度で変更になる場合もありますけどそういった場合は、表の数値などを少し修正するだけで済みますしね。
シリーズとなりだいぶ長くなりました。
年末調整に関わっている方が、今回のような関数や数式を参考にして、「楽しそうだから、私もマイツールを作成してみよう！」って少しでも思っていただければ、とっても嬉しいです！