How to Calculate Your Breakeven Analysis for Social Security
This blog is in conjunction with another recently posted blog: Importance of Using Future Value When Determining a Social Security Claiming Strategy. That blog discusses the importance of analyzing your Social Security claiming strategy from a Future Value of Benefits perspective, and not just the total amount of dollars received.
This blog here is intended to demonstrate how to perform that calculation. I’m going to be using Microsoft Excel, but an Excel substitute will work just fine as well. You can do the calculations by hand too, but Excel will save you significant time.
Let’s take a look at a hypothetical situation to help illustrate how to calculate the Future Value of Benefits for Social Security.
Before we begin, you will need a couple of things:
1.) An estimate of your benefits at each starting age. This can be found on a recent Social Security Statement or by accessing your ssa.gov/myaccount.
2.) An estimated average annual rate of return from your retirement portfolio. This is important, because each dollar you receive from Social Security is one less dollar you need to withdraw that given year. Be sure to be realistic with this figure. And you want to use a reasonable rate of return that your portfolio will generate in retirement. So, if your portfolio will be more conservative in retirement then it is now, you’ll want to consider that.
3.) An estimated average annual inflation rate. This is important because your Social Security benefits will receive annual Cost-of-Living-Adjustments (COLAs) to keep up with inflation.
Let’s look at our hypothetical situation. We’ll say it is for Mr. Ellis. Mr. Ellis is 62 and has just retired. He is trying to determine the optimal time to claim his Social Security. To do this, he’ll want to run a Breakeven Analysis. And as discussed in the previous blog, he’ll want to do this Breakeven Analysis based on the Future Value of Benefits, not simply the total dollars he anticipates receiving. Breakeven analysis will help Mr. Ellis see which starting age will provide him with the most value based on various life expectances.
Essentially, what we want to do is run a Future Value of Benefit Analysis for each starting age. Once we’ve done that, we’ll be able to compile the entire list together and see for every life expectancy which starting point would provide the most value. You can run this through any time horizon you’d like. We’ll run ours through age 90 to start.
As for our assumptions:
Mr. Ellis’ Benefit Estimates from his recent Social Security statement:
· Age 62: $2,240 (70% of FRA Benefit)
· Age 63: $2,400
· Age 64: $2,560
· Age 65: $2,773
· Age 66: $2,987
· Age 67 (FRA): $3,200
· Age 68: $3,456
· Age 69: $3,712
· Age 70: $3,968 (124% of FRA Benefit)
Average Annual Rate of Return: We’ll assume 5%
Average Annual Rate of Inflation: We’ll assume 2.5%
So, we’ll start with age 62. For our spreadsheet, we’ll build out five columns:
1. Year
2. Age
3. Estimated Annual Benefit
4. Cumulative Benefits Received
5. Cumulative Future Value of Benefits Received
Mr. Ellis is age 62 currently (2024). His estimated benefit for age 62 is $2,240 / month, or $26,880 / year. So, we have the information already for the first three columns. Column 4, Cumulative Benefits Received, is simply going to add the Annual Benefits together for each year, keeping a running total of how much Mr. Ellis has received in $’s from Social Security.
The first row in this situation would look like this:
This shows us that if Mr. Ellis were to start Social Security at age 62, he would receive $26,880 for the year. And the actual value of this is $28,224 ($26,880 x 1.05).
If you’re following along in Excel, Column 5 (Cumulative Future Value of Benefits) would equal Column 4 x 1.05. The 1.05 is due to the 5% average annual rate of return we are assuming from our portfolio.
Again, what this represents in real life is that because Mr. Ellis started taking Social Security at age 62, he received $26,880. That’s now $26,880 less he needs to withdraw from his retirement savings this year to help pay for expenses. That $26,880 that he would’ve withdrawn now stays invested and we’re assuming grows 5%. So that $26,880 that stayed invested in his portfolio grew to $28,224 by the end of the year. He wouldn’t have achieved that investment growth had he delayed Social Security to a later year and had needed to withdraw that $26,880 from his portfolio.
We then move the calculation on to year two. After completing year 2, if you’re using Excel, you’re able to simply drag the calculations down the entire way to complete the entire spreadsheet.
For the second year:
1.) Annual Benefits: We are going to multiply the previous year’s Annual Benefits by 1.025. The 1.025 comes from the average annual rate of inflation we chose. This is estimating that your Social Security will receive a 2.5% bump going from 2024 to 2025.
2.) Cumulative Benefits Received: This column will equal the Cumulative Benefits from the previous year plus the Annual Benefits from this year.
3.) Cumulative Future Value of Benefits Received: This column will equal The Cumulative Future Value of Benefits Received from the previous year plus the Annual Benefits from this year. Then you multiply that sum by 1.05.
We put the above three steps together and can look at 2025:
This illustrates to us that should Mr. Ellis start his benefits at age 62, he could expect his age 63 benefit to be ~$27,552. This is the 2024’s benefit multiplied by 1.025. It also shows us that through the first two years, he could expect to receive a total of ~$54,432 from Social Security. This is the Cumulative Benefits Received column. And lastly, it calculates the Cumulative Future Value of Benefits Received after two years is $58,565. This is the $28,224 from 2024 being multiplied by 1.05, plus this year’s benefit of $27,552 being multiplied by 1.05, then adding the two together.
So, through two years, Mr. Ellis has received $54,432 from Social Security, but it’s brought an overall value of $58,565 to his retirement.
At this point, we’re able to drag each cell down to the desired life expectancy. Again, we’re planning to run this through age 90. When we do this, we will have a spreadsheet that looks like the following:
This is the entire age 62 spreadsheet after dragging the calculations down through age 90. As you’re able to see, Mr. Ellis by age 90 expects to have received an estimated total amount of $1,125,097 from Social Security. This is the number I often see people use when running their breakeven analysis. But the total value he’s realized, either by being able to keep money invested, or by investing the benefits themselves, has been $2,336,640. The column on the right are the numbers we’ll use to create our Breakeven Analysis.
Now, we do this again for every starting age. There is only one minor, yet very important, additional step.
Let’s look at Mr. Ellis starting his Social Security at age 63. The additional step is that we need to account for the COLA that will occur should Mr. Ellis delay. For example, if we look back to our chart of his estimated benefits, it shows that he would receive $2,400 / month if he waited till age 63. However, it’s important to remember that these figures are quoted in today’s $’s. If there is a 2.5% COLA like we’re estimating in this analysis, Mr. Ellis would actually receive $2,460 / month his first year if he delayed to age 63.
We have to remember to account for this prior to each starting age’s analysis. We’ll review age 64 in full detail too after age 63 so you can get the hang of it.
Let’s continue with Age 63 here:
Now that we’re doing age 63, we $0 out Age 62 since Mr. Ellis won’t be receiving any benefits that year. In 2025, age 63, Mr. Ellis would receive an increased benefit compared to age 62 since he delayed. Again, this $29,520 figure comes from the $2,400 / month starting benefit listed on Mr. Ellis’ statement for age 63. But we account for the fact that the $2,400 was a 2024 quoted figure and would increase with inflation by the time he starts benefits in the year 2025. He would be expecting ~$2,460 / month (or $29,520 / year).
Everything from this point on is the exact same as the Age 62 spreadsheet. Let’s fast forward a year to get the hang of it some more:
As a refresher, the calculation for 2026’s Annual Benefits came from multiplying 2025’s Annual Benefits of $29,520 by 1.025 to account for the annual COLA. The Cumulative Benefits in 2026 is the Cumulative Benefits Received through 2025 plus the Annual Benefits in 2026 ($29,520 + $30,258). The Cumulative Future Value of Benefits Received in 2026 is the Cumulative Future Value of Benefits Received in 2025 plus the Annual Benefits in 2026, then multiplied by 1.05. So, it would be ($30,996 + $30,258) x 1.05 = $64,316.70.
Once again, after completing this row, we can drag down the calculations through age 90. When we do that, we see the following results:
Now that we have two spreadsheets completed (ages 62 & 63) we can begin to compare. We want to use the far-right column, Cumulative Future Value of Benefits Received, to do the comparing. For example, let’s look at the year 2034.
In 2041 (Age 79), we’re showing that if Mr. Ellis started Social Security at age 62, his Cumulative Future Value of Benefits would be $956,185. If Mr. Ellis started Social Security at age 63, in 2034 his Cumulative Future Value of Benefits would be slightly less at $955,173.
Then as we look in 2042 (age 80), if Mr. Ellis started Social Security at age 62, his Cumulative Future Value of Benefits would be $1,048,014. If Mr. Ellis started Social Security at age 63, in 2042 his Cumulative Future Value of Benefits would now be slightly more at $1,050,096.
This illustrates the Breakeven Point for Age 62 compared to Age 63.
So, if Mr. Ellis feels confident that he would live to at least 2042 (age 80 in this example), then he would be better off delaying beyond 62.
If Mr. Ellis were calculating this using simply total dollars received, or “Cumulate Benefits Received” as we’ve been calling it, he would’ve landed at a breakeven point of age 73, not age 80. Significantly different. As discussed in How to Determine a Claiming Strategy for Social Security, this is why it’s important to account for rate of return and COLAs.
Now, we complete this type of spreadsheet for the remaining starting ages:
This time, we start the Annual Benefits at age 64. Referring back to our Social Security statement, in today’s $’s, Mr. Ellis would receive $2,560 / month for delaying to age 64. However, after two years of COLAs (2024 to 2025 & 2025 to 2026) at 2.5% each, Mr. Ellis would receive $2,689.60 / month, or $32,275.20 / year. That’s how we determine the first annual benefit to start the spreadsheet calculation.
Here are the remaining spreadsheets for starting ages of 65 – 70:
Now we’ve projected out both the Cumulative $ for $ Benefits Mr. Ellis would receive from Social Security and the Cumulative Future Value of Benefits from Social Security for all nine starting ages. The hard part is over. From here we can use these results to create our Breakeven Analysis.
Breakeven Analysis helps you to determine at which point one starting age becomes more favorable to another. Or “how long would I have to live for starting benefits at age 63 to be more favorable than starting at age 62”, so on and so forth. You may often see a Social Security Breakeven Analysis chart in the form of a line graph. I prefer to organize it in the below manner:
These are all of our results from the spreadsheets organized into one chart. This is the Future Value of Benefits Chart and shows what the Future Value of Benefits would be at each life expectancy depending on which age you chose to begin benefits. It then highlights the highest total for each life expectancy in green.
This is a helpful way to look at your Social Security situation, because it allows you to choose the optimal claiming strategy based on a life expectancy that you feel is appropriate. It also allows you to see the difference in total value between claiming strategies. Meaning, if Mr. Ellis felt that he’d probably live till around his early 80s, we’re showing his optimal strategy would be claiming around 65-66. However, we’re showing it wouldn’t be a terrible mistake if he were to start sooner. The Future Value of Benefits at Age 80 are only $3,000 less lifetime should he start at 62 instead of the optimal 65. But on the other hand, if he was planning on delaying to age 70, his Future Value of Benefits would be ~$80,000 less than the optimal age 65. Probably not sweating over missing out on a few thousand dollars over the lifetime of your retirement. But $80,000 could be an extra vacation each year for a handful of your retirement years.
Since we have the numbers for the Cumulative $ for $ received as well, let’s take a look at how it compares to the Future Value of Benefits Chart. Below is the breakeven analysis if you were simply looking at how much money, or how many total dollars Mr. Ellis projects to receive from Social Security. It accounts for COLAs but doesn’t account for the time value of money like the previous chart.
You can see the large difference in results. If you were to analyze your Social Security in this manner, you would come to the conclusion that as long as you lived to age 82, you are best off delaying to age 70. However, we know from the previous chart that with an age 82 life expectancy, the optimal strategy be a starting age of 65.
Excluding the time value of money, or the return your retirement savings generate, will favor delaying. Because it is not taking into account the fact that by receiving $’s earlier, you’re withdrawing less from your portfolio. You’re keeping that money invested and growing. It’s only looking at how much money you received in payments from Social Security.
I’m sure I’ve belabored that point by now, but it’s the main purpose of this analysis.
Now, we’ve been running all this analysis with the assumption that Mr. Ellis’ portfolio returns an average of 5% per year. The rate of return is one of the bigger determinants of the outcomes in this analysis. A higher rate of return will favor an earlier claiming age. A lower rate of return will favor delaying.
For example, here is 3% average ROI, 5%, and 7% side-by-side:
As you can see, with the lower rate of return (3%), the breakeven to delay beyond claiming at age 62 occurs much sooner than the 5% and especially the 7%. This is because your portfolio’s returns are having less of an impact of the future value. On the other hand, Mr. Ellis’ portfolio is returning 7%, the breakeven point to beyond past age 62 is significantly extended. This is because, if your portfolio is returning 7%, it’s beneficial for your overall retirement to leave money in your portfolio and grow at that high rate. And starting benefits earlier reduces your withdrawals early in retirement and keeps more money in your portfolio growing at that higher rate.
So, the point here is that it’s important to understand your own unique portfolio, how it’s invested, and a reasonable rate of return to assume. This is one of those variables that you cannot 100% accurately predict. No one knows what the future holds as far as returns for your portfolio throughout your retirement. That would make everything a lot easier. So, you must make a reasonable assumption. If your portfolio is heavily weighted towards short-term bonds and CDs, it may not make sense to assume a 7% ROI in your analysis. If your portfolio is heavily weighted in stocks, and specifically growth stocks, a higher average annual return may be appropriate to consider in your analysis.
I’m aware this is an extensive blog. I am hoping it was somewhat easy to follow along. The whole purpose of this blog is to demonstrate how you can properly analyze your own Social Security situation based on the Future Value of Benefits, instead of just counting the total dollars you’d receive.
As I’ve mentioned before, most financial planning firms have software that can run this type of analysis for our clients.
Social Security software allows us to quickly assess your Social Security situation, as well as test it against varying life expectancies, rates of returns, and cost-of-living adjustments. It’s intended to help guide you and your spouse with your claiming strategies, so you can feel confident that you have the optimal plan in place.
If you would find value in this type of analysis for your own retirement plan, please feel free to reach out.
Also, if you have any questions regarding steps in calculating your breakeven analysis on your own, please don’t hesitate to contact me. You’re able to do so by scheduling an appointment or by submitting a contact inquiry.
I hope you found this helpful. Thank you for taking the time to read.
Have a great day,
Drew Schaffer, CFP®
Ellis Investment Partners, LLC (EIP) is an investment advisor in Berwyn, PA. EIP is registered with the Securities and Exchange Commission (SEC). Registration of an Investment Adviser does not imply any specific level of skill or training and does not constitute an endorsement of the firm by the commission. EIP only transacts business in states in which it is properly registered or is excluded from registration. A copy EIP’s current written disclosure brochure filed with the SEC which discusses among other things, EIP’s business practices, services and fees, is available through the SEC’s website at: www.adviserinfo.sec.gov The views expressed represent the opinion of Ellis Investment Partners, LLC (EIP) which are subject to change and are not intended as a forecast or guarantee of future results. Stated information is derived from proprietary and nonproprietary sources which have not been independently verified for accuracy or completeness. While EIP believes the information to be accurate and reliable, we do not claim or have responsibility for its completeness, accuracy, or reliability. Statements of future expectations, estimate, projections, and other forwardlooking statements are based on available information and management’s view as of the time of these statements. Accordingly, such statements are inherently speculative as they are based on assumptions which may involve known and unknown risks and uncertainties. Actual results, performance or events may differ materially from those expressed or implied in such statements. Past performance of various investment strategies, sectors, vehicles and indices are not indicative of future results. There is no guarantee that the investment objective will be attained. Results may vary. There is no guarantee that risk can be managed successfully. Investments in securities involve risk, including the possibility for loss of principal. EIP does not provide tax or legal advice. You should seek counsel from your tax or legal adviser for your specific situation. Drew Schaffer is an independent Investment Advisor Representative of Ellis Investment Partners, LLC (EIP). Financial Planning and Investment Advisory Services are offered solely by EIP, a registered Investment Advisor, 920 Cassatt Road, 200 Berwyn Park, Suite 115, Berwyn, PA 19312, 484-320-6300.
