In [269]:
# this book assumes you have wage index and earnings data up to last year (since they get filled in after the end of the year)
# I'm not doing this professionally so I'm not going to validate the inputs. Buyer beware.
YEAR_OF_BIRTH = 1979

# For the current year (2025)
FIRST_BEND_POINT = 1226
SECOND_BEND_POINT = 7391
TAXABLE_EARNINGS_LIMIT = 176100

# The amount of income you anticipate going forward in todays dollars
# FUTURE_SS_EARNINGS = 168600 # 2024 cap
FUTURE_SS_EARNINGS = 110000 

# from https://www.ssa.gov/OACT/COLA/awiseries.html and massaged to be clean tsv with no commas, dollar signs or percent signs
AWI_FILE = 'data/average_wage_index.tsv'

# Your earnings data from the SS website, formatted the same way. Sure would be nice if they had a download for this
EARNINGS_FILE = 'data/fakeearnings.tsv'
In [270]:
from datetime import datetime
import pandas as pd
import numpy as np
pd.options.display.float_format = '{:.2f}'.format
In [271]:
year_of_retirement = YEAR_OF_BIRTH + 67

print(f"Your full benefits are based on retirement in {year_of_retirement}")

Your full benefits are based on retirement in 2046
In [272]:
# Build a dataframe containing the SS earnings you'll generate in every year between now and your full retirement year. 
# Right now this just assumes a constant amount each year. If you wanted to model a different wage progression 
# (maybe you see a big promotion several years from now or the opposite, you're planning on coastFIREing) then you
# can do it by modifying how future_df is constructed here.
#
# Note 1. You don't need to consider future wage inflation because of how the calculations work. and
# 2. the numbers are going to get capped at this years SS taxable earnings limit which is, per
# the previous point a constant because future wage inflation isn't necessary to consider. 3. Whatever
# you do don't screw up the range of Year values. 

# this is + 1 because we need to get data for this year too.
years_to_retirement = year_of_retirement - datetime.now().year + 1
future_df = pd.DataFrame({
    'Year': range(datetime.now().year, datetime.now().year + years_to_retirement),
    'indexed_earnings': FUTURE_SS_EARNINGS
})

# TODO make sure values get clipped right
future_df['indexed_earnings'] = future_df['indexed_earnings'].clip(upper=TAXABLE_EARNINGS_LIMIT)

#future_df
In [273]:
awi_series = pd.read_table(AWI_FILE)

# index wages off of the most recent year this will obviously break 
# everything if the index file we loaded doesn't include data for the most recent year.
index_wage = awi_series[awi_series['Year'] == datetime.now().year - 1]['Wage'].item()
awi_series['index_multiplier'] = index_wage / awi_series['Wage']
In [274]:
# load wages and index each year using last year as the 1.0 index year
earnings = pd.read_table(EARNINGS_FILE)
indexed_earnings = awi_series.merge(earnings, left_on='Year', right_on='Work Year', how='inner')
indexed_earnings['indexed_earnings'] = (indexed_earnings['Taxed Social Security Earnings'] * indexed_earnings['index_multiplier']).round(2)

# add in the future years we already calculated
indexed_earnings = pd.concat([indexed_earnings, future_df], ignore_index=True)
In [275]:
# calculate AIME after each year of work
def calculate_all_aimes(df):
    """Calculate AIME for each year more efficiently"""
    results = []
    
    for year in indexed_earnings['Year']:
        earnings_to_date = indexed_earnings[indexed_earnings['Year'] <= year]['indexed_earnings']
        top_35 = earnings_to_date.nlargest(35)
        aime = top_35.sum() / 35 / 12
        results.append(aime)
    
    return results

indexed_earnings['AIME'] = calculate_all_aimes(indexed_earnings)
In [280]:
# And finally benefits
def calculate_benefits(aime):
    below_first = max(aime, FIRST_BEND_POINT)
    above_first = aime - below_first
    between_bands = max(aime, SECOND_BEND_POINT - FIRST_BEND_POINT)
    above_second = above_first - between_bands


indexed_earnings['below_first_bend'] = indexed_earnings['AIME'].clip(upper=FIRST_BEND_POINT)
indexed_earnings['between_bends'] = (indexed_earnings['AIME'] - indexed_earnings['below_first_bend']).clip(upper=SECOND_BEND_POINT - FIRST_BEND_POINT)
indexed_earnings['above_second_bend'] = indexed_earnings['AIME'] - indexed_earnings['between_bends'] - indexed_earnings['below_first_bend']
indexed_earnings['PIA'] = ((indexed_earnings['below_first_bend'] * 0.9) + (indexed_earnings['between_bends'] * 0.32) + (indexed_earnings['above_second_bend'] * 0.15)).round(2)
# indexed_earnings
In [277]:
indexed_earnings.plot(x='Year', y='PIA', figsize=(10, 6), grid=True)
Out[277]:
<Axes: xlabel='Year'>
No description has been provided for this image
In [278]:
# present key facts from the table
results_table = indexed_earnings[['Year', 'Taxed Social Security Earnings', 'indexed_earnings', 'PIA']]
results_table
Out[278]:
Year Taxed Social Security Earnings indexed_earnings PIA
0 2001 51848.09 110000.00 235.71
1 2002 52368.07 110000.01 471.43
2 2003 53648.22 109999.99 707.14
3 2004 56142.21 110000.01 942.86
4 2005 58196.46 109999.99 1130.13
5 2006 60871.35 110000.00 1213.94
6 2007 63633.80 110000.00 1297.75
7 2008 65097.64 110000.00 1381.56
8 2009 64115.92 110000.00 1465.37
9 2010 65631.30 110000.00 1549.18
10 2011 67687.75 110000.00 1632.98
11 2012 69801.33 110000.00 1716.79
12 2013 70693.49 110000.00 1800.60
13 2014 73202.84 110000.00 1884.41
14 2015 75749.59 109999.99 1968.22
15 2016 76605.57 110000.00 2052.03
16 2017 79250.96 110000.00 2135.84
17 2018 82123.40 110000.00 2219.65
18 2019 85201.02 110000.00 2303.46
19 2020 87608.40 110000.00 2387.27
20 2021 95398.50 110000.01 2471.08
21 2022 100469.71 110000.01 2554.89
22 2023 104921.37 110000.00 2638.70
23 2024 110000.00 110000.00 2722.51
24 2025 NaN 110000.00 2806.32
25 2026 NaN 110000.00 2890.13
26 2027 NaN 110000.00 2973.94
27 2028 NaN 110000.00 3057.75
28 2029 NaN 110000.00 3106.84
29 2030 NaN 110000.00 3146.12
30 2031 NaN 110000.00 3185.41
31 2032 NaN 110000.00 3224.69
32 2033 NaN 110000.00 3263.98
33 2034 NaN 110000.00 3303.26
34 2035 NaN 110000.00 3342.55
35 2036 NaN 110000.00 3342.55
36 2037 NaN 110000.00 3342.55
37 2038 NaN 110000.00 3342.55
38 2039 NaN 110000.00 3342.55
39 2040 NaN 110000.00 3342.55
40 2041 NaN 110000.00 3342.55
41 2042 NaN 110000.00 3342.55
42 2043 NaN 110000.00 3342.55
43 2044 NaN 110000.00 3342.55
44 2045 NaN 110000.00 3342.55
45 2046 NaN 110000.00 3342.55
In [279]:
results_table.to_csv('data/results_table.tsv', sep='\t', index=False)