Common Sense on Social Security

A Spreadsheet Model for Testing Reform Options

Anyone interested in learning more about Social Security reform can test a wide range of options by using our Solvency Forecasting Spreadsheet Model. This page explains the Model's Input-Output Section.

Our Solvency Forecasting Spreadsheet Model

Steven H. Johnson, Director
Common Sense on Social Security

If you're interested in experimenting with your own Social Security reform scenarios, you may wish to request a copy of our Solvency Forecasting Spreadsheet Model. Simply write us at, and we'll email you a copy. It's 1.8 MB in size and runs on the Microsoft Office 97 version of Excel.

It's a very powerful spreadsheet. A detailed explanation of how to use our Solvency Forecasting Spreadsheet Model follows.

Sheet 1. Introductory Remarks. Contains another copy of the remarks that you're reading here.

Sheet 2. Input-Output Scenarios. Contains the Input section, in which you select the scenarios and the values you'd like to test. Contains an Output section, showing the results produced by the scenario you're testing. Contains a Scenario section, into which you can copy the results of each of the scenarios you may have run. Sample scenarios are already recorded here. You will find them to the right of the Input/Output section.

Once you've run a scenario, copying it and saving it is mildly tricky. Follow these steps, and you'll do fine.

A) With the mouse, highlight the entire scenario, as outlined by the box,
from line 6 to line 56, in columns D and E.

B) Use Control C to copy the scenario.

C) Position the cursor in the top left cell of the location that you'd like to
copy the results into. This will be on line 6, somewhat to the right.

D) Use Control V to copy the results into that location.

E) Now press Escape to end the copy command you've just been using.

F) With the copied material still highlighted, do Control C again to copy it.
Leave the cursor where it is.

G) Go to Edit, then to "Paste Special", then, when the popup window
appears, click on "Values", and click "OK". The popup will disappear.

H) Hit Escape again, to bring the copy command to an end.

Do you know why you've just done this? If you hadn't done "Paste Values," the output cells in your copied scenario would still contain formulas, rather than actual values. Should you begin testing a new scenario, and inserting new input values, the Output Values in the copied scenario would begin changing accordingly. You wouldn't have a permanent record of your previously run and copied scenario.

By doing "Paste Special, Values," you change the formulas to values, and create a permanent record of your previously-run and copied scenario.

Detailed Discussion of Sheet 2 Inputs and Outputs

If you spend any time using this model, you will spend most of your time with Sheet 2. Sheet 2 allows you to test a wide range of assumptions, and shows you how they'll affect Social Security's long-run solvency and capacity to pay benefits.

The main purpose is comparative. If different scenarios are tested against the same forecast, how will each of them turn out?

What size front-end federal investment in Social Security is needed if solvency is to be achieved? If the federal investment option is chosen, how large will the Trust Fund become, as a percent of GDP? How large will PRA's become, as a percent of GDP? How will the size of the Trust Fund change, over time, as a percent of GDP? How will these values change, when other assumptions are changed?

If you decide to achieve solvency by cutting benefits, how deep will the cuts have to be? When will they be needed?

If you'd rather protect benefits by raising taxes, how high will you have to raise them?

Those are the questions you can ask, and test, using our Solvency Forecasting Spreadsheet Model.

INPUT Section

Tax Rate: Period 1 through Period 6. The first set of inputs, on Sheet 2, affect the Payroll Tax Rate. You can choose as many as six different rates over the 1999-2075 forecasting period covered by the model. It makes sense to let 1999 be 12.4%, as it is under current law, no matter what you do with later years. (Note: 12.4% is the combined employer-employee rate.)

Note the "Cautions" column, Column F. If you pick a Start Year for Period 2, say, which is later in time than the Start Year for Period 3, the Caution column will give you a warning in the line for Period 3, saying "Too Low".

Benefit Rate: Phase 1 through Phase 4. The second set of inputs allow you to adjust the benefit rate. If you keep it at 100%, that preserves the "current law" benefit schedule.

Lower rates allow you to simulate the effects of any policy change that might reduce, or increase, the total benefit percentage. Perhaps you've read that an increase in the retirement age can improve the actuarial balance by 0.49 percentage points. So you're looking for a reduction in the benefit percentage that corresponds to an actuarial balance of -1.57% (-2.06% + 0.49% = -1.57%). Changing the benefit percentage to 96.8% from year 2000 on will give you an actuarial balance of -1.57%. The same actuarial balance effect can be achieved by lowering benefits to 97% in 2028, to 94% in 2040, and to 91% in 2060.

Note the "Cautions" column, Column F. If you pick a Start Year for Phase 2, say, which is later in time than the Start Year for Phase 3, the Caution column will give you a warning in the line for Phase 3, saying "Too Low".

