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

Book Review: Master Math; Business and Personal Finance Math

I’m finishing up teaching an undergraduate course on Personal Finance this month, for which I find the assigned textbook totally useless, so I am on a quest to come up with a useful book to recommend for students as well as Bankers Anonymous readers.

What’s useful

The most impressive strength of Master Math: Business and Personal Finance Math by Mary Hansen is that it cuts out all the (mostly) banal ‘advice’ of a personal finance book, and concentrates instead on how to do the calculations.  The math level never rises beyond algebra, which frankly is all anyone needs to know, in order to competently manage their personal or small business finances.

I find this a useful guide for a ‘Do-It-Yourselfer,’ or a person intent on learning exactly how car loan companies calculate APR vs. APY, or insurance companies quote term life insurance.  A CFO for a small business or non-profit would also likely benefit from this useful introductory reference.

I’ve frequently paid but never personally calculated FICA taxes, for example, and it’s somewhat satisfying to learn how straightforward the math is.  I have personally prepared business balance sheets and budgets, debt to equity ratios, and tracked profitability, but the straightforward presentation would be useful to others who have not done so before, but who need to learn.

What’s missing

Missing from Master Math, however, is my personal pet project: Understanding discounted cashflows and compound interest – the keys to good personal finance decisions.  While the author presents a ‘compound interest’ table and defines the term (in contradistinction to simple interest), a table does not really cut it.

The limitations of print media for personal finance math

Reading the book this week has inspired a new thought, however, of which I’m increasingly convinced.

Personal finance and small business math, while not complicated, requires fluency with a spreadsheet program like Excel.

Master Math offers good, but somewhat convoluted algebraic formulas to calculate answers.  In print, the author cannot show the dynamic changes in personal finance outcomes from changes in variables.

Properly set up in a spreadsheet like Excel, by contrast, a change in loan interest rate, for example, alters every monthly payment as well as the total cost of a loan.  A small change in automatic monthly withholding, for example, changes everything when it comes to long-term retirement savings.  Only by seeing the dynamic effects, I think, can we understand what control we can have over personal financial decisions and outcomes.

What is the right media?

I know Khan Academy has changed everything when it comes to math pedagogy.  Although I enjoyed Master Math, I’m also sure personal and small business math has to be taught, and learned, through a combination of video, practice problem sets, and acquired fluency with Excel.  Static text on a page isn’t enough.

This is something I’d like to work on over the next few years.

Please see related post: All Bankers Anonymous Book Reviews in one place.

Master Math Business and Personal Finance Math


Post read (5589) times.