Social Security Spreadsheet Fun

Some dads whittle wooden cars for the soapbox derby. Others spend hours with the hood up in the garage tearing apart an old engine and putting it back together, just to see how it all works.

Me? I like to do the same thing, except with retirement income in a spreadsheet.

We think about Social Security as a cool benefit we collect at retirement age, but have you ever wondered exactly how it is calculated? I wondered too, so I built a Social Security benefits calculator. Now I get to explain it to you.

What you can find online easily

Before we begin this journey together, I have a few public service announcements to make. You don’t really need to talk nerdy to me like this if you don’t want to. Two places on the Social Security website will tell you all you probably want to know about your own benefits. Sure, its like cheating on the test compared to all the cool tinkering I did on my own spreadsheet, but fine, you might prefer quick answers.

For quick personalized answers, you should definitely sign up to access your own personal “My Social Security” report online.  My own report, which I signed up to receive at the tender age of 46, is quite fascinating. It lists all of my own earnings since 1986 (when I was 14 years old!), and how much I’ve paid in to the Social Security system until now. The report also shows an estimate – in my case, twenty years early – of how much I should expect to receive in benefits at my full retirement age.

You can also access a super-easy estimated-benefits calculator – specifically based on your own income data – on the Social Security Website.

This benefits estimator takes all your data, assumes you make an inputted income between now and time you retire, and then tells you what monthly benefit you can expect. It’s pretty cool.

Katrina Bledsoe, Management Analyst at the Dallas region Public Affairs Office of Social Security, wants people to know about these two online tools. Knowing about the personalized online calculators can save you time on the phone or in a Social Security office. I walked in to my nearest Social Security office in downtown San Antonio recently, and so I can testify that, yes indeed, you don’t really want to spend more time there than you have to. There’s the TSA-type screening to enter, the DMV-style vibe you get in the waiting room, and the nearly 2-hour wait I faced after I got a ticket with my number. So, yeah, you’re going to want to do as much stuff online as possible.

Bledsoe also mentions that signing up for the “My Social Security” report can reduce the possibility of identity fraud. If anyone tries to go online on Social Security, request a Social Security card, or go into a Social Security office using your identity, you would get an alert, so that’s nice too.

Cool SS stuff from my model

Ok, but most importantly, what cool stuff did I find in custom-built Social Security model?

Social Security benefits are determined by both your level of income and your years of earning income. More earning years and higher income translate to higher benefits.

Social Security benefits depend on calculating your average monthly income for your thirty-five highest earning years. You need a minimum of ten years to qualify for benefits. You increase your benefits for every year, up to thirty-five, in which you have income. For each of those qualifying years, Social Security caps the income you pay Social Security taxes on, and that cap serves as a “maximum income” for the purpose of benefits calculations.

Only earn income and pay taxes for thirty-four years? You can’t get the maximum benefit. Don’t hit the maximum income in every single year? You can’t get the maximum benefit.

I calculated the absolute maximum in my model of monthly Social Security benefits, assuming you qualified for full retirement in 2019, and had a max income in each of the previous thirty-five years: It’s $3,188.

I ran that number by Bledsoe, as a reality check. She said she’d never heard of any individual getting that much at “full retirement age,” probably because hardly anyone has a max income for all thirty-five of their earning years. Also, Social Security adjusts max income amounts every year, so the maximum possible benefits will go up slightly each year.

Early vs. Late Earnings

I asked my spreadsheet model to tell me the difference between earning retirement benefits early in one’s earning years versus late.

I was curious about this because one of the classic and true clichés of retirement planning is that putting away money in one’s twenties or thirties is far more valuable than putting away money in one’s fifties or sixties – due to the astonishing magic of compound interest math.

But Social Security doesn’t work that way.

I compared the effect of earning a max salary for ten years early in life – years one through ten – to earning a max salary late in life – years 26 through 35. To my surprise, the late earner would qualify for $1,503 per month as compared to the early earner qualifying for a little less, or $1,458.

The difference in benefits is not huge, but points out the dramatic difference between a Social Security benefit and self-directed retirement investing such as through a 401(k) or IRA. With self-directed retirement, the early years totally overwhelm the later years.

Turtle vs. Hare Earnings

Next, I wanted to know from my model whether the “turtle” or the “hare” gets better benefits under Social Security. I defined the hare as someone who earned a maximum income for ten years and then never worked again. I defined the turtle as someone who earned a solid but not massive salary over a full thirty-five years. I made the turtle salary $60,000, just under the current Texas median household income. My answer: The turtle would qualify in 2019 for $2,825 in monthly benefits, whereas the hare would qualify in that range that I mentioned above – between $1,458 and $1,503 – depending on whether that income was earned in the recent or distant past.

Within the Social Security rules, it’s better to be a turtle than a hare. Again, that’s possibly different from a professional career, especially an entrepreneurial one, in which ten good years of earnings can sometimes add up to more money than 35 years of median income.

Are these things interesting? I don’t know. They are to me. I’ll pass on a few more insights I got from my Social Security calculator in a subsequent post.

NOTE:  A number of folks have asked to get a copy of my spreadsheet. I originally sent it around in the months following this post, but then stopped sending it. One reason: The Social Security factors that adjust annual earnings change every year, so my spreadsheet built in 2018 would be out of date by 2019. I don’t want to keep updating it, or send out wrong information. Second, its easy enough to build your own, if you follow the suggested steps of these two posts. Third – You can always hire me to do some personal financial consulting work for you…but I don’t think an out-of-date spreadsheet built for me is exactly going to serve anybody else’s individual purpose. Each person’s calculation needs to be  individualized! The best way to do that and understand what you have is to build it yourself. Or, like I said, have me build it with you.

See Related post:

Social Security Calculations Part II – The Effects of Entrepreneurship

Post read (7184) times.