Figure I.2-1

Software

The model works on the Excel for Windows (version 5.0 or later) software package which is easily available and widely used. A good knowledge and experience of using Excel is required of the user, however, what is even more important is that the user has a solid quantitative background and preferably several years of experience in financial management of the social security scheme, and has sound programming knowledge.

The model uses both the Excel spreadsheets and Visual Basic for Applications (VBA) capabilities. The main projection component of the model has been integrated into an Excel workbook module and has been programmed in VBA.

 

Part II. General model structure

1. Methodology

This chapter explains the mathematical methods underlying the actuarial valuation of using ILO-PENS. Generally, the actuarial valuation is undertaken in two steps. The first step is to estimate the future expenditure and contribution base; the second is, based on the results obtained in the first step, to establish the long-term fund operation of the scheme, and to determine the future contribution rate on the basis of the financial system adopted by the scheme.

1.1 Method of cost estimate

1.1.1 General

The year-by-year simulation method is generally used to estimate future costs. The basic idea of this method is illustrated in Figure II.1-1. Generally speaking, for each generation, the transition of status of a person (active person, inactive person, pensioner) is mapped onto the next year's status by using actuarially assumed transition probabilities (mortality rate, retirement rate) and applying eligibility conditions and pension formula. This cycle is iterated until the end of the projection period. By summarizing age-specific results, global future costs are obtained.

The basics of the calculation can be symbolically explained as follows:

On the income side, the contribution base is calculated by multiplying the assumed contributors and assumed average insurable earnings (and collection factor):

Contribution base = (contributors) x (average insurable earnings) x (collection factor)

The three terms on the right-hand side of the above equation are exogenous.

On the expenditure side, the benefit expenditure is calculated by applying the survival factors and the adjustment factors to the previous year's expenditure and by adding the newly awarded pensions:

