More Nerdy Social Security Stuff I Found With My Spreadsheet


I went to my happy place this past week, which faithful readers (both of you!) will understand means I got to build myself a really neat-o spreadsheet.

I built it so that I could see “under the hood” of Social Security benefits calculations. This isn’t something I’d necessarily recommend to people who grew up in a loving household and who know how to relate to real live humans. But everybody has his or her hobbies. Don’t judge.

Generally, if you want to know what you should know about Social Security, I strongly recommend creating a My Social Security Account and I also strongly recommend going online to your Social Security benefits estimator. These online sources are easy to use, full of insights personalized to your own situation, and they could save you the hassle of a phone or in-person visit with a Social Security office.

I don’t tinker with cars in my garage

But for the small percentage of you who’d like to talk nerdy with me, I thought I’d add a few more insights into what I found as a result of my custom-built spreadsheet and by pulling my own reports.

These insights include the effect of low earnings, the effect of entrepreneurship, and a benefit of working deep into one’s retirement years.

My Own Report

In the example of my own Social Security report, since I graduated from college, between 1996 and 2017 I have twenty-two years in which I have reported taxes. (2018 taxes, obviously, are not yet filed.) Of those twenty two, eight years represent high earnings. Three years represent virtually no income (I’ll explain that a little later on). In the other eleven years my income was “meh.”

I mentioned in the last post on Social Security that having a high income for a short amount of time is not as valuable – for the purpose of maximizing Social Security benefits – as having a moderate income for a long period of time. That’s because every year out of 35 years of earning income and paying taxes matters. Miss out on one year of earnings, and your benefits go down, at least a little bit.

As a soon-to-be forty-seven year-old, I have twenty more years in which to earn income, pay Social Security taxes, and therefore maximize my benefits. “Full retirement” age for me, by the time I get there, is scheduled to be 67 years old.

Incidentally, for current Social Security retirees today, the “full retirement” age is 66 and a half, but that age keeps creeping upward. And it ought to creep upward because we live way longer on average than people used to, so it only makes sense for the solvency of the Social Security program to increase the retirement age. But that’s a controversial topic for another day.

The point is that I’ve got a mere twenty more years to maximize my eventual benefits. And yes, one way to increase benefits is to delay starting your monthly payments. But that’s a well-known fact, and also not the topic for today. The topic today is getting the best thirty-five years of earnings possible.

Racking up thirty-five steady years can be difficult, for example, if you start or run your own business.

My_social_securityEntrepreneurial income varies tremendously year to year, which will have an effect on your final Social Security Benefits. I noticed from my own Social Security report that in the first three years of founding my investment business in the mid-2000s that I earned essentially zero income. The losses incurred in my startup years zeroed out my taxes – which was delightful at the time – but it also meant I did not pay into the Social Security system. Since all thirty-five years of earnings count toward building up Social Security benefits, entrepreneurs with volatile earnings may accrue far fewer benefits over a lifetime than will long-time employees with steady earnings. Even very successful businesses have years of losses, at least from the owner’s tax perspective, so that could lower Social Security contributions and therefore eventually benefits.

Work Past Retirement Age

So how does somebody improve his or her benefits, even with some low-earning years?

My mother, age 76, presents an interesting data point on Social Security benefits, which she has been collecting for the last six years. After raising us kids, she racked up some low-paying years during her career as a private school teacher. She continues to work as a consultant now, however, making decent income well past her full retirement age. Because Social Security benefits depend on calculating your highest 35 working years, her recent income years – in her mid-70s – have been slowly replacing lower earning years from her 40s. As a result, her monthly Social Security check has adjusted upward by a little bit each year.

Katrina Bledsoe, Management Analyst at the Dallas region Public Affairs Office of Social Security, confirms that this upward adjustment should happen automatically every year, if you continue working. If you don’t see the automatic adjustment, Bledsoe says beneficiaries may contact their Social Security office. Don’t do so until the 2nd quarter of the year, however, because Social Security will need to see your official IRS tax data to recalculate benefits, and that generally doesn’t happen before April 15th.

Social_security_calculationsTwo final Social Security thoughts. Monthly benefits generally are not enough to live on, comfortably, in retirement. Social Security payments should be thought of as a living supplement, not a sufficiency.

On the other hand, as much as 40 percent of American households do not have appreciable retirement savings. The median net worth of Americans between ages 65 and 74 is $224,000. With that statistic, we can see that Social Security constitutes the single most important source of funds retirees have to live on.

To do some simple math 1 for which I don’t even need my cool spreadsheet, I would divide annual benefits by 5% to get a rough value of Social Security for an individual, as if it were the equivalent of a lump sum in the bank. That would make $2,500 in monthly Social Security benefits worth $600,000. That rough estimate shows us that Social Security is the largest safety net that a majority of Americans have.


Please see related post:

Social Security Nerdy Spreadsheet Part I – The Rabbit and the Hare

Post read (39) times.

  1.  And please don’t @ me to quibble about this last math assumption. Chill. I’m creating a quick and rough estimate for comparisons’ sake. Thank you.

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 them to you.

What you can find online easily

I don’t do soapbox derbies

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.

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

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

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


See Related post:

Social Security Calculations Part II – The Effects of Entrepreneurship

