A Retirement Tax-Planning Case Study (and Excel Toolkit!) – SWR Series Part 45

April 28, 2021 – Welcome back to another installment of the Safe Withdrawal Rate Series. In the previous installment, Part 44, I went through a number of general tax planning ideas, and I promised another post to introduce an Excel Sheet, I created to help me with my tax planning. There were numerous reader requests a long time ago when I ran the withdrawal strategy case studies (2017-2018) to publish not just the Safe Withdrawal Rate calculations but also the tax planning Excel Sheet. Well, I never published those Excel sheets because a) they were custom-tailored to those particular case studies, b) they potentially included personal information of the case study volunteers and c) they were created “for my eyes only” so I couldn’t really publish them without a massive effort to explain and document what exactly I’m doing there.

But now (with a three-year delay!) I’ve finally come around to creating something from scratch I feel comfortable publishing for a broader audience. It’s not a Google Sheet, but an MS Excel Sheet, more on that later. It’s probably still not a universally applicable tool.  And most importantly, it’s a tool that still requires a lot of Excel Spreadsheet mastery. It will not spit out “the” optimal tax strategy, it will only help me (and maybe you) find that optimal tax strategy. A lot of handiwork is still necessary! Much more handiwork than with Safe Withdrawal Sheet (and even that is already a handful!).

So, I like to go through a simple case study to show how this sheet works and showcase how you can “hack” your withdrawal tax optimization strategy in that one specific case. Even aside from tax optimization, the sheet helps me gauge what’s the average effective tax rate throughout retirement, to help me figure out how much of a gross-up I have to apply to translate a net-of-tax retirement budget into a pre-tax withdrawal percentage.

I can’t foresee what exact tax challenges you might face, but with my tool, I would have been able to handle what came across my desk so far, both in my personal finances and the case studies I’ve done so far.

So, let’s take a look…

Introducing the ERN Tax Planning Excel Sheet

You can download the Excel Sheet here:

Big ERN Excel Tax Tool v1.03

Changes since the initial publication:

• 4/28/2021: Fixed a bug in the calculation of the heir’s capital gains taxes. Thanks to reader “patientcash” for pointing this out!)
• 4/29/2021: Updated RMD table to reflect the lower RMDs announced in the Federal Register on 11/12/2020. Thanks to reader “patientcash” for pointing this out!)
• 6/28/2021: Minor bug fixed. If specifying no age for the spouse (=empty cells D7,D8) the filing status previously remained “Married” in the first year. Thanks to reader “FF” for pointing this out!

First of all, why is this an Excel Spreadsheet and not a Google Sheet, like the Safe Withdrawal Rate simulation tool (see Part 28 for details)? I certainly would have preferred the Google Sheets route but I don’t like the way the solver function works (or does not work) in Google Sheets. The challenge of this tax planning tool is that the computer needs to “solve” for the appropriate account withdrawals so that the after-tax cash flow exactly matches the specified retirement budget. So, just to curb everybody’s expectations, the user still has to do a lot of manual work. The Excel Sheet does not perform any kind of optimation on its own. This is not a simple app, where you enter your information and Excel spits out an optimized tax plan for you.

It’s also still “work in progress,” so all my fellow spreadsheet-eristas out there, please let me know if you find anything amiss, i.e., an outright calculation bug or other features you’d like me to implement.

Case Study Setup

This is a purely theoretical case study. It reflects neither my personal situation nor anyone else’s. Any similarities with actual people, living or dead, are a complete coincidence:

• A married couple, early-retired, aged 47 and 39 in 2021.
• A total of $1,200,000 in retirement savings spread over three different account types:
  • $600,000 in a taxable account with a $300,000 tax basis. All long-term gains. I assume that the tax basis is uniform, so every single tax lot has exactly 50% cap gains. The sheet is not set up to track dozens or even hundreds of tax lots over time!
  • $450,000 in a 401(k) with a zero tax basis, so any withdrawals will be counted as 100% ordinary income.
  • $150,000 in a Roth IRA.
