[令和4年]Excelで計算 年末調整の保険料控除

スポンサーリンク
スポンサーリンク
ピピンプイ
ピピンプイ

年末調整で保険料控除申告書に記入する際、間違いやすい計算をExcelで必要事項を記入して自動で計算できるように、計算式をサイトに載せました。

年末調整で記入する、新生命保険料や旧生命保険料、介護医療保険料、新個人年金保険料、旧個人年金保険料、地震保険料の計算をExcelで必要事項を記入して自動で計算できるように、計算式をサイトに載せました。

皆様の参考になればと思います。

他にも当サイト内に、令和4年分の年末調整の給与所得金額を、Excelで計算する方法も載せていますので、参考にしてみてください。→当サイト内のリンクはこちら

別の記事に、令和4年分 給与所得者の保険料控除申告書の書き方も掲載しています。当サイト内のリンクはこちら

1.Excelの計算結果と計算式の全体像

まずはExcelの計算結果と計算式の全体像をお見せします。

緑色のセルに必要事項を入力します。

青色黄色のセルに計算式を入力し、黄色のセルに保険料を表示します。

計算結果

年末調整の保険料控除の計算結果1
年末調整の保険料控除の計算結果2

計算式

年末調整の保険料控除の計算式1
年末調整の保険料控除の計算式2

2.一般の生命保険料の計算方法

まずは一般の生命保険料の計算をします。

一般の生命保険料の計算方法

1.「新旧の区分」のセルにリストで表示できるようにします。

後でSUMIF関数を使用するときに、「新・旧」の判断をするため、データの入力規則を使用します。

「新旧の区分」のセルにリストで表示

リストは「データ」→「データツール」→「データの入力規則」にあります。

データの入力規則

設定タブの条件設定の中の入力値の種類を「リスト」にし、元の値に「新,旧」と入力してOKを押します。

リスト

2.セルD7,8に保険料の合計額を反映させる計算式を記入します。

「新・旧」を判断するため、SUMIF関数を使用します。セルD3からD6の中に新か旧がある場合に、セルE3からE6「保険料等の金額(a)」の金額の合計を、セルD7(新保険料のA)とD8(旧保険料のB)で計算します。

一般の生命保険料の計算方法

(a)のうち新保険料のA =SUMIF($D$3:$E$6,”=新”,$E$3:$E$6)

(a)のうち旧保険料のB =SUMIF($D$3:$E$6,”=旧”,$E$3:$E$6)

一般の生命保険料の計算方法

3.新・旧で判断した保険料の合計額セルD7(A25,000円),セルD8(B80,000円)を使って、下記の計算式Ⅰ(新保険料等用)計算式Ⅱ(旧保険料等用)に当てはめてセルG7「①」G8「②」の計算をします。

.新・旧で判断した保険料の合計額

4.計算式Ⅰ(新保険料)とⅡ(旧保険料)に計算式を入力していきます。
IF関数とAND関数を使って、条件を満たしたら計算するように設定します。

計算式Ⅰ(新保険料)
Ⅱ(旧保険料)

例として計算式Ⅰ(新保険料等用)の20,001から40,000を入力していきます。

保険料の計算

計算式Ⅰ(新保険料等用)の20,001から40,000まで の場合はセルP4に=IF(AND($D$7>= L4, $D$7<=N4),$D$7,””)

セルD7(25,000)がセルL4(20,001円から)より>=(大きい場合)、 セルD7セルN4(40,000円まで)より<=(小さい場合)AND関数で条件を指定して、満たしたらIF関数で表示します。

生命保険料の計算結果

計算結果が表示されたら 計算式Ⅰ(新保険料等用)セルQ4に=IFERROR((P4*1)/2+10000,””) で計算して、控除額を求めます。

5.同じようにセルのP3からP5とQ3からQ6まで計算式を入力していきます。

6.セルのP3からP5とQ3からQ6まで計算が終わったら、計算された結果をセルQ7にSUM関数で合計します。

SUM関数で合計する

控除額の計算は1円未満切り上げなのでROUNDUP関数を使用します。

=ROUNDUP(SUM(Q3:Q6),0)

7.次に計算結果を「新保険料セルQ7」→「新保険料セルG7」に「=Q7」へ「旧保険料X7」→「旧保険料G8」に「=X7」で計算結果を反映させます。

計算結果を反映

※比較演算子の詳しい説明は、当サイトの別の記事に載せています。→該当記事はこちら