Post read (95) times.

A Spreadsheet Divided Them

For Sonia, it felt like her husband Dave was having a separate relationship, one that didn’t include her.

With whom, you may ask? With their household finance spreadsheet.

She told me “I felt Dave was having an affair with his spreadsheets, like it was something secret that I couldn’t take part in.” She said she wanted to be part of it, but it felt like he wouldn’t ever include her.

Dave, an Electrical Engineer in Space Sciences at a local nonprofit, research and development organization in San Antonio, admits he likes his spreadsheets. But Dave disagrees with Sonia’s description.

As he told me, “The truth is, Sonia helped me build the spreadsheet. I got the budget categories from [well-known finance guru] Dave Ramsey. I forwarded it to her, and she’d tweak it a little bit. I think she would agree she was included. She probably didn’t want to be included. My perspective is: We both came up with the plan. The problem with it was sticking to the plan. Then later she would say, ‘I don’t want to talk about the spreadsheet.’”

spreadsheet_divided_themAnd that description by Dave is quite different from the way Sonia experienced it. “I did have a part in creating the spreadsheet but once it was created, I felt excluded from maintaining it or suggesting changes to it,” she told me.

In my self-appointed role today of pretending to be a financial therapist, these different narratives are troubling.

Now is the time I should mention: Sonia and Dave got divorced. They agreed to speak with me separately about their distinct approaches to shared household finances.

There’s no villain here. Just two good people for whom shared financial responsibility couldn’t reconcile happily. They both agree that irreconcilable approaches to spreadsheeting did not cause the divorce, but they also both describe it as an unresolved tension in their marriage.

Totally irreconcilable approaches to money are extremely common in couples. Google “top reasons for divorce” and money generally makes the top three of any list.

Dave’s engineering mindset couldn’t cope with Sonia’s approach. He said, “I like to keep everything within a few squares. When I was a kid, I was certainly someone who likes to color within the lines. And Sonia was a person who colored outside the lines. I remember clearly coming home and there were a bunch of Target bags, and I’m like: where is this going to fit on the spreadsheet? We’re going over our budget.”

Yoga mindset…different from an engineer mindset

Dave described their differences like this: “She came at it from a quality of life approach. But the way I see it, you have $10, you can only spend up to $9.99. That thing over there would make my life enjoyable, but we don’t have the extra money to do it. I’m an engineer, so numbers explain the world. When I talked to her about money, I used to get a blank stare.”

Sonia also remembers being blamed, she felt unfairly, for making Target a favorite, well, target. Sonia, in recalling that same interaction, described it more as a glaring stare, rather than a blank one. “I felt like he overreacted when I’d made purchases that didn’t have a spreadsheet category – we never broke the bank – in fact with our two incomes, we had a promising little nest egg building up,” she said.

Sonia is an executive for a non-profit organization in San Antonio.  She has a professional life beyond yoga, but I first met her when she taught my daughters sun salutations in a local studio. In her office job she works with spreadsheets to track projects. But at home, rather than representing a shared solution, she felt the spreadsheet tended to limit their collaboration. She resented it.

I myself enjoy spreadsheets, but while listening to Dave I could empathize with Sonia’s reaction to his “stay inside the lines” approach. That, in turn, reminded me of reader feedback I received last fall.

Loyal readers of this blog (both of you!) may recall me asking for your help last November  with a project. We know that couples stress about, fight about, and sometimes even break up about money.

engineer_mindTo prevent that, what questions – I queried back in November – should couples ask each other before they get married or commit to a life together?

I figure good communication can’t guarantee a happy financial path but maybe is the first step to solving problems together.

I received some nice responses. Thank you. I also received some responses that made me laugh, ruefully. They frankly made me worry about the relationships of my readers.

Like when the husband responded to my open-ended query by mansplaining to me over email how his spreadsheet was the key to their financial success. Notice “my spreadsheet is a key to our success” is a far cry from the original topic of “what questions should new couples discuss together about their financial life?”

To state the obvious, “here’s the answer” is not the same as “what are the questions?”

Dave, with his engineer mindset, struggled with Sonia’s yoga-teacher mindset. Dave remembers their marriage counselor telling him, “your way is not the right way.  Or the only way.” So even while Sonia probably needed to work harder to fit inside the boxes, Dave probably needed to work harder to let go of his rigid plan. Sonia’s feeling of exclusion was real, even if Dave didn’t see that as a big problem, or the main problem.

The two now face financial challenges that come with divorce.

Sonia and I recently chatted about building her financial plan, now that she’s single and head of her own financial household. We talked about her mortgage, savings, and investments. I asked her if she had any money stranded in retirement plans with previous employers that she should “roll over” into a brokerage account. She looked sheepish.

She said, “I might have some.” Sonia wasn’t being coy. She just didn’t know.

“That’s why Dave loved his spreadsheets more than me. Because the spreadsheets always knew where the money was,” Sonia told me.

On a positive note, we’re coming up with a plan that doesn’t involve a spreadsheet. She gave full custody of that to Dave in the divorce decree.


A versions of this post ran in the San Antonio Express News and Houston Chronicle.


or as Wu-Tang_Financial would say:

Please see related post:

12 Money Questions To Ask Before You Get Married


Post read (146) times.