• The couple has no other side income but expects a modest Social Security income when the older spouse reaches age 70. Let’s assume that the younger spouse (with a shorter earnings history) also claims SS at that time for a combined $20,000 in benefits (in today’s dollars). This already takes into account a generous “haircut” to budget for potential benefit cuts. 75% of that income ($15,000 p.a. in today’s dollars) is the older spouse’s benefit and 25% is the younger spouse’s benefit. Once the older spouse passes, the younger will opt to receive the (full) benefit of the older spouse: $15,000 in today’s dollars. 
• The couple expects the older spouse to live until age 85 and the younger spouse to live until age 90.
• I assume a 2% expected inflation rate throughout the entire retirement horizon.
• The couple plans a withdrawal amount of $48,000 in the first year and that amount is adjusted by inflation every year. Right now, I assume that the retirement budget follows that same path even after the older spouse dies. The idea here is that the surviving spouse will stay in the same house, drive the same kind of car, and the “variable” cost of the other spouse in the household (food, electricity, etc.) is too small to really matter. But if you want to run your own case study and assume that the death of one spouse has a meaningful downward impact on the spending target, feel free to “hack” that into your copy of the file!
• The tax parameters are the 2021 federal tax brackets. I assume that the tax bracket cutoff points, as well as the standard deduction, grow at the inflation rate every year. But I also assume that the marginal rates will increase later in retirement, more on that below.
• Currently, this couple lives in a 0% income tax state (e.g., AK, FL, NV, SD, TX, WA, WY). However, to hedge against the possibility of either a move to a taxable location or the home state introducing an income tax later I introduce a (modest) state tax later in retirement. More on that below.
• The couple has one child who will inherit the leftover assets after the last spouse passes. The spreadsheet is set up to estimate the marginal impact of the inheritance on your heir’s tax bill, so we can factor in an “after-tax” inheritance estimate.
  1. A Roth IRA goes to the heir tax-free.
  2. A taxable account will go to the heir tax-free if the “step-up basis” is still available. If the tax step-up basis is not expected to survive that long, your heirs will have to pay long-term capital gains taxes!
  3. A retirement account will trigger the appropriate amount of ordinary income.

Tax parameters

I assume the tax bracket cutoffs adjust for inflation every year, and the tax rates will slowly adjust up over time. Specifically:

• In 2026, the marginal rates in the 3rd and higher tax brackets will jump to their pre-Trump-Tax-Cut rates again. Think of this as the tax rates for “the rich” jumping back to the pre-2018 levels while politicians want to be seen as not raising the burden on the “middle-class”.
• In 2030, the rates in those same tax brackets go up by 1 percentage point. So do the capital gains taxes that were 15%/20% previously.
• Between 2034 and 2046 all tax rates go up by 1 point every four years.

In this particular example, the couple is not impacted by any of the tax hikes until 2034 because they stay within the bottom 2 tax brackets for ordinary taxes and the 0% bracket for capital gains (and dividends).

Next, the state taxes:

• I assume that we start out in a zero-income tax regime.
• In 2030, a mild tax “only on the rich” is introduced, where you get a 3% marginal tax rate above the $50,000 income level (plus the same standard deduction as the federal level)
• In 2034, all tax rates are going up by 3 percentage points across the board

All of the tax parameters are free to pick once you download your own sheet. If you don’t like some of my assumptions, go at it and re-run everything with whatever you like!

Heir’s Tax Considerations

I ignore the estate tax (for now). Under our current law, spouses can inherit assets without any tax liability and our daughter can inherit over $20m free of any federal estate tax. It’s safe to assume that most ERN blog readers will not have to worry about estate tax issues unless the laws change again.

But that leaves the income tax liability that your heir(s) might face. It’s impossible to gauge how the inherited assets will be taxed 40+ years down the road but here’s my attempt:

