Question;In this assignment you will follow the steps to arriving at the PIA (Primary Insurance Amount) for ahypothetical individual. The end result will be a better understanding of the calculation used by theSocial Security Administration to determine a person's retirement benefit at their FRA (Full RetirementAge)1. Read SSA Publication No. 05-10070 to familiarize yourself with the calculation process. Thisdocument 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 SSApublication and also populated it with some hypothetical earnings history for a fictitious person born in1952.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 theindexed earnings based on the earnings history and the "Index Factor" for each year. Note: Yourequation 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 MonthlyEarnings based on the Indexed Earnings column from step 4. This is a little tricky. Use steps 3 and 4from the SSA publication for guidance. Some people will have to do some preliminary calculations toend up at the AIME, but for those of you who like a challenge, it IS possible to write one equation in cellH3 to calculate the AIME. Hint: The hardest part of this step is finding the largest 35 years of indexedearnings. The LARGE function in Excel is perfect for this. Use Excel help or Google for guidance onusing this function. Also, remember to round DOWN to the nearest dollar as it says in the SSApublication.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 forsome of you to do some preliminary calculations in other cells and then combine those to get the PIA incell 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 thesteps with a calculator. It will take some time, but is the best way to check your Excel answer if youdoubt your Excel abilities.
Paper#48708 | Written in 18-Jul-2015Price : $29