※IF関数やAND関数の使い方は当サイト内の令和4分の年末調整の給与所得金額の計算方法にも説明されています。→該当記事はこちら

この計算の内容を表にしました。

計算式Ⅰ(新保険料等用)

20,000円以下  =IF(AND($D$7<=L3),$D$7,””)=P3
20,001円から40,000円まで=IF(AND($D$7>= L4, $D$7<=N4),$D$7,””)=IFERROR((P4*1)/2+10000,””)
40,001円から80,000円まで=IF(AND($D$7>= L5, $D$7<=N5),$D$7,””)=IFERROR((P5*1)/4+20000,””)
80,001以上一律に40,000円=IF(AND($D$7>=L6),40000,””)
          ①=ROUNDUP(SUM(Q3:Q6),0)

8.次に計算式Ⅰ(新保険料)を参考に計算式Ⅱを入力していきます。

計算式Ⅱを入力

計算式Ⅱ(旧保険料等用)

25,000円以下  =IF(AND($D$8<=S3),$D$8,””)=W3
25,001円から50,000円まで=IF(AND($D$8>= S4, $D$8<=U4),$D$8,””)=IFERROR((W4*1)/2+12500,””)
50,001円から100,000円まで=IF(AND($D$8>= S5, $D$8<=U5),$D$8,””)=IFERROR((W5*1)/4+25000,””)
100,001以上一律に50,000円  =IF(AND($D$8>=S6),50000,””)
=ROUNDUP(SUM(X3:X6),0)
新旧合計計①+②=+Q7+X7

9.次にセルのJ7にIF関数を使ってセルX8(計算式Ⅰ+計算式Ⅱの合計額が記載されたセル)が40,000以下の場合はその金額を40,000以上なら40,000円を表示するようにします。

生命保険料最高40,000円
保険料の計算結果

10.最後にセルのJ8にIF関数を使って②と③の大きい方の金額を表示させます。

セルのJ8にIF関数を使って②と③の大きい方の金額を表示
一般の生命保険料の計算結果

これで一般の生命保険料が計算されます。

3.介護医療保険料

一般の生命保険料の計算とほぼ同じなので、上記と下記の計算式を参考に、介護医療保険料を作成します。

1.介護医療保険料の計算は計算式Ⅰ(新保険料等用)だけを使用します。

介護医療保険料の計算結果
介護医療保険料の計算方法

2.セルD15にセルE12からE14の合計額を求めます。

3.次にセルD15の計算結果を計算式Ⅰで計算します。

介護医療保険料の計算式1
介護医療保険料の計算式1

4.セルQ15で計算された結果をセルJ15に=IF($Q$15<=40000,$Q$15,40000)(最高40,000なので、その計算を入れます。)で求めます。

介護医療保険料の計算過程

介護医療保険料が計算されます。

計算式Ⅰ(新保険料等用)の表

20,000円以下  =IF(AND($D$15<=L11),$D$15,””)=P11
20,001円から40,000円まで=IF(AND($D$15>= L12, $D$15<=N12),$D$15,””)=IFERROR((P12*1)/2+10000,””)
40,001円から80,000円まで=IF(AND($D$15>= L13, $D$15<=N13),$D$15,””)=IFERROR((P13*1)/4+20000,””)
80,001以上一律に40,000円=IF(AND($D$15>=L14),40000,””)
        ロ=ROUNDUP(SUM(Q11:Q14),0)

4.個人年金保険料

一般の生命保険料の計算とほぼ同じなので、下記の計算式を参考に、個人年金保険料を計算します。

個人年金保険料の計算結果
個人年金保険料の計算結果

新・旧の条件指定と保険料の金額の合計を求めます。

=SUMIF($D$18:$E$21,”=新”,$E$18:$E$21)

=SUMIF($D$18:$E$21,”=旧”,$E$18:$E$21)

新・旧で判断した保険料の合計額セルのD22とD23を使って、下記の計算式Ⅰ(新保険料等用)計算式Ⅱ(旧保険料等用)に当てはめて計算をします。

個人年金保険料の計算結果1と2
個人年金保険料の計算式1
個人年金保険料の計算式2

計算式ⅠとⅡの結果を表示します。

セルG22に「=Q22」を入力して④セルのQ22の結果を表示します。

セルG23に「=X22」を入力して⑤セルのX22の結果を表示します。

④+⑤の合計額がセルのX23にあるので、セルJ22に=IF($X$23<=40000,$X$23,40000)でその金額と最高40,000を比較します。