• Roth IRAs stay tax-free for the heirs.
• Traditional IRAs are taxable but the withdrawals can be stretched over a 10-year window.
• Taxable accounts may or may not preserve the step-up basis. There is the parameter that you can toggle between: 1=step-up-basis in place and 0=step-up-basis goes away. If the step-up basis were to go away, I also assume that the capital gains in the inherited portfolio have to be liquidated over a 10-year window.

To account for the 401k/IRA and potential capital gains liability as well as the 10-year stretch period, I assume (for simplicity) that the impact on the table income is first divided by 10. Then we apply the marginal income tax impact of that 0.1x inheritance. And the tax drag will be 10 times that number, as a “quick-and-dirty” estimate for stretching the tax liability over 10 years. For example, if the heir gets a $1,000,000 IRA and a taxable account worth $800,000 with $500,000 in capital gains, I compute the impact of an additional $100,000 in ordinary income and $50,000 in long-term capital gains in one year. Let’s assume that raises the tax bill (federal and state) by $40,000. Then I’d gauge the heir’s income tax drag as ten times the tax estimate: $400,000. Thus, the net inheritance would be $1,000,000 + $800,000 – $400,000 = $1,400,000.

Main parameters

On the main sheet, you can enter the essential parameters in the top left: spending target, spending growth (set equal to the inflation rate to make sure your real spending doesn’t melt away!), age, and life expectancy, and also the expected income tax parameters of the heir when receiving the inheritance. To the right, I also enter the current account values, the basis, and the return expectations. I currently model 2 different taxable accounts (account 2 not used in this current case study) and 2 tax-deferred retirement accounts (only the 401k is used, the IRA is not in this example), and a Roth/Health Savings Account. You can also specify the expected returns and also – very important for the taxable account – the exact breakdown into different types of returns:

• Capital gains (taxed as long-term) (in this sheet, by the way, I assume that all gains are long-term)
• Dividends
• Interest (ordinary income)
• Tax-free interest
• Section 1256 (i.e., income from index futures and index options, as I would use in my options trading strategy)

In this case, I assume only a 5% return target for all accounts to stay on the safe side. In the taxable account, that translates into a 2% dividend yield and 3% expected capital gains a year.

Supplemental Cash Flows

Any other cash flows outside of portfolio withdrawals are modeled in the appropriate columns (BA through BC). The gross flow and the amount taxable as ordinary income and the tax-advantaged LT capital gains or Qualified Dividends category. In this example, I enter the Social Security payments in Column BA, and 0.85-times that amount as ordinary income, assuming that 85% of Social Security income is taxable as ordinary income. As I outlined in a post in 2019, the exact calculation is much more complicated. 85% is the maximum of Social Security income taxable but can be lower. I suspect though, that with the government strapped for cash, we’ll likely move to that 85% Social Security taxability over time. But if you think that’s too high, feel free to adjust it.

Some more Excel Spreadsheet joy

The account values over time, withdrawals, and some other necessary stats are tracked for each of the 5 account types (2 taxable, 2 tax-deferred, one tax-free) in columns G through AZ. Each one of the account types has a Contrib/Withdrawal column: negative=withdrawals, positive=contributions. Taxable accounts also keep track of the cost basis. I use the “average cost basis” for simplicity because in a simple Excel Spreadsheet like this it would be too cumbersome to monitor all the different tax lots. But if you have wildly different tax lots, you could model this as two taxable accounts and then put all the high-basis lots into Taxable 1 and all the low-basis lots in the other. (That’s what I did in my personal case study, and I will likely present that in another post. Stay tuned!)

The calculation of the cash flows, before tax (= all the withdrawals plus supplementary income), minus the federal and state tax, etc. takes place in columns CA to CI. In column CH we have the cash flow needs (in nominal terms) and CI is the difference, which should be zero in every year if we exactly hit the cash flow target every year. All the detailed tax calculations are in columns CL and onward. The spreadsheet goes all the way to column FX for a total of 180 columns. Maybe there would have been a more elegant way of programming this, but this is my brute-force sheet. If you have suggestions as to how to model the federal tax calculations more elegantly, please let me know!

