法人税、所得税、消費税、相続税、贈与税。税金の計算に使うExcel関数。ROUNDDOWN・INT・MIN・MAX・VLOOKUP

各種税金を計算、試算するときにExcelを使っています。
自分の知識の確認にもなりますし、Excelの勉強にもなるし、すばやく試算できるからです。

IMG_7218

※カフェにて iPhone X

端数処理にはROUNDDOWN、INT

ROUNDDOWN(ラウンドダウン)

税金は、

所得(税金上の利益)×税率=税金

で計算するのが基本です。

この場合、端数が出ることがあり、その端数処理はどの税金にもあります。
小数点以下は払えませんので。
また、端数処理のルールを決めておかないと、税金の金額が違ってきます。
そのためルールが決められているのです。

基本的なルールは、

所得(千円未満切捨)×税率=税金(百円未満切捨)

税率をかける前に、千円未満を切り捨てて、払う税金を計算するときに百円未満を切り捨てます。
(税金が戻ってくる=還付のときは、切り捨てずに円単位で戻ってきます)

所得税だとこんな感じです。
(他の関数と組み合わせていますので、後述します)

image

切捨てにはROUNDDOWN(ラウンドダウン)関数を使います。

=ROUNDDOWN(数値、桁数)

という関数で、
・千円未満だと、「桁数」は -3 →黄色
・百円未満だと、「桁数」は -2 →オレンジ
という指定です。

法人税も

image

消費税も

image

相続税も

贈与税も

image

同じしくみです。

ざっくり計算するなら関係ないのですが、答え合わせとして試算する場合は、端数処理もきっちりしたほうがいいでしょう。

INT(イント)

消費税を計算するときも端数が出ます。
今だと8%ですので、円未満の端数は切り捨てが必要です。
(四捨五入、切り上げにしている場合もありますが)

このときにも、ROUNDDOWN関数を使い、
=ROUNDDOWN(B2*1.08,0)

とすることもできます。

ただ、ROUNDDOWN自体長いので、INTがおすすめです。
(イントと呼んでいますが、インテジャーという呼び名も。短いのでイントにしています)

image

INTとROUNDDOWNは、数値がマイナスの場合、結果が変わりますが、試算するうえでは問題ないでしょう。

image

「マイナスの場合は、0」「800万円までは15%」というときはMAX、MIN

MAX(マックス)関数

税金は、所得がマイナスだと0です。

Excelで、所得×税率としていると、所得がマイナスになることもあり、そのまま計算されてしまいます。
たとえば、
-1000 ×15%=-150

と。

マイナスのときは、0、プラスのときは、所得×税率というIF関数を使ってもいいのですが、MAX関数が便利です。

たとえば、所得税の計算で、

=MAX(0,ROUNDDOWN(C2-C15,-3))

という関数を使っています。

image

これは、「0と、ROUNDDOWN(C2-C15,-3))のうち大きいほう」という意味です。
ROUNDDOWN(C2-C15,-3))は、所得。
所得がマイナスだと、0と比べて、0が大きい→0
所得がプラスだと、0と比べて、0が小さい→所得
が表示されます。

=IF(C2-C15<0,0,ROUNDDOWN(C2-C15,-3))

でもいいのですが、MAXのほうがすっきりしているかと。
また、これよりも数式(C2-C15の部分)が長くケースもあるので、応用がききます。

MIN(ミニマム)関数

法人税の計算をするときは、MINが便利です。
法人税は、このように計算します。
800万円までは15%、それ以上は800万円を超えた分の23.4%です。

image

このように、

=MIN(C21,8000000)*G3+MAX(C21-8000000,0)*G4

と使っています。

image

=MIN(C21,8000000)*G3は、C21(所得)と800万円の小さいほうと、G3(15%)。
所得が1000万円なら800万と比べて小さいのは800万になり、所得が200万円なら小さいのは200万です。

+MAX(C21-8000000,0)*G4は、C21-800000(800万を超える部分)と、0の大きいほうとG4(23.4%)。
所得が1000万円なら、1000万円-800万円=200万円と0の大きいほうは、200万円となり、所得が200万円なら200万円ー800万円=-600万円となり、大きいのは0となります。

IFで書くと、
=IF(C21=<8000000,C21*G3,8000000*G3+(C21-8000000)*G4
となります。

「もし800万円以下なら15%をかけて、8000万円超なら、800万円×15%+(所得ー800万円)*23.4%というものです。

どっちもどっちなところもありますが、やや複雑になるでしょう。

相続税でもこう使えます。

□スポンサードリンク

所得税、相続税、贈与税の計算にはVLOOKUP

VLOOKUP(ブイルックアップ)

所得税、相続税、贈与税の計算は、税金の計算表が準備されています。

たとえば所得税。
所得が500万円だったら、500万円×20%-427,500円=572,500円と計算します。

image

こういった表から計算するなら、VLOOKUP関数の出番です。

Excelに表を準備し、

image

=C16*VLOOKUP(C16,F1:H7,2,TRUE)-VLOOKUP(C16,F1:H7,3,TRUE)

というように入力します。

image

VLOOKUPでFALSEとすると完全一致したものを探し、TRUEとするとこのように範囲から探すことができるのです。
以前記事に書きました。

Excelで所得税を計算するには、IF関数よりもVLOOKUP関数のTRUE | EX-IT

消費税(簡易課税)でも、「サービス業」を表から探し、計算しています。

image

相続税は、各相続人ごとに計算するので、フォーマットはちょっと変わりますが、やっていることは同じです。

贈与税は、直系(祖父母、父母から20歳以上がもらう)かそうでないかで、表が変わるので、IFで分けています。
image

税金の計算をExcel上で再現すると、勉強になり、理解も深まるのでおすすめです。

ぜひやってみていただければ。

なお、記事中で使った数式を表示する関数は、FORMULATEXT(フォーミュラテキスト)です。
FORMULA、つまり数式をテキストにできます。
数式が入っていないところはエラーになるので、IFERROR(イフエラー)で「エラーだったら空欄にして」と処理しました。

image

 



【編集後記】

昨日は、メルマガ税理士進化論のオフ会の手配を。
今回は、土曜昼、平日夜、平日昼の3回やってみることに。
土曜昼は、子連れOKにしてみました。
うちの娘がどうなるかが一番心配ですが。

ブログのオフ会(という名の飲み会、ランチ会)もやってみようかと思っています。

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

デックスで店を予約
Xbox One X 予約

【昨日の娘日記】
スーパーに行くと、桃、バナナのリクエスト。
桃は高く、家計を圧迫しますが、まあ旬ですし大好きなのでしかたありません。
最近はセルフレジのタッチパネルも押すのが好きで教えています。