阅读说明：本技术 一种利用电子表格进行寿险精算模型校验的方法 (Method for checking life insurance actuarial model by using spreadsheet ) 是由 陈森 陈曦 于 2020-12-31 设计创作，主要内容包括：本发明公开一种利用电子表格进行寿险精算模型校验的方法。所述方法包括：基于产品形态设计精算模型；利用专业精算软件,基于计算机编程语言建立实现所述精算模型的第一模型；在电子表格上建立实现所述精算模型的第二模型,将第二模型转换成基于所述计算机编程语言的第三模型；比较在指定样本上运行第一模型和第三模型的结果是否一致,对所述精算模型进行验证。本发明能够解决现有技术利用电子表格模型公式进行校验,只能在一行中通过公式套用来表达逻辑,复杂的计算过程需要的公式很长、套用层级众多,从而无法清晰展现其逻辑结构,使模型校验的时间很长等问题。(The invention discloses a method for checking a life insurance actuarial model by using a spreadsheet. The method comprises the following steps: designing a actuarial model based on the product form; establishing a first model for realizing the actuarial model based on a computer programming language by using professional actuarial software; building a second model on the spreadsheet that implements the actuarial model, converting the second model to a third model based on the computer programming language; and comparing whether the results of the first model and the third model which are run on the specified sample are consistent or not, and verifying the actuarial model. The invention can solve the problems that the prior art uses a spreadsheet model formula to carry out verification, can only express logic in one line by formula application, needs very long formula and multiple application levels in a complex calculation process, cannot clearly show the logic structure of the complex calculation process, has long time for model verification and the like.)

1. A method for checking a life insurance actuarial model using a spreadsheet, comprising the steps of:

step 1, designing a actuarial model based on product form;

step 2, establishing a first model for realizing the actuarial model based on a computer programming language by using professional actuarial software;

step 3, establishing a second model for realizing the actuarial model on the spreadsheet;

step 4, converting the second model into a third model based on a computer programming language;

and 5, checking the actuarial model by comparing whether the results of the first model and the third model which are operated on the appointed sample are consistent.

2. The method for life insurance actuarial model verification using electronic forms of claim 1, wherein said second model is established based on the following convention:

table columns for prediction, one column representing a time series;

the table rows are used for representing time points, the ith time point is the first time point which is the prediction starting point of the specified mth action, the ith time point of the mth action + I-1, I is 1,2, …, and I is the number of the time points;

the content of the cells of all time points of each time sequence is a formula representing the time sequence, and the m-2 th action is the name of the time sequence;

all non-time-series calculation objects in the model are positioned in the specified nth column, and the content of the left cell of the cell of each non-time-series calculation object is the name of the cell;

the kth intermediate variable of the nested formula is represented as: let var _ k: ═ expression.

3. The method for checking a life insurance actuarial model using a spreadsheet according to claim 2, wherein said step 4 specifically comprises:

step 4.1, extracting a spreadsheet formula consisting of time sequence and/or non-time sequence calculation objects in the second model in a character string mode;

step 4.2, converting the content contained in the quotation marks into character string intermediate variables, and processing the quotation marks contained in the quotation marks as quotation marks in the character string;

4.3, taking a function or an operation symbol defined by spreadsheet software as a separator, and extracting a function name and a keyword of a calculation object;

step 4.4, determining a keyword containing a cell address according to an address naming rule, extracting a row address and a column address of the cell from the keyword, if the column address of the cell is n, obtaining a non-time-series calculation object in the cell, obtaining the name of the non-time-series calculation object from the cell on the left side of the cell, and replacing the keyword with the name; if the column address of the cell is not equal to n and the row address is greater than or equal to m, the cell is a time sequence, the name f of the time sequence is obtained from the m-2 th row of the column where the cell is located, the corresponding fixed time node or the expression of the time node variable t is determined, and the keyword is replaced by f (the fixed time node) or f (the expression of t);

step 4.5, scanning each character of the character string obtained in the previous step from left to right, starting to count the occurrence frequency of the left brackets and the right brackets when the sub-character string of the 'keyword + the left brackets' is scanned, and obtaining a complete nesting formula when the occurrence frequency of the left brackets and the right brackets is equal;