Base case

In the base case in tab “SWR45 Case Study – Base” I make the following assumptions. These are clearly (or maybe not so clearly) a sub-optimal withdrawal sequence, at least from a tax-optimization point of view:

Taxable Account: Withdraw from the taxable account to exactly match the cash flow needs until and including the year 2033 when the older spouse reaches age 59. Starting in 2034, draw down the remaining amount in the taxable account over ten years (before Social Security kicks in). I do that by “amortizing” the account, i.e., withdrawing equal amounts over 10 years using the Excel PMT function.

401k: In 2034, at age 60, tap the 401 (penalty-free) to fund the rest of the cash flow needs not covered by the taxabale account drawdown. Starting in 2046 at age 72, withdraw the RMD from the 401k. Notice that once the older spouse dies, the survivor RMDs are then tied to her age and the Uniform Life Table.

Roth: Any additional cash flow needs not covered by Social Security and the 401k RMDs are satisfied out of the Roth IRA:

Solving for the withdrawal amounts

When I wrote earlier that this is not a simple “plug in your numbers and out comes the optimal strategy” kind of Excel Sheet, I wasn’t kidding. Excel now has to solve for the exact withdrawal amounts to match your after-tax spending target. These are the numbers in the yellow cells in the screenshots above. The way I implement this is to use the Excel Solver function. I take the values of column CI (=how much we’re over/under our spending target) and minimize the squared sum of errors (Cell CI8), by changing the withdrawal amounts. For the 52 simulation years, there are 52 withdrawal amounts: years 1-13 from the taxable account, years 14-25 from the IRA, and years 26-52 from the Roth. Notice that the withdrawals from the taxable in years 14-23 and the RMDs from the 401k in years 26-52 are not variables we solve for, but rather they are determined by the respective account values (hence orange background cells = set by the user, not solved for).

Strictly speaking, we could solve the whole minimization problem all in one big step. But sometimes the solver plug-in has trouble finding a solution in a minimization problem with 50+ variables. One should do this in stage, e.g.,, solve only for the first 20 withdrawals, then the next 20 and then the remaining withdrawals. That’s because the whole minimization problem is sequential: If you solved the spending target for years 1-20, then changing the withdrawals for years 21-40 will have no more impact on the year 1-20 budget targets. The way to do the step-wise minimization of the loss function is to the weights of the respective 20-year windows to 1 or 0 depending on what window we like to solve for:

There are also a few “hacks” that make this particular case study really easy. Because the first 13 years of withdrawals come from the taxable account with a zero tax liability (all long-term gains in the 0% bracket), we don’t even have to “solve” for the withdrawals in those years. Just set them to the (-1)* the cash flow needs and we exactly hit our target. Likewise for the Roth withdrawals in years 26-52: Set them to zero initially, check how much the cash flow gap is on those years, and then simply copy/paste those gaps into the Roth withdrawal column to exactly match the spending target. That’s because the Roth withdrawals have zero impact on your tax bill. But we certainly have to solve for the 401k withdrawals in years 14-25 because there is a tax impact.

Results in the base case

So, here are the results in the base case. As I mentioned before, these are not optimal from a tax-planning point of view. We merely solved for the budget targets, not for the tax minimization! We can boil down the efficacy of this strategy into one single number: the after-tax amount we can leave behind for our heir in the final year of the simulation. That’s what I do in Columns O-Q toward the top of the tab. In real terms, we can leave behind about $106k in the 401k and about $296k in the Roth IRA, for a total of just over $402k. After accounting for taxes (not estate, just income taxes on the heir) that still leaves $382,071. This is the number we need to beat when we look for a better tax strategy! Because the consumption target along the way will be the same. The only way we can improve our result is to maximize the net bequest we have in the end!

Where are the tax-inefficiencies?