⑤と⑥のいづれか大きい金額を=IF(G23>J22,G23,J22)で比較します。

計算式Ⅰ(新保険料等用)

20,000円以下  =IF(AND($D$22<=L18),$D$22,””)=P18
20,001円から40,000円まで=IF(AND($D$22>= L19, $D$22<=N19),$D$22,””)=IFERROR((P19*1)/2+10000,””)
40,001円から80,000円まで=IF(AND($D$22>= L20, $D$22<=N20),$D$22,””)=IFERROR((P20*1)/4+20000,””)
80,001以上一律に40,000円=IF(AND($D$22>=L21),40000,””)
=ROUNDUP(SUM(Q18:Q21),0)

計算式Ⅱ (旧保険料等用)

25,000円以下  =IF(AND($D$23<=S18),$D$23,””)=W18
25,001円から50,000円まで=IF(AND($D$23>= S19, $D$23<=U19),$D$23,””)=IFERROR((W19*1)/2+12500,””)
50,001円から100,000円まで=IF(AND($D$23>= S20, $D$23<=U20),$D$23,””)=IFERROR((W20*1)/4+25000,””)
100,001以上一律に50,000円=IF(AND($D$23>=S21),50000,””)
=ROUNDUP(SUM(X18:X21),0)
新旧合計計 ④+⑤=+Q22+X22

5.生命保険料控除額

次に今まで計算してきた、一般の生命保険料控除、介護医療保険料、個人年金保険料を合計します。

生命保険料控除額
生命保険料控除額計算式

最高額が120,000円なので比較演算子を使って計算します。

=IF(J8+J15+J23<=120000,J8+J15+J23,120000)

以上で生命保険料の計算は終わりです。

6.地震保険料

次に地震保険料の計算をします。

地震保険計算結果
地震保険計算式
地震保険計算式

1.まずは一般生命保険料と同じで、地震・旧長期をリストを使って選択できるようにします。

設定の仕方は目次の 「一般の生命保険料の計算方法」を参照願います。

2.次にSUMIF関数を使用して地震と旧長期の合計金額を計算します。

=SUMIF($D$29:$E$31,”=地震”,$E$29:$E$31)

=SUMIF($D$29:$E$31,”=旧長期”,$E$29:$E$31)

地震保険計算式

3.地震保険料控除額は最高50,000円なのでセルD32(Bの地震保険料合計)の金額と比較します。

セルD34に=IF($D$32<=50000,$D$32,50000)

4.セルD33の金額「Cの旧長期損害保険料」(Cの金額が10,000円を超える場合はC×1/2+5,000円)の計算式を別途作ります。

旧長期損害保険料計算式
10000以下=IF(AND($D$33<=L30),$D$33,””)=N30
10000=IF(AND($D$33>L31),$D$33,””)=IFERROR((N31*1)/2+5000,””)
=ROUNDUP(SUM(O30:O31),0)

計算した結果をセルO32で求めます。

5. 旧長期損害保険料は最高15,000円なので、セルO32の金額と比較します。

セルG34に=IF($O$32<=15000,$O$32,15000)

地震保険料計算式

6.最後に地震保険料の金額と旧長期損害保険料を足した金額と最高50,000円を比較します。

=IF($D$34+$G$34<=50000,$D$34+$G$34,50000)

7.地震保険料控除額が計算されます。

地震保険料計算結果

以上で保険料控除申告書の作成方法の説明を終わります。

7.年末調整について詳しく知りたい場合は

年末調整で解らないことは、国税庁のホームページに詳しく掲載されています。

私の場合は国税庁のホームページと年末調整の書籍を購入して、作業を行っています。

8.マクロで特定のセルの文字や数値を1クリックで消去する方法

番外編として、入力したセルを1クリックで消去して、また最初から入力する場合の方法を当サイト内のマクロで特定のセルの文字や数値を1クリックで消去する方法で説明しています。

参考にしてみてください。→当サイト内のリンクはこちら

9.Excelブックのダウンロード

今回作成した、保険料控除の計算ができるExcelブックをダウンロードすることができます。

無料でご利用可能です。

ブックやシートにロックがかかっていませんので、ご自由に編集可能です。
ロックがかかっていませんので、誤って計算式などを消去してしまう恐れがありますのでご注意ください。

間違った箇所があるかもしれませんので、ご利用は自己責任でお願いします。

保険料控除の計算ができるExcelブックのダウンロードは下記からできます。

タイトルとURLをコピーしました