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

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

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.

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 (7253) times.

33 Replies to “Social Security Spreadsheet Fun”

  1. I enjoyed your article. How do you account for annual changes in the Social Security calculation? I too like to tinker with Excel. Can you send me your spreadsheet

  2. Also found your article and analysis very interesting, esp as I have moved overseas now and have had a ‘hare type`’of career thus far.

    Can you kindly send me your excel template as well to run scenarios?

  3. Hi, I am an IT person who is being asked to put together semi-plausible numbers for an early retirement incentive comparison to alleviate pressure from the Covid-19 fiscal damage. Therefore, I’d like to get a copy of your spreadsheet.

    I work in the Northeast.

  4. Hello Mr. Banker,

    I found your article a fascinating read … although I’ve been stranded with the “stay at home” order for 10 weeks now! LOL! I would be most grateful if you would share your spreadsheet with me.

    Thank you!

  5. Really enjoyed both articles, as I’m trying to look at the impact of retiring at different ages for my wife and me, and would like to bake the varying social security impact of when we stop working (which is not necessarily when we’ll start taking benefits) into my spreadsheet. Would you please send me your spreadsheet? I won’t share it with anyone. Thanks.

  6. Thought your two articles were interesting and beneficial. Can you post your spreadsheet, or email a copy? I would like to experiment with benefits start date for my wife while I wait as long as possible. Many thanks, Randy

  7. Loved this article. Was looking for a simple way to do a spreadsheet and it sounds like you already have it! If you wouldn’t mind sharing so I don’t have to reinvent the wheel it would be appreciated. Many thanks.

  8. Hello, could I please get your XL sheet please? Want to figure out how many more years I have to work after which I hit the point of diminishing returns for SS.

  9. I would love a copy of your benefit calculator!

    I too enjoy building excel models to do retirement planning (amongst other things), and I am using a plug right now to estimate in my current SS benefit plus a COLA, but I know I could make it more accurate. This article has me rearing to go.

  10. Hi! My request is a little different from those above. I will turn 65 in December and am trying to figure when I should start collecting. I have a disease that will most likely get me before my late 70’s, so I don’t think waiting until 70 is a possibility. I will be receiving half of my husband’s maximum benefit. If your calculator will be able to answer my question, would you be able to send it to me please?

  11. It would be great if you could send me your spreadsheet. I have a complex situation as I have not been in USA for 35 years, so a lot of my years will count as zero. It does say I have qualified though, but for how much!

  12. I would like to check out your Excel spreadsheet knowing the factors and bend points are dated, but I can modify these for present conditions. I would very much appreciate the information as I’m nearing retirement.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please Complete * Time limit is exhausted. Please reload CAPTCHA.

Public Speaking


I founded Bankers Anonymous because, as a recovering banker, I believe that the gap between the financial world as I know it and the public discourse about finance is more than just a problem for a family trying to balance their checkbook, or politicians trying to score points over next year’s budget – it is a weakness of our civil society. For reals. It’s also really fun for me.

Michael C Taylor's books on Goodreads


The Financial Rules for New College Graduates: Invest Before Paying Off Debt--And Other Tips Your Professors Didn't Teach You


Most Viewed Posts