はじめに
現在学部4年で、就職活動が一段落したところで就職活動中に
「年収400万円って手取りいくらもらえるんだ？」
と思い、調べて見ると このサイトにたどり着き、ざっくり各種税金などを計算してくれた。
しかし、いくら手取りがわかっても、奨学金の返済や家賃などの支払いで実際生活ができるのかを検証するために、Googleスプレッドシートで計算をしていた。
その都度このサイトで手取りを計算して、スプレッドシートに貼り付ける作業があったので、それ専用の関数を作れば解決すると思い。作りました。
元のソースコードはこのサイトから引用して、Googleスプレッドシートで使えるように多少変更しました。
動作
スプレッドシートのセルでssf_incometax関数を呼び出す。と
このように手取りと各種税金を計算してくれる。
引数１： 年収（円）
引数２： 配偶者なしの場合1,配偶者ありの場合0
引数3: ４０歳未満なら1,40歳以上なら0
年収４００万円で配偶者なし、４０歳未満の場合は以下のようになる。
=ssf_incometax(4000000,1,1)
ソースコード
以下のソースコードをGoogleスプレッドシートの[ツール]→[スクリプトエディタ]を開きコード.gsに貼り付ける。
貼り付けたあとはスクリプトエディタから[ファイル]→[保存]をする.
コード.gs
//スプレッドシートの関数から呼び出す用
function ssf_incometax(annual_income, partner, age) {
var all_income = annual_income;
var monthly_income = annual_income / 12; /*月収を計算*/
var income_tax = '';
var taxable_income = '';
var payroll_deduction = '';
var tax_deduction = '';
var partner_deduction = '';
var partner_deduction_residence = '';
var employment_insurance = '';
var care_insurance = '';
var income_tax_ratio = '';
var residence_tax_ratio = '';
var insurance_deduction_ratio = '';
var employment_insurance_ratio = '';
var real_income_ratio = '';
var insurance_fee_ratio = '';
var pension_ratio = '';
var care_insurance_ratio = '';
var insurance_deduction = '';
var insurance_fee = '';
var pension = '';
var taxable_residence = '';
var residence_tax = '';
var real_income = '';
//給与所得控除額を計算
if (all_income < 1625000) {
payroll_deduction = 650000;
} else if (all_income < 1800000) {
payroll_deduction = all_income * 0.4;
} else if (all_income < 3600000) {
payroll_deduction = all_income * 0.3 + 180000;
} else if (all_income < 6600000) {
payroll_deduction = all_income * 0.2 + 540000;
} else if (all_income < 10000000) {
payroll_deduction = all_income * 0.1 + 1200000;
} else if (all_income >= 10000000) {
payroll_deduction = 2200000;
}
//扶養控除を計算 面倒なので一律38万で計算
if (partner == 1) {
partner_deduction = 0;
partner_deduction_residence = 0;
} else if (partner == 2) {
partner_deduction = 380000;
partner_deduction_residence = 330000;
}
//社会保険控除を計算
if (monthly_income < 63000) {
insurance_fee = 2871;
pension = 8052;
care_insurance = 455.3;
} else if (monthly_income < 73000) {
insurance_fee = 3366;
pension = 8052;
care_insurance = 533.8;
} else if (monthly_income < 83000) {
insurance_fee = 3861;
pension = 8052;
care_insurance = 612.3;
} else if (monthly_income < 93000) {
insurance_fee = 4356;
pension = 8052;
care_insurance = 690.8;
} else if (monthly_income < 101000) {
insurance_fee = 4851;
pension = 8967;
care_insurance = 769.3;
} else if (monthly_income < 107000) {
insurance_fee = 5148;
pension = 9516;
care_insurance = 816.4;
} else if (monthly_income < 114000) {
insurance_fee = 5445;
pension = 10065;
care_insurance = 863.5;
} else if (monthly_income < 122000) {
insurance_fee = 5841;
pension = 10797;
care_insurance = 926.3;
} else if (monthly_income < 130000) {
insurance_fee = 6237;
pension = 11529;
care_insurance = 989.1;
} else if (monthly_income < 138000) {
insurance_fee = 6633;
pension = 12261;
care_insurance = 1051.9;
} else if (monthly_income < 146000) {
insurance_fee = 7029;
pension = 12993;
care_insurance = 1114.7;
} else if (monthly_income < 155000) {
insurance_fee = 7425;
pension = 13725;
care_insurance = 1177.5;
} else if (monthly_income < 165000) {
insurance_fee = 7920;
pension = 14640;
care_insurance = 1256;
} else if (monthly_income < 175000) {
insurance_fee = 8415;
pension = 15555;
care_insurance = 1334.5;
} else if (monthly_income < 185000) {
insurance_fee = 8910;
pension = 16470;
care_insurance = 1413;
} else if (monthly_income < 195000) {
insurance_fee = 9405;
pension = 17385;
care_insurance = 1491.5;
} else if (monthly_income < 210000) {
insurance_fee = 9900;
pension = 18300;
care_insurance = 1570;
} else if (monthly_income < 230000) {
insurance_fee = 10890;
pension = 20130;
care_insurance = 1727;
} else if (monthly_income < 250000) {
insurance_fee = 11880;
pension = 21960;
care_insurance = 1884;
} else if (monthly_income < 270000) {
insurance_fee = 12870;
pension = 23790;
care_insurance = 2041;
} else if (monthly_income < 290000) {
insurance_fee = 13860;
pension = 25620;
care_insurance = 2198;
} else if (monthly_income < 310000) {
insurance_fee = 14850;
pension = 27450;
care_insurance = 2355;
} else if (monthly_income < 330000) {
insurance_fee = 15840;
pension = 29280;
care_insurance = 2512;
} else if (monthly_income < 350000) {
insurance_fee = 16830;
pension = 31110;
care_insurance = 2669;
} else if (monthly_income < 370000) {
insurance_fee = 17820;
pension = 32940;
care_insurance = 2826;
} else if (monthly_income < 395000) {
insurance_fee = 18810;
pension = 34770;
care_insurance = 2983;
} else if (monthly_income < 425000) {
insurance_fee = 20295;
pension = 37515;
care_insurance = 3218.5;
} else if (monthly_income < 455000) {
insurance_fee = 21780;
pension = 40260;
care_insurance = 3454;
} else if (monthly_income < 485000) {
insurance_fee = 23265;
pension = 43005;
care_insurance = 3689.5;
} else if (monthly_income < 515000) {
insurance_fee = 24750;
pension = 45750;
care_insurance = 3925;
} else if (monthly_income < 545000) {
insurance_fee = 26235;
pension = 48495;
care_insurance = 4160.5;
} else if (monthly_income < 575000) {
insurance_fee = 27720;
pension = 51240;
care_insurance = 4396;
} else if (monthly_income < 605000) {
insurance_fee = 29205;
pension = 53985;
care_insurance = 4631.5;
} else if (monthly_income < 635000) {
insurance_fee = 30690;
pension = 56730;
care_insurance = 4867;
} else if (monthly_income < 665000) {
insurance_fee = 32175;
pension = 56730;
care_insurance = 5102.5;
} else if (monthly_income < 695000) {
insurance_fee = 33660;
pension = 56730;
care_insurance = 5338;
} else if (monthly_income < 730000) {
insurance_fee = 35145;
pension = 56730;
care_insurance = 5573.5;
} else if (monthly_income < 770000) {
insurance_fee = 37125;
pension = 56730;
care_insurance = 5887.5;
} else if (monthly_income < 810000) {
insurance_fee = 39105;
pension = 56730;
care_insurance = 6201.5;
} else if (monthly_income < 855000) {
insurance_fee = 41085;
pension = 56730;
care_insurance = 6515.5;
} else if (monthly_income < 905000) {
insurance_fee = 43560;
pension = 56730;
care_insurance = 6908;
} else if (monthly_income < 955000) {
insurance_fee = 46035;
pension = 56730;
care_insurance = 7300.5;
} else if (monthly_income < 1005000) {
insurance_fee = 48510;
pension = 56730;
care_insurance = 7693;
} else if (monthly_income < 1055000) {
insurance_fee = 50985;
pension = 56730;
care_insurance = 8085.5;
} else if (monthly_income < 1115000) {
insurance_fee = 53955;
pension = 56730;
care_insurance = 8556.5;
} else if (monthly_income < 1175000) {
insurance_fee = 56925;
pension = 56730;
care_insurance = 9027.5;
} else if (monthly_income < 1235000) {
insurance_fee = 59895;
pension = 56730;
care_insurance = 9498.5;
} else if (monthly_income < 1295000) {
insurance_fee = 62865;
pension = 56730;
care_insurance = 9969.5;
} else if (monthly_income < 1355000) {
insurance_fee = 65835;
pension = 56730;
care_insurance = 10440.5;
} else if (monthly_income > 1355000) {
insurance_fee = 68805;
pension = 56730;
care_insurance = 10911.5;
}
if (age == 1) {
care_insurance = 0;
} else {}
insurance_fee = insurance_fee * 12;
pension = pension * 12;
care_insurance = care_insurance * 12
//雇用保険料を計算
employment_insurance = all_income * 0.003;
//社会保険をまとめる 健康保険＋年金＋介護保険＋雇用保険
insurance_deduction = insurance_fee + pension + care_insurance + employment_insurance;
//課税所得＝総支給額－基礎控除－給与所得控除－扶養控除－社会保険控除
taxable_income = all_income - 380000 - payroll_deduction - partner_deduction - insurance_deduction;
taxable_residence = all_income - 330000 - payroll_deduction - partner_deduction;
//住民税を計算
residence_tax = taxable_residence * 0.1 + 5000;
//税額を計算 所得税＝課税所得×税率－税額控除額
if (taxable_income <= 1950000) {
income_tax = taxable_income * 0.05;
} else if (taxable_income <= 3300000) {
income_tax = taxable_income * 0.1 - 97500;
} else if (taxable_income <= 6950000) {
income_tax = taxable_income * 0.2 - 427500;
} else if (taxable_income <= 9900000) {
income_tax = taxable_income * 0.23 - 636000;
} else if (taxable_income <= 18000000) {
income_tax = taxable_income * 0.33 - 1536000;
} else if (taxable_income <= 40000000) {
income_tax = taxable_income * 0.40 - 2796000;
} else if (taxable_income > 40000000) {
income_tax = taxable_income * 0.45 - 4796000;
}
//復興特別所得税 2.1%を加算
income_tax = income_tax * 1.021;
//手取りを計算
real_income = all_income - income_tax - insurance_deduction - residence_tax - employment_insurance;
//パーセントの計算
if (income_tax < 0) {
income_tax = 0;
} else {
income_tax = Math.round(income_tax / 1000) / 10;
}
if (residence_tax < 0) {
residence_tax = 0;
} else {
residence_tax = Math.round(residence_tax / 1000) / 10;
}
insurance_deduction = Math.round(insurance_deduction / 1000) / 10;
insurance_fee = Math.round(insurance_fee / 1000) / 10;
pension = Math.round(pension / 1000) / 10;
care_insurance = Math.round(care_insurance / 1000) / 10;
employment_insurance = Math.round(employment_insurance / 1000) / 10;
real_income = Math.round(real_income / 1000) / 10;
// var result = [{
// '所得税': income_tax,
// '住民税': residence_tax,
// '社会保険料合計': insurance_deduction,
// '健康保険料': insurance_fee,
// '厚生年金保険料': pension,
// '介護保険料': care_insurance,
// '介護保険料': employment_insurance,
// '年収手取り': real_income,
// }];
var result = [
[
'所得税',
'住民税',
'社会保険料合計',
'健康保険料',
'厚生年金保険料',
'介護保険料',
'雇用保険料',
'年収手取り'
],
[
income_tax * 10000,
residence_tax * 10000,
insurance_deduction * 10000,
insurance_fee * 10000,
pension * 10000,
care_insurance * 10000,
employment_insurance * 10000,
real_income * 10000
]
];
return (result);
}
最後に
賞与ありの場合だと、おおよその年収がわかりにくいですが個人的に就職活動をしていて感じたこととして
年間賞与が 基本給の
中小企業：２ヶ月〜３ヶ月
大企業（東証一部上場系) 最低４ヶ月〜６ヶ月(業績によってもっと上がる）
Web系ベンチャー、メガベンチャー: 賞与なし,年俸制
と感じました。気になる方は面接や面談を通して去年の実績を聞いてみるといいかもしれません。
一年目は住民税が課税されなかったり、初任給（一ヶ月目）は所得税と雇用保険のみの課税になると思うので、この関数よりも手取りは多くなることが予想されます。
月収20万円(年収240万) の手取りが月:約15.8万 なので税金の計算はある程度考えておくのは大事ですね。