During the Great Depression, large segments of the population lived in poverty, particularly older people, who had a few job prospects and little savings. To reduce this hardship, the country’s legislature established the Social Safety Net fund. Under the new law, working people agreed to have their wages and salaries taxed to establish the fund. From this fund, retirees were paid an amount that was sufficient to stave off poverty.

Thus, the country’s younger people agreed to take care of elderly citizens. The fund was designed to survive in perpetuity, so that sacrifices made by young people would be recouped when they retired.

The country grew and prospered greatly in the ensuing decades. For many years, taxes paid into the fund exceeded payments to retirees, and the fund balance grew to exceed $2 trillion. However, in the past decade, the country’s economy has changed and the fund has been affected:

- The country has not fully recovered from the severe recession of 2008 and 2009, although the economy has resumed growing at a low rate. Newly created jobs often offer low pay or are part-time. As wages and salaries have stagnated, so have payroll taxes that go into the fund. And now, we do not know the full impact of the pandemic.

- The payroll tax has remained at 12.4 percent for many years. Of this tax, 6.2 percent is paid by the worker and 6.2 percent is paid by the employer on the first $137,700 of pretax income in 2020. The current political climate is anti-tax, so there is no chance of raising the tax rate to increase the size of the fund.

- The birth rate after World War II was high. The post-war generation, also known as the “baby boomers,” is very large and has begun to retire. The size of that generation, coupled with declining birth rates in recent decades, means that fewer workers are available to support retirees.

As a result of the preceding factors, it is now possible for fund payments to exceed taxes paid into it. The problem is aggravated by a few other financial factors:

- The trust fund is not actually held in cash. The government’s Treasury Department uses money from the fund to pay for national defense, national parks, and other aspects of the country’s business. The Treasury gives the fund special, nonnegotiable bonds. If the fund needs cash, the Treasury redeems the bonds. Naturally, the Treasury pays interest on bonds to the fund. The interest rate is substantially higher than the market rate. For example, the Treasury currently borrows at less than 2 percent on the open market, but it pays the fund more than 4 percent on its bonds. In effect, the Treasury is subsidizing the country’s retirees with the augmented interest payments each year. In fact, the Treasury takes the same approach with all the trust funds that it administers, including the retired military pension fund. In tight economic times, the question arises: Can the country afford to continue this subsidy?

- Benefit payments are indexed to the increase in the cost of living each year. This is called the COLA, or cost of living adjustment. For example, if inflation is 4 percent in a year, retirees are protected because their benefit payment is increased by 4 percent. The COLA is a generous idea, but it leads to a couple of problems. First, compounded interest, even at a low rate, can add up over time, so benefit payments can increase greatly. Second, the government has often erred on the high side when setting the COLA rate. Thus, if the inflation rate is 3 percent, for example, the safety net COLA might be set at 3.5 percent. This practice satisfies retirees, but it also tends to inflate fund payments.

- Taxes paid into the fund in 2020 are based on the first $137,700 of pretax earnings. Thus, if a household’s pretax income is $137,700, it pays Social Safety Net taxes of $17,074 ($137,700 multiplied by the payroll tax of 12.4 percent). If a household’s pretax income is $142,900, the social security taxes are the same: $17,074. This policy strikes some observers as unfair, especially in recent years, when the distribution of income and wealth in the country has become more skewed. The top 20 percent of households, or the top quintile, earns over 50 percent of the country’s pretax income. Some people argue that the pretax earnings threshold should be raised to increase payroll tax receipts.

Administration of the Social Security Net have just finished a multiyear projection of economic and demographic factors that affect the fund. Administrators are most interested in the fund’s ability to pay benefits through 2035. If the fund can remain solvent until then, administrators think the fund probably can survive because the baby boomers will be retired and their effects will be mostly absorbed. However, if the fund cannot remain solvent through 2035, major changes will be needed to save it. The administrators have asked you to create a spreadsheet model of the fund’s status as a way to guide decision making.

Creating a Spreadsheet

You are given a file Social Safety Net-Data-2020.xlsx

This sheet will have the following sections:

Constants

Inputs

Summary of key Results

Calculations

Constants:

• Expected Payroll tax receipts – Administrators have projected the number of workers contributing to the fund, the number of retirees drawing from the fund, increases in gross domestic product (GDP), and inflation rates. Based on these projections, payroll taxes paid into the fund have been estimated through2035. Note that GDP is projected to increase at an average of 3.5 percent, which is optimistic.

• Expected total Benefit payments, no COLA – This amount is the total payments to retirees each year before COLA is added.

• Number of households in top quintile – This row holds the number of households in the top 20 percent of pretax earnings.

• Average pretax household income, top quintile – This amount is the average income for a household in the top quintile.

• Number of households in fourth quintile – This row holds the number of households in the next highest level of pretax earnings.

• Average pretax household income, fourth quintile – This amount is the average income for a household in the fourth quintile.