step 4.6, starting from the character string with the highest use level of the nested formula, replacing the formula with the non-0 use level by using an intermediate variable, and converting the nested formula into a non-nested formula with the highest use level of 0 according to a rule set; the rule set is used for converting keywords in the spreadsheet formula into keywords of a programming language, and the keywords comprise operators, brackets, functions and control statements.

Technical Field

The invention belongs to the technical field of life insurance actuarial evaluation, and particularly relates to a method for checking a life insurance actuarial model by using a spreadsheet.

Background

The life insurance actuarial evaluation refers to an important work and technical means for calculating a series of related financial results including reserve, repayment capacity, intrinsic value and the like of the tested personal insurance products by an insurance company according to the basic principles of demographics and economics, related technical standard requirements and industry common operation practice. According to the requirements of relevant laws and regulations, the life insurance company needs to measure and calculate the financial results regularly and report the financial results to a specific supervision agency and/or release the results to the public.

The life insurance actuarial evaluation work needs to be carried out by depending on a life insurance actuarial model. The life insurance actuarial model for the life insurance evaluation work is a mathematical model which quantitatively describes the cash flow, liability, repayment capacity cost, profit and the reduced value of the cash flow and the profit of the insurance company in the future including premium and settled expenditure and the like by using an actuarial evaluation professional method on the basis of the products sold by the insurance company. The actuarial model is generally composed of a plurality of interrelated time series, the different time series having different actuarial meanings, such as the predicted death probability of a single specific policy per unit time period in the future, the probability of remaining valid at the end of each time period, the payment criteria of a specific payment category of a specific product, the expected payment cash flow in a specific time period in the future, the expected expenditure of a life insurance company in a specific time period in the future, the expected return on investment, the expected liability cost, the expected repayment capacity cost, and the like.

In the life insurance evaluation work, the correlation among all the time series is recorded and embodied in a life insurance evaluation model through a specific computer language based on certain actuarial modeling software. In particular, the inherent computational logic of each time series may be thought of as being represented by a particular function. For example: assuming that the function detatchclaim (t) represents the predicted death-paying cash flow in the time period t, in the case of the accounting software using C language for the carrier record logic, inForce (t), inForceRate (t), mortalityRate (t) and detatchbettefit (t) represent four further time sequences, respectively whether the policy for a certain time period is valid (inForce), the probability that the policy remains valid for the time period (inForceRate), the probability that the insured person dies if the insured person still survives for a certain time period in the future (mortalityRate) and the calculated result at time t, which specifies the death-paying amount that the insured person can obtain in the certain time period in the future.

Therefore, the evaluation model building work of a actuary professional can be considered as the writing work of the logic functions behind the hundreds of thousands of time series described above based on a specific computer language using specific actuary modeling software. Where the logical structure of many functions is much more complex than that described above for the illustrative example. After compiling the functions, the actuarial professional compiles all the set functions through actuarial software, runs the model aiming at specific policy data and checks the result of the sample policy. If the results of these samples are deemed to be correct, the model is further connected to a complete assessment policy database, the model is run on a case-by-case basis, the results are summed up, and the results are further checked and confirmed for further reporting or analysis. A complete evaluation policy database typically has tens of thousands to millions of data, with measurement times varying from a dozen minutes to a dozen hours. This is the most time-consuming work step in the life insurance actuarial assessment work. The insurance company can further check and evaluate the correctness of the model by internal and external reviewers, and if errors still exist in the model, the insurance company needs to turn over the work and recalculate the work. Therefore, insurance companies require actuarial modeling personnel to use the electronic form to perform parallel modeling, randomly extract certain sample model points, and compare whether the results between the electronic form model and the actuarial software model are consistent at key nodes of evaluation work so as to judge whether the actuarial software model is reliable.

