Click here to have a similar A+ quality paper

Order Now

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.

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

Order Now