As I mentioned above, this was just a first trial at a withdrawal strategy. My Excel Sheet has a few tools to help us find the tax inefficiencies. Above all, I like to do the tax-smoothing, i.e., Principle 3 in my post from a month ago. To search for such tax-inefficiencies I look at the two charts at the top of the tab, around column AC. The bottom chart plots the marginal tax rates during the couple’s retirement. Zero marginal taxes initially, but much higher rates later in retirement, both for ordinary and capital gains income. That could but does not have to be a sign of tax inefficiency. If we could shift some income from the later years and realize it earlier during the zero-tax years, we should be able to reduce our overall tax burden. Tax-Smoothing! That’s where the chart on the top comes in: Because we have unused space in the Federal standard deduction (blue line) and unused space in the 0% capital gains bracket until 2033, we should be able to improve our tax situation! We should also likely shift income from 2060 onward to earlier years when the marginal tax is lower!

So, there will be three steps we can take to make our withdrawal strategy more tax-efficient:

1. Perform Roth conversions early in retirement, filling up the unused space in the standard deduction.
2. Perform capital gains harvesting to walk up the cost basis in the taxable account to minimize the tax liabilities from the withdrawals later in retirement. Notice that step 2 is not really necessary if we believe that the 0% capital gains tax bracket stays intact and there will be state income tax later. But even in that case, it’s a nice hedge against future tax hikes!
3. Instead of dragging out the 401k withdrawals over the entire horizon through RMDs, let’s accelerate the 401k withdrawals and push them into the years when the older spouse is still alive, i.e., before your marginal taxes jump up.

Here’s how I calculate this path:

Taxable account 2021-2043: We now do $50k p.a. in tax gain harvesting until we reach 100% cost basis at the end of 2027. After that, we just harvest the annual gains. Also notice again that the fields in Yellow are marked as variables we solve for in the Solver add-in. The “amortization” phase, i.e. drawing down the account over a 10-year period is during 2034-2043, with a $0 balance in 2043.

401k: In 2021-2033 we perform the Roth conversions. That shows up as “withdrawals” in the 401k (with all the tax consequences) and an equal-sized “contribution” in the Roth IRA (more on that below). How much can we convert? Well, the “space left in the standard deduction” is plotted in the chart above, so we just look up the source data (column ET in the sheet) and copy/paste the value (and only the values, not the formulas!) into the appropriate columns in the 401k and Roth withdrawals: as a negative figure in the 401k and a positive number in the Roth. For the years 2034 and onward I assume that we simply withdraw the left-over space in the standard deduction. It turns out that this will indeed deplete the 401k, completely tax-free by the year 2056, see the chart below.

Roth IRA: Notice how the Roth conversions appear as positive figures in 2021-2033. Afterward, we “solve” for the appropriate Roth withdrawals to match our target. Well, there isn’t really much to solve with the Excel tool, because if we initially set the Roth withdrawals to zero, then check the shortfall in column CI and just copy over those values into the Roth withdrawal column we should obviously hit our after-tax spending target.

How much will be our after-tax estate with this strategy? Let’s look at the amount we can leave as a bequest, see the table below. We would leave a bequest, all in a Roth IRA worth about $591k, more than $200k above the baseline.

We can also gauge our “effective retirement taxes” with the table to the right, see the screenshot below. We are extremely tax-efficient! In order to consume $48,000 p.a., we need to withdraw on average only $48,044. So, we have really only a 0.1% tax drag. This couple will likely be able to do their safe withdrawal analysis without any issues about gross vs. net!

Keep the bequest constant, and raise the annual withdrawals instead?

We can also phrase the question the other way around: instead of raising the estate value, what if we plan to leave the same estate as in the baseline but rather increase our withdrawals? I do that in the next tab “SWR45 Case Study – Higher wd”.