Expenditure = (previous year's expenditure) x (survival rate) x (adjustment factor) + (newly awarded pensions)

The previous year's expenditure is known; the survival rate and the adjustment factor are to be assumed. The newly awarded pensions are derived as a result of projections.

In the following sections, the general methodology is explained in more detail.

1.1.2 Estimating the covered population

(i) Definitions

Reg(x,t): Registered population in year T is defined as the persons who are registered in the scheme and have made contributions during at least one contribution period (usually, one month) until that year. Those who have already died or become pensioners should be excluded.

Act(x,t): Active population in year T is defined as the persons who have made at least one contribution during that year.

Figure II.1-1: Simulation of the pension scheme (conceptual)

Inact(x,t): Inactive population in year T is defined as the persons who are registered in the scheme but have made no contribution throughout year T.

From this, it follows that the sum of active population and inactive population is the registered population:

Reg(x,t) = Act(x,t) + Inact(x,t)

Cont(x,t): Contributors in year T is defined as the average of the persons who made the contributions of each contribution period during year T.

Generally, the number of contributors is not more than that of the active population, because not all workers work on a full-time basis and without any cessation of employment. In other words, the difference between these two numbers indicates the degree of completion of the covered employment. Thus, we define the "density factor" as the percentage of the contributors to the active population.

Dens(x,t) = Cont(x,t) / Act(x,t)

Nent(x,t): New entrants in year T is defined as the persons who are newly registered during year T and have made at least one contribution.

Rent(x,t): Re-entrants in year T is defined as the persons who belonged to the inactive population in year T1 but belonged to the active population in year T.

(ii) Estimation The number of active population is estimated by applying the coverage rate to the projected base population by age and by sex. The coverage rates are assumed by taking into account the forecast of the labour force participation rate, unemployment rate and the observed past experiences. The base population can be national population, labour force population, or employed population.

Act(x, t) = Covrate(x, t) x Pop(x,t)

where:

Pop(x,t): base population in year T

Covrate(x,t): coverage rate in year T

Let S[Act(x,t)] be the members of Act(x,t) who remain in active population in year T+1 (how S[Act(x,t)] is calculated will be explained below). Then, consider the difference D(x+1,t+1) = Act(x+1,t+1)S[Act(x,t)]. The following two cases may occur:

(a) D(x+1,t+1)>0

Usually, this occurs at a younger age. In this case, the gap is to be made up for by either new entrants or re-entrants. We introduce an exogenous variable NR(x,t), the ratio of new entrants to re-entrants. (NR(x,t)=1 at younger age, =0 at older age).

The new and re-entrants are estimated as:

Nent(x+1,t+1) = NR(x+1,t+1) x D(x+1,t+1)

Rent(x+1,t+1) = [1NR(x+1,t+1)] x D(x+1,t+1)

In case there are not enough inactive persons to become re-entrants, i.e. S[Inact(x,t)]<Rent(x+1,t+1), then Act(x+1,t+1) would need to be adjusted (reduced) so that it allow for the maximum possible number of re-entrant. At the same time, the {Act(k,t+1) ; k<x+1} would need to be readjusted so that the total number of active population remains the same as assumed.

(b) D(x+1,t+1)<0

Usually, this occurs at older age. In this case, D(x+1,t+1) should be classified as the inactive population.

Thus, we can have the number of active population, new entrants, re-entrants of the next year. The registered population, inactive population and contributors are estimated as follows:

Reg(x+1,t+1) = S[Reg(x,t)] + Nent(x+1,t+1)

Inact(x+1,t+1) = Reg(x+1,t+1) - Act(x+1,t+1)

Cont(x+1,t+1) = Act(x+1,t+1) x Dens(x+1,t+1)

1.1.3 Transition from active to pensioners (demographic part) The transition from active (or inactive) to pensioners is simulated by using transition probabilities:

VACT = Act(x,t) x Invrate(x,t)

DACT = Act(x,t) x Mort(x,t)

RACT = Act(x,t) x Retrate(x,t)

ZACT = S[Act(x,t)] = Act(x,t) - VACT - DACT - RACT

From these, the number of new pensioners is calculated:

NINV#(x+1,t+1) = VACT

NRET#(x+1,t+1) = RACT

NSURV#(s(x),t+1) is calculated by a subroutine (note that survivors also occur on the death of pensioners).

1.1.4 Transition from active to pensioners (financial part)

The new pensioners' pensions are calculated by using the assumed acquired credit and assumed past salary.

The active population, as well as the inactive population, are classified by their acquired past credits and by income level. In the simulation, the credit distribution is constructed by taking account of an influx of contributions paid in that year and an outflux of contributions withdrawn in that year.

The pensions of newly awarded pensioners can be estimated by applying the eligibility conditions for pension and the pension formula to all subgroups of population by credit and salary (and their correlation).

NINV$(x+1,t+1)

NRET$(x+1,t+1)

At the same time, the credit which turned into benefits should be deducted from the active person's cumulated past credits.

1.1.5 Transition from active to active

If an active worker stays active one year, then the credit will increase by the contributed period.

Cred(x+1,t+1) = Cred(x,t) + Dens(x,t)

Bal(x+1,t+1) = Bal(x,t) x [1+Int(t)] + Contrate(t)Sal(x,t) x Dens(x,t) x Int(t)/2

where:

Cred(x,t) = average acquired credit

Bal(x,t) = average balance of individual savings accounts (this is used for the valuation of defined-contribution schemes)

Int(t) = Interest rate

Cont rate (t) = Contribution rate

Sal (x,t) = average insurable salary

1.1.6 Transition from pensioners to pensioners

This transition can be simulated as follows:

Pens#(x+1,t+1) = Pens#(x,t) x [1q(x,t)] + NPens#(x+1,t+1)

Pens$(x+1,t+1) = Pens$(x,t) x [1q(x,t)] x [1+adj(t)] + NPens$(x+1,t+1)

where:

Pens#(x,t) = Number of pensioners

Pens$(x,t) = Amount of pension benefits

 

1.2 Indicators for long-term financial developments

1.2.1 Basic equation of the financing

Let

F_t : Reserve at the end of year t.

I_t : Annual total income in year t (including interest income).

P_t : Annual contribution income in year t (excluding interest income).

R_t : Annual interest income in year t.

S_t : Annual expenditure in year t.

G_t : Total insurable earnings in year t.

p_t : Contribution rate in year t.

i _t : Interest rate in year t

Then, the following accounting identities hold:

By using the above equations, the fund operation is simulated year-by-year.

From those equations, it follows that

, or

where, v_t = (1+ i_t)^-1.

This is a recursion formula with respect to {F_t}; it describes the evolution of the fund in each year. The solution is as follows:

where,

and,

1.2.2 Major financial systems

 

This contribution rate may be expressed as a product of two factors:

where, d_t is called the "system demographic dependency ratio", and r_t, the "system replacement ratio" such that:

d_t = (number of pensioners in year t) / (number of active contributors in year t)

r_t = (average pension in year t) / (average insurable earnings in year t).

(2) Level contribution rate The level contribution rate (or discounted average premium) for the period [n,m] is given:

By tending m to ad infinitum, the general average premium can be obtained.

(3) Contribution rate keeping target reserve ratio

Let a_t= F_t-1 / S_t call "the reserve ratio" which measures the reserve in terms of annual expenditure. Suppose the target value of the reserve ratio is given a₀ , the contribution rate under which the reserve ratio attains the target value at the end of the period [n,m] is given:

If we substitute a₀=0 in the above equation, we obtain the formula of the Level Premium over the period [n, m-1].

(4) Contribution rate keeping target balance ratio Let b_t= (S_t P_t) / R_t call "the balance ratio" which indicates the income/expenditure situation. Suppose the target value of the balance ratio is given b₀, the contribution rate under which the balance ratio attains the target value at the end of the period [n,m] is given:

If we substitute b₀=1 in the equation above, we obtain the formula of the so-called Thullen's Scaled Premium, which enables the fund to be stable (i.e. balance is zero) at the end of the period.

 

2. Model structure

File structure

The ILO-PENS consists of the following set of Excel files.

2.1 Input files

Two kinds of input files need to be prepared. One is the economic-demographic file (hereafter referred as EconDem.xls) that contains the economic factors and mortality rates, and is used in common with the different groups. The other file is the group data file (the N-th group data file is referred to as GroupN.xls). It contains the statistical data of a specific group of the covered population. This file has to be prepared for each different group.

2.2 Input making files

In order to facilitate the preparation of the input data, several additional files have been developed. They are: Famstr.xls, Covpop.xls, Penpop.xls, and Credist.xls.

These input-making files need not necessarily be used, if sufficient data are available.

2.3 Projection programme file

The essential part of the long-term cost estimate is conducted in the projection programme file ILOPENS.

2.4 Base files

For converting the output Text files (.txt) into Excel files (.xls), three framework files have been prepared: RbaseT.xls, RbaseX.xls, RbaseTC.xls.

2.5 Output files (result files)

(i) The text files

As direct outputs of the projection programme, four kinds of text result files are generated for each group.



(ii) The Excel files by group

Next, each text file is converted into an Excel file by using the base files mentioned above.



(iii) The Excel file of the total group

Finally, the total file is created by consolidating all Groupwise Excel files.

2.6 Long-term account file

After the estimated values of the expenditures and the insurable base are obtained, the next step is to make long-term accounting exercises and to determine the future contribution rate. For this purpose, the long-term account file (referred as AccountG.xls) has been developed.

The inter-relationship between these files is shown in Figure II.2-1.

Figure II.2-1 : General file flow of ILO-PENS

Part III. Application

Introductory remarks

Part III of this technical guide focuses on the application of ILO-PENS.

In the first phase, the required data need to be collected. The list of data requirements are explained in section 1.

During the second phase, the collected data are analysed and put into the proper format required by the model. This process is explained in section 2. To compile the standardized input results of the relevant models in the family (e.g. population projection) might be needed. For the details of other models, please refer to the respective technical guides.

The third phase consists of the actuarial projections, which are explained in section 3. This process is the pivotal point of the actuarial valuation process. Section 3 describes the main modules of the projection programme (written in VBA); it indicates the modification of the program, and it explains how to run the projection projections.

The final and fourth phase is the analysis of the results of projections. Section 4 deals with technical aspects of output file making. In section 5, the method of analysis and of reporting is explained.

The general workflow of the actuarial valuation, using the ILO model family, is illustrated overleaf.

General flow of actuarial valuations

 

1. Data requirements

The actuarial projection requires a considerable amount of data input. Therefore, data collection is a crucial part of the preparation stage of the actuarial valuation. In this chapter, we give a list of the statistical and financial data that are to be collected.

1.1 General statistics

The following is the list of data that can be obtained from general statistical publications. The data listed in this section significantly overlaps the data required for the other models in the ILO model family, in particular ILO-POP, ILO-LAB, and ILO-ECO. It would be preferable, therefore, that these data be obtained in a timed series (past five years or longer). Reference books are cited in case no source is available.

(i) Demographic data - National population (by sex and age)

- Life table (mortality table, by sex)

- Life expectancy (by sex)

- Total fertility rates (by age group)

- Migration statistics

Reference publications - UN "World Population Prospects" (published every two years)

- UN "The sex and age distribution of world populations" (published every two years)

- UN "Model life tables" (1982)

(ii) Labour statistics - Economically active population (by sex and age, by sector, if necessary)

- Employed population (by sex and age, by sector, if necessary)

- Unemployment rate (by sex and age)

- Average wage (by sex and age)

- Legal minimum wage

Reference publications - ILO "Economically active population 1950-2010" (fourth edition, 1996)

(iii) Macroeconomic and financial statistics - GDP and its growth rate (real and nominal)

- Rate of inflation

- Rate of interest

- Government's expenditure on social security programmes

Reference publications: - WB "World development report" (every year)

- UNDP "Human development report" (every year)

(iv) Household/family statistics - Proportion of married

- Age difference between husbands and wives

- Average number of children

- Age difference between children and parents

- Distribution of income

(v) Forecasts - Population projections

- Labour force forecast

- Forecast or outlook of macroeconomic indicators

- National development plan (if it exists)

Reference publications - UN "World Population Prospects" (published every two years)

- UN "The sex and age distribution of world populations" (published every two years)

- ILO "Economically active population 1950-2010" (fourth edition, 1996)

1.2 Scheme-specific data and information

In addition to the general national statistics, data which indicate the characteristics of schemes are necessary. These data are to be collected on an institutional basis. The blueprints of scheme statistics are given in the Appendix.

(i) Information on legislation - Pension formula (benefit rate)

- Contribution rate

- Eligibility condition

- Minimum and maximum insurable earnings

- Funeral grant

- Adjustment factor (e.g. in line with wage or CPI)

Reference publications: - United States SSA "Social security programs throughout the world" (every two years)

(ii) Data on the scheme- Registered population (total insured population):

- Newly registered persons:

- Active population (current insured population):

- Inactive population (latent insured population):

- Contributors:

- Existing pensioners: (Old-age, invalidity and survivors)

- Newly awarded pensioners (Old-age, invalidity and survivors)

- Financial statements, including the revenue and expenditure statement, and the balance sheet.

- Portfolio of the invested asset:

2. Input preparation

In this chapter, we explain the methods of how to create the input files needed for the projection programme.

2.1 Input data

The input data are comprised of the base data and the assumptions of future developments.

The base data concern the statistics of the base year of projection, which include the age and contribution structure of the covered population, and the age and payment structure of the pensioner population. These data are to be collected.

The assumptions of future developments concern the macro-economic factors (e.g. GDP, CPI, salary increase, etc.), the future coverage, and the actuarial assumptions (e.g. mortality rates, entry rates into invalidity, etc.). These data are to be assumed or derived from the results of the other models of the model family.

2.2 Input data files

Input data are to be prepared in an appropriate format. For the projection programme, the following two kinds of input files need to be prepared.

- the economic-demographic file; and,

- the group file(s).

Usually, the total covered population is composed of several groups that have different characteristics (e.g. male/female, public/private). A projection is done for each group (legislation might provide different treatment according to the group, e.g. normal retirement age, pension formula, eligibility condition, etc.).

The group file contains information specific to a certain group; therefore, this file should be prepared for each group. (The number of the groups is limited to 10.) Conversely, the economic-demographic file contains the information which is in common to all groups.

Two Excel files were prepared as the format framework of the input data files, the formats of which are compatible with the projection programme. They are called EconDem.xls, and GroupN.xls. The contents of these input files are shown in Figure III.2-1 on the following page.

2.2.1 The economic-demographic file

Contents of the file

The economic-demographic file, EconDem.xls, contains the following worksheets:

- Econ : Economic factors

- MortM : Mortality rates for males

- MortF : Mortality rates for females

The explanation for each worksheet is given below.

Figure III.2-1 : Contents of the input files

Econ worksheet

The format of the worksheet ECON is shown in Figure III.2-2. The following data need to be input:

- Annual increase rate of the average earnings

- Annual increase rate of pensions in payment

- Annual interest rate

- Average legal minimum wage

- Average minimum insurable earnings

- Average maximum insurable earnings

- Amount of funeral benefit

- Contribution rate

- Contribution collection rate

Figure III.2-2

(1) Annual increase rate of the average earnings

Description: The annual increase rate of the average earnings in year T is the increase rate of the annual average earnings of the covered population in year T compared to the previous year (T1). These data are used for reevaluating the past salary to the present salary level in the calculation of the reference salary of pensions.

(2) Annual increase rate of pensions in payment

Description: The annual increase rate of pensions in payment in year T is the adjustment rate of the pension in payment in year T compared to the previous year (T1).

(3) Annual interest rate

Description: The average annual rate of return on the investment of the overall reserve. The same rate is used to calculate the interest on the income/payment associated to the cash-in/out-flow during that year.

(4) Average legal minimum wage

Description: The average amount of the legal minimum wage in year T. The average is taken from the beginning to the end of the year T.

(5) Average minimum insurable earnings

Description: The average amount of the minimum insurable earnings in year T. The average is taken from the beginning to the end of the year T.

(6) Average maximum insurable earnings

Description: The average amount of the maximum insurable earnings in year T. The average is taken from the beginning to the end of the year T.

(7) Amount of funeral benefit

Description: The annual average amount of the funeral benefit in year T. The average is taken from the beginning to the end of the year T.

(8) Contribution rate

Description: The annual average rate of contribution in year T. The average is taken from the beginning to the end of the year T.

(9) Contribution collection rate

Description: Ratio of the amount of contributions which are actually collected to the amount of contributions which should be paid in year T. The latter amount is given as the product of the total insurable earnings and the contribution rate in year T.

MortM and MortF worksheets

The format of the worksheets MortM and MortF is shown in Figure III.2-3 on the next page. The following data needs to be input:

- Mortality rates for male population

- Mortality rates for female population

For each group of the covered population, one will need to select one of these two rates in the projection programme. For the mortality rates of the covered population and invalids, see the explanation of the programme submodule Projection().

(1) Mortality rates for male population

Description: The probability that a male life of exact age X in year T will die before reaching his (X+1) birthday.

(2) Mortality rates for female population

Description: Similar to the above (for females).

Figure III.2-3

2.2.2 The group file

Contents of the file

The group file, GroupN.xls, contains the following worksheets:

- CovPop : Covered population

- Dens : Density factor

- SalL : Insurable earnings (low income level)

- SalM : Insurable earnings (medium income level)

- SalH : Insurable earnings (high income level)

- Family : Family structure

- Pasts : Past credits

- Pens : Pensioners existing in the base year

- Inv : Entry rates into invalidity

- Inact : Inactive population by past credits

- REent : Re-entrance rates

- Leaving : Leaving rates

The explanation for each worksheet is given below.

CovPop worksheet

The format of this worksheet is shown in Figure III.2-4. The data of the projected number of the covered population by age need to be input.

Remark: See also the explanation of the density factor and the explanation of the programme submodule Projection().

Figure III.2-4

Dens worksheet

The format of this worksheet is shown in Figure III.2-5.

The data on the assumed rates of the density factors by age need to be input.

Remark: The density factor refers to the average completeness of the working time (for a full-time worker the density is 100%, for a part-time worker or a worker who has an intermittent unemployment period the density is less than 100%). This does not take into account the compliance of the contribution collection or under-declaration of the insurable earnings. See also the description of the contribution collection rate and the insurable earnings.

SalL, SalM, SalH worksheets

Figure III.2-6 below illustrates the format of these worksheets. The following data on the projected average insurable earnings by income group and by age need to be input.

Figure III.2-5

Figure III.2-6

The annual average insurable earnings are calculated by income level. For each year and each age, the insurable earnings are calculated for the three percentile groups. The low income level is the lowest 30 percentile value of the distribution of the insurable earnings. The high income level is the highest 30 percentile value. The medium income level represents the middle income band, i.e. from 30 percentile to 70 percentile values.

Family worksheet

The format of this worksheet is shown in Figure III.2-7. The following data , which are necessary for estimating the survivors' pensions, need to be input:

- The average probability of having spouse (married).

- The average age of spouse.

- The average number of children.

- The average age of children.

- The continuing probability of the orphans' pensioner.

Figure III.2-7

(1) The average probability of having spouse

Description: The percentage of the covered persons whose spouse is eligible for the widow(er)s' pensions.

(2) The average age of spouse

Remark: In the model, the future change in these ages are not considered. In the model, a certain dispersion of the average age is made. See the explanation of the programme submodule SDIST().

(3) The average number of children.

(4) The average age of children

Remark: In the model, the future change in these ages are not considered. In the model, a certain dispersion of the average age is made. See the explanation of the programme submodule SDIST().

(5) The continuing probability of the orphans' pensioner

Pasts worksheet

Figure III.2-8 below shows the format of this worksheet. The data on the distribution of the past credits by age in the base year need to be input.

Figure III.2-8

Range: For the insurable ages (min: 15, max 69)

Pens worksheet

Figure III.2-9 below illustrates the format of this worksheet. The following data need to be input:

- The number of pensioners existing in the base year by benefit and by age

- The average amount of monthly pension of the pensioners above by benefit and by age

Figure III.2-9

Description: The average number of pensioners who receive benefits during the base year needs to be collected for each type of benefit and for each age. The average amount of pensions paid during that year also needs to be collected.

Normally, the data are taken as of a certain time point. If the timing is well-chosen (e.g. mid-year), then one can assume that the number of pensioners at that time would be in the neighbourhood of the average number of pensioners. In the same way, one can assume that the average amount of pensions at that time could represent the annual average of the pensions. It must be noted that the adjustment of pension is assumed to be taking place at the beginning of the year. If this is not the case, the average pensions should be modified accordingly.

To be consistent with the macro data that are found in the financial statements, one should always calculate the total amount by summing up the product of the number of pensioners and their average pension over age and compare the result with the macro figure in the financial

statements. If these two figures do not match, then it is suggested that the average pensions be checked.

Inv worksheet

Figure III.2-10 shows below the format of this worksheet. The data on the age-specific entry rates into invalidity for each year need to be input.

Figure III.2-10

Description: The entry rates into invalidity of the aged X in year T.

Range: From age 15 to age 99; from the base year to the end of the projection year

Inact worksheet

The data on the number of the inactive population by age and by past-credit in the base year need to be input.

Range: For the insurable ages (min: 15, max: 69)

REent worksheet

The format of this worksheet is shown in Figure III.2-11. The age-specific re-entry rates from inactive population into covered population for each year need to be input.

Figure III.2-11

Range: For the insurable ages (min: 15, max: 69)

Leaving worksheet

Figure III.2-12 below shows the format of this worksheet. The age-specific leaving rates from covered population into inactive population for each year need to be input.

Figure III.2-12

2.3 Compilation of the input files

2.3.1 Using ancillary models

To complete the input files, some data can be drawn from the results of other ILO models. The use of these results does not simply facilitate the compiling of input files, but is necessary to keep the consistency of the whole modelling process.

ILO-POP provides the future mortality rates used for the national population projection, which are to be imported to the sheets MortM and MortF.

ILO-ECO would provide the assumptions of the macroeconomic indicators which are to be imported into sheet Econ.

ILO-DIST would provide the results of insurable salary with respect to three income groups, which results are to be imported into sheets SalL, SalM, and SalH. In addition, the assumption of the wage increase is in accordance with the economic assumption.

ILO-LAB would provide the labour force population and employed population which are to be used as the basis of the projection of the insured population.

2.3.2 Using input making files

In order to facilitate the preparation of the input files, the following set of Excel files, called 'Input making files', have been developed.

- Covpop.xls

- Famstr.xls

- Penpop.xls

- Credist.xls

COVPOP.xls

This consists of the following 8 worksheets:

- InitialM - InitialF

- EmplpopM - EmplpopF

- CovratesM - CovratesF

- CovpopM - CovpopF

The postscripts M and F refer to males and females, respectively. For simplicity, explanations are given for one sex in the following.

(1) InitialM, InitialF

Figure III.2-13 shows the format of these worksheets. The employed population are given in sheets EmplpopM and EmplpopF. By linking cells, the age-specific employed population in the base year is given in column H. The 5-year age-class data are calculated in column C.

Figure III.2-13

One should find the covered population in the base year in the data collection. Two cases are distinguished. In the first and usual case, if one has only the 5-year age-class data, then one should input these data in column B. To interpolate these data into age-specific data, three options are given: the Sprague formulae, the uniform distribution, and the linear interpolation. One should select one of the interpolation methods in the drop-down bar in cell G3. Then the age-specific results are calculated in column H. In the second case, if one can obtain the single-age data, then one should input in column G by overwriting the formulae already written in those cells in that column. In this case, the linkage between column B and G is lost, therefore one should copy the formulae in column C to column B to have 5 year age-class summary.

Of the three interpolation methods, the Sprague formulae are most widely used. However, these formulae could produce negative values, especially in the end points. A check is made in cell G1. If there are some negative values in column G, it says "Negative value found" and the negative numbers are indicated in red; if not, it says "No negative value". If negative values are observed, one should modify them so that the total of the class to which they belong does not change. It could be suggested that one should use the uniform distribution for these classes; the results are found in column O.

It should be also noted that if one uses the linear interpolation, then the total of the interpolated values is not necessarily equal to the original total value.

The coverage rate in the base year is calculated as a result for both single-age and 5-year age-class. The results are shown in columns D and I, respectively. By its definition, the coverage rates should range between 0% to 100%. By virtue of the negative value check, the coverage cannot be negative. To check the other possibility, another check is done in the cell G2. If there are values bigger than 1 in column I, it says "Over coverage"; if not, it says "No over coverage". If some coverage rates are bigger than 100%, one should modify the covered population in column G (or B) so that the coverage is less than 100%, without changing the total covered population; or, one should check the employed population. Alternately, one can admit a coverage rate higher than 100% due to the inconsistency of the data source.

(2) EmplpopM, EmplpopF

The format of these worksheets is shown in Figure III.2-14. Normally, the projection of the employed population is done by using ILO-LAB. The age-specific results should be imported in the range B19:CX73. Then, they are abridged into the 5-year age class in the range B7:CX18.

Figure III.2-14.

(3) CovratesM, CovratesF

The format of these worksheets is shown in Figure III.2-15. The age-specific coverage rates should be input in the range C19:CX73. The 5-year abridged rates are shown in the range B7:CX18. They are calculated by dividing the abridged results of the CovpopM- F by the 5-year employed population.

Figure III.2-15

(4) CovpopM, CovpopF

The covered population is calculated by applying the coverage rates to the employed population in the range C19:CX73. The format of these worksheets is shown in Figure III.2-16.

Then, they are abridged into the 5-year age class in the range B7:CX18. The results in the range C19:CX73 are to be imported to the worksheet CovPop.

Figure III.2.16

FAMSTR.xls

The format of this worksheet is shown in Figure III.2-17. This file should normally be used as a standard in case only 5-year age group data are available. If one inputs the 5-year abridged data in the input columns (males and females, respectively), then the linearly interpolated results are given in the output columns. The negative value check is done in cells in row 1. If negative values are found, it will say "Negative value!"; otherwise "OK".

Figure III.2-17

PENPOP.xls

This file is used as a standard tool to interpolate the 5-year age group data. There are three options for interpolating the number of pensioners of the 5-year age-class into single-age: the Sprague formulae, the uniform distribution, and the linear interpolation. The uniform distribution is always applied for the average pensions.

The input making file PENPOP.xls contains the following 5 worksheets:

- INPUT

- Sprague

- Uniform

- Linear

- COPY

(1) In the sheet 'INPUT', one should input the 5-year age-class data. The format of this worksheet is shown in Figure III.2-18.

(2) In the intermediate sheets 'Sprague', 'Uniform', 'Linear', the interpolations are done.

(3) In the sheet 'COPY', the interpolated data are found. The format of this worksheet is shown in Figure III.2-19. One should select the appropriate interpolation method in the drop-down bars (cells B3, D3, F3, H3). The standard setting is to use the Sprague for the old-age, invalidity and widow(er)s' pension and to use the uniform distribution for the orphans' pension. The negative value check is done in the first row; the negative values are shown in red. The total checks are done in the second row.

The results should be imported in the worksheet Pens of the input file.

Figure III.2-18

Figure III.2-19

CREDIST.xls

This file creates the distribution of the past credits by assuming the normal distribution; therefore, the average and the standard deviation need to be assumed.

The file CREDIST.xls consists of the following 3 worksheets:

- Input

- Normdist

- Realdist

(1) Input

The format of this worksheet is shown in Figure III.2-20. In this sheet, one has to input the following data in the base year for each age:

- the average years of past contributions (column C),

- the standard deviation of credit distribution (column D),

- the number of covered population (column B).

Figure III.2-20

The number of the covered population is already given in COVPOP.xls. The average years of the past contribution should be collected. Normally these data should be available. Therefore, the remaining parameter is the standard deviation of the credit distribution. In fact, there is no standard theory to estimate this factor. Therefore, it should be assumed in an ad hoc way. One possible way would be to set the standard deviation equal to a certain percentage of the average.

(2) Normdist

In this sheet, by using the two determinant parameters assumed in the sheet 'Input', the credit distribution is calculated. The format of this worksheet is shown in Figure III.2-21.

The methodology is explained as follows:

Figure III.2-21

Let:

XX : X15, i.e. age15

I : Year of the past contributions (I=1,2,3,...,55)

f(I) : The percentage of covered persons whose past contribution year is between I1 and I.

N(;,) : Probability density function of the normal distribution of average: and standard deviation:.

AN(;,) : Distribution function of the normal distribution of average: and standard deviation:.

From the assumption, we know the values of _x and _x for each X. Then, the credit distribution f_x(I) is calculated as follows:

(1) f_x(I) = AN(1;_x,_x) (for I = 1)

(2) f_x(I) = AN(I;_x,_x) AN(I1;_x,_x) (for 2 I XX)

(3) f_x(I) = 1 AN(XX;_x,_x) (for I = XX+1)

(4) f_x(I) = 0 (for XX+2 I 55)

(1), (3), (4) are necessary to truncate the tails of the distribution which lie outside the appropriate range of the contribution year.

The results should be imported into the worksheet Past of the group file.

(3) Realdist

The number of the initial covered population by credit year is calculated by applying the distribution to the total covered population by age. As a result, the global credit distribution is obtained.

 

3. Projections

In this chapter, we explain the structure of the projection programme, how to modify the programme, and how to run the programme. A printout of the programme of the most important part of the model is attached as Annex I.

3.1 General

Overview of the programme

- The projection programme is referred to as ILOPENS.xls. It is written in Excel Visual Basic for Applications (Excel VBA).



- To make projections, the input files are needed. The explanation of the input files is given in 3.3.



- The maximum years of projection is set at 100 years.



- The projection results are stored in the text files by each group. These text files are then converted into Excel files and consolidated into the total result file. The explanation of the result files is given in 3.5.

Contents of the file

The contents of ILOPENS.xls is shown in Figure III.3-1. Of the worksheets, two are normal Excel spreadsheets (Cover and InputS); the rest are filled with the modules programmed in VBA (shown in double-line boxes in the Figure). Each module worksheet contains one or several modules. The overall programme is comprised of these modules, and they are grouped with respect to their functions and are stored in different worksheets.

In the following sections we shall explain, in detail, the modules relating to the projection; we shall call them simulation modules. For the explanation of the worksheets Cover and InputS, see III.3-4. The modules stored in worksheet 'menu' are used for making result files; its explanation is also given in III.3-4.

3.2 The structure of the simulation modules<

3.2.1 The module flow

The methodology of the simulation is explained in Part II.1. The projection model is a realization of the simulation algorithm shown in Figure II.1-1.

Figure III.3-1 Contents of the Projection files

Figure III.3-2 Module flow of ILO-PENS

The flow chart of the projection programme is shown in Figure III.3-2. In that Figure, each box indicates a simulation module; the broad line indicates the main flow; the normal horizontal line indicates that the module on the right-hand side is called in the main module in the left-hand side; the dotted line indicates that there is an iterative process (loop) with respect to a certain variable.

There are three main modules on the main flow. They are: ILOPENSION(), VALUATION(), and Projection(). We shall define the depth of a module as follows. The depth of ILOPENSION() is 0. If a module is called in a module of depth n, the depth of that module is (n+1). For example, Preparation() is called in VALUATION(); and, the depth of VALUATION() is equal to 1, since it is called in ILOPENSION(). Therefore, the depth of Preparation() is equal to 2. The maximal number of the depth is 5 (RetPos() and PensionR()).

Five loops are found in the figure. The biggest loop is with respect to the group. Inside this loop (i.e. for each group), there are loops of the year and of the age. Inside the age-loop, there are loops with respect to credit distribution. For the old-age pensioners, there is another loop of the income level.

Module flow (summary)

(1) Starting from ILOPENSION(), it reads the economic-demographic file, then links VALUATION().



(2) The beginning of the group-loop.



(3) In VALUATION(), it reads the data in the base year (T=0) then links Projection().



(4) The beginning of the year-loop.



(5) The beginning of the age-loop.



(6) In Projection(), it calls modules to simulate the transition of one year to the next year.



(7) Go to the next age (=> (5))



(8) In Projection(), it calls modules for summing up with respect to age and writing results to text files



(9) Go to the next year (=> (4))



(10) Go to the next group (=> (2))

3.2.2 Descriptions of the modules

In this section, detailed descriptions of each simulation submodule are given.

(1) Submodule: ILO-PENSION() Worksheet: Main_1

Links (calls): ReadEconDem(), VALUATION()

Operation:

1) Defining variables.

