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.
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.