• Interest rate, trust fund – The Treasury is projected to pay 4 percent on trust fund bonds in the period under review.

• Expected income tax rate on benefit payments – Retirees do pay taxes on benefits payments, although not much on average. Historically, the rate has been 5 percent. The fund is credited for income taxes collected.

Inputs:

• COLA percentage in all years (.XX) – Enter a decimal number for the COLA each year. For example, if 4 percent is expected, .04. Note that 3.5 percent is anticipated by fund administrators.

• GDP Deflator (.XX) – As noted previously, the payroll tax receipts projection is for a GDP growth of 3.5 percent each year, which might be too high. If you want to see results with a GDP growth of 2 percent, enter .015 in this cell. (So, GDP will be 2% by deflating it by 1.5%)

• Interest Rate Deflator (.XX) – As noted previously, interest earnings are projected at 4 percent, which might be too high. If you want to see results with a 2 percent rate, enter .02 in this cell. (4%-2%)

• Additional payroll tax base, all years ($XX) – The tax base, or pretax income threshold, is $137,700. For example, if you want to see results with a base of additional $10,000, you would enter 10,000 in this cell.

Summary of Key Results:

• Primary Surplus (Deficit) – This value is the sum of payroll tax receipts and income taxes on benefits, minus benefits paid in the year.

• Total Surplus (Deficit) – This value is the sum of payroll tax receipts, income taxes on benefits, and interest earned, minus benefits paid in the year.

• Trust Fund Assets – This value is the fund balance at the end of the year.

• Expected Payments exceed Assets? – This value (Yes or No) indicated whether the expected benefits in the year exceed the fund assets at the start of the year.

Calculations:

• Expected total Benefit payments, with COLA – This amount is the expected total benefit payments from the Constants section increased by the COLA percentage from the Inputs section.

• Overstatement of payroll tax receipts – GDP deflator – This amount is a function of the GDP Deflator percentage from the Inputs section and expected payroll tax receipts from the Constants section.

• Additional payroll tax receipts – 4th quintile – This amount is a function of the additional payroll tax base from the Input section, the tax rate of 0.124, and the number of households in the fourth quintile, which is a value from the Constants section. Because the tax rate is always 0.124, it can be hard-coded. For example, if the base is increased by $1,000 and 10 million households are paying, the added receipts would be $1,000 * 10,000,000 * 0.124.

• Additional payroll tax receipts – top quintile – This amount is a function of the additional payroll tax base from the Inputs section the tax rate of 0.124, and the number of households in the top quintile, which is a value from the Constants section. Because the tax rate is always 0.124, it can be hard-coded.

• Total Payroll tax receipts – This value is the expected payroll tax receipts from the Constants section, plus additional receipts from the fourth and top quintiles, minus overstated receipts. Except for the expected payroll tax receipts, all the values for this calculation come from other cells of the Calculations section.

• Income Taxes received on benefits – This amount is function of benefits paid to retirees (from the calculations section) and the tax rate from the Constants section.

• Payroll tax receipts plus income taxes on benefits – This value is the sum of total payroll taxes received and income taxes on benefits received.

• Interest earned on Trust Fund Assets – This amount is a function of trust fund assets at the beginning of the year and the interest rate from the Constants section.

• Total Safety Net Income – This value is the sum of payroll tax receipts, taxes on benefits, and interest earned.

• Total Fund balance at year-end – This value is the fund balance at the beginning of the year, plus total safety net income, minus expected total benefits payments with COLA.

• Primary Surplus (Deficit) in Year – This amount equals payroll tax receipts plus income taxes on benefits, minus total benefit payments with COLA. Both values in the equation come from other cells of the Calculations section. This value contrasts cash received by the Treasury with cash to be paid out.

• Total Surplus (Deficit) in Year – This amount equals total safety net income minus total benefits payments with COLA. This value contrasts cash received plus interest received with cash to be paid out. The cash plus interest received would be in the form of additional bonds.

• Expected Payments exceed Assets? – If the year’s expected benefit payments with COLA exceed trust fund assets at the start of the year, enter YES in this cell. Otherwise, enter NO. This value is an indicator that trust fund assets are nearly exhausted and the fund is operating from year to year.

Using the spreadsheet for Decision Support

You have built the spreadsheet to create “what-if” scenarios with the model’s input values. The inputs represent the logic of a question, and the outputs provide information needed to answer the question. The scenarios are based on the following seven questions from fund administrators.

Question 1: 

What is the funds’ status in the base case?

COLA – 0.035 (3.5 percent)

There is no added tax base – 0

Number GDP deflator – 0

Number Interest deflator – 0

Enter the inputs and then observe the outputs in the Summary of Key Results area. Next, you should record the results in a summary area by copying the data from the Summary of Key Results cells and pasting it into the summary area. Highlight the area to be copied, and click the Copy button in the Clipboard group of the Home tab. Next, click the Paste button in the Clipboard group, select Paste Special from the menu, and then select Values in the next window. Use a second worksheet for this purpose.