2) Setting maximum age (=69) and minimum age (=15) of coverage and the ultimate age of life span (=99).

3) Reading general information from the input worksheet "InputS" (link: ReadEconDem()).

4) Reading economic factors and future mortality tables from the economic-demographic file (link: ReadEconDem()).

5) Controlling the simulation by group (link: VALUATION())

(2) Submodule: VALUATION()

Worksheet: Main_2

Links (called from): ILOPENSION()

Links (calls): Preparation(), SUMoK(), SUMoX(), FILEW(), Projection().

Operation:

1) Reading output file information of the group.

2) Preparing the names of four text files. (See the convention of the output files naming)

3) Reading the data of the initial year (T=0) (link: Preparation())

4) Summarizing the data of the initial year (link: SUMoK(), SUMoX())

5) Opening the output text files.

6) Writing the results of the initial year on the text files (link: FILEW())

7) Making the projection (link: Projection()).

8) Closing the output text files.

9) Erasing the variables.

(3) Submodule: PROJECTION()

Worksheet: Main_3

Links (called from): VALUATION()

Links (calls): Finsal(),Balance(), InsSurv(), InsInv(), InsIns(), InvSurv(), RetSurv(), Invalids(), Retireds(), Survivors(), SUMoN(), SUMoK(), SUMoX(), FILEW().