Spreadsheets, also known as spreadsheets, are a class of computer programs that simulate a computational form on paper. Spreadsheets express the computational logic using a table formula, and actuarial software expresses the logic using a computer programming language. When a modeler checks and calculates the models, and when the results of the two models are inconsistent, the modeler needs to compare and confirm the expression forms of the two models. Assume that a tabular model is built using Excel at the modeler, in which the modeler represents a particular time series on a per spreadsheet column basis, and each cell in the column represents a time series node. In this model, column X represents the expected death delivery in each time period in the future, column P represents whether the policy is still in the insurance period in a particular time period in the future, column Q represents the probability that the policy is still valid at the beginning of the time period, column R represents the probability that the insured will die in the time period IF it still survives at the beginning of the time period, column H represents the estimated death delivery of the insured in the time period under the policy contract, row 22 represents the 0 time point of the prediction process, the formula in cell X100 for estimating the expected death delivery at month 100 is "IF (P100, Q100R 100H 100, 0)", and it is clearly not intuitive compared to its expression in the actuary software. The verifier needs to remember the time series represented by column P, Q, R, H appearing in the formula to understand the meaning of the formula. A actuarial model contains thousands of time series, then the spreadsheet model for verification must also contain thousands of columns. It is impossible for the verifier to remember them without error, and they can only be repeatedly turned over back and forth during the verification work. Meanwhile, the spreadsheet model formula can only express logic in one row by systematic formula apply. For a complex calculation process, the formulas are long, and the application levels of the system formulas are numerous, so that the logic structures of the system formulas cannot be clearly shown. The net result is that verification based on spreadsheet models may take longer than model building based on actuarial software.

Disclosure of Invention

In order to solve the above problems in the prior art, the present invention provides a method for checking a life insurance actuarial model by using a spreadsheet.

In order to achieve the purpose, the invention adopts the following technical scheme:

a method for checking a life insurance actuarial model using a spreadsheet, comprising the steps of:

step 1, designing a actuarial model based on product form;

step 2, establishing a first model for realizing the actuarial model based on a computer programming language by using professional actuarial software;

step 3, establishing a second model for realizing the actuarial model on the spreadsheet;

step 4, converting the second model into a third model based on a computer programming language;

and 5, checking the actuarial model by comparing whether the results of the first model and the third model which are operated on the appointed sample are consistent.

Compared with the prior art, the invention has the following beneficial effects:

according to the method, a actuarial model is designed based on the product form, professional actuarial software is used, a first model for realizing the actuarial model is established based on a computer programming language, a second model for realizing the actuarial model is established on an electronic form, the second model is converted into a third model based on the computer programming language, whether the results of operating the first model and the third model on a specified sample are consistent or not is compared, and the actuarial model is verified.

Drawings

Fig. 1 is a flowchart illustrating a method for checking a life insurance actuarial evaluation model using a spreadsheet according to an embodiment of the present invention.

Detailed Description

The present invention will be described in further detail with reference to the accompanying drawings.

An embodiment of the present invention provides a method for checking a life insurance actuarial model by using a spreadsheet, a flowchart is shown in fig. 1, and the method includes the following steps:

s101, designing a actuarial model based on product form;

s102, establishing a first model for realizing the actuarial model based on a computer programming language by using professional actuarial software;

s103, establishing a second model for realizing the actuarial model on the spreadsheet;

s104, converting the second model into a third model which is based on a computer programming language and eliminates a nested formula level;

and S105, checking the actuarial model by comparing whether the results of the first model and the third model which are operated on the appointed sample are consistent.

In the present embodiment, step S101 is mainly used to design a actuarial model based on the product form. To facilitate understanding of the technical solution, an example is given below. A certain insurance company newly develops a regular life insurance product with 10-year payment period, 20-year guarantee period and death claim insurance amount, and needs to build an actuarial model suitable for relevant criteria for the product. First, the modeler identifies all cash flows associated with a product according to terms (for simplicity, the model does not consider the cost of a company selling and maintaining the product and simplifies the calculation process of refund payments), including: within 20 years from the date that the client starts to guarantee, if the client lives and the policy is not terminated, the client pays the premium per year according to the agreement; if the customer dies and the pre-die policy is valid, the company pays the beneficiary of the customer by the agreed premium, and the policy is terminated after the payment; if the client refunds within 20 years, the company pays the client with the contracted refund corresponding to the current time point (the refund corresponding to different policy years is different), and the policy is terminated after payment. Then, a model is built to predict the cash flow associated with this product: premium revenue is expected, death benefits are expected, and refund benefits are expected. Since the assumptions and methods of calculating the expected cash flow under different criteria may be different, the model needs to predict the cash flow separately for each relevant criterion.

In this embodiment, step S102 is mainly used for building the actuarial model based on the computer programming language. For convenience of description, we refer to the actuarial model created using the computer programming language as the first model. There are many computer programming languages that can be used for modeling, such as C, and the embodiment is not limited to a specific programming language.

