Workflow Sample: Automate Complicated Calculation for "Insurance Premium Deduction" at Year-end Adjustment!

Japanese people are fond of "life insurance".

Despite the population of approximately 100 million people, 40-50 trillion JPY money is paid for insurance every year. That calculates the total of 400,000 JPY per person a year.

There may be various reasons for being fond of insurance, such as

The risk of family will be serious when the wage-earner dies, because of mostly wives are full-time housewives. (There are fewer working-couples in Japan.)
Insurance companies also make social contributions, such as being major shareholders of companies and constructing buildings.
First of all, national characteristic is strongly savings-oriented.

Whereas, it is also in consideration of the existence of Tax incentives which is

Income tax and personal residence tax will be reduced. (Up to 240,000 JPY per year)

That is, in Japan, under the thoughts of "More people should buy insurances", there is a system that "You've got a benefit of cashback for buying insurances of up to 240,000 JPY worth. 32,000 JPY at the maximum* will come back to you!". (* In the case of income tax rate 20% and personal residence tax 10%)

Income tax refund of "up to 120,000 JPY deduction × tax rate"
Residence tax reduction of "maximum 84,000 JPY deduction × tax rate"

In fact, there are many cases that buying insurances in accordance with the "maximum amount of deduction".

However, you must make application at each year-end to receive this benefit. That is the one which is indicated as "Insurance premium deduction" among two of year-end-adjustment document to submit.

A) Application for Deduction for Insurance Premiums for Employment Income Earner and Application for Special Exemption for Spouse of Employment Income Earner for 2015
B) Application for (Change in) Exemption for Dependents of Employment Income Earner for 2016

The following Workflow is a mechanism for auto-generation of this "Application form PDF". You work hard on it once, and it will relieve you from working since the next year by data copy (Start in reusing data).

[Year-end-adjustment-Insurance premium Deduction]

Year End Adjustment is a good opportunity to re-recognize "contents of insurance that you bought".

However, the "summary of insurance" which has been done by all effort will not remain when it is submitted in paper. (Even though you should rather keep a copy of it,) you would like to keep it in your hand as a digital information. Using this Workflow, you will be able to confirm in PDF of the document you have submitted. And since the next year, you will need to be careful for only the differences.

By the way, the institution of this "Insurance premium deduction" was greatly changed in January 1st, 2012.

That is, "Long-term care medical insurance premium deduction" has been added in addition to "General life insurance premium deduction" and "Personal pension insurance deduction". The maximum deduction has been also expanded to 120,000 JPY from 100,000 JPY. However, the application form has become more complicated by the addition of the terms of "Former institution" and " New institution".

Using this workflow, it is possible to describe various notes as Hints for Input. It will make possible to continue to brush up your company's Year End Adjustment work, little by little. Moreover, there will no longer need to decrypt the complicated calculation formula.

[Year-end-adjustment-Insurance premium Deduction:"1. Register Information" screen]

Setting Sample for [Input Hint] for "Activity details" (HTML/JavaScript)

<script type="text/javascript">
jQuery('input[name="data\\[14\\].input"], input[name="data\\[22\\].input"], input[name="data\\[30\\].input"],input[name="data\\[38\\].input"],input[name="data\\[13\\].selects"], input[name="data\\[21\\].selects"], input[name="data\\[29\\].selects"], input[name="data\\[37\\].selects"] ').on('keypress keyup change',function(){ 
//データ項目番号「14,22,30,38, 13,21,29,37」のTextフォーム値に変化があれば、

//// Retrieving
var checked13 = jQuery("input[name='data\\[13\\].selects']:checked").val(); // ラジオボタン
var checked21 = jQuery("input[name='data\\[21\\].selects']:checked").val(); 
var checked29 = jQuery("input[name='data\\[29\\].selects']:checked").val(); 
var checked37 = jQuery("input[name='data\\[37\\].selects']:checked").val(); 

var insurance14 = jQuery("input[name='data\\[14\\].input']").val() - 0;
var insurance22 = jQuery("input[name='data\\[22\\].input']").val() - 0;
var insurance30 = jQuery("input[name='data\\[30\\].input']").val() - 0;
var insurance38 = jQuery("input[name='data\\[38\\].input']").val() - 0;

//// Calculating
var insuranceNew = 0;
var insuranceOld = 0;

if (checked13 == "新"){
  insuranceNew += insurance14 - 0;
} else if (checked13 == "旧"){
  insuranceOld += insurance14 - 0;
}

if (checked21 == "新"){
  insuranceNew += insurance22 - 0;
} else if (checked21 == "旧"){
  insuranceOld += insurance22 - 0;
}

if (checked29 == "新"){
  insuranceNew += insurance30 - 0;
} else if (checked29 == "旧"){
  insuranceOld += insurance30;
}

if (checked37 == "新"){
  insuranceNew += insurance38 - 0;
} else if (checked37 == "旧"){
  insuranceOld += insurance38 - 0;
}

var deductionNew = 0;
if (insuranceNew < 20000){
  deductionNew = insuranceNew;
} else if (insuranceNew < 40000){
  deductionNew = insuranceNew / 2 + 10000 - 0;
} else if (insuranceNew < 80000){
  deductionNew = insuranceNew / 4 + 20000 - 0;
} else if (insuranceNew >= 80000){
  deductionNew = 40000;
}

var deductionOld = 0;
if (insuranceOld < 25000){
  deductionOld = insuranceOld;
} else if (insuranceOld < 50000){
  deductionOld = insuranceOld / 2 + 12500 - 0;
} else if (insuranceOld < 100000){
  deductionOld = insuranceOld / 4 + 25000 - 0;
} else if (insuranceOld >= 100000){
  deductionOld = 50000;
}

var cap40k = deductionNew + deductionOld; 
if (cap40k > 40000){ cap40k = 40000 }
var cap50k = 0;
if (cap40k < deductionOld){ cap50k = deductionOld }
if (cap40k >= deductionOld){ cap50k = cap40k }

//// Updating
jQuery('input[name="data\\[39\\].input"]').val( insuranceNew );
jQuery('input[name="data\\[40\\].input"]').val( insuranceOld );
jQuery('input[name="data\\[41\\].input"]').val( deductionNew );
jQuery('input[name="data\\[42\\].input"]').val( deductionOld );
jQuery('input[name="data\\[43\\].input"]').val( cap40k );
jQuery('input[name="data\\[44\\].input"]').val( cap50k );

});
</script>

◆44,60,90→91 (91の入力ヒント)
<script type="text/javascript">
jQuery('input[name="data\\[44\\].input"], input[name="data\\[60\\].input"], input[name="data\\[90\\].input"] ').on('keypress keyup change',function(){ 
//データ項目番号「44,60,90」のTextフォーム値に変化があれば、

var insurance44 = jQuery("input[name='data\\[44\\].input']").val() - 0;
var insurance60 = jQuery("input[name='data\\[60\\].input']").val() - 0;
var insurance90 = jQuery("input[name='data\\[90\\].input']").val() - 0;

var cap120k = 0;
cap120k = insurance44 + insurance60 + insurance90 - 0;
if (cap120k > 120000 ){ cap120k = 120000 }

jQuery('input[name="data\\[91\\].input"]').val( cap120k );

});
</script>

[Data Items list]