Operation:

1) The simulation by year (T=1 to TMAX).

2) Reading data of the year T.

3) The simulation by age (X=(Jmax1) to Jmin, step 1)

4) Preparing the average insurable earnings.

5) Preparing the survivors' components.

6) Calculating the final average salary (link: Finsal())

7) (option) Calculating the accumulated value of contributions (link: Balance()).

8) Decrement from active population.

9) Decrement from inactive population.

10) Transition from the insureds to the survivors pensioners (link: InsSurv())

11) Transition from the insureds to the invalidity pensioners (link: InsInv())

12) Transition from the insureds to the insureds or the old-age pensioners (link: InsIns())

13) Transition from the invalidity pensioners to the survivors pensioners (link: InvSurv())

14) Transition from the old-age pensioners to the survivors pensioners (link: RetSurv())

15) Next age X1 (Return to (3))

16) Calculating total number of newly awarded pensioners over categories (link: SUMoN).

17) Transition of the existing pensioners in the last year and aggregating the newly awarded pensioners for the invalidity pension (link: Invalids())

18) Transition of the existing pensioners in the last year and aggregating the newly awarded pensioners for the old-age pension (link: Retireds())

19) Transition of the existing pensioners in the last year and aggregating the newly awarded pensioners for the survivors pension (link: Survivors())

