Excelゴールシークで、合計所得金額から年収を逆算

  • URLをコピーしました!

数値を逆算する場合、Excelのゴールシークを使うと便利です。
EX IT 3
※スクリーンショットで作成

合計所得金額とは

2018年(平成30年)から、「合計所得金額」が900万円を超えると配偶者控除が減っていき、1000万円を超えると、配偶者控除がなくなります。

900万円以下だと38万円
900万円超950万円以下だと26万円
950万円超1000万円以下だと13万円
1000万円超だと0円です。

EX IT 6

じゃあ、自分はどうなるのか?と思ったときにひっかかるのが、「合計所得金額」という言葉。

この金額は、年収なのか、手取りなのか・・。
答えはどちらでもありません。

収入が給料のみの場合、「合計所得金額」は、年収(額面)から、給与所得控除といわれる経費を引いた金額をいいます。
(個人事業主で事業の収入のみの場合、事業の利益から青色申告特別控除を引いた金額です)

給与所得控除とはこの表で計算します。
年収500万円だったら、500万円×20%+54万円=154万円が給与所得控除。
合計所得金額は、給料500万円ー154万円=346万円です。
EX IT 5

年収1,000万円だったら、220万円が給与所得控除。
これ以上年収がいくら上がっても、給与所得控除は増えません。
平成29年から、このようになります。。。給与所得控除が増えないということは、合計所得金額は減らず、税金が増えるわけです。
実はこっそりこういうところで増税されています。

年収1000万円の合計所得金額は、1000万円ー220万円=780万円です。

さきほどの
900万円以下だと38万円
900万円超950万円以下だと26万円
950万円超1000万円以下だと13万円
1000万円超だと0円
では、まだ38万円の範囲となります。

合計所得金額900万円、950万円、1000万円は、年収でいうといくらなのか?
Excelならかんたんに計算できます。

この記事もExcelを使って計算した結果を書きました。
【関連記事】扶養の範囲で働くか160万円以上稼ぐか。配偶者控除は増税・配偶者特別控除は減税
リンク

Excel給与所得控除の計算

合計所得金額(給与所得)=給与収入ー給与所得控除

ですので、この計算をExcelでまずやります。

使うのは、給与所得控除の式とVLOOKUP関数です。
EX IT 5

VLOOKUP関数で、計算表から、「5,000,000」を探します。
EX IT 10

この場合、VLOOKUP関数内で、「TRUE」と指定していれば、近似一致、つまり、ぴったり一致していなくても、範囲内で判定してくれるのです。
「FALSE」(完全一致)だと「5,000,000」という数値がないので、エラーがになります。
EX IT 9

500万円は、360万円超660万円なので、この位置(4行目)に該当し、その2列目の20%、3列目の54万円を読み取り、結果的に、500万円(I1)×20%+54万円=154万円という数値が給与所得控除の金額です。
EX IT 10

今回求める1000万円以上は、220万円なので入力してもいいのですが、給与所得控除本来の計算をしておきましょう。

Excelのゴールシークで合計所得金額から年収を逆算

給与所得控除の数式を入れ、給与所得(合計所得金額)の数式(収入ー給与所得控除)を入れたら、ゴールシークを使い、まずは、合計所得金額が、900万円のときの年収を逆算します。

求めるべき数式が入っているセル(この場合はJ3)にカーソルを置いて、[データ]→[What-IF分析]の[ゴールシーク]をクリックしてみてください。
EX IT 11

このようなボックスが出てきますので、
・「数式入力セル」に該当の数式が入っているセル
・「目標値に目標」に「9000000」
・「変化させるセル」に「j1」(大文字でも可)
を入れましょう。
変化させるセルは、数式は入っていません。この収入を変化させることで、給与所得控除、給与所得が計算され変化します。
EX IT 12

OKを押すと、目標値に合うように計算してくれるのがゴールシークです。

EX IT 13

解答を見つけると(この場合、給与所得が900万円となる、給与収入の金額)、J1に数値を入れてくれます。
EX IT 14

同様にやると、合計所得金額(給与所得)が950万円、1000万円のときの収入もわかるわけです。

1つずつゴールシークの操作をしなければいけません。
複数をゴールシークする方法はありますが、まずは1つずつを使えるようにしてみましょう。
EX IT 15

合計所得金額が
900万円以下だと38万円
900万円超950万円以下だと26万円
950万円超1000万円以下だと13万円
1000万円超だと0円

というのは、

年収(給与収入)が
1,120万円以下だと38万円
1,120万円超1,170万円以下だと26万円
1,170万円超1,220万円以下だと13万円
1,220万円超だと0円

と言いかえられます。
サンプルはこちらです。
EX-ITサンプル 所得税試算ゴールシーク.xlsx

ゴールシークはこういった使いみちもあります。
【関連記事】Excelゴールシーク ーキリのいい金額で給料を支払う場合・利益から売上を逆算する場合ー
リンク


【編集後記】

先日、マンションのエレベーターにクロスバイクと一緒に乗っていたら、途中で乗ってきた方に「井ノ上先生ですよね?」と。
びっくりしました。
顔と自転車でわかったそうです。
しかも税理士受験生で毎日ブログを見てくださっているそうで。
ありがたいです。

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

高洲公園までトライアスロンバイクで往復
MavicPro 機体・送信機ファームウェアアップデート
ZOOMでセミナー打ち合わせ

  • URLをコピーしました!