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

Federal Tax rates over time.

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
State Tax Parameters

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.

Main parameters.

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.

Withdrawals in the taxable account: 2021-2043

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.

401k withdrawals

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!

Results in the base case

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.

Withdrawals in the taxable account: 2021-2043. Same as before, but with the tax gains harvesting early on!

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.

Withdrawals in the 401k. Initially only Roth conversions, then keep withdrawing An amount equal to the standard deduction.

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.

Roth withdrawal strategy: Conversions early on (+), then withdrawals (-) until the end of the retirement horizon

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.

After-tax bequest with a better tax strategy

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.

Same after-tax bequest as the baseline, but $1,790 higher annual withdrawal!

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.

Please download your own copy!

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

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

  1. Thank for the post! I took a quick crack at downloading a copy for myself and then running a couple scenarios. Right out of the gate, I increased my Spending Target (C2) significantly above base case and re-ran the solver (minimized CI8 by changing column CI cells). I expected to see the accounts draw down significantly, but the withdrawals seem to be “locked in” at $48K base case. What triggers columns K, W, and AH (the withdrawal columns) to adjust with the modified spending targets? I would have assumed re-running the simulation with the new spending targets would raise these column amounts to “cover” what we need to spend. Thanks!

    1. Thanks for the (timely) information. I look forward to using it later this week. I was already modeling with Excel and saw how much tax drag we have and thought I was overlooking something with our CPA. Your comments at the end confirm what I observe. The other item I wanted to model was health insurance cost with the various Roth conversion scenarios – I think for simplicity I should just keep that separate and identify the smoothest tax scenario first. Thanks again for your content.

    2. If I start form the base case and set the variables to solve for as:
      $K$23:$K$35,$AH$36:$AH$47,$AY$48:$AY$74
      And then change the spending target to $49,000 and solve the whole thing, the solver indeed finds a solution after a few dozen iteration.
      Column W should not be used.

  2. I agree with your statement about how personal this is. My family’s calculations also need to factor in our health-care subsidy under the ACA until we hit Medicare age. Some things we could do to harvest capital gains aren’t possible because they add health-care costs more than they save taxes. The new law now gives us two years with no subsidy cliff, but it could go away. And even with the cliff removed, there are some break-points in income where health insurance is a different sort of “tax” that we need to factor into our calculations.

      1. Wonderfully done, I’ve always been looking for a tool like this but haven’t found anything as in depth as this!
        ACA subsidies can be tricky since they are based on family size which can vary over one’s lifetime, but one way to account for them with this tool is to bump up the lower tax brackets until qualifying for Medicare. Its tricky, since the subsidies don’t line up with the federal tax brackets but for instance the ACA subsidy (loss) for a couple going from $26k->$44k is equivalent to a marginal 10% tax and going from $44k->$61k is equivalent to a 15% marginal “tax”. So one could bump up the figures on sheet “income tax parameters” in column F from 12% to 22-25% until age 65.

        1. All good points. I should just introduce another tax function with different kink points where the user can model the ACA and other tax traps. I will do that in the next version! 🙂

  3. My head is spinning a little bit after reading all of this. 🙂 I’m looking forward to digging into the spreadsheet and plugging in my own scenarios. Thanks, as always, for all of the great work you publish and the knowledge you share.

    A couple editing notes:
    In the case study setup, there’s a typo: “All long-erm gains”. Although, I think the term “Long-ERN gains” has some potential.

    I might be misunderstanding, but in the “Results in the base case” section you say: “we can leave behind about $106k in the 401k and about $402k in the Roth IRA”. I think you mean $296k in the Roth IRA for a total of $402k.

  4. Thanks for the work on this file. I found one tax rate that needs correction. On the “Income Tax Parameters” tab starting in cell F11 (year 2026), the rate should increase to 15% from 12%.

    1. In addition, the standard deduction and exemptions go back to the pre-2017 method. And don’t forget that the standard deduction increases, too, after age 65.

      1. I doubt that. As I mentioned in the post and in another comment: I assume that the middle class Trump tax cuts will remain (as did the middle class Bush tax cuts).
        If you have a strong feeling about those parameters, you can always set them in your copy.

    2. Thanks!
      I keep that at 12%. As I mentioned, our politicians will likely keep the middle-class tax cuts and only try to hose “the rich”. Same as after Bush tax cuts expired.
      If you have a strong feeling that all taxes will jump back to the 2017 regime, you can certainly do so in your own copy. 🙂

    1. Set the withdrawals to 0 for 3 more years and then also likely model the 401k contributions and other savings as positive values (=contributions). Also set the cash flow target to zero manually for those years (column CI)

  5. Big ERN,

    Another masterful piece of work. And thank you for all of your wise counsel and and insights – I love what you do and share your passion for models. The task of a generalized multiyear tax optimization is difficult (to say the least) and there are an incredible number of permutations. Personal finance and tax planing are indeed PERSONAL.

    I did find a formula error MS Excel version – (1-#REF!) in column CP. Also, see cell CP72 which references cell C1 (which is the beginning year). I suspect a missing “$” for the row reference before you copied the formula.

    Here are a few other “enhancement” ideas should you decide to tackle them:
    1. add the medicare tax for ordinary and investment incomes greater than $250k. Also there is NO inflation adjustment at this point.
    2. add the qualified business income (QBI) deduction for anyone with a small business (can you say blogging).
    3. pre-Medicare health insurance subsidies on the marketplace We can thank Biden for the HUGE temporary (2 year) improvement in the March 2021 American Rescue Plan (ARP) capping medical insurance at 8.5% of AGI.
    4. Perform a NPV calculation of the total tax paid. Timing matters in a multiyear tax optimization.

    In my case, I am tackling the following issues:

    1. Capital gain harvesting vs. Roth Conversion
    I reached the conclusion that Roth is more effective way to fill the 12% ordinary income bracket and 0% capital gain / dividend bracket. You pay more now, but save much more later.

    2. SS start date vs. Roth Conversions
    Especially important in the 62 to 70 age time frame.

    3. SS start date vs longevity assumption
    What is the after tax IRR of delaying the start of SS vs longevity?
    This is dependent on the investment return assumption and tax impact on both the investment returns and what % of SS is taxed.

    I love your dedication to educating and the your expertise and the time you invest. Truly a labor of love.

    1. Thanks for great comment.
      A lot to chew on.
      I corrected the formula in column CP. It was a bad bug and did not cause any trouble in this example but would have created wrong results if you change the life expectancy. Thanks so much for that. I posted a corrected version. v1.01

      I should add the medicare tax as an additional tax when income is >$250k. Yes, good point. On my to-do list!

      Thinking about the ACA math issue!

      The QBI doesn’t require any work on my side. You model your business income (column BA) and you factor in the QBI deduction by reducing your taxable ordinary income (column BB).

      I have a NPV of the tax paid, see the results in Column U. True, it’s discounted by “only” the inflation rate, but it’s a discounted sum. Maybe I could calculate it with a different discount factor?

      Your issue 3: I like https://opensocialsecurity.com/ It performs a maximization of the NPV and gives you an optimal SS claiming strategy. I won’t reinvent the wheel for that one. 🙂

      Thanks a ton for your comments!

  6. Big ERN,
    Thanks for your comments and feedback. I noted a couple of other changes needed to the IRA/401k RMDs:

    1. In 2020 SECURE Act increased the age for RMDs and they now start at 72 (previously 70.5);
    2. On 11/12/20, IRS released the new life table that are effective 1/1/2022 and results in an almost 7% reduction in the age 72 RMD. You can find them from the Federal Register or kitces.com.

    These should both be relatively simple updates to the model.

      1. I concur that the v1.01 is using the new RMD age of 72. And you found the new IRS tables at the Federal Register, but V1.01 is using the current Uniform Life Table, not the new tables that will be effective 1/1/22. Please double check. Here is a comparison of the old and new Uniform life tables:

        Age Curr New
        70 27.4 29.1
        71 26.5 28.2
        72 25.6 27.4
        73 24.7 26.5
        74 23.8 25.5
        75 22.9 24.6
        etc.

        You can find all three current and new tables here: https://www.kitces.com/current-vs-new-uniform-lifetime-table-rmd-as-a-percentage-of-account-balance/

  7. Another wonderfully useful toolbox ERN! Thanks again!
    In addition to ACA subsidies that others have mentioned, there are some other credits that create additional “hidden” marginal tax brackets for those who continue to work in retirement especially those with kids such as on the low income end: EITC (which now allows for $10k in investment income to qualify), FAFSA expected family contribution which determines how much student aid you qualify for, and the saver’s credit which is 10-20% of retirement contributions. Also, those in higher tax brackets may miss out on some of the child and dependent care credits that phase out as you move up the income chain if one isn’t careful with managing agi.
    As someone who is planning on semi retiring with kids, I’m grappling with paying off the mortgage to keep low agi to qualify for some of these credits vs keep the mortgage @2% and pay it off with my stable part time job in semi-retirement but lose some of these credits which can amount to thousands of dollars per year. When one factors in all the lost credits particularly for those in the middle five figure agi range with kids, the effective marginal tax rate ends up being 40-50%+!

    1. Generally: if you face an additional tax rate (FAFSA, ACA, etc.) you can certainly “hack” it by simply lifting the existing tax brackets. Same with the child tax credit.
      I’m afraid the sheet gets too cluttered if I model each little tax hack individually.

      1. Yeah, I found I had to “hack” it a little more by lowering the standard deduction some since it would look for Roth conversion space in the leftover standard deduction space in some low income years, however this raises AGI for ACA and other credits.

        Another thing I did was forecast lower than average expected returns (3-4%) as a way of hedging against sequence of return risk. Some might be tempted to put in 6-10% returns but it will lead you to pay extra taxes now to avoid future RMD’s/realized gains. However, if you end up in a 1929 like situation, RMD’s down the road won’t be a problem since your portfolio could be a fraction of your starting size. I don’t mind paying a little more in RMD’s later if my portfolio continues multiply several times over.

  8. I didn’t read the article yet except the scenario… Here’s my question:
    Are you sure this statement of yours is correct?
    “Once the older spouse passes, the younger will opt to receive the (full) benefit of the older spouse: $15,000 in today’s dollars.”
    IIRC, it’s either YOUR full benefit or HALF of the deceased spouse whichever is higher…
    However, I don’t claim to know this 100% as we are 20 years away and it’s too early for me to study this topic :-).

    1. No. While both spouses are alive, each spouse has the option of claiming their own benefits or half of the other spouses benefits, whichever is higher.
      Once the higher-benefit spouse dies, the surviving spouse can then get the ENTIRE benefit of the deceased spouse, not just 50%.

  9. Thanks. Now it definitely seems I don’t need to work anymore if I can claim my spouse’s SS. Of course, the wild card is what the gov’t might decide in the next decade. My DH might be grandfathered into his SS by the time anybody starts discussing how to fix SS, but I’m 7 years his junior, so they might reduce his SS for me, but it still would be much higher than my own.

    Out of curiosity, what exactly are you trying to achieve with this spreadsheet?
    Wouldn’t it be more reliable to purchase i-orp or some other retirement planning tool to run various scenarios? As a FatFIRE’s your readers could definitely afford one.
    I am definitely not trying to belittle your work here because you do an awesome job educating people. I would guess that building this spreadsheet was a nice challenge and an outlet to occupy yourself…you’re FIRE’d and nothing else to do ;-).

    I’m actually also planning to download your file and see if I can change some variables for my situation and see what happens. I didn’t know about the Solver feature in MS Excel, but luckily it seems available in the 2010 version and was able to add it. Thanks for the directions on how to do it. 180 columns…?? That’s a piece of work for sure!

  10. Nice work here Big ERN. Just downloaded 1.02. Couple small things I noticed…
    1) NIIT is not modeled for Cap Gains taxes. Is that a planned addition?
    2) Cell C23 has a bug for SINGLE filing. I blanked out the Spouse2 ages above, but for my first year, filing status comes up Married. It is single for all but the first year. I haven’t looked deeply at the formula yet to suggest a fix.

  11. Hi, This post is very important information. I have learning more. I really enjoyed its reading this post enough to search for writing this excellent article and helpful post thanks for sharing.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.