20) Calculating total number of the existing pensioners over categories (link: SUMoK()).

21) Calculating total number of the existing pensioners over ages (link: SUMoX()).

22) Writing the results of the initial year on the text files (link: FILEW())

23) Next year T+1 (return to (1))

(4) Submodule: INSINS()

Worksheet: Insured

Links (called from): Projection()

Links (calls): InsRet()

Operation:

1) Calculating the credit distribution of the ZACT and ZNACT.

2) Transition between the active and inactive population

3) Calculating the sum of the inactive population.

4) If the sum is positive then consider the transition to the old-age pension (link: InsRet()).

5) Calculating the sums of the active and inactive population.

6) Adjusting the credit distribution of the active population by taking into account the contribution density in the relevant year.

7) Adjusting the credit distribution of the inactive population.

(5) Submodule: FINSAL()

Worksheet: Final_Sal

Links (called from): Projection()

Return values: FINS(I, JKC) (I=1 to Imax; JKC=0 to 3)

Operation:

1) Calculating the final average salary of the last IE years for each credit and each income level. See the note on final salary.

(6) Submodule: BALANCE()

Worksheet: Final_Sal

Links (called from): Projection()

Return values: BAL(I, JKC) (I=1 to Imax; JKC=0 to 3)

Operation:

1) Calculating the accumulated values of the contributions for each credit and each income level. See the note on final salary.

(7) Submodule: PENSIONR()

Worksheet: Pens_formula

Links (called from): InsRet()

Main input values: CDT: credit, I : credit year, JKC: income level

Return values: P: pension amount, JPR: subcategory of old-age pensions

Operation:

1) Calculating the old-age pension by pension formula for each credit I and for each income class JKC. See the note on modification of the pension formula.

2) Check the maximum pension.

3) Check the minimum pension and judge whether or not the original pension is lower than the minimum pension. See the note on the estimation of the minimum pensioners.

(8) Submodule: PENSIONI()

Worksheet: Pens_formula

