Social Security Add-in

SSxl97Fut.xla is an Excel '97 add-in containing a function called SS() that calculates PIAs under chosen assumptions. Download the zipped file addins.zip.

Instructions: After unzipping the addins file, copy the add-in file to the Library folder of your Excel program. Next, run Excel, and choose Add-Ins... under the Tools menu option. Check the box next to the "Social Security Add-in" option and OK. If it does not show up, you have either copied the file to another location (in which case you can Browse... to find it), or you have not gotten out of Excel and subsequently back in after copying (in which case you can get out and back in, or Browse).

The SS function will appear under the User functions section of Insert/Function..., or you can just type it in [=ss(birth,mindate,pay,...)]. It has three or more arguments. Birthdate, Mindate, and Pays. The Mindate is a new parameter which forces the calculation to a later date than the age specified with the SSControl. Leave blank to ignore. Any number of pays can be included (at least 1), the first of which is the SS Law year pay, and subsequent ones, if any, are preceding years. Pays before the last one entered are estimated using the last pay, at either NAE or 6%. Pays after the ss law year are assumed level, unless you specify a $0 amount at later time in the assumptions, explained below.

The assumptions for how the SS function behaves are displayed under the Data/SSControl menu option. Here you can specify the Law Year (1979-2004), Benefit Commencement Age (SSNRA, or fixed age), Future $0 Pay (never, after law year, or after a given age or year). Calculated PIAs are automatically updated when you change the assumptions.

Technical: for the two years prior to the ss law year, if no pay is entered, a discount rate of 1% more than the most recent cpi is used to estimate them. That follows the intermediate assumption the SSA uses for the real wage differential (1%), which is the average wage over the consumer price index.

Increases after the ss law year for any of the law amounts (e.g., wage bases, bend points, average earnings, pay,...) can now be entered in the SSControl.

Warning 1: Because the SS function relies on the assumptions in the SSControl, you would be wise to paste value the answers after calculating them and to label them. If not, you might change the assumptions for another worksheet, and the answers will change the next time you retrieve the first worksheet.

Warning 2: There is a known flaw in Excel for User-defined Functions that rely on the ParamArray keyword (which the SS function does, it allows multiple pays, it's like a Sum function). If you have more than one worksheet open at once, and hit calculate while on another sheet, the SS function will return a false number. Therefore, enter the function, calculate it, and paste value it (as in 1). The Excel 97 version does not have the problem.

The intent of the add-in is to be able to do PIA calculations inside of Excel, where data already is, for multiple employees at once, and without having to open up another program and enter data. The idea was to keep it simple. Mission accomplished.

The program has been checked against other programs, but you are encouraged to check it yourself before relying on it. It is complimentary from us, Dean & Company, to anyone interested.

If you have questions or comments, please contact us at Dean & Company.