所得拡大促進税制の計算をExcelピボットテーブルで効率化

所得拡大促進税制の計算を、Excelで効率よくやるなら、現状はこうやっています。
所得拡大促進税制 Excel

所得拡大促進税制Excelに必要なデータ

所得拡大促進税制は、
・今年度の給与支給額が基準年度(※)より2%アップ(平成27年4月1日以降に開始する事業年度は3%。平成28年4月1日以降に開始する事業年度より後は5%)
・今年度の給与支給額が前年度からのアップ
・今年度の平均給与額(継続雇用者の給与支給額/継続雇用者の延べ給与支給者数)が前年度からアップ
という条件を満たせば、

最大で、基準年度からの給与支給額アップ分×10%が控除される

という制度です。

※基準年度とは、平成25年4月1日以降に開始する事業年度の前事業年度をいいます。
3月決算なら、原則として平成25年3月期です。

※給与支給額に含めない
→役員・その親族、定年退職後の継続雇用制度利用者

詳しくはこちらの記事に書いています。
【関連記事】雇用促進税制・所得拡大促進税制。人を雇った場合の2大節税入門 | EX-IT
リンク

今回の事例は、
・給与支給者すべてが雇用保険に加入(平均給与額の計算に使用)
・定年後の継続雇用制度利用者はいない
・社員に役員の親族なし
・平成27年(2015年)3月決算→基準年度は、平成25年3月期
という条件で考えました。
経過措置も考慮していません。

必要なデータは、次の通りです。
・基準年度(平成25年3月期)の給与・賞与(役員を除く)の総額
・前年度(平成26年3月期)の給与・賞与(役員を除く)データ
・今年度(平成27年3月期)の給与・賞与(役員を除く)データ

基準年度は総額を使うので、金額があれば大丈夫です。
前年度、今年度は、継続雇用者(前年度・今年度で継続して雇用されている者)のチェックに使うため、給与データが必要となります。


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


給与データをピボットテーブルで計算

給与データは、次のように、氏名、区分(給与か賞与か)、対象(役員か社員か)、支給額、年月、期といった項目を作ります。
スクリーンショット 2015 05 08 8 58 42

今回は、社員に、役員の親族がいない設定ですが、もしいる場合は、別途対象から外さなければいけません。
給与と賞与の区分が必要なのは、継続雇用者のカウントで、同じ月に給与、賞与があれば、1と数えるからです。

これをピボットテーブルで集計します。
ピボットテーブルについては、こちらのカテゴリを参考にしてください。
【関連記事】Excelピボットテーブル | EX-IT
リンク

ピボットテーブルは、このように集計します。
[フィルター]に[対象]を置き、フィルタリングで「社員」のみを表示しましょう。
スクリーンショット 2015 05 08 9 02 07

さらに継続(前年度、今年度両方にいるかどうか)をチェックします。
IFとORを使い、「もし、前年度か今年度の給与支給額が0だったら、0(対象外)、そうでなかったら1(対象)」という数式です。
これを判定で使います。
今年度のみの三好さん、渋谷さん、尾崎さんは対象ではありませんし、前年度のみの冨田さんも対象ではありません。
スクリーンショット 2015 05 08 9 04 17

ピボットテーブルをコピーして、もう1つ作り、[フィルター]に[区分]を追加し、「給与」でフィルタリング、
スクリーンショット 2015 05 08 9 05 52

さらに、データを右クリックし、[値の集計方法]を[データの個数]にします。
スクリーンショット 2015 05 08 9 06 55

これで、給与の延べ支給人数(支給人数×月数)をカウントできました。
同様に、継続チェックの数式も入れます。
前述の数式を利用する方法もあるのですが、間違いを防ぐために、こちらにも入れておいた方が無難です。
(賞与のみ払っている月がある場合は、正しくカウントできませんが、そういったことはほとんどないと考えられるので、無視してます)
スクリーンショット 2015 05 08 9 08 04

集計し、所得拡大促進税制の別表の形式にアウトプット

所得拡大促進税制の計算に必要な数値の集計

まず、基準年度の給与合計(対象のもののみ)を直接入力し、その後、ピボットテーブルから数値を参照します。
スクリーンショット 2015 05 08 9 16 37

継続雇用者については、SUMIF関数を使い、「もし、D列が1だったら、B列を集計する」という式を入れます。

■スポンサードリンク

=SUMIF(PIVOT!D:D,1,PIVOT!B:B)

前年度・今年度の継続雇用者給与、雇用者数にそれぞれ入れましょう。
スクリーンショット 2015 05 08 9 17 29

あとは、割り算で平均給与を計算します。

所得拡大促進税制の要件を満たしているかの判定

上記の集計表を判定に使います。

■基準年度から今年度の給与支給額が2%以上増えているかのチェック
スクリーンショット 2015 05 08 9 21 02

■前年度から今年度の給与支給額が増えているかのチェック
スクリーンショット 2015 05 08 9 21 18

■前年度から今年度の平均給与額が増えているかのチェック
スクリーンショット 2015 05 08 9 21 26

この3つの要件をみたせば、基準年度からの増加額(前年度からではないことに注意!)の10%の税金が控除される可能性があります。
スクリーンショット 2015 05 08 9 21 45

「限度額」とあるのは、最大で法人税額の20%まで控除できるからです。

さらに、別表の形式に、数式を連動しています。
こうしておくと、申告ソフトへ入力するときに楽です。
(わかればいいので、形式は簡素化しています)
スクリーンショット 2015 05 08 9 28 47

この事例だと、275万円の税金が控除されます。
集計に手間はかかりますが、絶対に計算してみるべきです。
(私のお客様でもかなりの額の控除になりました)

なお、この事例で、雇用促進税制(純増の人数×40万円が控除)を適用すると、2人×40万円=80万円。
所得拡大促進税制の方が有利です。
(雇用促進税制は、ハローワークへの事前届出が必要です)

サンプルはこちらです。
EX-ITサンプル 所得拡大促進税制.xlsx
ピボットテーブルを更新すること、継続チェックの数式をコピーして確認することに注意し、ご自身の判断でお使いください。
(継続雇用制度利用者、雇用保険に入っていない人がいるケースなど特殊な事例には対応していません。計算のアイデアとして使っていただければ)





■スポンサードリンク
【編集後記】
GW明け、予定を入れずみっちり税理士業をすすめました。
夜は、土日のトライアスロンバイク合宿に向けて準備。
館山までバスで行くので、その予約を。
今日は早めに荷造りします。

【昨日の1日1新】
※詳細は→「1日1新」

高速バスネットでバスを予約




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』