Total Federal Subsidy to Social Security (in Trillions of $). Input any number you like. Watch the effect on the Trust Fund ratio in 2070 and 2075, in the Output Section below. Once the Trust Fund ratio is just as high in 2075 as it is in 2070, you've discovered the starting subsidy that assures lasting solvency.

Number of Years Needed for Subsidy to be Paid. Determines the time period over which the subsidy is given to Social Security. The more years taken, the weaker the impact. The faster the subsidy goes in, the stronger the impact.

Trust Fund Portfolio Real ROI (2000-2045). (Inflation is handled separately. This line controls the real return rate on Trust Fund investments, not the nominal return rate.) If the Trust Fund invests solely in government bonds, the default real return rate is 2.9%.

If you anticipate a different type of portfolio, the value you choose depends on two main variables - the portfolio mix you want, e.g. 50% stocks and 50% bonds, and what you think the real rate of return on stocks will be through 2045. If you think the real return on stocks will be 5% and the real return on bonds will be 3%, and you want a portfolio mix that's 50% stocks and 50% bonds, you'll pick a 4% real return for this line.

Trust Fund Portfolio Real ROI (2046-2075). This allows you to estimate the real return rate from 2046 onward. If you think that slowing population growth will also slow down the GDP growth rate and the real return rate on stocks, you might lower the estimate slightly on this line.

How many years ramping up to the target ROI? If the Trust Fund begins adding stocks, it can't convert overnight from an all-bonds portfolio to a partially stocks portfolio. This line allows you to set the speed at which it ramps up to its target return rate.

Are Faster GDP Growth and Slower Inflation Assumed? (Y or N) If Y, then the model uses Social Security's cost and revenue numbers for 1% faster GDP growth and 1% slower inflation. Otherwise it uses the Social Security numbers from the Trustees' report. Note: If you type in "Yes", the Cautions column will warn you to type "Y" or "y" only. And the model will treat a "Yes" as a "No."

Is Earned Income Cap increased? (Y or N) If Y, the model projects an immediate expansion in the size of the tax base, followed by an eventual expansion in the obligations to retirees.

Are state and local government new-hires added? (Y or N) If Y, the model projects a gradual increase in the size of the tax base, followed by a gradual expansion in the obligations to retirees.

Is Medicare diversion ended? (Y or N) If Y, the model projects an increase in the taxes collected from retirees.

PRA Contribution Rate. If you don't want to model the effects of a PRA program, input zero. If you do, type in the contribution rate (as a percent of payroll). e.g. 2%, or 2.4%, or 3%, etc.

Senator Kerrey's Birth PRA Tax Rate (0%, 0.1%, or 0.2%) If you don't want to model the effects of a Birth PRA, input 0.0%. If you do want to model it, input the additional tax rate, as a percent of taxable payroll. 0.2% is recommended, but you can choose any amount. (NOTE: For Excel to read 0.2% as 0.2%, you must type in the zero, the decimal, and then the two. If you only type the decimal and the two, Excel will read it as 20%. An annoying quirk, I'm afraid.) In this model, contributions for Birth PRA's start in 2000, are compounded for twenty years, and then deposited in each cohort's accounts as they enter the workforce. (I assume that the actual contribution comes not from payroll taxes, but from the federal budget. It is modeled as a payroll tax contribution simply for computational convenience.)

PRA Portfolio Real ROI: 2000 - 2045. (Inflation is handled separately. This asks for the real return rate, over and above inflation.) It won't matter what you input here if you set the PRA contribution rate at 0.0%. However, if you're modeling the effects of a PRA, you'll want to choose a reasonable estimate for the real ROI. (NOTE: This line reads 2000 - 2045 no matter what the model's start year is. This works fine so long as the 1999 Trustee numbers are being used. As soon as a new set of Trustee numbers are used, these dates become slightly misleading. )

PRA Portfolio Real ROI: 2046 - 2075. Same as above, except this allows you to change assumptions on the real rate of return for the last 30 years of the model.

PRA Real ROI, Age 62 to Retirement. This allows you to set a lower real rate of return for each cohort's last five working years. Many observers argue that PRA portfolios would shift to an all-bond mix for the last five years. If you agree, a real return rate of 3.0% would make sense. (As always, inflation is handled separately by the model.)

PRA Real ROI, Retirement Years. It is widely assumed that PRA's will be converted to annuities on retirement. It is also assumed that the real return rate on annuity investments will be the bond rate. If you agree, a real return rate of 3.0% would make sense. (As always, inflation is handled separately by the model.)

PRA Management Fee. The model subtracts this fee from the Real ROI return rate. 0.1% is suggested, although you may think a higher or lower rate is appropriate. No fee is subtracted from the real return rate, age 62 onward.

