by Brian J. McKenna, J.D., CFP®, CPA, CFA
The Social Security Administration is required to provide all eligible individuals with an annual benefit statement. But their calculations include a zero inflation assumption, continued earnings through the retirement age, and future earnings equal to the worker's current earnings level. A working knowledge of the benefit calculation will allow financial planners to easily modify these assumptions to evaluate the benefit impact of different scenarios.
This paper will review the basic Social Security retirement benefit calculation using an Excel spreadsheet. Three series of calculations are required to determine the benefit. The first series of calculations involves four steps and produces an earnings amount known as the average indexed monthly earnings (AIME). The second series of calculations involves two steps and produces the primary insurance account (PIA). This is the individual's age 62 early retirement benefit. The third series of calculations increases the PIA to include cost-of-living-adjustments between early retirement and full retirement. An Excel spreadsheet can be an excellent tool to quickly determine all of these calculations while also fully disclosing all of the underlying assumptions embedded in the calculations.
Basic Full Retirement Benefit Calculations
The calculation of Social Security benefits can be a daunting task for several reasons. The formula for determining benefits is a complex calculation that includes the 35 highest-earnings years. These earnings are adjusted upward with a wage indexing formula that changes the index value each year until age 60. The benefit formula relies on "bend points," which are increased by the wage index until age 62, and a different indexing method known as the cost-of-living adjustment is used after age 62. The full retirement age, the reductions for early retirement, and the increases for delayed retirement will vary depending on the year the client was born. All of these calculations are required to determine the basic retirement benefit.
Additional calculations would be required to determine other Social Security benefits such as disability or spouse annuity. A spouse may be entitled to a benefit equal to the greater of their own earnings record or one-half of the other spouse's earnings record. For purposes of this analysis, we assume the spouse has a higher benefit under his or her own earnings record and the client's retirement decision can disregard any impact on the spouse.1
Average Indexed Monthly Earnings
Two numbers are central to the Social Security benefit calculation. These numbers are the average indexed monthly earnings (AIME) and the primary insurance amount (PIA). Since the PIA is a product of the AIME, we will address the AIME calculations first. This calculation requires four steps.
Actual lifetime earnings. The first step is to determine your actual Social Security earnings during your lifetime. This information is reported to you annually on your Social Security Statement. I recommend starting an Excel spreadsheet with the following three columns:
A
B
C
Year
Age Actual Wages
The first entry in the "Year" column will be the year of birth and the first entry in the "Age" column will be zero. The second entry in the "Year" column would be the year that is one day before the individual's first birthday and the age would be one. Social Security considers an individual to have attained a given age the day before their birthday.
You can quickly complete the rows for columns A and B through age 70 with the fill handle function in Excel. Excel will automatically fill cells based on the relationship of the data in adjacent cells. The fill handle function can be activated by depressing and holding the left click button on the mouse to highlight the two columns and two rows that include ages 0 and 1. A plus sign will appear in the lower right corner of the highlighted space. Place the cursor on the plus sign and depress and hold the left-click button of the mouse while pulling the cursor down to row 72 (Age 70). The space borders are marked as the cursor is pulled down. When the left button of the mouse is released, the columns and rows will automatically fill with the correct years and ages.
The next step is to insert the client's actual wages for each year in column C based on the information reflected in client's Social Security annual statement. The first wage entry may not occur until you are about 16 to 18 lines down—that is, when taxable employment began as a teenager. While there are no entries in these initial wage lines, this format will be helpful with future calculations tied to ages 60, 62, 66, and 70.
Indexed lifetime earnings. The next three columns should be labeled as follows:
D
E
F
Average Wages Index
Factor Indexed
Wages
The average wages column represents the National Average Wage Index compiled by the Social Security Administration. This information is available on the Social Security Web site.2 While this information must be entered manually the first time, financial planners can copy and paste this information for subsequent use with other clients. This is the first of the indexing calculations.
The Average Wage amounts are known through the year 2003. You could project the average wage amounts after 2003. Social Security assumes there will be no increases in the average wage amount. Financial planners might consider multiplying each successive year by 1.04923 because the geometric average annual increase in this index series has been 4.923 percent for the 52 years from 1951 to 2003. (((2003 Average Wages / 1951 Average Wages) taken to the 1/52 exponential power) – 1). But financial planners need to be alert to the fact that Social Security reform proposals have been made that would modify or replace this series with a future index series that would be expected to produce a lower value.
The Index Factor is a formula that divides the most recent available average wages ($34,064.95 in 2003) by the average wages for a given year. For example, the current Index Factor for 1970 would be 5.506568 (the 2003 average wage of $34,064.95 divided by the 1970 average wage of $6,186.29). The Wage Index Factor will change every year for an individual until they reach age 60. For example, if the 2004 average wages were $36,000, the new index factor for 1970 would be 5.81937 (the average wage in 2004 of $36,000 divided by $6,186.29 the average wage in 1970). At age 60, indexing stops and the index factor will be simply one for each year of earnings after age 59.
A financial planner can forecast future average wages until the year the client reaches age 60. The Average Wage amount for age 60 becomes an absolute cell reference for future calculations. If the actual 1970 average wage amount were in cell D20 and the forecasted average wage amount for the client at age 60 was in cell D55, the index factor formula for 1970 would be: E20 = (d20/$d$55). This formula could then be copied down the Index Factor in Column E. The Average Wage column can be left empty for years after age 60 and a value of one can be inserted in the Index Factor column. The final column, Indexed Wages, is simply the product of Actual Wages for each year times the Index Factor. You can write the formula once, (F20 = C20*E20), and copy this down the column.
Highest 35 years of indexed earnings. The next step to calculate AIME is to identify the client's 35 highest years of indexed wages. For purposes of control and verification, I would recommend that you create four columns:
G
H
I
J
Included Wages Excluded Wages Included Years Excluded Years
You can use the data/sort command to rank the indexed wages. Once you have determined the wage amount that is the 35th highest earnings year, enter this amount in cell K1 and undo the sort so that the data returns to a chronological order.
The formulas for Included Wages and Excluded Wages would then be as follows:
Included Wages: G1 = IF (F1>=$k$1,F1,0)
Excluded Wages: H1 = IF (F1<$k$1,F1,0)
This Included Wage formula provides that the indexed wages for this year will be included in the final calculation if they are equal to or greater than the cut-off dollar amount locked in at cell K1. If the wages are below the cut-off amount, a zero will appear in this column for this year. The Excluded Wage formula provides that the indexed wages for this year will be excluded from the final calculation if they are less than the cut-off dollar amount locked in at cell K1. If the wages are above the cut-off amount, a zero will appear in this column for this year. You can copy these formulas in each column down through each row until the row for age 66.
A control function can be built into this calculation by counting the included and excluded years. The formula for each column would then be as follows:
Included Years: I1 = IF (G1>0,1,0)
Excluded Years: J1 = IF (H1>0,1,0)
This Included Years formula provides that the year will be included in the 35 highest years if the wages are included. If the wages are excluded, a zero will appear. The Excluded Years formula provides that the year will be excluded from the 35 highest years if the wages are excluded. If the wages are included, a zero will appear. You can copy these formulas in each column down through each row until the row for age 66. An entry of 1 will be required in the Excluded Years column for any years where the client had no taxable wages.
Average indexed monthly earnings. The final step is to total the columns of Indexed Wages, Included Wages, Excluded Wages, Included Years, and Excluded Years. The formula would be F69 = sum(f2:f68). A financial planner can copy this formula for the other four columns. If the total for Included Years does not equal 35, you will need to adjust the cut-off value up or down depending on whether you need to include or exclude more years. As a final check on the accuracy of the calculations, the total of Included Wages and Excluded Wages should equal the total of Indexed Wages. The total of Included Years and Excluded Years should equal the final year of the calculation—for example, 66 if you are calculating through age 66. The amount you determined for total Included Wages should be divided by 420 (35 years * 12 months). The resulting amount is the AIME.
Primary Insurance Amount
The calculation of the primary insurance amount (PIA)—the individual's age 62 early retirement benefit—starts with the AIME. The PIA is calculated like a regressive income tax with the AIME being the equivalent of taxable income. There are three brackets known as "bends," and three rates. The brackets are indexed to the National Average Wage Index. The indexing continues until age 62 based upon the age 60 average wages. The bends in effect during the year the individual turns 62 are the brackets used to calculate the PIA. The bends for the year 2005 cohort (for our purposes, those turning 62 in 2005) are $0–$627 for the first bend, $628–$3,779 for the second bend, and anything over $3,779 for the third bend.
Bend calculations. The first bend is calculated as follows for 2005: ((2003 Average Wages divided by 1977 Average Wages) times the 1979 first bend point) equals (($34,064.95 / $9,779.44) * $180) = $627
The second bend is calculated as follows for 2005: ((2003 Average Wages divided by 1977 Average Wages) times the 1979 second bend point) equals (($34,064.95 / $9,779.44) * $1,085) = $3,779
The only value that will change in this calculation is the current year average wages. This value is the 2003 average wages of $34,064.95 used to determine the 2005 bend points. The financial planner can forecast the average wage amount expected for the year the client turns age 60 to forecast the bends that would apply to the client's cohort.
Rate calculations. The rate in the first bend is 90 percent. The rate in the second bend is 32 percent. The rate in the third and last bend is 15 percent. As an example, if the client's AIME were calculated to be $4,000 and we were to use the 2005 brackets, the PIA would be as follows:
First Bend: $
627 @
90%
= $ 564
Second Bend: $3,152 ($3,779 –
$627) @ 32% = $1,008
Third Bend: $ 221 ($4,000
– $3,779) @
15%
= $ 33
PIA
$ 1,605
Cost-of-Living Adjustments
A few final points need to be made to determine the basic full retirement benefit. As you will recall, wage indexing stops at age 60 and the wage inflation indexing of the bends stops at age 62. If this individual were entitled to receive full retirement benefits at age 66 and they were to wait until age 66 to apply for benefits, a final inflation adjustment would be needed. This is known as the cost-of-living adjustment (COLA). The COLA index series can be found at the Social Security Web site.3
The geometric average annual increase in the COLA series has been 4.46 percent for the 30-year period from 1975 to 2004. A financial planner might use this value to forecast future changes in the COLA. One reform proposal that has been made would be to modify or replace this series with a future index series that would be expected to produce a lower value. This might be accomplished by adjusting elements of the CPI that might have a lesser impact on senior citizens such as new housing.
If we assume that the cost-of-living-adjustments were 3 percent in each of the four years from 62 to 65, the final benefit would be calculated as follows:
PIA
$ 1,605
Age
62—COLA
x 1.0300
Age
63—COLA
x 1.0300
Age
64—COLA
x 1.0300
Age
65—COLA
x 1.0300
Full Retirement Benefit $
1,806
The retirement benefit can be increased if the client elects delayed retirement and will be decreased if he or she elects early retirement.
Conclusion
The Social Security Administration offers a free software program for calculating benefits.4 This program allows financial planners to modify the base case assumptions included in the annual benefit statement—that is, zero inflation, continued employment wages at the current level, and employment until full retirement age. But using an Excel spreadsheet to calculate benefits will allow a financial planner to integrate all aspects of the client's financial plan into a single document.
As an example, an Excel spreadsheet could be used to examine various economic scenarios with different future inflation rates that would affect the client's entire financial plan rather than just the Social Security benefits. Additionally, an integrated spreadsheet could consider the full financial impact of continued employment versus retirement (wages, income taxes, employment taxes, retirement benefits, and so on). The Social Security Administration software could be used to confirm the accuracy of the Excel spreadsheet benefit calculations, but could not provide the client with a complete financial picture.
An additional use of the Excel spreadsheet would be to calculate Social Security benefits to determine the impact of proposed amendments to the Social Security formula. The current benefit calculation uses two inflation-adjustment formulas: (1) the wage indexing formula through age 60 for purposes of wages and through age 62 for purposes of determining bends, or brackets; and (2) the cost-of-living adjustment thereafter. A change in these indexing formulas could significantly reduce future benefits without appearing to be a benefit reduction. At a minimum, the financial planner should know how to calculate the financial impact of any proposed change to the indexing formulas. All of these calculations are transparent in the Excel spreadsheet.
Brian J. McKenna, J.D., CFP®, CPA, CFA, serves as the director of the Center for Financial Analysis in the Graham School of Management at Saint Xavier University in Chicago, Illinois.
Endnotes
- Clarence Rose, "Social Security Retirement Planning Considerations for Married Couples in Family-Owned Businesses," [ITAL BG] Journal of Financial Planning [ITAL END] August 2004: 56–63.
- The National Average Wage Index can be found at www.ssa.gov/OACT/COLA/AWI.html.
- The Cost of Living inflation series can be found at www.ssa.gov/OACT/COLA/colaseries.html.
- The software can be downloaded for free at
www.ssa.gov/OACT/ANYPIA/anypia.html.
