In this assignment you will follow the steps to arriving at the PIA (Primary Insurance Amount) for a

hypothetical individual. The end result will be a better understanding of the calculation used by the

Social Security Administration to determine a person’s retirement benefit at their FRA (Full Retirement

Age)

1. Read SSA Publication No. 05-10070 to familiarize yourself with the calculation process. This

document is attached to this assignment as a separate PDF file.

2. In the Excel workbook on the worksheet labeled “PIA Calc” I have replicated the table for the SSA

publication and also populated it with some hypothetical earnings history for a fictitious person born in

1952.

3. The yellow highlighted cells indicate the calculations that you are responsible to enter.

4. In the column labeled “Indexed Earnings” you need to write an equation to accurately calculate the

indexed earnings based on the earnings history and the “Index Factor” for each year. Note: Your

equation must accomodate the years where the person earned more than the Maximum Earnings limit.

The MIN or IF functions in Excel are helpful.

5. In the cell labeled “AIME” (cell H3) you need to calculate the person’s Average Indexed Monthly

Earnings based on the Indexed Earnings column from step 4. This is a little tricky. Use steps 3 and 4

from the SSA publication for guidance. Some people will have to do some preliminary calculations to

end up at the AIME, but for those of you who like a challenge, it IS possible to write one equation in cell

H3 to calculate the AIME. Hint: The hardest part of this step is finding the largest 35 years of indexed

earnings. The LARGE function in Excel is perfect for this. Use Excel help or Google for guidance on

using this function. Also, remember to round DOWN to the nearest dollar as it says in the SSA

publication.

6. In the cell labeled “PIA” (cell I3) you need to calculate the person’s PIA (Primary Insurance Amount).

Follow steps 5 and 6 from the SSA publication to accomplish this. Once again it may be easier for

some of you to do some preliminary calculations in other cells and then combine those to get the PIA in

cell I3, but it IS possible to write one equation in cell I3 to calculate the PIA.

Hint: To check your accuracy you can fill out the SSA Publication table by hand and walk through the

steps with a calculator. It will take some time, but is the best way to check your Excel answer if you

doubt your Excel abilities.

Click here to have a similar A+ quality paper

Click here to have a similar A+ quality paper