"PRA Incentive" (Annuity % not counted toward SS benefits). This only applies if PRA's are being funded, obviously. 10% is suggested, on the theory that Congress would be likely to exempt at least ten percent of a person's monthly annuity payment before taking the rest of the annuity into account in determining how much the Social Security benefit check should be reduced by. If Feldstein's formula is adopted, this would be set at 25%. If the Archer-Shaw formula is adopted, this would be 0%. If you set this at 0%, the model will include all PRA annuity payments as part of the total benefit flow.

PRA Inheritance Leakage Factor - Last 20 Work Yrs. This is set at 0.1% a year, based on estimates by Social Security actuaries that the total leakage will reduce PRA assets at retirement by about 2 1/2 %. If you have another estimate you like better, you can change it.

PRA Inheritance Leakage Factor - Retirement. This is set at 0.0%, on the assumption that the annuitization of PRA's makes this irrelevant. But if you foresee retirees drawing down their own PRA's, you might want to input some value that accounts for inheritance leakage during retirement.

PRA Duration (10 or 15 or 20, default 10). If you believe that PRA's will be converted on retirement into ten year annuities (the most cost-effective strategy), input 10. If you believe that they will be converted into lifetime annuities, input 20. For convenience in calculating, the model assumes that everybody lives for 20 years after retirement, draws down their assets at a steady pace as they do, and then dies. Obviously not an accurate assumption, but once it's averaged across a number of cohorts, it's probably pretty close. The model will also support an intermediate scenario of 15 years. It doesn't support any other choices.

What borrowing rate is allowed? (0 or 1) The default value is 0. But the model also supports the Weaver-Scheiber strategy of funding a transition to PRA's through borrowing, then ultimately paying back the debt as PRA's mature. If you want to test this strategy, input 1. If borrowing is allowed, Social Security never becomes insolvent, but the debt can grow without limit. You will find the ultimate size of the debt, as a percent of GDP, in the Output section if you choose this option. (Note: If you don't enter 0 or 1, the Cautions column will ask you for a 0 or a 1.)


Year that Trust Fund Becomes Insolvent. If the Trust Fund balance drops to 0, the model will report the year in which the Fund first became insolvent.

75 Year Actuarial Balance (as of Dec. 31, 1998). The model calculates the Actuarial Balance for the first 75 years. If 1999 data are being used, this covers 1999-2073, and calculates the Present Values as of Dec. 31, 1998. If later data are being used, the starting point changes, and the first 75 years change as well. The "as of" would then need to be changed, in order to remain accurate. NOTE: Social Security's official number for its 1999 report is
-2.07%. Because the model calculates in whole years, while Social Security calculates in half years, the model reports a Base Case actuarial balance of

% of GDP - in 2025, 2050, 2070, and 2075, for the Trust Fund, and for PRA's. These lines report the total size of the Trust Fund, and of PRA accounts, as a percentage of GDP, for the years given. If you see the Trust Fund declining as a percent of GDP, that's a clear sign that lasting solvency has not been achieved.

Trust Fund Ratio in 2070, and in 2075. These two lines report the Trust Fund Ratio, the ratio of the Trust Fund at the end of the year to the benefit obligations for that year. If the ratio is declining from 2070 to 2075, lasting solvency has not been achieved. If the ratio remains stable, lasting solvency has been achieved.

(Note: The model compares the size of the Trust Fund to the size of the benefit obligations for the same year, while Social Security compares one year's Trust Fund balance to the size of the benefit obligations for the following years. I would have done that, but I wanted to use 2075 as my end year, and I didn't have a number for 2076. I think the Ratio comparisons between 2075 and 2070 as I've constructed them serve exactly the same purpose, though, so I don't believe this discrepancy matters at all.)

Insolvency Scenario 1 - Benefit Cuts: Maximum benefit cut needed by 2075. If the program is to remain solvent, with no increase in taxes, a benefit cut may be necessary. This line quantifies the size of the benefit cut that's needed to maintain solvency, as a percentage of the benefit cuts that are otherwise scheduled. (Current law benefits, minus any benefit reduction you've already plugged into the model.)

Total value of additional benefit cuts 2000 - 2075 ($ in trillions) If further benefit cuts are needed in order to avoid insolvency, on top of any cuts you may have allowed for by reducing the benefit percentage in the Input section, this line will quantify the total.

Insolvency Scenario 2 - Payroll Tax Hikes. If the program is to remain solvent, with no reduction in benefits, a tax hike may be necessary. This line quantifies the size of the tax increase needed, as a percentage of taxable payroll, as of 2075.

Payroll Tax Rate in 2075. This line builds on the previous one, and reports the total payroll tax rate that's needed in 2075, if insolvency is to be avoided.

