## Optimized Roth Conversion Model

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Optimized Roth Conversion Model

A bit of an introduction- I'm a "long time lurker" here on Bogleheads.org; I just noticed some recent posts concerning Roth Conversion modeling that fits in with some modeling I've been doing about conversions from traditional IRAs (tIRAs)to Roth IRAs (Roth) accounts.

Some background: Most of our portfolio is in tax deferred accounts. I did some initial analysis to determine the effects of Required Minimum Distributions (RMDs) and it seemed that these could cause a fairly large tax bill. I looked around and found a few web sites that provided some analysis of Roth conversions; however it wasn't clear to me how they worked or what assumptions they made.

I ended up writing my own model using MS Excel. It is a pretty large workbook that uses a MILP (mixed integer linear programming) to find the optimal sequence of Roth Conversions, Roth Withdraws, tIRA withdraws, and selling of assets outside of IRAs (including capital gains) to:

a. Meet annual expenses (including taxes), and
b. Maximize the ending value of the portfolio.

The size of the model exceed the capability of the standard excel "solver" add-in; fortunately there is a good GNU "freeware" add in called "OpenSolver" (https://opensolver.org/) that can handle a model of this size (it ended up having 2022 variables with 2580 constraints).

I included income taxes, capital gains taxes and taxes on social security (which BTW end up being quite complicated).

Most of what the model shows are consistent with the conventional wisdom about Roth conversions:

1. Using Roth conversions can end up saving a fair amount of money if you have a large amount of your portfolio in tIRA accounts, but you need to have a long investing horizon. In most cases it takes 20-25 years for the portfolio using Roth Conversions to overtake the portfolio Roths.

2. The main benefit of Roths seems to be reducing the RMDs (now you must take them starting at age 70 1/2, proposed to be age 72).

There are also estate planning benefits to passing Roth accounts to your heirs (which will be greater if the proposal to force inherited tIRAs to be sold over a 10 years period).

However, there is one benefit of Roths that the optimization model shows that I didn't expect.

In some cases the "optimal" solution the program kicks out has Roth withdraws for the first couple of years that you collect Social Security Benefits. This reduces the percentage of the SS benefit that is taxed since Roth WDs don't count toward the income used to determine how much of your social security benefit that is subject to tax. In some cases this can result in a pretty sizable reduction in income taxes.

Has anyone on the boards have used Roths in this way?

Here's an example from the model to illustrate this:

The scenario:

1. Initial traditional IRA (tIRA) of \$2M.
2. Initial Roth IRA of \$0.
3. Initial funds outside of the IRAs of \$500K with a cost basis of \$400K
4. Start at age 60 with 30 years in the plan.
5. Need \$100K real (ie before inflation) per year plus whatever income taxes are due from the prior year.
6. The portfolio returns an average of 2.5% real. Also, the non tIRA accounts have a qualified dividend rate of 2% and a non qualified dividend rate of 0.25%.
7. Social Security Benefits are \$40K per year starting at age 67.
8. Tax filing status is married filing jointly
9. RMD must start at age 72.
10. All numbers are real (ie inflation adjusted) numbers. Note that tax brackets and social security benefits are adjusted for inflation each year.
11. No state income taxes are include in this scenario.
12. 2018 tax tables are used, with the standard deduction of \$24K. All 7 tax brackets are included, as well as the three cap gains rates (0%,15%, 20%). Things like alternate minimum taxes and the reduction of the standard deduction as income increases are not included.
13. Two cases are examined: One which allows Roth conversions and the other does not allow Roths.

First, here's the portfolio values over the course of the plan:

Note that it takes about 20 years for the value of the portfolio using the roth to exceed the non-roth scenario. At the end of the model period the value of the Roth scenario is about \$55K greater than the non-Roth scenario.

Here's the Roth conversion/Roth withdraw sequence:

a. The way the tax laws are written the capital gains tax rate is 0% if the sum of taxable income less deductions plus capital gains is less than \$78950 (2018 married filing jointly) . The model seemed to have selected a Roth conversion amount that keeps the capital gains plus taxable income just below this threshold.
b. The Roth withdraws between age 67 and 72 seem to be to reduce the amount of taxable social security.

Percentage of social security benefit that is taxable:

Note the effect of the Roth withdraw on taxable SS benefit between ages 67 and 72. After age 72 the RMD causes taxable income to increase to the point where you can't use Roth withdraws to reduce income.

Taxes due per year:

Required minimum distributions:

Conversion/withdraw patterns for the Roth conversion scenario:

Conversion/withdraw patterns for the non-Roth conversion scenario:

The interesting part of this conversion pattern is that it seems to be "saving" the non-tIRA funds until after the social security benefits starts. Also, tIRA withdraws are started early to both allow for this "saving" of non-tIRA funds as well as reducing the RMD at age 72.

Any thoughts/comments would be greatly appreciated.
Last edited by Indyhou on Sun Jul 21, 2019 4:21 pm, edited 1 time in total.
WoodSpinner
Posts: 1657
Joined: Mon Feb 27, 2017 1:15 pm

### Re: Optimized Roth Conversion Model

Impressive ....

Is your model something that could be used by others?

In your solution, how does the acreage AGI during the conversion years compare to a similar length period when RMDs start.

Currently using this approach (e.g. leveling AGI during conversion (60-69) with the average AGI from once RMDs begin (70-79)).

Very cool!

WoodSpinner
is50xenough
Posts: 122
Joined: Sat Jul 28, 2018 1:37 pm

### Re: Optimized Roth Conversion Model

Agree. Very cool. An area I am very interested in. What does your model show with death of one spouse at age, say 80 years?
bsteiner
Posts: 5117
Joined: Sat Oct 20, 2012 9:39 pm
Location: NYC/NJ/FL

### Re: Optimized Roth Conversion Model

You have to run it through the year when your beneficiaries have to take their last required distributions.

I think the benefit of no required distributions from the Roth during lifetime is the second largest benefit, and the largest benefit is that by paying the tax on the conversion out of other assets you're effectively shifting additional wealth into the tax-free Roth environment.

For example, at a constant 30% Federal and state bracket, suppose you have a \$100 traditional IRA and \$30 cash in your taxable account. If you convert you'll have a \$100 Roth IRA. If it grows to \$200, you or your beneficiaries can withdraw \$200 tax-free. If you don't convert, your traditional IRA will grow to the same \$200, or \$140 after taxes. But your \$30 taxable account won't grow to \$60 since you'll pay tax on the income and gains on it each year.

The issue is generally the extent, if any, to which you can convert at a tax rate less than, equal to, or not too much higher than the tax rate that would otherwise apply to the distributions.
D-Dog
Posts: 82
Joined: Sun Dec 02, 2007 12:44 am

### Re: Optimized Roth Conversion Model

Indyhou wrote: Sat Jul 20, 2019 5:41 pm The size of the model exceed the capability of the standard excel "solver" add-in; formality there is a good GNU "freeware" add in called "OpenSolver" (https://opensolver.org/) that can handle a model of this size (it ended up having 2022 variables with 2580 constraints).
Nice work building the model!

A while back I created an Excel model that does the same thing except I used the built in Excel solver. As you noted, there are limits to the built in solver so I was forced to simplify the model in a way that caused me to question the results. Thanks for providing the link to the OpenSolver. I’m going to check it out.
earlyout
Posts: 1430
Joined: Tue Feb 20, 2007 5:24 pm

### Re: Optimized Roth Conversion Model

Very interesting work. Thank you.

It appears you are judging the results of different scenarios by looking at the total of the values of your accounts at the end of your study period. When doing this, do you use just use the account balances in the traditional IRAs or do you discount these IRA amounts for future income taxes that must be paid eventually? Using discounted IRA balances at the end of a scenario might provide a more realistic comparison.
CAsage
Posts: 1863
Joined: Sun Mar 27, 2016 6:25 pm

### Re: Optimized Roth Conversion Model

Humble and Ignorant question. Those several years shown in the middle with no taxes paid seem to be a missing gap in theory vs. practical application. I do not grasp higher mathematics (hence humble...) but would wonder how any solution that skips doing either withdrawals or Roth conversions below the 24% or (if only..) 15% bracket hasn't glossed over that opportunity? I don't think I could convince myself that was ever better in real life. I will re read everything and hope for enlightenment; massively useful task and model!
Salvia Clevelandii "Winifred Gilman" my favorite. YMMV; not a professional advisor.
dodecahedron
Posts: 5351
Joined: Tue Nov 12, 2013 12:28 pm

### Re: Optimized Roth Conversion Model

earlyout wrote: Sun Jul 21, 2019 11:32 am When doing this, do you use just use the account balances in the traditional IRAs or do you discount these IRA amounts for future income taxes that must be paid eventually? Using discounted IRA balances at the end of a scenario might provide a more realistic comparison.
^The above is a very important point. Adding tIRA and Roth IRA values to compute a ¨total portfolio value¨ is an apples and oranges comparison unless you adjust for the present value of the future taxes that will still need to be paid on the remaining tIRA funds.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

Is your model something that could be used by others?
Probably. You'd have to have a version of MS Excel running on Windows that can handle 200+ columns. Also you'd need to install the Opensolver add-in and enable macros in Excel. The model works best on a 64 bit OS but can be run on a 32 bit machine (but it can take a long time to solve. I've run it on Excel 2007 and the Excel version in Office 365 without any problems.
In your solution, how does the acreage AGI during the conversion years compare to a similar length period when RMDs start.
Here's a link to a pdf of all the graphs and the spreadsheets (you'll probably need to download the pdf to magnify the document enough to read it):

Also, here's a pdf of a powerpoint slide deck which explains the model and shows the equations and constraints used.

Note: Changed to point to latest document 7/30
When doing this, do you use just use the account balances in the traditional IRAs or do you discount these IRA amounts for future income taxes that must be paid eventually?
Good point. The model has the capability to "discount" the ending value of the tIRA to reflect the fact that a dollar in a Roth is probably more valuable than a dollar in a tIRA. I didn't discount the tIRA in the examples I ran just to keep in simple. When I run the model for my portfolio I discount the final tIRA by 10%.
You have to run it through the year when your beneficiaries have to take their last required distributions.
What does your model show with death of one spouse at age, say 80 years?
Both of these scenarios would be interesting. The model handles either single or married filing jointly but not switching between the two during the course of a model (ie married for first 20 years, then single thereafter). It could be done but would require a fair amount of work.

Modeling the effect on heirs would be simpler to implement since the model does supports earned income and periods up to 50 years.

I'll look at both of these when I get a some time.
Last edited by Indyhou on Tue Jul 30, 2019 5:15 pm, edited 1 time in total.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

but would wonder how any solution that skips doing either withdrawals or Roth conversions below the 24% or (if only..) 15% bracket hasn't glossed over that opportunity? I don't think I could convince myself that was ever better in real life.
I put a "what if" spreadsheet in the model to help me understand why the model is recommending. Here's the distribution patterns for the first 7 years and associated taxes:

Whats happening in the first four years is that you sell off the non-tIRA assets to meet your financial needs and tax bill. This causes you to incur capital gains. Note that the optimizer came up with a roth conversion amount to keep the capital gains tax rate to zero.

Here I've increased the first Roth conversion to \$80K. Note that this causes part of the capital gains to be taxed at 15%.

At age 65 the non-tIRA has been drawn completely down so the only the tIRA and Roth have money in them. In age 65 and 66 the model mostly draws money out of the tIRA with a small amount coming from the Roth to keep income taxes down to 12% marginal bracket.

Here I've removed the small roth distribution from age 65 and increased the tIRA withdraw. Note that this kicks you up to the 22% marginal tax bracket.

When you start taking social security at age 67 the model uses roth distributions to decrease the percentage of your social security benefit that is taxable. This continues until you are forced to take RMDs at age 72.

Note that this sequence is dependent on your initial values, amount needed, etc.
is50xenough
Posts: 122
Joined: Sat Jul 28, 2018 1:37 pm

### Re: Optimized Roth Conversion Model

Indyhou wrote: Sun Jul 21, 2019 2:14 pm
Is your model something that could be used by others?
Probably. You'd have to have a version of MS Excel running on Windows that can handle 200+ columns. Also you'd need to install the Opensolver add-in and enable macros in Excel. The model works best on a 64 bit OS but can be run on a 32 bit machine (but it can take a long time to solve. I've run it on Excel 2007 and the Excel version in Office 365 without any problems.
In your solution, how does the acreage AGI during the conversion years compare to a similar length period when RMDs start.
Here's a link to a pdf of all the graphs and the spreadsheets (you'll probably need to download the pdf to magnify the document enough to read it):

Also, here's a pdf of a powerpoint slide deck which explains the model and shows the equations and constraints used.

When doing this, do you use just use the account balances in the traditional IRAs or do you discount these IRA amounts for future income taxes that must be paid eventually?
Good point. The model has the capability to "discount" the ending value of the tIRA to reflect the fact that a dollar in a Roth is probably more valuable than a dollar in a tIRA. I didn't discount the tIRA in the examples I ran just to keep in simple. When I run the model for my portfolio I discount the final tIRA by 10%.
You have to run it through the year when your beneficiaries have to take their last required distributions.
What does your model show with death of one spouse at age, say 80 years?
Both of these scenarios would be interesting. The model handles either single or married filing jointly but not switching between the two during the course of a model (ie married for first 20 years, then single thereafter). It could be done but would require a fair amount of work.

Modeling the effect on heirs would be simpler to implement since the model does supports earned income and periods up to 50 years.

I'll look at both of these when I get a some time.
So won't run on MaCOS?
Peter Foley
Posts: 5103
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

### Re: Optimized Roth Conversion Model

Thanks for working on this important topic. More later.
smitcat
Posts: 6464
Joined: Mon Nov 07, 2016 10:51 am

### Re: Optimized Roth Conversion Model

Indyhou wrote: Sun Jul 21, 2019 2:14 pm
Is your model something that could be used by others?
Probably. You'd have to have a version of MS Excel running on Windows that can handle 200+ columns. Also you'd need to install the Opensolver add-in and enable macros in Excel. The model works best on a 64 bit OS but can be run on a 32 bit machine (but it can take a long time to solve. I've run it on Excel 2007 and the Excel version in Office 365 without any problems.
In your solution, how does the acreage AGI during the conversion years compare to a similar length period when RMDs start.
Here's a link to a pdf of all the graphs and the spreadsheets (you'll probably need to download the pdf to magnify the document enough to read it):

Also, here's a pdf of a powerpoint slide deck which explains the model and shows the equations and constraints used.

When doing this, do you use just use the account balances in the traditional IRAs or do you discount these IRA amounts for future income taxes that must be paid eventually?
Good point. The model has the capability to "discount" the ending value of the tIRA to reflect the fact that a dollar in a Roth is probably more valuable than a dollar in a tIRA. I didn't discount the tIRA in the examples I ran just to keep in simple. When I run the model for my portfolio I discount the final tIRA by 10%.
You have to run it through the year when your beneficiaries have to take their last required distributions.
What does your model show with death of one spouse at age, say 80 years?
Both of these scenarios would be interesting. The model handles either single or married filing jointly but not switching between the two during the course of a model (ie married for first 20 years, then single thereafter). It could be done but would require a fair amount of work.

Modeling the effect on heirs would be simpler to implement since the model does supports earned income and periods up to 50 years.

I'll look at both of these when I get a some time.

"Good point. The model has the capability to "discount" the ending value of the tIRA to reflect the fact that a dollar in a Roth is probably more valuable than a dollar in a tIRA. I didn't discount the tIRA in the examples I ran just to keep in simple. When I run the model for my portfolio I discount the final tIRA by 10%. "
That kinda misses the huge value of performing Roth conversions for us anyway. Just using balances at the end of time periods will favor non conversions especially in the early years. 10% does not come close to the value of Roth dollars vs TIRa dollars in our accounts at and after 70 years of age.
If I just wanted to measure the total dollars in our accounts at 70 I would never convert.
If I want to measure the total amount of dollars we can spend I am then motivated to convert.
petulant
Posts: 1901
Joined: Thu Sep 22, 2016 1:09 pm

### Re: Optimized Roth Conversion Model

Seems like the model is saying it's smarter financially during early years to pay the lower tax hit selling taxable assets than lock in taxes on tIRA. That seems odd to me since taxes will be so much more on the RMDs and if spouse dies while the taxable assets have much more flexibility in the future with step-up basis and cap gains only being part of the proceeds from a sale. On the other hand, saving substantial taxes today is generally more valuable than saving them ten years down the road. Is there any inflation or discount rate built in to the model to explicitly favor money today versus tomorrow (other than built-in rates of return)? Also, does running the model with a, say, 20-25% discount on final tIRA balances shift anything more than the 10%?

What about setting RMDs to start at 70?

What about income needs being lower than \$100,000? The trick about \$100,000 is that it's also the crunch point between the 12% and 22% brackets for MFJ. Further, it's high enough that it means any taxable income taken during SS will probably use up all of the valuable SS tax-free space, then pass the high SS marginal brackets, then be back in normal brackets. What if needs were only \$75,000?

What if there was a modest flat state tax of 5%?

What if returns are a bit higher, say 5%?

I guess I'm a bit concerned that since this is a comprehensive analysis of an investor situation, it's worth understanding how specific tweaks may materially change the outcome, or whether a few realistic tweaks together do so. For example, a \$100K/yr spender in NY or CA might have a very different optimal strategy from a \$50K/yr spender in Midwest due to different state marginal tax rates, cost of living, and % of spending handled by SS.
JoinToday
Posts: 905
Joined: Sat Mar 10, 2007 9:59 pm

### Re: Optimized Roth Conversion Model

bsteiner wrote: Sun Jul 21, 2019 8:38 am You have to run it through the year when your beneficiaries have to take their last required distributions.

I think the benefit of no required distributions from the Roth during lifetime is the second largest benefit, and the largest benefit is that by paying the tax on the conversion out of other assets you're effectively shifting additional wealth into the tax-free Roth environment.

....
The issue is generally the extent, if any, to which you can convert at a tax rate less than, equal to, or not too much higher than the tax rate that would otherwise apply to the distributions.
I think bsteiner is hitting the nail on the head here.

Bottom line for me is I want to maximize the amount of "spendable" money for me + my heirs. To do so, you have to estimate future tax rates for me and my heirs, and also consider possible changes in the tax laws (like the possibility of a required 5 or 10 year payout of IRAs for non-spouse beneficiaries). The uncertainty/unknowns are huge, swamping out everything else. I decided to just suck it up, and do Roth conversions to the top of the 24% bracket until tax rates go up (2026?).

I did a giant spreadsheet also. It was educational, but in the end, not especially useful beyond pointing me in the right direction.

For what it is worth, I use 25% discount for my T-IRA to account for taxes (incl 9.3% Calif and 3.8% NIIT). May be 1% or 2% low
Peter Foley
Posts: 5103
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

### Re: Optimized Roth Conversion Model

When modeling Roth conversions (using this new calculator, i-orp, or the Retiree Portfolio Model) one key component is spending as has been emphasized in this thread. Another key component is the ratio of funds in taxable, TIRA, and Roth. Without a balance in taxable from which to pay the taxes on a Roth conversion, Roth conversions take a longer time to "catch up."

In addition, one should consider how assets in various account types are invested. Bonds in tax deferred (TIRA) and equities in Roth is the usual recommendation. If historic returns are then applied to remaining TIRA funds and all Roth funds this may improve the accuracy of the forecast.

RPM does this quite effectively.
WoodSpinner
Posts: 1657
Joined: Mon Feb 27, 2017 1:15 pm

### Re: Optimized Roth Conversion Model

OP,

Sent a PM on some documentation questions I have in setting up my input file. Not sure if the PowerPoint you provided is the latest version (or perhaps) not complete.

Got it downloaded and running, now I just need to figure out how to setup my model.

Thanks

WoodSpinner

P.S. — I really appreciate the hard work that went into building this tool. Can’t wait to play with it.
is50xenough
Posts: 122
Joined: Sat Jul 28, 2018 1:37 pm

### Re: Optimized Roth Conversion Model

Is one of the links the actual model that we can use? Will it work on a mac? Thanks so much.
WoodSpinner
Posts: 1657
Joined: Mon Feb 27, 2017 1:15 pm

### Re: Optimized Roth Conversion Model

is50xenough wrote: Tue Jul 23, 2019 12:05 pm Is one of the links the actual model that we can use? Will it work on a mac? Thanks so much.
I have it running in Mac with a Parallels, Win10 VM with Excel 2013.
Retiredron
Posts: 80
Joined: Mon Mar 27, 2017 9:43 pm

### Re: Optimized Roth Conversion Model

Indyhou,

Thanks for your post and work on the Roth conversion model. I have been struggling to find a financial advisor or CPA with such a model and every time I make a call I get the same reply - "I don't have any specific models or tools I use to optimize Roth conversions"! I've read almost all of the posts on this board relative to Roth conversions and I feel fairly educated about making conversion decisions but I would love to have a model that would help optimize and validate those decisions.

I suspect this board will see more and more posts about Roth conversions in the future. Like me, there are many folks out there moving into retirement and waking up to the fact that their large tIRA balances have significant RMDs and taxes due. I think you might actually have the foundation for building a validated software model you can market (sell) so that sound Roth conversion decisions are made by people like me. My own simple spreadsheet and research has led me to conclude that there are many factors, some quite complicated, that must be considered before one pulls the Roth conversion trigger. I've tried to use the retirement portfolio model cited extensively on this board but just find it too complicated to use.

All the best on progressing your model.
smitcat
Posts: 6464
Joined: Mon Nov 07, 2016 10:51 am

### Re: Optimized Roth Conversion Model

Retiredron wrote: Wed Jul 24, 2019 6:41 am Indyhou,

Thanks for your post and work on the Roth conversion model. I have been struggling to find a financial advisor or CPA with such a model and every time I make a call I get the same reply - "I don't have any specific models or tools I use to optimize Roth conversions"! I've read almost all of the posts on this board relative to Roth conversions and I feel fairly educated about making conversion decisions but I would love to have a model that would help optimize and validate those decisions.

I suspect this board will see more and more posts about Roth conversions in the future. Like me, there are many folks out there moving into retirement and waking up to the fact that their large tIRA balances have significant RMDs and taxes due. I think you might actually have the foundation for building a validated software model you can market (sell) so that sound Roth conversion decisions are made by people like me. My own simple spreadsheet and research has led me to conclude that there are many factors, some quite complicated, that must be considered before one pulls the Roth conversion trigger. I've tried to use the retirement portfolio model cited extensively on this board but just find it too complicated to use.

All the best on progressing your model.
"I've tried to use the retirement portfolio model cited extensively on this board but just find it too complicated to use."
But it works and is well worth the time for those of us that have made the time to load and understand the outputs.
Retiredron
Posts: 80
Joined: Mon Mar 27, 2017 9:43 pm

### Re: Optimized Roth Conversion Model

I've actually spent many an hour working with RPM and have exchanged numerous emails with the model developer. This model is valuable but not complete. For example, it does not have the ability to account for after tax money in a tIRA as you evaluate a Roth conversion.
smitcat
Posts: 6464
Joined: Mon Nov 07, 2016 10:51 am

### Re: Optimized Roth Conversion Model

Retiredron wrote: Wed Jul 24, 2019 7:41 am I've actually spent many an hour working with RPM and have exchanged numerous emails with the model developer. This model is valuable but not complete. For example, it does not have the ability to account for after tax money in a tIRA as you evaluate a Roth conversion.
"For example, it does not have the ability to account for after tax money in a tIRA as you evaluate a Roth conversion."
It does not show you your proposed taxes and tax rates?
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

### Re: Optimized Roth Conversion Model

WoodSpinner wrote: Tue Jul 23, 2019 3:00 pm
is50xenough wrote: Tue Jul 23, 2019 12:05 pm Is one of the links the actual model that we can use? Will it work on a mac? Thanks so much.
I have it running in Mac with a Parallels, Win10 VM with Excel 2013.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

First off I very much appreciate the comments and questions. I find the topic topic of very interesting and I think its important.

I apologize for taking so long to respond; I have no problem sharing the model but I wanted to add some functionality (based on prior comments). I also wanted to take a bit of time to test the state income tax modeling (I live in a state with no state income tax so haven't really tested it as much as I would like).

I've finished adding the functionality and testing; it looks relatively clean.

Based on comments and questions here I've added some capabilities to the model. Specifically, I've added the capability to:
a. model change in filing status part way through the model run
b. model an inherited/roth at the end of a run, and allow the maximization of the inherited portfolio.
c. model proposed changes to the law which will force the liquidation of tIRAs after 10 years.

Here's a link to the excel model if anyone is interested:

The file name is Roth Linear Program Rev 45 Example Case No SS 0 percent tIRA.xlsm.

As discussed before, you need the opensolver add in https://opensolver.org/ to run the model. As an aside, I ended up putting the add-in in the same directory as my roth models so the I know where (I had a bit of trouble with excel mysteriously dropping the add ins and its makes it easier to re-load it).

This model using the same starting account values, social security benefits, etc as the prior model posted except:

1. The case lasts for 35 year.
2. For the first 20 years the filing status is married filing jointly.
3. For the next 5 years the filing status is single. The social security benefit is reduced by 66% (ie 1/1.5).
4. The portfolio is inherited at year 25 by an heir who is 50 years old that makes \$100K per year and who files as married filing jointly. All Social Security benefit and pensions stop. The heir takes minimum distributions from the tIRA per the IRA's single life table and pays all taxes (including their income) from the inherited portfolio.

The base case allows Roths and uses current law (ie RMD at 70, no requirements to liquidate at year 10 after inheritance). The second case allows Roths and models the proposed new law (ie RMD at 72, liquidate at year 10 after inheritance). The third case does not allow Roths and uses the proposed new law.

I strongly suggest you first run this model "as is" to establish that everything is working and to get a handle on how well your particular hardware can run the model. The model in this spreadsheet approximates taxable social security instead of calculating it and does not discount the final value of the tIRA. Both of these factors can slow down the model (and are configurable on the "input" worksheet).

When you open the workbook, excel will ask you if you will permit running of macros. After you enable macros a couple of screens will come up that you have to agree to (acknowledging the spreadsheets are for research and education only and that results should be verified). After this the input screen will be shown. Press the "Run Model" button. This will start a macro that will go to each of the three cases (Base Case, Alternate Case, Alternate Case 2). ( In most versions of excel you can see this happening; for some reason in Excel Office 365 you only get partial screen. This isn't a problem though). Once all three cases are solved the macro will load up the base case in the "what-if" workbook (you'll see a bunch of worksheet data flash by) and then take you to the Summary Graphs workbook.

On my computer (64 bit window mid-range laptop) each case solved between 51 seconds and 1 minute 16 seconds (these figures can be found on the top left corner of the "what-if" worksheet). I also ran it on a mid range dell desktop running 32 bit windows and it took between 2 minutes and 3 minutes 3 seconds. If your computer takes a much longer to run you might have an issue running more complex cases.

As I mentioned earlier the model is a large mixed integer linear (MILP) programming model. The reason I had to use integer programming is that there are several parts of calculating taxes that are non-linear. Unfortunately integer programming is a much more difficult problem and can take much longer to solve. Also you can't guarantee that the solutions found are actually optimal. And you can't guarantee that the model will converge- I have had some model take an hour or more to converge (most don't however).

If you want to start putting in your own data or make changes, I suggest that you first try them without social security calculations and a 0% discount of the tIRA ending value. A few other cautions:

a. All data changes should be made to the "input" worksheet.
b. This model was originally for my own use so I didn't put very many "data checks" on entries. It is possible to put in values that can cause the spreadsheet to produce invalid results.
c. There are several "advanced" scenarios in the model, including allowing the donations of appreciated securities to a Donor Advised fund to meet charitable giving, using the tIRA RMD for charitable giving, and funding a large purchase (such as a house) with withdraws from a portfolio or taking out a mortgage. I probably won't use these until you become familiar with the model. Also, the mortgage functionality in the model is only for funding a large purchase and is not intend to model an existing mortgage.

A bit of technical background on the execution time issue: In a regular excel model you can use min/max functions or if/then statements. However, you can't have any of these functions in the model code between the variables the model manipulates (Roth conversions and distributions, tIRA/non-tIRA withdraws, etc.) and the value you are optimizing (ie total discounted portfolio less taxes still due). So, any time I needed have use min, max, etc in the tax functions I had introduce integer variables.

As an aside, if the only taxes I wanted model were federal and state income taxes then linear programming model would work (it it executed very well). Its only when I added capital gains, doing functions that require you to chose between the standard deduction and itemizing and calculating taxable social security that I had to add integer programming.

There is one thing I want to emphasize: these models do not contain all aspects of federal and state taxes. They should be used only to get insight and general understanding of the issues around taxing tIRAs and Roths.

You can use them to get some ideas but it is imperative that you try what you want to do in a reputable tax program. (I've been using some version of this roth conversion spreadsheet for a couple of years and I always run the roth conversions, charitable donations, etc through Turbotax to make sure I'm not running into any issues like deduction phaseouts, loss of tax credits, or Alternative minimum tax issues). I haven't exactly followed what the "Optimal" conversion for the year said to do. Also, you should re-run the model each year to adjust for differences in estimated vs actual returns.

I've have two model cases that I'm going to summarize in a separate post when I get a time (I'll include the workbooks in those also):

a. Studying the effect of the proposed new tax law with calculated SS taxable amount and discounting tIRA values by 20%.
b. Running a case with state income tax (I picked California since someone mentioned it).
Last edited by Indyhou on Tue Jul 30, 2019 5:09 pm, edited 2 times in total.
is50xenough
Posts: 122
Joined: Sat Jul 28, 2018 1:37 pm

### Re: Optimized Roth Conversion Model

Continued congratulations on the work. Excuse my ignorant question but if in excel isn’t it possible to get to work on a Mac without using emulation? I always thought stuff done in the office group could cross work but I guess not?
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

That's a good question. I get the idea from the open solver website that they do support mac (OSX?) but it sounds like it only is supported for certain versions:

https://opensolver.org/installing-opensolver/
OpenSolver for Mac: OpenSolver currently has limited support for Excel 2016 for Mac. Version 2.8.3 of OpenSolver includes initial support for Excel 2016 for Mac, and supports model creation and modification, as well as solving with the CBC and Gurobi solvers. Make sure your version of Excel 2016 is at least 15.28 for this to work. Unfortunately, the late 2018 release of Excel 16.16.7 has broken OpenSolver; if you can help us debug this, please email us at help@opensolver.org. However, our user Alexander reported in April 2019 that “OpenSolver works for MacOS HighSierra with the latest Excel update 16.24, but if you upgrade your OS to Mojave it no longer works.” (Thanks, Alexander.)

As far as Excel functionality, I don't think I've done anything in Excel that shouldn't work on any version of excel. I've run it on Excel 2010, Excel 365, and Excel 2007.
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

### Re: Optimized Roth Conversion Model

Indyhou wrote: Sun Jul 28, 2019 2:39 pm Here's a link to the excel model if anyone is interested:....
Thanks for sharing it - hope you don't mind some questions of the "just getting started" variety.

E.g., the comment for cell Input!A4 made sense for the first two sentences (assuming "non-rIRA" is a typo and should be "non-tIRA"). But the highlighted part of the last sentence seems to negate what came before. Can you clarify?
The basic model assumes that the sale of assets in non-tIRAs have no or insignificant long term capital gains. If there are some appriciated assets in the non-rIRA, you may enter the current value of these assests. This value should NOT be included in the non-tIRA account in the basic model since it will considered as seperate asset class.
Thanks!
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

That comment is unclear, sorry.

It dates back to when I first started the model; the thing quickly got too be to try to document it with embedded comment and I switched to using the powerpoint document I included in a previous post as the combined spec/documentation (

I haven't really updated them for a long time (though most of them are probably still valid.

So far in the discussion of the model I've just been considering one non-tIRA account with an value (\$500K) and an initial cost basis (\$400K). If you look a couple of lines down in the "advanced model" you'll find a second non-tIRA fund (non-tIRA fund 2) with its separate cost basis.

What I was trying to say with that comment was that if you wanted to use both of the non-tIRA accounts the best way to use them is to split your non-tIRA money into the accounts by having one account consisting of mostly non-appreciated assets (or things like money market funds or CDs) and another with the remaining non-tIRA assets.

In our example, lets say that our \$500K of assets consists of \$100K of money market funds (cost basis \$100K) and \$400K of remaining assets (cost basis \$300K). You could model it like we have been (one tIRA account with \$500K cost basis cost basis \$400K) or you could split them into two tIRA accounts (non-tIRA value \$100K, cost basis \$100k, with second non-tIRA account with a \$400K value and a \$300K basis. The model will the optimal spend pattern for both assets taking into the different cost basis, etc.

As an aside, one of the reasons I started this program was that I had some highly appreciated assets in my non-tIRA accounts and I wanted to see if the optimizer would create different spend patterns. I also want to let the optimizer have the opportunity to pick the low cost basis stock for donation to a Donor Advised fund (to see if that actually was the optimal use of those securities).

Hope this helps. Again, I appreciate the questions.
Last edited by Indyhou on Tue Jul 30, 2019 5:13 pm, edited 1 time in total.
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

### Re: Optimized Roth Conversion Model

Indyhou wrote: Sun Jul 28, 2019 5:50 pm Hope this helps. Again, I appreciate the questions.
Understood, thanks!

Next question: in the google drive file, cell Input!B39, Base Case: Min Value of tIRA at end of case, has a value of 9999999, while B58 and B77 have 0. I tried a run with base case max Roth conversion (B37) twice that of Alt Case (B56), and Alt Case 2 (B75) = 0 and got "unexpected" results. Changing B39 to 0 and rerunning produced results that "appear reasonable."

In no case was the ending value of the tIRA \$9,999,999 or higher - that would be a nice problem to have. Did read through the documentation pdf but didn't see (could have overlooked) comments on this input cell.

Thoughts? Thanks again!
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

Next question: in the google drive file, cell Input!B39, Base Case: Min Value of tIRA at end of case, has a value of 9999999, while B58 and B77 have 0.
In no case was the ending value of the tIRA \$9,999,999 or higher - that would be a nice problem to have. Did read through the documentation pdf but didn't see (could have overlooked) comments on this input cell.
Sorry, the descriptions of Input!B39, B58 and B77 was an error on my part. It should have read "Max Value of tIRA". These are new entries that I put in as part of the modeling of the forced liquidation of tIRAs- If you set the max Value of the tIRA to a large number then the optimizer is free to let the end value of the tIRA assume any value. If you set it to 0 then the optimizer is forced to liquidate the tIRA by the end of the case.

I've corrected this description (along with a few other descriptions that were confusing and an error I found in the standard deduction determination when filing status changes) on Rev 44. I haven't gotten a change to update the documentation for this and the inherited IRA features yet so you won't find it in there.

One other thing about about Max Value of tIRA- if you look closely at the alt case you'll notice that I add two to the tIRA max. For some reason the model fails with an infeasible solution if the max is set to zero. It works fine if the number is slightly above zero. I don't know why this is but the difference between 0 and 2 is insignificant.
I tried a run with base case max Roth conversion (B37) twice that of Alt Case (B56), and Alt Case 2 (B75) = 0 and got "unexpected" results. Changing B39 to 0 and rerunning produced results that "appear reasonable."
I'm not sure whats going on there. I ran a case with a max Roth conversion of \$300K and another with \$150K and got identical results. The only difference I saw was is the the \$300K Roth max took 5 seconds longer to converge (1:01 minutes vs 0:56).

This brings up an point about this model- The Roth max value does effect the execution (probably due to the non-linear nature of the model). It will converge with a larger number but it will take longer. If you start modeling larger portfolio values and you get some Roth distributions that are "pegged" at \$150K you might want to increase the max.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

Here's a Roth Model run that looks at the effects of proposed tax changes.

My take away: If the proposed tax changes (forcing the liquidation of inherited tIRAs 10 years after they are inherited) are enacted, the benefits of Roth conversions may increase. However, you'd have to be comfortable paying considerable additional taxes up front.

The scenario:

1. Initial traditional IRA (tIRA) of \$2M.
2. Initial Roth IRA of \$0.
3. Initial funds outside of the IRAs of \$500K with a cost basis of \$400K
4. Start at age 60 with 30 years in the plan.
5. Need \$100K real (ie before inflation) per year plus whatever income taxes are due from the prior year.
6. The portfolio returns an average of 2.5% real. Also, the non tIRA accounts have a qualified dividend rate of 2% and a non qualified dividend rate of 0.25%.
7. Social Security Benefits are \$40K per year starting at age 67.
his model using the same starting account values, social security benefits, etc as the prior model posted except:
8. The case lasts for 35 year.
9. For the first 20 years the filing status is married filing jointly.
10. For the next 5 years the filing status is single. The social security benefit is reduced by 66% (ie 1/1.5).
11. The portfolio is inherited at year 25 by an heir who is 50 years old that makes \$100K per year and who files as married filing jointly. All Social Security benefit, pensions and Roth conversions stop. The heir takes minimum distributions from the tIRA per the IRA's single life table and pays all taxes (including their income) from the inherited portfolio.

Base Case: Max Roth \$150K, Current Tax law: RMD 70, final value of tIRA is discounted by 20% to account for taxes still due.
Alt Case: Max Roth \$150K, Proposed Changes to tax law: RMD 72, inherited tIRA must be liquidated 10 years after it is inherited.
Alt Case 2: Max Roth \$0, Proposed Changes to tax law: RMD 72, inherited tIRA must be liquidated 10 years after it is inherited.

The value of the inherited tIRA under the proposed tax law are not discounted since their ending value is zero.

Here's some images of the result:
Roth Conversion patterns:

Note that the Roth Withdraws after inheritance due to RMDs

Account Values:

In this case the value of the roth conversion cases do not exceed the non-roth cases until after the tIRA is inherted.

Taxes due:

Total (cumulative) Taxes:

Note the increase in taxes due for the non-roth case after inheritance.

Withdraw Pattern of Roth allowed under proposed tax changes:

Note that there are Roth WDs between ages 80 and 85. This seems to be to make up for the reduction in social security benefits.

Withdraw Pattern of No Roth allowed under proposed tax changes:

Notice large tIRA withdraws toward the end of the period.

Account Composition for Roth Allowed Proposed Tax Changes:

Note that a significant amount of the tIRA has been converted before the portfolio is inherited.

Account Composition for No Roth Allowed Proposed Tax Changes:

Link to PDF of all graphs and models:

Link to excel worksheet (see above discussion on how to use this):

Execution time on 64bit Windows Computer: between 1 minute 21 seconds and 5 minutes 49 seconds. m m

Issues with model:

1. Modified 7/30 to remove issue with Roth RMD
Last edited by Indyhou on Wed Jul 31, 2019 11:32 pm, edited 6 times in total.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

Here's a model run for Roth Conversions including state taxes.

My take-away: It looks like in this case state taxes don't change the "optimal" Roth Conversion sequence by very much.

The scenario:

1. Initial traditional IRA (tIRA) of \$2M.
2. Initial Roth IRA of \$0.
3. Initial funds outside of the IRAs of \$500K with a cost basis of \$400K
4. Start at age 60 with 30 years in the plan.
5. Need \$100K real (ie before inflation) per year plus whatever income taxes are due from the prior year.
6. The portfolio returns an average of 2.5% real. Also, the non tIRA accounts have a qualified dividend rate of 2% and a non qualified dividend rate of 0.25%.
7. Social Security Benefits are \$40K per year starting at age 67.
his model using the same starting account values, social security benefits, etc as the prior model posted except:
8. The case lasts for 35 year.
9. For the first 20 years the filing status is married filing jointly.
10. For the next 5 years the filing status is single. The social security benefit is reduced by 66% (ie 1/1.5).
11. The portfolio is inherited at year 25 by an heir who is 50 years old that makes \$100K per year and who files as married filing jointly. All Social Security benefit, pensions and Roth conversions stop. The heir takes minimum distributions from the tIRA per the IRA's single life table and pays all taxes (including their income) from the inherited portfolio.

Base Case: Max Roth \$150K, Current Tax law: RMD 70, final value of tIRA is discounted by 20% to account for taxes still due. California State income taxes apply.
Alt Case: Max Roth \$150K, Current Tax law: RMD 70, final value of tIRA is discounted by 20% to account for taxes still due. No State income taxes apply.
Alt Case 2: Max Roth \$0, Current Tax law: RMD 70, final value of tIRA is discounted by 20% to account for taxes still due. California State income taxes apply.

The model currently support 4 state tax brackets. Since CA has more than that I chose the married filing jointly brackets between \$16K and \$107K of taxable state income and then just extended them down to \$0 and up to \$99M:

Here's the Roth conversion pattern:

It appears that the Roth Conversions are slightly effected by state taxes but not by a great amount. Also note Roth RMD after inheritance.

Taxes Due per year:

Marginal Tax Rates:

PDF of all graphs and models:

Link to excel worksheet (see above discussion on how to use this):

Execution time: between 1 min 11 seconds and 2 minutes 14 seconds.

Issues with model:

1. The model currently only supports one state tax table. The state taxes between between age 80 and 85 (when the filing status is single) would be less than what is indicated.
2. RMDs for Roth added 7/30
Last edited by Indyhou on Wed Jul 31, 2019 11:35 pm, edited 1 time in total.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

I just finished the documentation on the model to include information on changing filing status during a run, inherited portfolios and simulation of the proposed changes in tax law to require liquidation of tIRAs in 10 years after they are inherited.

Also, I upgraded the spreadsheet input worksheet to provide more information on what the parameters do and how to set up a model.

I also added RMDs to inherited Roth accounts were not not included in the previous version.

The file name is Roth Linear Program Rev 45 example case No SS tIRA 0 percent discount.xlsm.

I also updated previous links in this thread to point to the new documents.

Please let me know if you have any questions or there are issues. Thanks.
Last edited by Indyhou on Wed Jul 31, 2019 11:23 pm, edited 1 time in total.
abc...xyz
Posts: 21
Joined: Thu Aug 20, 2015 3:45 pm

### Re: Optimized Roth Conversion Model

“The cell 'AF45' in constraint 'AF23:AF65 = GF23:GF65' has a divide by zero error or has a problem with the function used ( eg #DIV/0! or #VALUE!).”

Are there other changes that I’d need to make for a ‘single’ filer? I assume that pre-tax 401k amounts would be added in with the tIRA amount at cell B10 of the Input tab?

The spreadsheet is a very impressive effort and looks like it will be very useful in minimizing/leveling taxes between ‘before’ and ‘after’ starting SS and RMDs.

One suggestion would be to model the Medicare income premiums as additional taxes as those premiums can be quite substantial when doing large Roth conversions and/or RMDs, and would probably skew the optimum Roth conversion amounts.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

“The cell 'AF45' in constraint 'AF23:AF65 = GF23:GF65' has a divide by zero error or has a problem with the function used ( eg #DIV/0! or #VALUE!).”
My apologizes; looks like in adding some of the feature I broke the logic for the single filer SS benefit % taxable calc.

Here's the fix:

Are there other changes that I’d need to make for a ‘single’ filer? I assume that pre-tax 401k amounts would be added in with the tIRA amount at cell B10 of the Input tab?
Not single filing status per se. This particular case models a situation where filing status changes at case year 20 and is inherited at case year 25. This might not be what you want to do. Change B56 and B60 to zero not to do this on the base case and the corresponding case for Alt case and Alt case 2.

Thanks for trying this out.
Point
Posts: 245
Joined: Mon Jul 10, 2017 9:33 pm

### Re: Optimized Roth Conversion Model

It also seems like you’ll be paying the increased Medicare cost with the increase in income from the Roth conversion. This will add up for two people over multiple years.

Another factor to consider are LTC costs. If LTC is considered medically necessary, then RMDs are non taxable to the extent the income covers LTC cost and is greater than the 7.5% of income threshold.
Topic Author
Indyhou
Posts: 16
Joined: Tue Nov 01, 2016 12:15 pm
Location: Texas

### Re: Optimized Roth Conversion Model

One suggestion would be to model the Medicare income premiums as additional taxes as those premiums can be quite substantial when doing large Roth conversions and/or RMDs, and would probably skew the optimum Roth conversion amounts.
It also seems like you’ll be paying the increased Medicare cost with the increase in income from the Roth conversion. This will add up for two people over multiple years.

Another factor to consider are LTC costs. If LTC is considered medically necessary, then RMDs are non taxable to the extent the income covers LTC cost and is greater than the 7.5% of income threshold.
I'll look at that when I get some time. I reasearched social secucirty benefits when I did the model but didn't look look at medicare or LTC. Thanks for the comments.
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

### Re: Optimized Roth Conversion Model

Point wrote: Thu Aug 01, 2019 8:55 am It also seems like you’ll be paying the increased Medicare cost with the increase in income from the Roth conversion. This will add up for two people over multiple years.
Yes, IRMAA tiers can be significant. Not exactly taxes, but they walk and quack much the same.
Another factor to consider are LTC costs. If LTC is considered medically necessary, then RMDs are non taxable to the extent the income covers LTC cost and is greater than the 7.5% of income threshold.
Sort of. The LTC would be an itemizable medical expense. Ten percent (starting in the 2019 tax year) of AGI is subtracted from total itemizable medical expenses to get the medical itemizable deduction, which is then added to other itemizable expenses to get the total itemized deduction. The total itemized deduction amount above the standard deduction then reduces taxable income.

Yes, anyone paying out of pocket for a nursing home or similar facility is likely to have plenty of medical expenses.
czaj
Posts: 50
Joined: Sun Oct 04, 2015 4:01 pm

### Re: Optimized Roth Conversion Model

Thanks for sharing your model and insights. This has influenced me to do some modeling of my own.

• If the model is using real dollars, and you're including the taxation of social security in the model, you'll need to factor inflation into the upper and lower base values. The upper and lower base (32K and 44K for MFJ, respectively) do not increase with inflation like others. This essentially makes it more difficult to avoid the high tax bumps over time. The same should be applied to anything else that isn't inflation-adjusted (i.e. a loss in real value over time)
• The discounted account values at the end seem to be important to me. I've only been looking at 20 years for now for simplicity and model testing, but when I tried varying the traditional account value discounts, there can be a tipping point that leads to more Roth conversions being more optimal. This discount value is highly personal so everyone's going to be different in terms of how Trad vs Roth vs Taxable is valued.
• I think converting up to the top of the 12% is basically a no-brainer for a lot of people. I think using taxable accounts for early spending before SS while converting Roth up to the top of the 12% bracket makes a lot of sense. This depends on your cost basis of course, but assuming it's not too high, you can convert more into Roth while enjoying 0% on LTCG.
maryannesk
Posts: 20
Joined: Thu Jan 03, 2008 3:28 pm
Location: Stone Harbor, NJ

Indyhou,