I repeat that whole exercise with a higher withdrawal amount: $49,790 p.a., and lo-and-behold, the after-tax inheritance is within a few hundred bucks of the baseline after-tax estate. Close enough for “government work!” But the withdrawals are about 3.7% higher. Pretty nice. But considering that the baseline plan was a pretty obviously inefficient and boneheaded plan, tax-optimization didn’t really give you all that much in additional retirement spending. Hence my (slightly controversial) claim last month: tax optimization is a second-order problem. Getting your safe withdrawal rate right is the #1 issue I worry about.

Troubleshooting

I can’t foresee all possible problems you might encounter. But here are a few:

1: I can’t edit the file posted on Google Drive! Great! You shouldn’t be able to edit anything I post online: not my files and not my blog posts either. You have to download the file to your own computer first, then you can edit it.

2: I can’t find the Solver function. On the menu, go to data, on the right side there is the solver function:

3: I can’t find the Solver function in that menu either! Make sure the solver is installed as an add-in:

4: The solver doesn’t find a solution.

• Make sure you designate only one withdrawal amount per year from one single account type in “By Changing Variable Cells”.
• Often it helps to split the solver into several steps, i.e., solve the first 20 years of expense targets, then the next 20 years, and then the remaining years.

To Do

There are a few improvements worthwhile to add. But I haven’t gotten around to implementing them yet. Among them:

• Model the taxability of the Social Security benefits more explicitly.
• Keep track of the Roth IRA basis that’s available for penalty-free withdrawals before age 59.5 to more explicitly model the Roth conversion ladder. In the current case study, this issue never came up because we had enough money available in the taxable account. But other people might find this useful
• and many more. Please let me know what you think I should model in this sheet! 🙂

Conclusion

As I mentioned last month: we should obviously never ignore taxes. In our simple case study, going from a clearly tax-inefficient plan to one that looks optimal or close-to-optimal to me, we can raise the withdrawal amount by 3.7% while leaving the after-tax inheritance roughly constant. Or alternatively, leave the withdrawals the same and plan for a much larger estate you can leave to your loved ones! Nothing to sneeze at, but really also nothing to stress out over! Especially considering that we can’t fully plan for all the tax law changes and other contingencies.

I’m much more worried about getting the SWR right. 3% vs. 4% vs. 5% makes a much bigger difference! Going from 3 to 4% is a 33% increase and from 4% to 5% is a 25% increase in your retirement budget. That’s where the “meat” is!

Nevertheless, I like my little tool here because you can get a rough estimate for your personal average retirement effective tax. For example, if your average tax drag is 10% and you set your safe retirement (after-tax) budget to 3.5% of your (pre-tax) portfolio and you think you did such a good job at staying under 4%, then think again! In order to generate 3.5% annual withdrawals (say, $140k out of a $4m portfolio) you need to “gross-up” your withdrawals to 3.5%/(1-0.1)=3.89%, already pretty close to the often not-so-safe 4% rate!

Tax issues are also another example of the insanity of the 4% Rule that’s shilled to a lot of unsuspecting retirees. Personal Finance is surprisingly personal. Who would have guessed?! To craft a sensible withdrawal strategy, we need to factor in a lot of idiosyncratic factors. For a lot of retirees in the FIRE community, taxes are going to be surprisingly low and there’s no sizeable difference between the net and the gross withdrawal rates. But many of us in the FatFIRE could easily face a 10% average effective tax rate just for the federal part and another 5% or so for the state tax, depending on your location. At a 15% tax drag, your 3.5% pre-tax safe withdrawal rate melts down to only 2.975% after-tax. Suddenly, your after-tax retirement budget of 3% of your assets wouldn’t seem so crazy anymore, right?

I should probably do another case study with a bigger tax drag, loosely modeled after my own situation soon. But I’m pushing 4,500 words today, so I plan to do that in a future post. Stay tuned, everyone!

Thanks for stopping by today! Please leave your comments and suggestions below! Also, make sure you check out the other parts of the series, see here for a guide to the different parts so far!

Title picture credit: Pixabay.com