In the present embodiment, step S103 is mainly used to build the actuarial model on the spreadsheet. Also, for ease of description, we refer to the actuarial model built on the spreadsheet as the second model. The purpose of creating the second model is to verify the actuarial model. The prior art is to verify the actuarial model by directly comparing the results of the operation of the second model with the results of the operation of the first model, which are built on the spreadsheet. The present embodiment is not limited to the spreadsheet software used, and the most commonly used spreadsheet is Excel.

In this embodiment, step S104 is mainly used to convert the second model into the third model. As mentioned above, the verification is performed by directly using the operation result of the second model established on the spreadsheet, and many defects exist, such as long formula, multiple formula nesting levels, long model verification time and the like. To this end, the present embodiment converts the second model built on the spreadsheet into a third model based on a computer programming language that removes the nested formula hierarchy. The usage level of the nested formula represents the nesting hierarchy of the formula. If a formula does not exist inside any other formula calling process in the form of a formula parameter, the usage level is 0; otherwise, its usage level is the usage level of the outer formula that calls it as a parameter plus 1.

In this embodiment, step S105 is mainly used to verify the actuarial model. The checking method comprises the following steps: and respectively operating the first model and the third model on the same appointed sample, and verifying the actuarial model by comparing whether the results of the two models are consistent.

As an alternative embodiment, the second model is established based on the following convention:

table columns for prediction, one column representing a time series;

the table rows are used for representing time points, the ith time point is the first time point which is the prediction starting point of the specified mth action, the ith time point of the mth action + I-1, I is 1,2, …, and I is the number of the time points;

the content of the cells of all time points of each time sequence is a formula representing the time sequence, and the m-2 th action is the name of the time sequence;

all non-time-series calculation objects in the model are positioned in the specified nth column, and the content of the left cell of the cell of each non-time-series calculation object is the name of the cell;

the kth intermediate variable of the nested formula is represented as: let var _ k: ═ expression.

The present embodiment presents some of the main conventions for building the second model on a spreadsheet. These are mainly given for time-series and non-time-series computational objects contained in the model, such as their formula and row-column position of the name, and representation method of intermediate variables of the nested formula, etc. In this embodiment, only some more important commonly used conventions are given, and not all conventions are included, nor different convention methods for the same content are excluded, for example, the position of a non-time-series calculation object, the position of a time-series time 0 point, and the like.

As an optional embodiment, the S104 specifically includes:

s1041, extracting a spreadsheet formula consisting of time sequence and/or non-time sequence calculation objects in the second model in a character string mode;

s1042, converting the content contained in the quotation marks into character string intermediate variables, and processing the quotation marks contained in the quotation marks as quotation marks in the character string;

s1043, taking a function or an operation symbol defined by spreadsheet software as a separator, and extracting a function name and a keyword of a calculation object;

s1044, determining keywords containing cell addresses according to an address naming rule, extracting row and column addresses of the cells from the keywords, if the column addresses of the cells are n, obtaining non-time-series calculation objects in the cells, obtaining names of the non-time-series calculation objects from the cells on the left sides of the cells, and replacing the keywords with the names; if the column address of the cell is not equal to n and the row address is greater than or equal to m, the cell is a time sequence, the name f of the time sequence is obtained from the m-2 th row of the column where the cell is located, the corresponding fixed time node or the expression of the time node variable t is determined, and the keyword is replaced by f (the expression of the fixed time node) or (the expression of t);

s1045, scanning each character of the character string obtained in the previous step from left to right, starting to count the occurrence frequency of the left brackets and the right brackets when the sub-character string of the 'keyword + the left brackets' is scanned, and obtaining a complete nesting formula when the occurrence frequency of the left brackets and the right brackets is equal;

s1046, replacing the non-0 using level formula with an intermediate variable from the character string of the highest using level of the nested formula, and converting the nested formula into a non-nested formula of which the highest using level is 0 according to a rule set; the rule set is used for converting keywords in the spreadsheet formula into keywords of a programming language, and the keywords comprise operators, brackets, functions and control statements.

The embodiment provides a technical scheme for converting an electronic form model into a programming language model.

In the present embodiment, step S1041 is used to express the spreadsheet formula in the second model in a character string form. For example, if the original formula at A1 cell is: IF ($ W $2 ═ M ", FLOOR (1.1), $ K $3) +7, the extracted string is: IF ($ W $2 ═ M ", FLOOR (1.1), $ K $3) + 7.