Combined Payroll + PRA Tax Rate in 2075. This line builds on the previous one, and reports the total payroll tax rate plus any PRA contribution rate, for 2075. (NOTE: If the borrowing option is chosen, this line and the previous four will all say "NA", for Not Applicable.)

Insolvency Scenario 3 - Borrowing. Maximum Debt % of GDP. If the "No Borrowing" option is selected above, this will return "NA", for Not Applicable. If the borrowing option is chosen, this will report the maximum size of the debt vs. GDP over the time period 2000 - 2075.

Year of Maximum Debt/GDP Ratio. If the borrowing option is chosen, this will report the year in which the debt/GDP ratio is the highest. (It looks exclusively at Social Security debt, of course)

Debt % of GDP in 2075 If the borrowing option is chosen, this line reports the size of Social Security's debt as a % of GDP in 2075.

Archer-Shaw INPUT - First Year Archer-Shaw tax credit subsidy ends. If you're modeling the Archer-Shaw proposal, you'll need to input on this line the year that the tax credit subsidy ends.

Archer-Shaw OUTPUT - Cumulative cost of Archer-Shaw tax credit subsidy ($ in Trillions). If you've entered a number on the INPUT line above, you can read the amount of the subsidy on this line. (If you're not modeling the Archer-Shaw proposal, and you're ready to copy the scenario you've created, you won't even need to pick up these last two lines.)

Sheet 3. Solvency Calculations. This page performs most of the calculations. (If your PC overloads, and cells don't update properly, you'll have to look line by line for values that don't match your Sheet 2 Input values. Once you find them, refresh the formula, and the values will update.)

Sheet 4. PRA Investments and PRA Annuities. This page calculates the growth of PRA investments, age group by age group, and the drawdown of PRA funds once the age group retires. The total money drawn by retirees each year is calculated on Sheet 4, then picked up on Sheet 3 and incorporated into Sheet 3's solvency calculations.

Sheet 5. Data from the Trustees' Report, and Other Assumptions. This sheet contains the year by year data from the Social Security Trustees Annual Report. It also contains data obtained from Social Security on the adjustments to the data that need to be made, if one assumes 1% faster GDP growth and 1% slower inflation.

Note: If you wish, you can update this model yourself in the spring of 2000, or 2001, by replacing on Sheet 5 the numbers from the old Trustees' Report with the numbers from the new one. (There is an exception. The "faster GDP growth, slower inflation" number set isn't available in the Annual Report. I will obtain those numbers from Social Security again, when the new report comes out, and email copies to those who have already received copies of this report from me with its original 1999 numbers.)

To request your copy of the Solvency Forecasting Spreadsheet Model, you may email your request to us at

CAUTION: Before you start using this file, make sure you save a backup copy under a different name. That way, if you make changes, or something goes wrong, you'll still have a working copy of the original.

CAUTION: This is a very large file. It's a good idea not to have any other programs running when you use this file. It can overload a PC, and the symptoms aren't always noticeable. If it overloads, not all values will get changed when they're supposed to, in all the cells, and the residue values from previous scenarios will give you incorrect results on your current scenario.

One good way to check for incorrect values is to reset everything, from time to time, to the base case. (Payroll tax at 12.4%, benefits at 100%, no changes to the current situation.) f you do get an insolvency date of 2034, and an actuarial balance of -2.06%, you're probably OK. On the other hand, if you don't get an insolvency date of 2034 and an actuarial balance of -2.06%, that's a good sign that something is amiss.

If the values in a small string of cells haven't been properly updated, there's no easy way to find the flaw. I tend to look on Sheet 3, looking line by line at the values in the out-years, 2060 to 2075, to see if I can find the cells that are still retaining out-of-date values. If you're running PRA scenarios, you may also want to check Sheet 4, just to see if the values on Sheet 4 are as you expect them to be.

When I do find the cells that aren't showing the proper values, given the current input setting, I re-enter the same formulas in those cells that they already had.

As soon as the formulas in the offending cells have been refreshed, the value also updates.

As this model is presently designed, it's just barely works on my current PC. (200 megahertz, 32 MB of RAM) If I were to expand the model much at all, Excel probably wouldn't be able to handle it accurately. How it performs for you will depend on the capacity of your PC. As currently designed, though, it normally works fine. As you use it, you'll find yourself gaining a greater and greater degree of insight into Social Security's future.

Return to our Home Page

Common Sense on Social Security
An initiative of The Wallcharts Workshop
A Non-Profit Successor to the Collaborative Democracy Project

Questions? Reactions? Feedback?
You can reach us at

For more information on Social Security, the following web sites are suggested

The Concord Coalition

The Social Security Administration

Page Version 1.01
Revision Date April 13, 2006