SocSec

SocSec returns the calculation of a Social Security benefit amount.

Example: if the DeterminationYear is 2021, SSCOLA is calculated using the average CPI-W for 2021 divided by the CPI-W for 2020, and is used to determine the Social Security benefits payable in calendar year 2022.

More information can be found at the Social Security web site.

Syntax

SocSec (CurrentYear, Birthdate, StartDate, QuitDate, EarningsAmount, [EarningsYear], [EarningsRange], [EarningsRate], [EarningsRatePast], [WageInflation], [COLA], [CalcType], [ROR], [iRate], [Mort])

Parameter Name

Description

CurrentYear

Law year, a 4 digit number.

Birthdate

Date of birth as m/d/yyyy.

StartDate

Date (or age) of benefit commencement. The following options are available:

StartDate	Description
Date	Date of commencement date as m/d/yyyy.
Age	Age of commencement.
“fra”	Social Security full retirement age
“era”	Social Security earliest retirement age

QuitDate

Date (or age) of when the person stops working. The following options are available:

QuitDate	Description
Date	Date of quit working as m/d/yyyy.
Age	Age of quit working.
blank	Same as StartDate.

EarningsAmount

Date (or age) of when the person stops working. The following options are available:

EarningsAmount	Description
A dollar amount	Earnings for the year specified in EarningsYear.
“range”	Earnings in each year is provided in a named range of cells a specified in the parameter EarningsRange.
“max”	Earnings in every year equal to the taxable wage base; this gives the maximum Social Security benefit amount.

EarningsYear

Year of the earnings, if EarningsAmount is a dollar amount. This parameter is ignored if EarningsAmount = "range" or "max".

Optional: default = CurrentYear.

EarningsRange

Name of a 2-column range of year & earnings. Used only if EarningsYear = "range".

Optional.

EarningsRate

Future earnings increase rate (e.g. 5.25% or .0525) to project earnings forward from the last provided amount. This parameter is ignored if EarningsAmount = "max".

EarningsRatePast

Prior earnings increase rate (e.g. 5.25% or .0525) to project earnings backward from earliest provided amount. This parameter is ignored if EarningsAmount = "max".

Alternatively, you can enter "nae" to project earnings backward using past NAE ratios.

WageInflation

The assumed rate of increase in the National Average Wage.

This is used only for a projected calculation.

Optional: default = 0%

COLA

Future Social Security COLA assumption (based on CPI-U) to determine the benefit amount.

CalcType

The value to return:

CalcType	Value to Return
“benefit”	Social Security benefit amount.
“elig yr”	Eligibility year; the year in which a person is first eligible to receive benefits. For retirement, eligibility is at age 62.
“index yr”	Indexing year; an individual's earnings are always indexed to the average wage level two years prior to the eligibility year.
“final earn”	Earnings in the final year of employment.
“total indexed earn”	The sum of up to 35 years of the highest indexed earnings.
“aime”	Average indexed monthly earnings. This average summarizes up to 35 years of a worker's indexed earnings.
“bp1”	First bend point in the PIA formula.
“bp2”	Second bend point in the PIA formula.
“b1”	PIA amount based on 90% of AIME up to the first bend point.
“b2”	PIA amount based on 32% of AIME from the first bend point up to the second bend point.
“b3”	PIA amount based on 15% AIME in excess of the second bend point.
“pia”	The "primary insurance amount" (PIA) is the benefit (before rounding down to next lower whole dollar) a person would receive if he/she elects to begin receiving retirement benefits at his/her normal retirement age. At this age, the benefit is neither reduced for early retirement nor increased for delayed retirement.
“piaadj”	The PIA adjusted for COLAs from the eligibility year to the year of commencement.
“yoc”	Year of commencement.
“commage”	Age of commencement.
“ss fra”	Full retirement age (or Normal Retirement Age, NRA), the age at which retirement benefits (before rounding) are equal to the "primary insurance amount."
“mo early”	The number of months by which the commencement age precedes the full retirement age. (A negative number is returned for late commencement.)
“ss erf”	The benefit early reduction factor for commencement prior to the full retirement age. (Also, the benefit late increase factor for commencement after the full retirement age.)
“pv factor”	A present value factor used to illustrate the value of the Social Security benefit, based on: · Benefit amount. · Age at commencement. · Future annual benefit increases based on the COLA parameter. · Discount rate based on the iRate parameter. · Mortality table based on the Mort parameter.
“pv benefit”	The present value of the Social Security benefit = “benefit” × “pv factor”.
“tot tax ee”	Total taxes paid by the employee.
“tot tax er”	Total taxes paid by the employer.
“tot tax”	Total taxes paid.
“tot tax ee wint”	Total taxes paid by the employee with interest based on the ROR parameter.
“tot tax er wint”	Total taxes paid by the employee with interest based on the ROR parameter.
“tot tax wint”	Total taxes paid with interest based on the ROR parameter.
“value ratio”	“pv benefit” ÷ “tot tax wint”
“ror”	The theoretical rate of return; i.e. the effective rate of return such that the “tot tax wint” = “pv benefit”.
“replacement ratio”	Social Security benefit ÷ final earnings.

Optional: default = “benefit”.

ROR

Assumed rate of return, used only to calculate “tot tax ee wint”, “tot tax er wint”, or “tot tax wint”.

Optional.

iRate

Assumed discount rate, used only to calculate “pv factor”.

Optional.

Mort

Assumed mortality table, used only to calculate “pv factor”.

Optional; default = “LS2022” (unisex mortality based on the 417(e) Applicable Mortality Table for lump sum payments made in 2022).