スポンサーリンク

Excel VBA で作ったユーザー定義関数を用いて簡易課税制度を適用した場合の消費税額を計算する方法


同業の方でないと知らないと思われるマニアックな内容ですが、消費税額の計算をする際に「簡易課税制度」を適用する場合があります。

「簡易課税制度」を適用 した場合における消費税額の計算ができるExcel(エクセル)シートを作成してみました。

Excel VBA で作った関数をユーザー定義関数として使っています。

今日は、その作り方についてお話しします。

※なお、今日は「簡易課税制度」の内容自体を詳しく説明するつもりはありません。

また、余談ではありますが 原則課税・簡易課税どちらが得か判定するExcelツールの使い方をYoutubeでアップしておりますので参考にして下さい▼

チャンネル登録は、こちらからお願い致します。

スポンサーリンク

1、「簡易課税制度」を適用した場合において消費税額を計算するシート

以下のシートの色付き(クリーム色)のセルに入力すれば、右下のセル「I16」に「1年間の消費税額」が表示される仕組みです。

Image(4)

※なお、このシートでは貸倒損失がある場合や複数の消費税率が混在している場合の計算はできません。

入力が必要な項目

入力が必要な項目は以下のようにしております。

●セル「D2」:  消費税率

消費税率として、8 又は5 を入力します。

●セル「D6 〜 D11」: 収入金額

次の第1種から第6種事業の収入金額を入力します。

※税込金額です。

第1種  卸売業
第2種  小売業
第3種  製造業
第4種  その他
第5種  サービス業
第6種  不動産業

●セル「E6 〜 E11」: 不課税取引

第1種から第6種事業の収入金額のうち「不課税取引」に該当する金額を入力します。

●セル「E6 〜 E11」: 非課税取引

第1種から第6種事業の収入金額のうち「非課税取引」に該当する金額を入力します。

セルでの計算

シート内で数式を埋め込んでいる箇所を説明します。

●セル「G6 〜 G11」: 税込課税売上高

第1種から第6種事業それぞれ

収入金額から不課税取引、非課税取引を引いて、税込課税売上高を計算しています。

Image(5)

●セル「H6 〜 H11」: 消費税

第1種から第6種事業それぞれ税込課税売上高に含まれる消費税額を計算しています。

以下の計算の小数点以下を四捨五入↓

税込課税売上高 × 消費税率 / (1 + 消費税率)

Image(6)

●セル「I6 〜 I11」: 税抜課税売上高

第1種から第6種事業それぞれ税抜課税売上高を計算しています。

税込課税売上高 − 消費税

Image(7)

●セル「I16」: 1年間の消費税額

VBA で作ったユーザー定義関数「Syouhizei」関数を用いて1年間の消費税額 を計算しています。

引数は6つ、第1種から第6種事業それぞれの「税抜課税売上高」です。

Syouhizei(引数1,引数2,引数3,引数4,引数5,引数6)

Image(8)

今日はこのユーザー定義関数についての VBA プログラミングコードを中心に説明します。

なお、消費税率を入力するセル「D2」に 8 又は 5 以外の数値を入力すると、アラートのメッセージボックスが出るようになっています。

「消費税率は 8 又は 5 を入力して下さい。」

Image(9)

2、プログラミングコード

ユーザー定義関数を作るための VBA コードを示します。

Function プロシージャを使っています。

以下↓のようにFunction と End Functionの間にコードを書かなければなりません。

————–

Function  関数名()

     コード

End Function

————–

3、コード説明

プログラミングコードについて説明します。

Function プロシージャ

Syouhizei という名前の引数6個の関数を定義しています。

Function Syouhizei(rng1, rng2, rng3, rng4, rng5, rng6 As Variant)

引数 rng1 〜 rng6 にはそれぞれ Excel シート上の第1種から第6種事業の「税抜課税売上高」の値が入ります。Variant型(すべての型を含む)で定義しています。

シート上で見れば、このように↓セルを6つ指定していますよね。

Image(10)

消費税率と税抜課税売上高の合計の読み込み

Excel シートのセル「D2」から「消費税率」を読み込んで 100 で割っています。

(上記47、48行目)

税抜課税売上高の合計をセル「I9」から読み込んでいます。

(上記51行目)

国税分の税率と地方税分の税率の場合分け

消費税率が8%の場合と 5%の場合で、国税分の税率(Zeiritsu_K)と地方税分の税率(Zeiritsu_C)を分けています。

また、消費税率が 8 又は 5 以外の場合は「消費税率は 8 又は 5 を入力して下さい。」というメッセージを出して注意喚起するようにしています。

(上記54~63行目)

消費税額の計算

まず、課税標準額の計算のため先に読み込んだ税抜課税売上高の合計の1,000円未満を切り捨てます。

続いて、課税標準額に対する消費税額の計算のため課税標準額に国税分の消費税率を掛けます。

(上記66、67行目)

付表5の中の以下の赤枠で囲った部分を計算していることになりますね。

Image(11)

次に事業別の消費税額を計算します。事業別の税抜課税売上高に国税分の税率を掛けます。

今回のユーザー定義関数の引数となっている事業別の税抜課税売上高に国税分の税率を掛けて小数点以下を切り捨てています。

(上記71~77行目)

付表5で言えば、以下の部分の部分になりますね。

Image(12)

控除対象仕入税額の計算 原則計算

控除対象仕入税額の計算を行います。

計算方法は、原則、事業1種類で75%以上の場合、事業2種類で75%以上の場合各々で計算した控除対象仕入額のうち最大のものを選びます。

まず、最初に原則計算を行っています。

(上記81~87行目)

計算式の内容は以下の付表5の赤枠で囲った箇所と同じことを行っています。

控除対象仕入税額の計算 1種類で75%以上の場合の計算

次に、1種類で75%以上の場合の計算 を行っています。

(上記91~104行目)

計算式の内容は以下の付表5の赤枠で囲った箇所と同じことを行っています。   

控除対象仕入税額の計算 2種類で75%以上の場合の計算

次に、2種類で75%以上の場合の計算 を行っています。

(上記111~199行目)

計算式の内容は以下の付表5の赤枠で囲った箇所と同じことを行っています。

控除対象仕入税額の計算 最大値の選択

これまでに計算した以下の中から最大となるものを選び出します。

・原則計算
・1種類で75%以上の場合の計算
・2種類で75%以上の場合の計算

ワークシート関数の1つである MAX関数を使っています。

計算式の内容は以下の付表5の赤枠で囲った箇所と同じことを行っています。

消費税額の計算

消費税のうち国税分、地方税分を計算した後、1年間の消費税額(国税と地方税の合計)を計算しています。

4、まとめ

簡易課税制度を適用した場合の消費税額の計算を VBA で作ったユーザー定義関数を用いて行いました。

日頃は会計ソフトで計算してしまう内容のことですが、改めて自分で計算式を作ると消費税法自体の勉強にもなりますね。

ユーザー定義関数はワークシート上で他の関数と同じように使うことができます。

自作の便利な関数を作ることを考えて頂ければと思います。

余談ではありますが、原則課税・簡易課税どちらが得か判定するExcelツールの使い方をYoutubeでアップしておりますので参考にして下さい▼

チャンネル登録は、こちらからお願い致します。