Excelで表を作成する目的は様々だと思いますが、データテーブルという機能を使って簡単なシミュレーションができる、という事を知ったときは「こんなに便利な機能があるのか」と驚きました。
税務の仕事をしていると、「条件を変えることによって税額が変わる」ということがよくあり、条件をどう設定すれば最も税額が少なくなるかを確認したいと思うことが多いのですが、Excelのデータテーブルは条件を変えることによる計算の結果を表にしてくれる機能です。
その表を見れば、最も税額が少なくなる条件を確認することができます。
サンプルのExcelブックのテータテーブルは、列の値のみを入力するものです。色のついているセルが「列の代入セル」です。
条件はa-1、a-2のように数値でもb-1、b-2のように文字列でも可能です。
また、計算式はa-1、b-1のようにデータテーブルの中に書くこともできますし、a-2、b-2のように他の場所に書いた計算式をデータテーブルの中で参照することもできます。
これまで作成したデータテーブルによるシミュレーションで面白かったのは、
- 個人事業主が専従者給与を支払う場合における計算上最適な専従者給与の額
- ふるさと納税の効果の検証
の2つです。
専従者給与の額
個人事業主が専従者給与を支払うと、事業主の事業所得の一部が専従者の給与所得となり、専従者は給与所得控除を受けることができるので、結果として事業主と専従者の所得税と住民税の合計額を減少させることができます。
専従者給与の額をだんだん増やしていくと、所得が分散されることにより、最初は事業主と専従者の税の合計額が少なくなっていくのですが、専従者給与の額がある金額を超えると逆に税の合計額が増えるであろうことが何となく予測できるのですが、本当にそうなるのか、税の合計額が増え始めるときの金額はいくらになるのか、ということが知りたいと思ってシミュレーションの表を作成してみました。
以下のようなイメージの表です。
- 専従者給与が0円・・・税額計は 247,865円
- 1,200,000円・・・税額計は 121,504円
- 2,400,000円・・・専従者は住民税 5,500円のみ 事業者は所得税・住民税とも0円で税額計が最小になる
- 2,600,000円・・・専従者に住民税の所得割が発生する
- 3,000,000円・・・専従者に所得税が発生する
これ以降は専従者の所得税・住民税が増えていく一方になると思われます。
実際に専従者給与をいくらに設定するかは別途検討しなければならないとして、例えば1,200,000円に設定すれば税額の合計が半分になる、という効果があることが確認できます。
あらかじめ
- 専従者の所得税と住民税を計算するためのワークシート
- 事業者の所得税と住民税を計算するためのワークシート
をそれぞれ作成しておく必要があります。
個人事業者の場合、国民健康保険税の税額も所得金額によって変動しますので、本来は国民健康保険税も計算に含めると良いのですがそこまではできませんでした。
ふるさと納税の効果の検証
上の例と同じように所得税・住民税を計算するワークシートを使ったデータテーブルです。
この例で言うと寄付金の額が76,000円までは「寄付金額が0円の時との差額」が2,000円以内に収まっているのですが、寄付金の額が78,000円を超えるとその差額が2,000円を超えて、その後はだんだん増えていくことになりますのでおおよそ76,000円くらいが寄付金の限度額ということになります。
データテーブルの利点
もしもデータテーブルの機能がなかったら、条件を変えるたびにその計算結果を転記して表を作成しなければなりません。
また、データテーブルを利用すれば、条件となる金額の間隔を「1,000ずつ増やす」「5,000円ずつ増やす」「10,000円ずつ増やす」というように切り替えるのも簡単にできます。
