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

数値を逆算する場合、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年から、このようになります。。。給与所得控除が増えないということは、合計所得金額は減らず、税金が増えるわけです。
実はこっそりこういうところで増税されています。


------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

井ノ上陽一のプロフィール
■11/24東京・11/5福岡WordPressブログ入門セミナー
※11/29または12/1で名古屋でのセミナー、コンサルティングを承っております。 ■個別コンサルティング
■セミナー要望フォーム
■スポンサードリンク


年収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でセミナー打ち合わせ

■スポンサードリンク




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