Links (called from): InsInv()

Main input values: CDT: credit (including additional years), I : credit year

Return values: P: pension amount, JPI: subcategory of invalidity pensions

Operation:

1) Calculating the invalidity pension by pension formula for each credit I. (Unlike old-age pension, income class is not considered). See the note on modification of the pension formula.

2) Check the maximum pension.

3) Check the minimum pension and judge whether or not the original pension is lower than the minimum pension. See the note on the estimation of the minimum pensioners.

(9) Submodule: PENSIONS()

Worksheet: Pens_formula

Links (called from): InsSurv()

Main input values: CDT: credit (including additional years), I : credit year

Return values: P: pension amount

Operation:

1) Calculating the survivors pension on the death of the active persons by pension formula for each credit I. (Unlike old-age pension, income class is not considered). See the note on modification of the pension formula.

2) Check the maximum pension.

3) Check the minimum pension and judge whether or not the original pension is lower than the minimum pension. See the note on the estimation of the minimum pensioners.

(10) Submodule: RETPOS()

Worksheet: Eligibility

Links (called from): InsRet()

Main input values: CDT: credit (including additional years), X+1 : age

Return values: GER: result of the examination of the eligibility condition

Operation:

1) Check the eligibility condition for the old-age pension.

(11) Submodule: INVPOS()

Worksheet: Eligibility

Links (called from): InsInv()

Main input values: CDT: credit (including additional years), X+1 : age

Return values: GEI: result of the examination of the eligibility condition

Operation:

1) Check the eligibility condition for the invalidity pension.

(12) Submodule: SURVPOS()

Worksheet: Eligibility

Links (called from): InsSurv()

Main input values: CDT: credit (including additional years), X+1 : age

Return values: GES: result of the examination of the eligibility condition

Operation:

1) Check the eligibility condition for the survivors' pension on the death of the active persons.

(13) Submodule: INSRET()

Worksheet: Old-age

Links (called from): InsIns()

Links (calls): RetPos(), PensionR()

Main input values: B(I): the number of inactive persons (after considering the transition from active population), X+1 : age

Main intermediate values: RACT, RRACT, ARET, ARES, AREU

Return values: NPR: newly awarded pensioners, AGRT: beneficiaries of grant

Operation:

1) Calculating the ad hoc correlation between credits and income levels. See the note on the correlation.

2) For each credit year I and for each income level JKC, simulating the old-age pensions and grants. (link: RetPos(), PensionR() ). See the note on the transition.

(14) Submodule: RETIREDS()

Worksheet: Old-age

Links (called from): Projection()

Main input values: NPR: newly awarded pensioners, Q: mortality rates, RINFB : rate of increase of benefits

Return values: RET: total pensioners

Operation:

1) For each age XR and for each category K, calculating the death of the pensioners and aggregating the newly awarded pensioners.

2) Catching up the minimum pensions.

(15) Submodule: INSINV()

Worksheet: Disabled

Links (called from): Projection()

Links (calls): InvPos(), PensionI()

Main input values: F(I, XX), Fg(I, XX), VACT, VNACT, PNINV

Return values: NPI: newly awarded pensioners, AGRT: beneficiaries of grant

Operation:

1) For each credit year I, simulating the old-age pensions and grants. (link: InsPos(), PensionI() ).

(16) Submodule: INVALIDS()

Worksheet: Disability

Links (called from): Projection()

Main input values: NPI: newly awarded pensioners, QI: mortality rates, RINFB : rate of increase of benefits

Return values: DIS: total pensioners

Operation:

1) For each age XI and for each category K, calculating the death of the pensioners and aggregating the newly awarded pensioners.

2) Catching up the minimum pensions.

(17) Submodule: InsSurv()

Worksheet: Wid_Orph

Links (called from): Projection()

Links (calls): SurvPos(), PensionS(), SDIST()

Main input values: DACT, DNACT, F(I,XX), Fg(I,XX), PFUNB

Main intermediate values: DDACT, DDD1, DDD2, JCG, P, PDW

Return values: AFUNB, AGRT, (NPW, NPO)

Operation:

1) Calculating the funeral benefit

2) For each credit I, simulating the survivors' pensions. (link: SurvPos(), PensionS(), SDIST() )

(18) Submodule: RetSurv()

Worksheet: Wid_Orph

Links (called from): Projection()

Links (calls): SDIST()

Main input values: RET, Q, PFUNB

Main intermediate values: DRET, DDD1, DDD2, JCG, PDW

Return values: AFUNB, AGRT, (NPW, NPO)

Operation:

1) Calculating the funeral benefit

2) Simulating the survivors' pensions on the death of the old-age pensioners . (link: SDIST())

(19) Submodule: INVSURV()

Worksheet: Wid_Orph

Links (called from): Projection()

Links (calls): SDIST()

Main input values: DIS, Q

Main intermediate values: DINV, DDD1, DDD2, JCG, PDW

Return values: AFUNB, AGRT, (NPW, NPO)

Operation:

1) Calculating the funeral benefit

2) Simulating the survivors' pensions on the death of the invalidity pensioners. (link: SDIST() )

(20) Submodule: SURVIVORS()

Worksheet: Wid_Orph

Links (called from): Projection()

Main input values: NPW, NPO, PW, PO, RINFB

Return values: WID, ORP

Operation:

1) For each age XS and for each category K, calculating the death of the pensioners and aggregating the newly awarded pensioners.

(21) Submodule: SDIST()

Worksheet: Wid_Orph

Links (called from): InsSurv(), InvSurv(), RetSurv()

Main input values: DDD1, DDD2, AVSP, RWP, ROP, AVCH, JCG

Main intermediate values: DISW, DISO

Return values: NPW, NPO

Operation:

1) Ad hoc deviation from average age difference (center=2)

2) For each age JS and for category JCG, summing the newly awarded survivors' pensioners.

3.3 Modification of the programme

For each country, the projection programme needs to be modified to reflect the legislative framework of a specific scheme. Since there are various types of schemes and various reform measures, it is almost impossible to give the full description of all possible modifications.

The programme is, however, divided into submodules that have particular functions, and the submodules with close relationships are grouped in the same worksheet. Therefore, it becomes easier to find the part of the programme where necessary modifications are to be made.

In the following sections, typical changes are set out and technical comments on how to modify the programme are indicated. The eligibility condition, the pension formula, and sensitivity tests are necessary modifications for each scheme. Although the following indications do not cover the full list of modifications, they might provide useful information in the process of modelling. In essence, it must be noted that the success of effective modelling still relies on the concentration and carefulness of the user.

3.3.1 Brief technical guide to the modification of the programme

(1) Eligibility condition

Worksheet: Eligibility

Submodules: RetPos(), InvPos(), SurvPos()

Note: The eligibility conditions according to the legislation of a specific scheme are to be modelled in the above submodules.

(2) Pension formula

Worksheet: Pens_formula

Submodules: PensionR(), PensionI(), PensionS()

Note: The pension formulae according to the legislation of a specific scheme are to be modelled in the above submodules. The following four examples are offered as ideas for when users have to modify the pension formula.

Note that in those submodules the input variables are: CDT, FINS(I, JKC), and the output variable is P.

Example 1 (Defined benefit; earnings-related pension)

Pension is calculated as a certain percentage of the reference salary. Basic benefit rate 40% (payable if the eligibility conditions, e.g. 10 years contribution, are met). Supplementary rate 2% in excess of 25 years' credit.

TT=CDT 25 ' Credit years in excess of 25 years

If TT<0 Then TT=0' Take the maximum of TT and 0

P = 0.01 * (40 + 2 *TT) * FINS(I, JKC)

Example 2 (Defined benefit; flat pension + earnings-related pension)

Pension is calculated as a sum of the flat-rate portion and the earnings-related portion.

Flat-rate portion FPEN(T). Earnings-related portion: 1% of accrual rate for each credit year.

