Pension Reform Options Simulation Toolkit (PROST) The Social Protection Team The World Bank Table of Contents I. INSTALLING PROST 2 II. OPENING EXCEL AND LOADING PROST 2 III. CHANGING AND CHOOSING LANGUAGES 3 IV. LOADING AND CHANGING INPUT FILES 4 V. CREATING OUTPUT FOR THE SINGLE FILE 6 MODULE 1. POPULATION PROJECTION 8 MODULE 2. DEMOGRAPHIC STRUCTURE 9 MODULE 3. FINANCES OF MONOPILLAR PAYG 11 MODULE 4. FINANCES OF MULTIPILLAR SYSTEM 113 MODULE 5. INDIVIDUAL RETIREMENT ACCOUNTS 14 VI. SUMMARIZING, COMPARING AND ADDING CASES 15 PROST 1 5/21/2018 I. Installing PROST You will be getting a flash drive with the file PROST.zip on it. The first step is to copy the file to your hard drive. Then open the folder, which will cause the file to unzip. You will see the file PROST15.xlsm is appearing in your directory together with sample input file(s) beginning with I_ and having .xlsx as an extension. System Requirements • Hardware: It is highly recommended for the user to run PROST on a Pentium-based computer with at least 32 MB of RAM and a processor speed no lower than 200 Mhz. • Software: English language version of Microsoft Office97 or higher is needed to run PROST. Please ensure that the correct version of Excel is being used. II. Opening Excel and Loading PROST Locate the directory where you have saved the downloaded PROST folder. Open the file PROST15.xlsm from the folder. The snap shot below shows that the user saved the PROST folder in their ‘Documents’ and they can open file ‘PROST15.XLSM’ by highlighting the file and double left clicking on it. PROST 2 5/21/2018 You will be taken to the PROST welcome screen which looks like this. If you get the message “PROST15.xlsm contains macros”, click on “Enable macros” to get the PROST opening screen. From the ribbons in excel, if you click on ‘Add- ins’, you will find copies of seven major buttons . These are the same buttons as present on the welcome screen, and you can use them at any time during your PROST session. All dialog boxes have to be closed for the toolbar to work. The buttons under ‘Add-Ins’ are touch sensitive. By placing the mouse/cursor over each icon, a brief help text appears describing what the buttons are used for. To change languages, click on button . To load the input file(s) click on the button . You can check the input data by clicking button . To run one of the specific PROST tasks, click . For printing, individual output tables use button . To print an entire output workbook, click . Finally, to end a PROST session and clear your workspace click . III. Changing and Choosing Languages All the buttons on PROST and the output headings can be translated into the language of your choice. To do the translation, notice that PROST.xlsm contains three sheets, Front Page, Back Page, and Text. If you click on Text, you will see a table where the leftmost column contains the English language text for the headings and buttons. Currently, we PROST 3 5/21/2018 provide translations in French, Chinese, Russian and Spanish. You can simply left click on the button until you get to the language of choice. If you would like to access PROST in a language which is not covered above you could go to the sheet ‘Text’ and enter the translations of each heading in the column G titled ‘Reserved.’ Save the file. Then return to Front Page. Click on the Pick a Language button and choose your language. IV. Loading and Changing Input Files Click on the second button of the PROST toolbar (Load input file). You will see the following dialogue box. Note that input files must start with I_ . Click on the button Load a Case to select an input file for analysis. You will see a dialogue box that asks you to select an input file. After selecting the appropriate file (which, for illustrative purposes is being called I_Country.xls) click on the Open button. PROST 4 5/21/2018 You will return to the previous dialogue box. The name and path of the file you have just selected will appear in the box titled Cases Selected. Highlight the file name with the click of left mouse button. Now the Simulate a Case button becomes enabled. Click on Simulate a Case to start the analysis of the file you have selected. You may also load additional input files by repeating this step. If you have chosen to load a few input files, PROST allows a comparison of the outcomes from each input file or an addition of outcomes from different cases. To perform this routine, highlight more than one input file by clicking once on the each of the file names. If you do that, the buttons Compare Cases and Add Cases will now become enabled. Clicking on the Compare cases button enables the user to make graphical comparisons of different cases. The Compare Cases routine is described later. Clicking on Add Cases allows users to add the output from multiple input files, for example an input file for teachers and an input file for civil servants to the input file for private sector employees. However, please note that the running time for the program is longer with more cases since all the calculations need to be performed for each of the input files before the output appears. Furthermore, the output variables shown under both of these options, are more limited than otherwise. If you have loaded only one input file, using the button Simulate a Case will show you the following box with series of buttons. PROST 5 5/21/2018 This dialogue box gives you an opportunity to review your input file data and make corrections if necessary. The eight highlighted buttons labeled General, Population, Labor, Pension, Profiles, Factors, Reform General and Reform Pension allow the user to go to the specific input file worksheet by clicking on any of them. To get back to the dialogue box click icon in the Add-Ins or go to the ‘Front Page’ of the macro file. Clicking on Cancel button brings the user back to the PROST opening page. The Output button takes the user to the PROST output generation dialogue box which is described in section V. If you have clicked on any of highlighted buttons, you will be taken to the specific worksheet of the input file. If you agree with all the data you can inspect the output file by clicking . To better understand the data or to change it consult the input manual. If you have changed at least one cell in the input file you must save the file and reload it with the help of button, before you can inspect the output. When you click on , a dialog box will appear giving you another chance to save the files in case you have not done so already through normal Excel procedure. If you do not want to save the changes to the existing file but would like to create a new one you can do that by changing the file and then saving the file under a new name through File / Save As … . Be sure your new name starts with the symbols “I_” as well. Having several input files, each different from the other in key parameters/assumptions, will enable you to use the case comparison option, described later. V. Creating Output for the Single File Click and then Output button or click under Add-Ins or from ‘Front Page’ of macro sheet to get to the output generation dialogue box. Notice that there are 5 modules – Population Projection, Demographic Structure, Finances of Monopillar PAYG, Finances of Multipillar System and Individual Retirement Accounts. Each of these modules has 5 tasks associated with it. Running all the modules and tasks will generate about 60 spreadsheets of simulation output. Clicking on any of the module buttons activates the task list associated with that module. For instance, the Population Projection module has five tasks as seen in the right-hand side of the panel below, which could be activated by clicking on it. PROST 6 5/21/2018 Clicking on the task buttons instructs PROST to perform the task associated with that button. Note that it is not necessary to go through the output modules in the order listed. The user can proceed to any output module directly since PROST does the necessary calculations from other modules internally. Clicking on the Summarize button produces a tabular summary of the simulations which is quite useful to get an overview of the simulation results and produces output tables corresponding to a number of the most relevant tasks. Depending on the system you are modelling (monopillar or multipillar) the summarize button will output all the relevant results. Further details on outputs under monopillar and/or multipillar schemes can be found under heading Module 4 of this manual. To get back to the welcome screen click Cancel. While any task is running, a message appears on the bottom left corner of the Excel screen informing the user about the status of the calculations. All the key results of the simulations are produced in one simple table for each task. You can print these results by clicking on . This would send the output on the screen to the printer after formatting it in a special way to make the output fit on one page. User can always return to the output generation dialog box by clicking . The 10 most important output tables can be printed in one step, by first canceling the dialogue box and then clicking icon. As you perform simulation tasks, PROST adds them to the output file it creates and names o_{name of input file}. Please note that this file is a temporary one and it will disappear if you rerun your input file. If you would like to save this file it is recommended to save it with a different name eg Output_{name of input file}. You can access the saved output file, which you have already run, by opening it as a regular excel file. Each output task produces at least one worksheet. By clicking on the different worksheet tabs, you can look at the different tables you have already created. Since this output is also produced in the form of simple EXCEL spreadsheets, you can copy the data to another spreadsheet and combine it with other pieces of data. For example, suppose you want pension spending per capita. The model does not output that value, but outputs pension spending and population, so using normal EXCEL formulas you can create the value you want. There are also I and D buttons in the upper left-hand corner of most output tables. Clicking on I expands the visible number of years to every single year in the simulation period. PROST 7 5/21/2018 Clicking on D reduces the number of years visible, to every 5 or every 10 in order to produce a page that is easily printable. Module 1. Population Projection Task Name What it does Population Projection & Pyramids Generates age specific population projections and plots population pyramids – including the option of visualizing dynamic pyramids Life Table Generates male, female and unisex life tables for any gender and year in the simulation horizon. Life Expectancy Changes Produces a table of gender specific life expectancy changes during the simulation horizon for various ages including life expectancy at retirement. Summary of Population Projections Summarizes population projections and calculates population dependency ratios Graphical Summary: Module 1 Produces a graphical summary of module 1 tasks The first task produces 3 output worksheets, PL_Pyramid, PP_F, and PP_M. The first is the population pyramid graph shown below. The user can select individual years by scrolling along the scroll box to the left of the Dynamic Pyramids button. By clicking on the Dynamic Pyramids button, the pyramid on the top left panel changes continuously to show the population pyramids for every year in the simulation horizon. The other two worksheets are age and gender specific projections of the population for every year. Example - By clicking on the first module and the first task, the user is taken to an output page which is shown below: PROST 8 5/21/2018 The second task produces one output sheet LT, the life table. There is a scroll bar on top which allows the user to look at life tables for different years. There are also three buttons on the top right, Male, Female, and Both Genders for each of the 3 different population pools: All Population, Disabled and Old Age Pensioners. Clicking on them produces life tables specific to that gender and population pool. Unisex life tables use the weighted average of the mortality rates of the two genders. The third task produces a worksheet named life, which provides life expectancies at several ages for both males and females, as well as at the retirement age specified in the input file. Clicking on a button in the upper left-hand corner called All Life Expectancies produces two more worksheets, Full_LE_M and Full_LE_F, which show the life expectancies at each age for each year of the simulation horizon for males and females, respectively. Note that the unisex life expectancy can be taken from the life tables, but does not appear in these task 3 tables. The fourth task produces a worksheet named Group which takes the population data from Task 1 and produces summary data by groups of ages, 0-14, 15-Retirement age, and Retirement Age+. The growth rates of each group are calculated as well as the share in the population of each group. The population dependency rates, the old age dependency rate and support ratios are calculated along with the gross birth rate, death rate, and fertility rate. The fifth task produces the sheet Group (same as that produced by task 4) and another worksheet PL_POP which takes the data from sheet Group and shows it in a graphical format. The user will see two graphs in this sheet, one on population share and how it changes over time for ages 0-14, 15-retirement age and retirement age +, and the other showing population dependency ratios over time. Module 2. Demographic Structure This module produces head counts of people within the pension system and within the employed labor force. Module 3 will translate these head counts into financial terms. The first two tasks produce very detailed output, not commonly required, but extremely helpful in finding calibration errors. The remaining tasks are more commonly used in standard analyses of pension systems and contain the essential results in grouped or summarized format. Task Name What it does Contributors and Beneficiaries Age and gender specific projections for employed, the exempt, contributors and beneficiaries Length of Service and Retirement Age and gender specific projections for average accrued Patterns length of service each year, length of service at retirement, percent of retired population PROST 9 5/21/2018 Demographic Structure of Pension Summary of the demographic results from the simulations of System the pension system Pension System Dependency Summary of pension system dependency ratios Graphical Summary: Module 2 Graphical summary of the output from Module 2 Task 1 produces detailed age and gender projections for each year for the employed, for contributors, and for beneficiaries. The existing disabled are found under the output sheets ED_F, and ED_M, for female and male respectively. The newly disabled are found under the output sheets ND_F and ND_M. Existing pensioners and new old age retirees are found under similarly titled worksheets, EP_M, EP_F, NP_M, and NP_F. The categories for contributors are slightly different, there are effective contributors, those who are earning credit for the current service year and actually contributing, EC_F and EC_M, the total numbers of people earning service credit viz the nominal contributors NC_F and NC_M, and finally the newly joined contributors, JC_F and JC_M. Employment data is contained in the worksheets Employment_F and Employment_M. Task 2 produces detailed age and gender projections for each year of the average length of service that each cohort has completed. These projections are found in worksheets labeled LOS_Average_F and LOS_Average_M. Sheets LOS_AtRetirement_F and LOS_AtRetirement_M report length of service at retirement. Finally, sheets Retiree Rate_F and Retire Rate_M produce percent of retired population. Note, that length of service at retirement and percent of retired population might be different, from those specified in your input file. That is because PROST checks and adjusts your inputs if it finds inconsistent data or is asked to implement behavioral changes due to the retirement age increase. Task 3 produces a worksheet titled Composition which aggregates the data from Task 1 into total contributors, male and female, for each year and total beneficiaries, by type of beneficiary, and provides similar information for those who newly fall into one of these categories in each year. Task 4 produces a worksheet titled Dependency which shows the dependency rates each year, coverage rates and growth of different groups in the pension system, again at the aggregate level. Task 5: By clicking on the Graphical Summary: Module 2 task button, the following output page is produced on the screen which may be printed for later analysis by clicking on the icon in the PROST toolbar. PROST 10 5/21/2018 Module 3. Finances of Monopillar PAYG Task Name What it does Individual Wages Outputs age and gender specific projections of earnings and percent of contributors earning minimum wage and wage above the contribution ceiling Macroeconomic Trends Outputs table containing macro forecasts and projections about wages and revenues Pension Benefits Outputs pension benefit projections for both the existing and new pensioners and tables showing a distribution of minimum pension recipients Financial Flows & IPD Outputs revenues and expenditures of the pension system for the simulation horizon along with required adjustments to contribution rates and replacement rates for zero current balance and the implicit pension debt. Graphical Summary: Module 3 Produces graphical summary of output from Module 3 Task 1 produces several age and gender specific matrixes. AtMin_F and AtMin_M, report the percentage of contributors of each age and gender that are earning minimum wage. Matrices Above_F and Above_M report the percentage of contributors earning wage above the contribution ceiling. Unconstrained_F and Unconstrained_M show the average total wage of contributors of each age and gender, while Constrained_F and Constrained_M show the average wage subject to social insurance contribution. Task 2 produces a worksheet titled MacroIndicators which show the output on macroeconomic environment, detailed information on wages of covered population and contribution revenue of the pension system. Task 3 produces a worksheet titled Benefit, which calculates a variety of statistics on new pensioners, both old age and disabled, such as average retirement age, average length of service at retirement, average replacement rate initially, the expected value of the pension PROST 11 5/21/2018 stream and the percent of beneficiaries receiving minimum pension. Average replacement rates and percent of minimum pension recipients are also provided for existing pensioners. These differ from the replacement rates for the new pensioners due to factors such as less than full wage indexation of pensions post-retirement in the face of positive wage growth and differences in length of service. The total payments to each category of beneficiary are also summed and outputted here. Detailed matrixes At_Minimum_F and At_Minimum_M show a distribution of minimum pension recipients among old age pensioners by age and gender. Task 4 puts together the revenue from Task 2 and the expenditures from Task 3, adding in other sources of revenue and other costs such as administrative costs to arrive at the financial picture. The main output table Balance provides the fiscal balance in the pension system and also calculates the annual pension fund reserves, if they exist. All of these values are expressed as a percentage of GDP and as a percentage of the wage bill in addition to being shown as raw numbers, for the convenience of the user. Two other important calculations appear here, the contribution rate required to maintain fiscal balance each year and the net present value of financial flows (revenue, expenditures, current account balance, debt etc.). This output table also reports consolidated implicit and explicit debt of pension system and pension related government obligations. There is a button Implicit Pension Debt on the top left-hand corner of the output table. Clicking on that produces another sheet IPD_New which outputs the implicit pension debt in the system, broken down into debt to contributors, to pensioners, to the disabled, etc. and expressed as a percentage of GDP. Implicit pension debt is what the pension system owes the current covered population in present value terms. Pensioners are owed pensions for the rest of their lives. The disabled are also owed pensions for the rest of their lives. Contributors are owed pensions proportional to the number of years of service they have already completed relative to what they will eventually have completed. Task 5: Clicking on the Graphical Summary: Module 3 button generates the following output. In particular, the graphs depict pension system contribution revenues, pension expenditures, fund coverage rate. It also suggests policy changes to contribution rates and replacement rates to ensure a zero-current balance. PROST 12 5/21/2018 Module 4. Finances of Multipillar System Task Name What it does Old Age Pensioners in Conventional Outputs pension benefit projections for new and existing PAYG Pillar pensioners (both switchers and non-switchers) in the conventional PAYG pillar Old Age Pensioners in Notional PAYG Outputs pension benefit projections for new and existing Pillar pensioners (both switchers and non-switchers) in the Notional PAYG pillar Old Age Pensioners in Funded PAYG Outputs pension benefit projections for new and existing Pillar pensioners (both switchers and non-switchers) in the funded pillar All Pillars: Financial Flows & IPD Outputs revenues and expenditures of both PAYG and funded pillars. Also report IPD of the PAYG system after the reform. Results of the Reform Compares benefit projections and financial standing of the pension systems under the mono-pillar and multi-pillar scenarios. Task 1 produces a worksheet called Reformed_PAYG which displays variety of financial statistics on average new pensioner (switcher and non-switcher) and his/her benefits from the conventional PAYG. Other statistics describe average replacement rates from the conventional PAYG pillar to the existing pensioners and aggregate financial flows related to conventional old age pension program. Task 2 produces a worksheet called NotionalPillar which displays variety of financial statistics on average switcher and his/her benefits from the Notional PAYG. Other statistics PROST 13 5/21/2018 describe average replacement rates from the Notional PAYG pillar to the existing pensioners and aggregate financial flows related to the notional old age pension program. Additional 2 sheets named NotionalAnnuities_M, NotionalAnnuities_F present the replacement rates of switchers (male/female) by age and year. Task 3 produces a worksheet called FundedPillar which displays variety of financial statistics on average switcher and his/her benefits from the funded pillar. Other statistics describe average replacement rates from the funded pillar to the existing pensioners and aggregate financial flows related to the funded old age pension program. Additional 2 sheets named FundedAnnuities_M, FundedAnnuities_F present the replacement rates of switchers from the funded pillar (male/female) by age and year. Task 4 puts together 2 balance sheets for PAYG and funded programs, presented in the table ReformedFlows. The statistics reported here are almost identical to the statistics reported in Module 3, Task 4. The user can produce an additional output table IPD_reformed by clicking on the button “Implicit Pension Debt” on the top of the sheet ReformedFlows. The statistics reported here again correspond to the ones reported in Module 3, Task 4. This time implicit pension debt is calculated for both conventional and notional PAYG pillars together. Task 5 gives a comparison table ReformResults. Here the comparisons of most important statistics are made for mono-pillar and multi-pillar systems. Among the compared statistics are value of pension stream of the average new pensioner, average replacement rates of existing pensioners, total revenues and expenditures of the pension system and implicit pension debt of the PAYG system. Module 5. Individual Retirement Accounts Task Name What it does Statutory PAYG System Projects life-time contributions and benefits for six different individuals specified in the ‘Profiles’ input sheet and gives individual related summary statistics assuming that individuals participate in the pension system specified in the input file PAYG with Adjusted Contribution Projects life-time contributions and benefits for six different individuals specified in the ‘Profiles’ input sheet and gives individual related summary statistics assuming that individuals participate in the pension system with the contribution rates adjusted so that the system is always in balance PAYG with Adjusted Benefits Projects life-time contributions and benefits for six different individuals specified in the ‘Profiles’ input sheet and gives individual related summary statistics assuming that individuals participate in the pension system with the replacement rates adjusted so that the system is always in balance Multi-pillar for Switchers Projects life-time contributions and benefits for six different individuals specified in the ‘Profiles’ input sheet and gives individual PROST 14 5/21/2018 related summary statistics assuming that individuals participate in the multi-pillar scheme as switchers Multi-Pillar for Non-switchers Projects life-time contributions and benefits for six different individuals specified in the ‘Profiles’ input sheet and gives individual related summary statistics assuming that individuals participate in the multi-pillar scheme as non-switchers, i.e. remain in adjusted PAYG scheme after the multi-pillar reform The model takes six individuals specified in the input file and projects the outcome of participation in the specified pension system for those six individual types beginning work this year, beginning work next year, and for each year of the simulation period. This allows the user to do both intergenerational and intragenerational analysis. While the model can simulate individuals, who began work in the past and who are close to retirement today or who are mid-career or who have even already retired, such an analysis would require starting the simulation period 40 years or more ago and would require the filling in of all macro and wage data for intervening years. In the usual case, these data are not available and so the projection is prospective rather than retrospective. Each task outputs the summary table in the same format (StatutorySummary for task 1, AdjContrSummary for task 2, AdjReplSummary for task 3, PAYGPartSwSummary, NotPartSwSummary, FundPartSwSummary, MultiSwSummary for task 4 corresponding to the PAYG, notional, funded and all 3 pillars combined, MultiNswSummary for task 5). Each of these summaries report the same 6 statistics: 1) initial replacement rate in terms of average wage, 2) initial replacement rate in terms of individual’s last wage, 3) replacement rate at age 75 in terms of average wage, 4) probability of getting old age pension at age 75 evaluated at the start of the career, 5) internal rate of return and 6) net present value of being covered in terms of average wage. All these characteristics also depend on the year when the specified individuals start their career. To see detailed individual accounts from which all these summary statistics are derived you can inspect more detailed sheets which are also outputted. These are StatutoryDetails for task 1, AdjContrDetails for task 2, AdjReplDetails for task 3, PAYGPartSwDetails, NorPartSwDetails, FundPartSwDetails, MultiSwDetails for task 4 corresponding to the PAYG, notional, funded and all 3 pillars combined, MultiNswDetails for task 5. To see the details for individual who will enter labor market later choose a different year in the drop-down box at the upper-left corner of your screen. As before the resulting tables can be printed by clicking on the icon in the PROST toolbar. This module is very useful to look at the equity issues. You can see how the pension system treats people of different ages, genders and health. You can also compare people with different working careers, income and education levels (different initial wage and wage growth parameters) and see which ones have highest incentives to evade. PROST 15 5/21/2018 VI. Summarizing, Comparing and Adding Cases The Summarize button in the output generation dialog box allows the user to generate the summary table Summary that includes most important statistics from modules 1-3. If you have loaded several input files and used the button Add Cases you will see a similarly looking table which reports the same statistics that are added/averaged among the several input files. When you load several input files and press a Compare Cases button a following screen appears. If you want to see how any of the time series compare, choose one of the available series in the drop-down box below the graph. Only series with the different values in the summary sheets are available for comparison. The buttons below the graph allow you to compare the series in nominal values (button Derived Values), in percentage difference from the base case (button Pct of Base Case) or in the percentage changes from the base year values (button Pct of Base Year). You can print these graphs by clicking on . PROST 16 5/21/2018