In this embodiment, step S1042 is used for processing a character string in a quotation mark. The contents contained in the quotation marks are converted into string intermediate variables. For example, IF ($ W $2 ═ M ", FLOOR (1.1), $ K $3) +7 is changed to:

let var_1:＝“M”

IF($W$2＝var_1,FLOOR(1.1),$K$3)+7

if the quotation marks are also contained in the quotation marks, the quotation marks in the inner layer are treated as quotation marks in the character string. If two consecutive quotation marks are included in a quotation mark pair, the two quotation marks are considered as one quotation mark, and the formula "number" "5" "should be converted into the number" 5 ".

In the present embodiment, step S1043 is for extracting a function name and a keyword of a calculation target. The extraction method uses the function or operation symbol defined by the spreadsheet software as the separator, such as "+, -," +,/, < >, ", (),", etc. From the string obtained in the previous step, we can obtain: IF. $ W $2, $ var _1, $ FLOOR, 1.1, $ K $3, 7.

In this embodiment, step S1044 is configured to replace the keyword with the formula name of the time-series and/or non-time-series calculation object corresponding to the keyword including the cell address. There are four forms of cell addresses, which are "$ alphabet-combination $ number-combination", "$ alphabet-combination number-combination", and "alphabet-combination number-combination". When $ appears before a letter combination or a number combination, it represents a column or row, respectively. Therefore, keywords $ W $2, $ K $3 containing the cell address can be obtained from the string obtained in the previous step. Then, a row-column address is extracted from the keyword, whether the cell corresponds to the time-series or non-time-series calculation object is judged according to the convention about the time-series and non-time-series calculation objects in the previous embodiment, the names of the time-series or non-time-series calculation objects are obtained, and the keyword is replaced by the names. The time series name is in the form of "electronic worksheet name } - > time series name (time node)", such as inforceBlock- > inForceRate (15); the non-time-series calculation object name is in the form of an "electronic worksheet name" - > calculation object name ", such as dataBlock- > issue _ age. The electronic worksheet name may be omitted if the name is directly within the current electronic worksheet. Depending on whether the address representation is in absolute form (comprises $), the time nodes of the time series have two different forms (fixed form and variable form), for example, column X represents death payment (deothclaim), while in the formula of a10 there is a reference to column X, if the reference is in the form of X $10, the time series is represented as deothclaim (-12), and the time node is a constant "-12"; if the reference is of the form X11, the time series is denoted as deothClaim (t +1) and the time node is a linear expression t +1 for the variable t. After this step of processing, the following expression is obtained:

let var_1:＝“M”

IF(genderStr＝var_1,FLOOR(1.1),indForFemale)+7

in this embodiment, step S1045 is used to obtain a complete nested formula. Different nesting formulas may vary widely, but they have in common that they all begin with a keyword followed by a left bracket and end with a right bracket, with the left and right brackets being equal in number. Accordingly, a complete nesting formula can be obtained by scanning each character of the character string obtained in the previous step from left to right. Scanning the character string obtained in the previous step to obtain a nesting formula: IF (genderStr ═ var _1, FLOOR (1.1), indForFemale).

In the present embodiment, step S1046 is used to convert the nested formula into a non-nested formula. The conversion method comprises the following steps: starting with the string of the highest usage level of the nested formulas, replacing the non-0 usage level formulas with intermediate variables and converting the nested formulas to non-nested formulas according to a rule set. The nested formula obtained in the last step is converted into:

let var_1:＝“M”

let var_2:＝FLOOR(1.1)

IF(genderStr＝var_1,var_2,indForFemale)+7

to improve program readability, the IF function may be further converted into IF (conditional)/ELSE/enddi form:

let var_1:＝“M”

let var_2:＝FLOOR(1.1)

IF(genderStr＝var_1)

let var_3:＝var_2

ELSE

let var_3:＝indForFemale

ENDIF

var_3+7

the rule set is a conversion rule table from a spreadsheet to a programming language defined (agreed) in advance, and is used for converting keywords in a spreadsheet formula into keywords in the programming language, wherein the keywords comprise operators, brackets, functions and control statements. And will not be described in detail here.

The above description is only for the purpose of illustrating a few embodiments of the present invention, and should not be taken as limiting the scope of the present invention, in which all equivalent changes, modifications, or equivalent scaling-up or down, etc. made in accordance with the spirit of the present invention should be considered as falling within the scope of the present invention.