P = FPEN(T) + 0.01 * CDT * FINS(I, JKC)

Example 3 (Defined benefit; different benefit rate with respect to salary bands)

Pension is calculated as a percentage of the reference salary. Different benefit rates are applied to the different portions of reference salary.

Benefit rate: The portion of the reference salary lower than BP1(T) * 90%

+ The portion of the reference salary between BP1(T) and BP2(T) * 30%

+ The portion of the reference salary higher than BP2(T)

The bend points BP1(T) and BP2(T) are given in the worksheet "Econ" in the economic-demographic file.

RSAL1 = 0 : RSAL2 = 0 : RSAL3 = 0

RSAL2 = FINS(I, JKC) BP1(T)

RSAL3 = FINS(I, JKC) BP2(T)

If RSAL2 < 0

Then RSAL1 = FINS(JKC) : RSAL2 = 0 : RSAL3 = 0

RSAL1 = BP1(T) : RSAL3 = 0

Else

RSAL1 = BP1(T) : RSAL2 = BP2(T)

End if

P = 0.9 * RSAL1 + 0.3 * RSAL2 + 0.15 * RSAL3

Example 4 (Defined contribution; annuitisation of individual balance)

Pension is calculated by dividing the final balance of the individual savings account by a certain annuity factor (e.g. 12).

ANN = 12

P = BAL(I, JKC) / ANN

(3) Reference average salary

Worksheet: Final_sal

Submodules: Finsal(), Balance()

Formula of the reference salary for pension

Let

T : Year

X : Age

I : Years of credits

JKC : Income level (1: low , 2: medium, 3: high)

SAL(X, T, JKC) :Salary of age X of income class JKC in year T (exogenous)

Suppose that the reference salary is calculated as the average salary of the final k years of one's working life. Then, FINS(I, JKC)⁽¹⁾, the average salary of the person aged X of the income level JKC with credit I in year T, is given as follows:

IE = min{k, T+5, X15}

adj(T, J) = 1 (if past salaries are not revalued)

= ARINFS(T1) / ARINFS(TJ) (if past salaries are revalued)

ARINFS(T): accumulated values of the salary increase

In deriving the above formula, we assume the following:

(1) There is no transition between different income groups.

(2) This model can refer to the years prior to the date of valuation up to the past 5 years. In case more years are necessary (e.g. career average), a certain modification is needed.

Formula of the accumulated value of the contribution (for defined-contribution scheme)

CONT(T): contribution rate in year T.

(4) Adjustment of pensions in payment

Note: Change RINFB(T) in the economic-demographic file

(5) Normal retirement age

Note: Change NRA(T) in the economic-demographic file. Change the retirement rate R(X)

3.4 How to run the programme

3.4.1 General instruction

When the input files are ready, one should follow the instructions below to run the programme.

(1) Before running the program, one has to specify the general and file information in the input sheet "InputS".

(2) To start the programme, select the command "Calculation" in the special toolbar that appears in the top-left of the interface screen.

(3) To create the result file, select the command "Create result files" in the special tool bar. (This is explained in chapter 5)

3.4.2 Input sheet

The format of the input worksheet, "InputS", is shown in Figure III.3-3. In this sheet, one has to specify the general information and the file information.

After each run of the programme, the file name is automatically printed in cell B1; the date and time is updated in cell B2; and this sheet is printed. The print-out can be used as the job report of the run.

(1) General information

The following information should be specified:

- Title (B4)

- Base year (B5)

- Years of projection (B6): The number of years projection (maximum 100 years).

- Number of groups (B7): The number of groups of the covered population (maximum 10).

- Unit of input average amounts (B8): The unit used for the input average amounts in the worksheets Econ in the economic-demographic file, Pens and SalL-SalH in the group file. One should select one in the drop-down bar. There are 3 choices: in nominal, in thousands, and in millions.

Figure III.3-3

- Basis of input average amounts (B9): The basis used for the input average amounts in the worksheets Econ in the economic-demographic file, Pens and SalL-SalH in the group file. One should select one in the drop-down bar. There are 3 choices: monthly, yearly, and daily.



- Unit of output total amounts (B10): The unit used for the output total amounts in the result files. One should select one in the drop-down bar. There are 5 choices: in nominal, in thousands, in millions, in billions, and in trillions.



- Unit of output average amounts (B11): The unit used for the output average amounts in the result files. One should select one in the drop-down bar. There are 3 choices: in nominal, in thousands, and in millions.



- Basis of output average amounts (B12): The basis used for the output average amounts in the result files. One should select one in the drop-down bar. There are 3 choices: monthly, yearly, and daily.



- Options of the funeral grants and the invalidity and survivors' pensions (B13-B16): One should specify "Yes" or "No" in the drop-down bar. See also technical note 5.

(2) File information

(1) Input files (group files)

According to the number of groups that one inputs in cell B7, the headings of the groups appear in row 19. For each group, the following information should be specified:

- Description (row 20): The name of the group. This is for clarification.

- Sex (row 21): One should select appropriate sex in the drop-down bar.

- Drive and directory (row 22): The name of the drive and directory under which the file is stored. For example I:\pensmod\test.

- File name (row 23): The name of the file. For example Male1. Full name is created in the programme i.e. I:\pensmod\test\Male1.

- Normal retirement age (row 24): See the variable NRA.

- Benefit rates for widows and orphans (row 25 and 26): See the variable RWP, ROP. Note the partition rule of the survivors' pension in the legislation.

(2) Input file (economic-demographic file)

The following information should be specified:

- Description (row 29): The name of the assumption. This is for clarification.

- Drive and directory (row 30)

- File name (row 31)

(3) Result files

The following information should be specified:

- Drive and directory (row 34)

- Prefix of the file name (row 35): The prefix of the name of the result file. See the naming convention of the result file in Part III.4.

- Option for printing (row 37): One should specify in the drop-down bar which kind of results files will be converted into Excel files. This is to save execution time. See the naming convention of the result file in Part III.4.

(4) Base file

The base files are used to convert text files into Excel files. These are Excel files in which the format framework is already prepared. See Part III.4.

The following information should be specified:

- Drive and directory (row 43)

- Name of the file name (row 44)

3.4.3 Special menu-bar