Question 2: 

What is the fund’s status if the legislature decides to eliminate the interest rate subsidy by reducing the rate two percentage points? In other words, how important is interest income to the fund?

COLA – 0.035

There is no added tax base -- 0

Number GDP deflator -- 0

Number Interest deflator -- 0.02

Enter the inputs and then observe the outputs in the Summary of Key Results Area. Next, you should record the results in a summary area in the sheet you created above.

Question 3

What is fund’s status if the GDP does not grow as assumed? This scenario would result in lower payroll tax receipts. The inputs are shown below:

COLA – 0.035

There is no added tax base – 0

Number GDP deflator – 0.015

Number Interest deflator – 0

Record the results in the summary area.

Question 4

What is the fund’s status if the COLA is eliminated? The legislature might need to eliminate the COLA as an extreme measure if the economy is suffering, which would result in lower benefit payments. The inputs are:

COLA – 0

There is no added tax base – 0

Number GDP deflator -- 0

Number Interest deflator – 0

Record the results in the summary area.

Question 5

What is fund’s status if the tax base (pretax income threshold) is increased? The legislature might be able to increase receipts using this “back door” method, as opposed to legislating an increase in the tax rate.

COLA – 0.035

There is no added tax base – 10000

Number GDP deflator – 0

Number Interest deflator – 0

Record the results in the summary area.

Question 6

Some members of the legislature might want to make changes to the fund in accordance with their own fiscal philosophy: Pay no COLA, do not increase the tax base, use a realistic GDP estimate, and do not subsidize interest. What is the fund’s status if these changes are made?

COLA – 0

There is no added tax base – 0

Number GDP deflator – 0.02

Number Interest deflator – 0.02

Record the results in the summary area.

Question 7

What would be a presumed worst-case scenario for the fund’s health? In other words, what would happen if the legislature pays the COLA, does not increase the tax base, assumes little or no GDP increase, and does not subsidize interest?

COLA – 0.035 percent

There is no added tax base – 0

Number GDP deflator – 0.03

Number Interest deflator – 0.02

Record the results in the summary area.

Submit your spreadsheet along with a memo that answers the seven questions given above.

The memo should summarize your general conclusions about the health of the fund.

• It should have proper headings such as Date, To, From, and Subject. You can address the memo to the administrators of the Social Safety Net fund.

• Briefly outline the situation. However, you need NOT provide much background – you can assume that readers are familiar with the situation.

• Answer the Seven questions in the body of the memo.

• Describe the general conclusions you draw about the status of the fund. What factors are most important for financial health? Administrators would be concerned if the fund balance dipped below $1 Trillion or if expected payments exceeded fund assets. What situations would cause these events?

• State your general conclusion about the health of the Social Safety Net fund. Does the program require major changes, or will it survive in its current state?

 

 

Safety Net Summary SOCIAL SAFETY NET ESTIMATOR CONSTANTS Expected Payroll tax receipts Expected total Benefit payments, no COLA 2019 NA NA 2020 9.308E-11 1.05333E-12 2021 9.736E+11 2022 1.02228E-12 2023 1.07339E-12 1.12559E-12 1.19312E+12 1.26471E+12 2024 1.12706E+ 1.34059E Number of households in top quintile NA 26,803,384 27,071,418 27.342,132 27,615,553 27,8 Average pretax household income, top quintile NA 387,410.268 399,032.576 411,003.553 423,333.659 436,033.66 Number of households in fourth quintile NA 25,731,248 25,988,561 26,248,447 26,510,931 Average pretax household income, fourth quintile NA 135.286.125 139,344.709 143,525.05 147,830.802 26,7 152.265.72 Interest rate, trust fund NA 4% 4% 4% 4% Expected income tax rate on benefit payments NA 5% 5% 5% 5% INPUTS COLA percentage in all years (XX) Additional payroll tax base, all years (SXX) 0.0% GDP Deflator (XX) Interest Rate Deflator 0% (XX) 0% SUMMARY OF KEY RESULTS 2020 2025 2035 Primary Surplus (Deficit) Total Surplus (Deficit) Trust Fund Assets Expected Payments exceed Assets? CALCULATIONS 2019 2020 2021 2022 2023 2024 Expected total Benefit payments, with COLA NA Overstatement of payroll tax receipts GDP deflator NA Additional payroll tax receipts 4th quintile NA Additional payroll tax receipts-top quintile Total Payroll tax receipts Income Taxes received on benefits Payroll tax receipts plus income taxes on benefits Interest earned on Trust Fund Assets Total Safety Net Income NA NA NA NA NA NA Trust Fund balance at year-end 2.69983E+12 Primary Surplus (Deficit) in Year Total Surplus (Deficit) in Year Expected Payments exceed Assets? 333