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

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

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

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

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

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

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

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まで の場合はセル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関数で合計します。

控除額の計算は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円を表示するようにします。

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

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

3.介護医療保険料

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

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

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

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

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を使って、下記の計算式Ⅰ（新保険料等用）計算式Ⅱ（旧保険料等用）に当てはめて計算をします。

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

セル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ブックのダウンロード