In the worksheets Cover and InputS, a special menu-bar "Actuarial" has been created (see Figure III.3-3. There are two commands "Calculation" and "Creating result files". If one selects "Calculation", then it starts the main module ILOPENSION(). (If one selects "Creating result files", then it starts the result file making module CreateFilesXL().) To see the mechanism of this menu-bar, go to any module worksheet, then select Tools then select Menu Editor.

4. Results

In this chapter, after finishing the projection programme, we explain how these results are put into files.

4.1. The text output files

The general file flow is shown in the Figure III.4-1. The results of the projection programme are generated in the text files by each group. The headlines of these results are shown in the Figure III.4-2. For each item, the demographic result and the financial result are shown together. The programme creates each of these items by age and by year. The programme produces two kinds of output files. One is the result of total ages by year; the other is the result with age breakdown for each year.

In the projection, each year new pensioners are produced and aggregated to the surviving pensioners from the previous year. The programme produces not only the results of the aggregated cases but also the new cases.

Therefore, the programme produces four output text files in total. The names of these files are automatically made by the following convention. This is done in the programme submodule VALUATION(). Let the prefix of the result file be 'aaaa', and the group is the k-th group.

- aaaakT.TXT Total numbers and amounts of the total cases (new cases are aggregated) for each year of projection (T)



- aaaakTN.TXT Total numbers and amounts for only new cases for each year of projection (T)



- aaaakX.TXT Numbers and average amounts of the total cases (new cases are aggregated) by age (X) and by year of projection (T)



- aaaakXN.TXT Numbers and average amounts for only new cases by age (X) and by year of projection (T)

Note that the financial results in the age-specific file are expressed in the average, whereas those in the age-total file are expressed in total.

Figure III.4-1 : Creation of the result files

Figure III.4-2 : Headings of the output files (Raw data)

4.2 The conversion into the Excel files

The next steps to be taken are: (i) to convert the selected result text files into Excel files, and (ii) to consolidate the group results and make the total result file.

These are done in the submodule "menu". To run this, the command "Create Result Files" has been created in the special menu-bar "Actuarial".

4.2.1 The base file

The base files give the framework of the result, as well as main demographic and financial indicators. The contents of these files are shown in Figure III.4-3.

(1) RBASET.XLS

This file is used for the age-total files. This file contains two worksheets: 'RawData' and 'Average'. The format of this file is shown in Figure III.4-4.

(2) RBASEX.XLS

This file is used for the age-specific files. This file contains three worksheets: 'RawData' and 'cohort' and 'module1'. The format of this file is shown in Figure III.4-5.

(3) RBASETC.XLS

This file is used for the consolidated results of all the groups. It contains 7 worksheets:

- RawData : Raw data

- DemogProj : The demographic results (in nominal number)

- DemogRatio : The demographic results expressed as a percentage of the covered population

- FinancialProj : The financial results (total amount)

- FinancialRatio : The financial results expressed as a percentage of the total insurable earnings

- Average : The average amounts

- ReplaceRatio : The average results expressed as a percentage of the average insurable earnings.

4.2.2 The conversion of text files into Excel files

(1) Select the files to be converted

In the worksheet 'InputS' of the projection file, one has to select in the drop-down bar in the cell B37, which asks "which results would you like?". Four choices are given: all results (t, x, tn, xn); year results (t, tn); total cases (t, x); year & total (t).

Figure III.4-4

Figure III.4-5

(2) Conversion

The conversion is made in the submodule "menu". It reads the contents of the text files, and copies them onto the worksheet 'RawData' of the appropriate base file. This process is done for each group.

The same naming convention is applied to the Excel files. The TXT extension is replaced by the XLS extension (e.g. aaaakT.TXT becomes aaaakT.XLS).

(3) Additional worksheet

For the age-total file, the average amounts are automatically calculated in the worksheet 'Average'.

For the age-specific file, the cohort tracing can be done in the worksheet 'Cohort'. If one choses the age in the drop-down bar, it returns the number and average figures of the specific generation.

4.2.3 The consolidation of group Excel files

Subsequently, the consolidation of results is done for the groups. The consolidation is done only for the age-total results (t, tn).

The group number is dropped and the post script "C" is added to the name of the consolidated file (e.g. aaaaTC.XLS and aaaaTNC.XLS).

4.3 Main output results

The results are generally classified as those of demographic projection and financial projection.

4.3.1 Results of demographic projection

The demographic results are given in nominal number of relevant persons for each category indicated above. The age breakdown is available from the age specific files (postfix X). The results for the newly awarded pensioners are available from the new case files (postfix N) The relative percentage of pensioners in terms of active population is calculated in the sheet DemogRatio.

4.3.2 Results of financial projection

The financial results are given in nominal amount of relevant pensions for each category indicated above. The age breakdown is available from the age specific files (postfix X). The results for the newly awarded pensioners are available from the new case files (postfix N). The relative percentage of pensions in terms of total insurable earnings is calculated in the sheet FinancialRatio.

The average amount is calculated in the sheet "Average". The system replace ratio, which is the average pension as a percentage of average insurable earnings, is available from the sheet ReplaceRatio.

5. Analysis and conclusion

5.1 Financial analysis of the projection results

Once the long-term estimates of the contribution base and the expenditures have been established, the next step is to project the long-term financial development of the scheme and to test the financial solvency of the scheme under different financing options.

Generally, two different cases may be possible.

The first case is the scheme where the contribution rate is already given; then one would project the future current surplus/deficit and the development of reserves under a defined benefit scheme.

- The scheme is managed on the defined-contribution basis.

- The future contribution rates are already provided by the legislation (e.g. OASDI).

- To see the long-term financial situation of the status quo scheme, assuming that the present contribution rate remained unchanged, or the development of average benefits and total reserves under a defined contribution plan.

Under this situation, the main issue in the actuarial valuation is to ensure that the adopted contribution rate is sufficient to guarantee long-term financial solvency. If the results do not meet the test of the financial equivalency, necessary actions are to be recommended in order to restore financial solvency.

The second case is the scheme where the contribution rate has to be determined in accordance with the financial system adopted to the scheme. The main financial systems which are adopted by social security pension schemes have already been enumerated. Generally, the legal provisions of a scheme define an actuarial equilibrium, i.e. the level of reserves which the scheme has to maintain over a defined period (periods of equilibrium) in order to be considered in "equilibrium".

5.2 Long-term account file

In order to establish the long-term reserve developments on the basis of projected expenditure and the earnings base, an Excel file, called "AccountGxls", has been developed. The contents of this file is shown in Figure III.5-1. This file contains the following 4 worksheets:

- INPUT

- ACCOUNT

- GSPA

- GSPB

(1) INPUT

The contents of the worksheet 'FinancialProj' of the consolidated result file are to be imported in this worksheet.

Figure III.5-1 : Contents of the long-term accounting file

(2) ACCOUNT

Based on the results of the worksheet 'INPUT', the long-term account is established. The format of the Worksheet "Account" is shown in Figure III.5-2. One has to input the contribution rate of each year and the amount of the reserve at the beginning of the base year. The contribution rate to guarantee the given target value of the reserve ratio is given in column S. The contribution rate to guarantee the given target value of the balance ratio is given in column T. The target values are to be input in the cell S2 and T2, respectively. In both cases, the contribution determination period starts in the base year.

(3) GSLA, GSLB

The additional worksheets 'GSLA' and 'GSLB' have been developed to provide generalized scaled premia over an arbitrary period [n, m]. The format of these worksheets is shown in Figure III.5-3. The initial reserve is taken from the worksheet "Account".

5.3 By way of conclusion: Contents of the Actuarial report

Actuarial reports for social security pension schemes are much more than descriptions of model results. They normally address the following issues:

- The necessity of the actuarial valuation. The social and economic (and political) context underlying the social security scheme.

- Brief description of the scheme; identification of proposed changes.

- Description of the financial system.

- Analysis of present financial situation; description of recent developments of the scheme.



Figure III.5-2



Figure III.5-3

- Description of demographic and economic assumptions adopted for valuation; background data on the assumptions.

- Demographic and financial projection based on status quo condition; financial diagnosis of the present scheme.

- Discussion of the issues and options for reform; formulation of the reform packages.

- Financial analysis of the reform options; evaluation of the financial implication of the proposed options.

- Conclusions and recommendations, including



• suitability of financial system;
• adequacy of contribution rate; proposed contribution rate
• efficiency of benefit provision;
• adequacy of the adjustment of pensions in payment
• efficiency of administration and the level of its expenses;
• investment policy and performance (safety, return, liquidity)




- The appendix to the report should contain base data, detailed results, and methodological basis used for the estimates.

The essential message of an actuarial report is whether or not a scheme will be financially sound in the long term. This assessment of soundness, as well as the choice of methods and assumptions used for modelling, depend largely on the personal judgement of an informed and experienced actuary.

Therefore, although our models are now PC-based, are easier to handle and produce a more sophisticated product, it should not be thought that models themselves, sophisticated or otherwise, could replace an actuary. Models can only serve as a support, they should not replace sound personal judgement and experience.



Footnotes:

1. Note that FINS is recalculated in each X and each T.

Updated by JD. Approved by ER. Last update 7 December 2001

---

For further information, please contact the Social Security Department
at Tel: +41.22.799.6634, Fax: +41.22.799.7962 or E-mail: bezou@ilo.org

SOC/SEC: [ Top | PROTECTION Home | SOC/SEC Home | SOC/FAS Home | SOC/POL Home
Introduction | Programme | Publications | Statistics | ILO Facts | Social Budget | Qua train | Contact us ]