This exercise addresses a simplified version of some of the issues involved
Background: Many cities such as Detroit, MI have been in the news regarding underfunded pension funds. This exercise addresses a simplified version of some of the issues involved (doing this as a starting point is common: the key generally is to address the core work, adding more complexity later to make the analysis more accurate).
One way to handle pension obligations is to set aside money during the employee’s working life to fund an annuity that will pay out during the employee’s retirement years. The calculations will require assumptions about the number of years of working life (use 30 in this exercise), the number of years of pay-out, and the return on investment (interest rate) during the two periods. One article states that for the last 26 years, Detroit has used an interest rate of 8.9% for calculations. Recent research suggests that a more accurate estimate would be 6.3%. Most companies use a rate between 7% and 8%.
Background data:
Life expectancy at age 55: male = 23.68 years, female = 27.31 years
Life expectancy at age 66: male = 15.86 years, female = 18.30 years
Average pension = $30,000 per year
Number years to earn full pension = 30
Number of city employees = 12,900
Number of current retirees = 21,000
Analysis:
We will base the analysis on calculations for an average city employee. Calculations will be per year.
- Calculate the value of the pay-out annuity at retirement. Do this twice: once for 6.3% and once for 8.9%. Use the same number of years for each (you will need to decide how many years to use in this calculation). Estimate the city obligation for each of these (first result times number of city employees).
- Calculate how much must be set aside each year of working life to accumulate the value of the pay-out annuity at retirement. Do this for both 8.9% and 6.3%. Estimate the city obligation for each.
Consider the following questions:
- Does the analysis suggest why a city (e.g. Detroit) might prefer to use an 8.9% return rather than a 6.3% return? Provide an explanation of your answer.
- If the city uses an 8.9% return and in actuality sees a 6.3% return, what are the consequences? For example, determine the shortfall both in value available for annuity and what pay-out can be supported by the actual value accumulated.
- If the shortfall is to be made up by the city, what is the annual obligation incurred? Is this substantial enough to seriously impact city finances?
Report structure
Your report will be in two sections:
Section 1: Report. The first part of your report should describe the calculations you did and the rationale for any decisions (for example, why you chose the number for years of retirement) you made. The second part will summarize any observations or findings you have. The last part will present your responses to the questions. The report section should be between one half and one page long, single spaced.
Section 2: Data. Create and print a table of the provided background data. Print the calculation results in one or more tables for reference by the report section. Any additional calculations you want to include also go in this section.
Grading. The report should look professional (not hand written, neat, well-structured). The report will be graded for clarity (how easy it is to understand you), completeness (no significant gaps in the information provided) and correctness (the values and descriptions are correct). The report will also be graded on adherence to the report standard. Your conclusion should be supported by both the calculation results and the report narrative.
Final Comment: the purpose of this exercise is to provide you with experience using Excel and experience in constructing a coherent report. While future exercises may be focused on coming to the “correct” conclusions, this is not the case with this exercise. The correct conclusion is one that is supported by the presented data and narrative.
