Retiree Roth Conversion Decision Model

[BigFoot48]

I recently started a thread to get advice on whether, as a retiree, I should do some Roth conversions as I have none presently. I got some good advice and ideas, and over the next month I hope to decide whether to do it or not. That thread is here: http://www.bogleheads.org/forum/viewtopic.php?f=1&t=96928

As part of that effort I created a fairly simple Excel 2003 model (that has evolved into a fairly sophisticated model) to try and get a handle on all the investment accounts, income sources, living expenses, and taxes that we have, and see how a series of Roth conversions would impact our financial life. I thought I would share that effort, so I did some extra work on it that would allow others to use the model by just filling in their own numbers in a input section. I've put it in my Dropbox folder and this is the link to download it:

Download the current version 13.1: https://www.dropbox.com/s/hp8nmddqxmz64zj/Retiree%20Roth%20Conversion%20Model%20v13.1.xls

The current version of the model can be imported into the Google Spreadsheet on-line program and/or can be used with the free program LibreOffice Calc, available here: http://www.libreoffice.org/default/. (Note: The warning when opening in Libre that macros have been disabled is a false-positive. There are no macros in this model.)

What will this model do?
> Quantify the financial impact on your portfolio and income taxes from doing Roth conversions, providing critical information to determine if it's worth doing in your situation.
> Calculate yearly results, including income, expenses, taxes, inheritances, and asset sales for your major portfolio accounts for the time period you select, from 1 to 40 years.
> Model future common events, like death of a spouse, receiving an inheritance, selling a home, buying an RV, etc., and having a large tax rate increase!
> Show how conversions will impact your marginal taxes each year, allowing you to adjust conversion amounts to test for the best tax outcome.
> Provide an accurate Federal tax calculation, including determining taxable Social Security benefits, within the limits of the input factors.
> Use two almost identical financial models, with the only difference being a few Roth conversion and expenditure amounts, to focus on answering the "should I" question.
> Provide a free, unprotected, fully transparent yearly financial forecasting model for those needing such a tool but unable or not interested in preparing their own.
> Bonus feature: Compare two different options for starting Social Security benefits for the most common methods, e.g. spousal at 66, self at 70, or spouse at 62, self at 66, etc..

What will this model won't do?
> Predict the future. All input factors are under the user's control but the future will likely provide different results.
> Be completely useful for taxpayers with taxable income or deduction items not included in the calculations, like having dependents, qualified dividends, AMT, tax credits, etc..
> Automatically correct for results such as negative account balances. The user is expected to review the yearly schedule and make refinements as necessary to avoid this.


Please note this is a simple model, designed for married or single retirees, with limited selections of asset and revenue items. While I believe it is operating correctly as designed, it may not be and users may find errors or bad modeling, which I will attempt to address if reported to me. Feel free to modify it as you see fit and provide an improved version if you wish. I do plan to add a "single taxpayer" option in the future [and did!], to provide for that inevitable event that most of us will face, [and to make the model useable by those filing as Single].

So, plug in your own numbers and see if it gives you any interesting or useful insights. Note the "do Roth" section and the resulting comparison is optional, and can be ignored if all you want is to look at your current situation.

[backofbeyond]

Excellent...Many Thanks for passing this on!

[BigFoot48]

A new and improved version (2.0) of this model is now available for downloading: [EDIT] See even newer, improved version below!!
The primary improvements are:

1 - A user can switch tax status from married filing jointly to single at any year, reflecting an event most of us will face. Since the big advantage of a Roth is the tax-free withdrawals, coinciding them with a change to single status may be beneficial.

2 - The model now supports single tax filing status.

I have tested the model against Turbotax and it correctly calculates Federal taxes (within a few dollars). It models a simple return and doesn't take into account dividend tax rates, or other rates and credits taxpayers "enjoy".

Please let me know of any errors or suggested improvements. I think the model is now complete enough for me to use it for evaluating the financial benefits to doing Roth conversions after retirement.

[BigFoot48]

A new and much more user friendly version 3.0 of this model is now available at: [EDIT - New version now available. See below.]

Updates include:
1. A much improved casual user interface, including descriptions and explanations of how the model works and what each input item does.
2. Major sections now on separate pages for easy access.
3. A fix to a lookup table.

As usual, I welcome [strike]beta-testers[/strike] people to use the model and see if it works and provides useful data in the "should I do Roth conversions" question process.

As for myself, a 64-year old retiree with no Roth IRAs, I am still evaluating the decision with the help of this model, but at this point, and for our situation, I'm not seeing a huge advantage to doing so. However I'm going to use the model in the next few months to do some worse-case scenarios, such as significantly higher tax rates in my MRD years, as I need to get started this year if I'm going to do it.

Use and I hope it helps some people get started in this decision process!

[trailmk]

Thank you for sharing your work. I'm going to have fun playing with your spreadsheet.

trailmk

[BigFoot48]

Thank you for sharing your work. I'm going to have fun playing with your spreadsheet.

trailmk

Yes, have some fun with numbers and do let me know if you find any problems, or have useful improvements ideas!

[BigFoot48]

I've updated and improved the model by adding an itemized deduction option to the federal tax calculation and providing a summary of key results. I also corrected a tax factor escalation calculation, and added or changed numerous other descriptive and help information items. Version 3.1: [EDIT - Replaced by newer version. Get link below.]

I've yet to get any feedback so I don't know if those who have used it have found it of value. I do think the model is now at a point where I can rely on it for making my own evaluation of doing Roth conversions. Just looking at the model sample data, which approximates some of mine, I'm not too impressed with the results favoring conversions for my situation. And while I call this a Retiree Model, I suppose someone who is 50 and working could use it with more encouraging results. I have not tested that.

Hope you find it of use, and let me know of any problems or suggested improvements.

[BigFoot48]

I realized my personal situation that MRDs from my IRA was all that I will require to live on might not work for everyone, so I modified the model to allow additional IRA withdrawals post-MRD start. I also put in an error check to keep the IRA balance from going negative due to all these automated withdrawals, and added some warning messages should any account end up with a negative balance as they don't as yet have a procedure to keep them from going negative.

Version 3.2 now available: REPLACED. See later post.

[dcb]

I've updated and improved the model by adding an itemized deduction option to the federal tax calculation and providing a summary of key results. I also corrected a tax factor escalation calculation, and added or changed numerous other descriptive and help information items. Version 3.1: [EDIT - Replaced by 3.2. Get link below.]
I've yet to get any feedback so I don't know if those who have used it have found it of value. I do think the model is now at a point where I can rely on it for making my own evaluation of doing Roth conversions. Just looking at the model sample data, which approximates some of mine, I'm not too impressed with the results favoring conversions for my situation. And while I call this a Retiree Model, I suppose someone who is 50 and working could use it with more encouraging results. I have not tested that.

Hope you find it of use, and let me know of any problems or suggested improvements.

Thank you for your efforts in making this model available to all. I too did not see much benefit in doing additional Roth conversions, but then i may not have entered all the inputs correctly, or my situation is somewhat unique. (I am now withdrawing my RMD plus a few thousand more from my IRA for living expenses. I delayed Soc. Sec. to age 70 so that is the bulk of my living expenses. Also, I already have about half of my IRA in a Roth.) My question is: What do I enter for post RMD IRA withdrawals? Is it the same as Pre RMD withdrawal or just the additional amount above the required amount? I would like to add more to my Roth. Not because it will help my bottom line necessarily, but because of the tax advantage of a Roth to my heirs. But then I do not wish to pay a high price for doing it either.

[BigFoot48]

Thank you for your efforts in making this model available to all. My question is: What do I enter for post RMD IRA withdrawals? Is it the same as Pre RMD withdrawal or just the additional amount above the required amount? I would like to add more to my Roth. Not because it will help my bottom line necessarily, but because of the tax advantage of a Roth to my heirs. But then I do not wish to pay a high price for doing it either.

Thanks! Glad you're finding it useful.

Enter the new total IRA withdrawal amount for the post-MRD years on the Input form line 44 field, and then adjust your Roth contribution amount on the Roth page accordingly.

Of course, by doing this you have created a situation where the non-Roth model is no longer directly comparable to the Roth model, because you're making withdrawals you wouldn't have otherwise made just to fund the Roth. So you might want to copy the non-Roth model summary numbers before you make these changes, then compare the three results.

[BigFoot48]

A maintenance update of this model is now available: EDIT: Replaced by new version. See later post.

An information and instruction page has been added, along with a number of title and formatting changes. Error checking has been added to warn if any account balance becomes negative due to excessive withdrawals, and an automated reduction in user IRA withdrawal amounts to prevent negative balances is now in place.

As always, comments and suggestions for changes and improvements are welcome.

[BigFoot48]

Version 4.0 of this model is now available: Updated: See later post.

In finally getting serious about making my own decision on Roth conversions for this year, and the next six, I used the model and discovered it didn't address my situation where I am taxable funds poor, and will need to withdraw more from my IRA to pay taxes and the model didn't provide for that. So I added it and one thing lead to another and I updated, changed or fixed a number of items.

If you have previously downloaded this model, I recommend you update to this one for the state income tax fix alone (wrong sign used - sorry about that). The other changes are as follows:

Added Input option to allow IRA deductions during Roth conversion years to be higher than in the Base Case in order to pay income taxes.
Added separate page for inputting Roth conversion amounts, allowing one to monitor the effect it has on the yearly Federal
income taxes to stay within the desired brackets.
Separated lump sum inheritance amounts and asset sales to better reflect when these events will occur.
Added line breaking out the Inherited IRA amount in the Account Changes section.
Improved automated IRA deduction formula to prevent a negative balance, although not quite perfect yet as small positive balances can remain.
Fixed tax calculation of zero taxable income years and state income tax which used the wrong sign in calculating net income.
Improved labels and notes.
Added Change Log

As usual, if anyone has any comments or suggestions, I would welcome them.

[BigFoot48]

Found a formula error in the Base Case IRA withdrawal calculation which now has different input factors than the Roth Case. Updated 4.1 version: Update: New version now available. See later post.

[SGM]

I like your spreadsheet. I played with it for a few minutes and I am sure I will use it further. I have been converting since 2010.

My questions may be premature because I have not had much time to use the spreadsheet.

Non conversion: Say you have 100 k in tIRA and 25k in after tax. In the 25% tax bracket you have 100k-25k(expect 25% tax rate) +25k = 100k buying power
Conversion: If you have 100k in a Roth and 25k in after tax- 25k paid in taxes from your after tax acct= 100k buying power.

You have the same buying power with the conversion or without. I always think of the value of my tIRA or t401ks as its value minus my expected tax rate. Do you feel your spreadsheet addresses this issue?

How about higher tax rates if the present law goes into effect? I think your max rate was 33%.
Good job and lottsa effort. Thanks.

[BigFoot48]

I believe the model contains the calculated data to make the after-tax buying power comparison between the Base Case (no conversions) and the Roth Conversion Case, but it's not really set out in the totals or summary. Check it out and see if it provides the data you want.

The tax rates for the four brackets included can be changed (but not the bracket amounts except in year one) in a future year to test the scenario that drives many a Roth conversion decision. Look for the tax factors at the bottom of the Input page.

Let me know if you find any problems or have any suggestions for improving it. I plan to use the model myself to make my own Roth conversion decision soon and hope it will provide a reasonable analysis of the financial impacts of doing so, although I think some of the data on the Results page could be improved to help in that decision.

[SGM]

There is an excellent article from 2010 in the Journal of Retirement Planning. The authors look at comparisions of a variety of scenarios 10 years out. You might want to plug some of their numbers into your spreadsheet for comparison. The article does not account for expenditures during retirement. Have you found that expenditures make much of a difference in the outcomes? I find it difficult to predict changes in expenditures other than using an inflation factor.

An interesting scenario which may increase the benefit of conversion is when a spouse passes on. In the example the other then pays at a higher rate say from 25 to 28% even with a loss of the lower of 2 SS payments. I see your program seems to take that into account.

To Convert or Not to Convert,
That is the Question
By Robert S. Keebler and Stephen J. Bigge

[BigFoot48]

I'll see if one or more of their examples can fit into my model. I have not run any test cases to see how varying expenses affects the outcome. but I can see how the model would benefit from a couple of expenses refinements.

First, it assumes expenses go up every year based on the selected escalation rate but, I believe, the reality is that expenses usually decrease later in life (and, for some, jump back up at the end). Second, it doesn't have a provision for making a large purchase that might be funded by a Roth account. And lastly, while the model allows for triggering a single taxpayer rate in any year, it doesn't provide for decreasing the SS benefits resulting from the death of a spouse. I will try to add these to the next version.

The article: http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CCIQFjAA&url=http%3A%2F%2Fwww.keeblerandassociates.com%2Ffiles%2FJORP_10-03-07%2520To%2520Convert%2520or%2520Not%2520to%2520Convert.pdf&ei=qp9xUI-jKZCUigL8y4DYCA&usg=AFQjCNGCUUMtY4mMHCshC11_MYtboMJvyw&sig2=C2RdhI3XvMFmjYKtBEPt_A

[crow]

Thank you so much for all the work you put into this. I had done some basic calculations on my own, but this is a lot more detailed and helped me to ultimately decide not to convert.

Crow

[ResNullius]

OK, I have a Roth question. I'm retired, so I have no earned income. Can I contribute to a Roth IRA? If so, what is the max that I can contribute each year? I'm 62. I've always assumed that I couldn't do a Roth while I was working due to the income limits. Now that I'm not working, I've been assuming that I still can't because I don't have earned income. Also, I've assumed that the max contribution anyway would be only $6K for someone my age, so it really doesn't seem to be worth the trouble. Am I off base with these assumptions?

[BigFoot48]

Crow, I'm glad you have found it useful. Do revisit your decision if you used the model and see a major update to it via the other tread. For example, I decided to add some flexibility to the expense forecast today and that lead me into splitting the SS benefits, adding a spouse age, and allowing SS benefits to start and end at different ages. Look this revised model by tomorrow.

ResNullis, this thread is about converting a portion of a traditional IRA into a Roth after 59 1/2, which to the best of my knowledge can be done in any amount if one wants to pay the tax on the withdrawal. There may be some limitations on this that I'm unaware of and perhaps someone will expand on the process.

[ResNullius]

Oh, yes, I got mixed up. The issue for me and the OP is whether to convert a TIRA to a Roth IRA, and that's quite different from making normal contributions to a Roth.

[DickBenson]

OK, I have a Roth question. I'm retired, so I have no earned income. Can I contribute to a Roth IRA?

No. Your assumption that you need earned income is correct.

Dick

[DickBenson]

... while the model allows for triggering a single taxpayer rate in any year, it doesn't provide for decreasing the SS benefits resulting from the death of a spouse. I will try to add these to the next version.

Does the model take into account any changes in the amount of SS benefits that become taxable when the tax bracket changes come sooner when filing as a single taxpayer?

Dick

[BigFoot48]

Dick, it calculates how much of SS benefits are taxable based on other income and uses either the married or single filing choice in doing the calculation. The primary fields from Form 1040 for the taxable SS calculation are in the yearly summaries, and can be viewed by unhiding the rows.

A new version of the model with many nice improvements will be posted tomorrow if you want to check it out.

[BigFoot48]

A nicely improved, if I must say so myself, version of the model is now available - see later thread for latest version.

This calculates the data the same as previous models, but adds a number of refinements to allow users to more accurately reflect their situation. Primarily, SS benefits which were previously entered for both the user and spouse, but only the users start date was used, can now be started for each individual, such as at 66 and 70. In addition, either benefit can be stopped at a future date to stimulate the death of a spouse.

The example data used in the model indicates about a 4% higher portfolio balance at age 93 by doing Roth conversions. If an across-the-board 5% tax rate increase happens in 2020, this jumps to 7.6%. Unfortunately we're all not example data, but the assumptions are realistic for a lot of people, I believe.

This is the list of all the changes.
> Social Security benefit start years for a married couple can now be different (amounts always were) and either benefit can have an end date to model the lost of benefits occurring from the death of a spouse,
> Living expenses usually drop later in life. A percentage reduction can now be entered at a future age.
>One benefit of doing a Roth conversion is to use those funds tax-free in a future year. A large expenditure can now be input in a future year to simulate the use of these funds, rather than just their accumulation in the taxable account. Pending: Option to use IRA withdrawals in the Base Case to fund triggering a tax event!
> Existing Roth account separated from previous single row for all Roths. Withdrawal calculation set up for existing Roths.
> Trigger "year" type input data has been changed to the more intuitive trigger "age".
> Added a "Percent of Tax Bracket Used" to Roth Conversions Input page to help determine optimum conversions each year.
> Added summaries showing cash flows to bottom of Base and Roth pages.
> Numerous title and description refinements to be more descriptive, and other changes to make the model more warm and fuzzy!

As usual, if anyone finds any errors or problems with the model, let me know.

[earlyout]

A tremendous amount of effort to get this put tegether. Thank you!

Yesterday afternoon I tried to determine if the value of traditional IRA used to generate "Portfolio Balance at age 93" is discounted for the taxes that will have to be paid on the tIRA. If so, what tax rate did you use?

Thanks.

[BigFoot48]

Thanks, you're very welcome. No, the values at the end of the model are not discounted for their possible tax burdens, but certainly an important factor, as a basket of Roth IRAs at 93 would be significantly more valuable than a basket of TIRA's! I will add that to think "think about doing this" list for the model.

[BigFoot48]

A new version is now available with some very useful improvements - 5.0: TEMPORARILY WITHDRAWN

The most generally useful change is rather than being a fixed 33 years, the model now extends to 40 years and the user can select however many years he/she wishes, from 1 to 40. So a person 50 may select a 40 year modeling period, which a person 66 a 24 year period. This makes the ending year data much more useful, I believe.

The other major change is allowing the user to trigger an IRA withdrawal, above and beyond the normal pre-MRD/post-MRD withdrawals, at any age and for however many years desired. This will produce a better comparison between the use of tax-free Roth funds to make expenditures later in life, to that required if taxable IRA funds were withdrawn.

For example, and as use in the model's sample data, a person may want to buy a $150k RV at age 75. If they did a Roth conversion years earlier, those funds would be available tax-free, but if they needed to withdraw money from an IRA to fund it, say over the previous three years to minimize tax bracket creep, then a tax cost would be incurred. I wanted the model to analyze this situation.

Other minor improvements have been made in titles and explanations. While no calculating changes have been made as best I can recall, I recommend all previous users of this model upgrade for the useful improvements.

Enjoy, report back, and now, finally, maybe I can finish my own conversion analysis!

Change Log:
Total number of years in model has been increased to 40.
A user can chose the number of years to forecast, ranging between 1 and 40.
The Base Case now has a feature to make a TIRA deduction, at any age and for any number of years, to mimic a Roth deduction in the same period.
Expanded some calculation details, normally hidden, to provide easier understanding and an audit trail.
Improved instructions and labels.

Oct 17: I just notice while doing mine that the SS benefits start dates must be equal to or greater than your current ages as input for the model to include SS benefits. I added a cell comment noting this and resaved the model.

[BigFoot48]

An new version is now available - 5.1: New version now available. See below.

Sorry about an update so soon, but I discovered a one-year shift error in a formula today while doing my own numbers.

Changes:
Fixed Lump Sum Change event that was included in wrong year.
Added several error checks to alert for negative account balances in any year, and SS start date earlier than users' ages.

[SlowLane]

Bigfoot, thanks for your Roth Model. It is a very good tool for learning about Roth IRAs and the tax situation. My question was "how much should I convert in 2012, before the 2013 tax increases?" I had to do some fiddling, but I arrived at the answer using your Roth Model. You did a great job in creating it!

I offer these ideas for improving your Roth Model:
1) Instead of entering annual withdrawal amounts from the Taxable Account and Existing Roth Account, it would be helpful for the Model to automatically drain the Taxable Account before touching the Existing Roth Account. Perhaps set a priority list of the different accounts and automatically drain them in the specified order for taxes and expenses.
2) I used "Single" tax filing and the RMD was not being taken from the IRA account in the "Roth Case". The "Base Case" worked fine, so I just copied the RMD line to the "Roth Case"
3) The top tax rates are needed to get a complete picture. Nice foresight to make tax tables accessible, and this feature saved the day for me. For higher growth rates, it pays to convert more aggressively, at higher tax rates.

Thanks again for a job well done.

[BigFoot48]

I'm glad you found it useful and thanks very much for the suggestions, and finding that calculation error. I will correct that immediately. One advantage of using two almost identical models to make the calculation is that by removing the few input differences, like Roth conversion amounts, the two models' results should be identical, making debugging easier, but I missed that one because I don't test the Single option enough!

As I originally developed the model for my situation, I didn't model the higher brackets but will consider putting them in, perhaps in the new year with any changes in the rates, etc.

I will see if your idea of setting an account priority list can be accomplished without too much difficulty. I've left some input items to be user-regulated, and provide messages when an account goes into negative balance, rather than just automatically adjusting withdrawal amounts. I may be able to refine this in future releases.

UPDATE: RMD calculation for IRA fixed on Roth side of models (had spouse age and should have been user age). Download again if you used the Single option, or your spouse's age is different than your own, AND you have IRAs (existing or inherited) in your case.

[SlowLane]

BigFoot, you are on the ball! You provide better support than commercial software vendors!

Strategies for Tax-Efficient Retirement Withdrawal is a whole other topic. Here is one link:
http://www.fpanet.org/journal/CurrentIs ... lPlanning/

The above article is dated, and does not consider Roth accounts. If you modified your Roth Model, you could discover the optimal strategies and publish a paper. That is, if you are so inclined.

Thanks for fine work!

[BigFoot48]

A new version of this model, 5.5, is now available. Newer version is now available. Find in later post.

I recommend users of earlier version download this and re-input their data as there are a few error corrections that may have affected some users' results.

The primary changes are:
1. Added 33% and 36% tax brackets
2. Added start year to Taxable and Roth accounts withdrawals, allowing more flexibility in matching withdrawal rates and timing with needs,
3. Added field to enter Case Description to track and label results and remember model input factors.
4. Fixed age selection error in determining IRA MRD in Roth model. Spouse age was used rather than "self" age as used in the Base Case.
5. Fixed taxable Social Security calculation for error from a married couple having a "become single" year entered which forced the use of the single SS factor early.
6. Added error checking and messages, including hint to match going to a Single tax rate for married couples in the same year that SS benefits end for one.

Three of these changes come from SlowLane's suggestions (thanks!). When I originally prepared the model I ignored the higher income brackets as they didn't apply to me, or most people, but decided to add them as there's evidence there's at least one person that would benefit, and for future flexibility as rates are likely to shift in years to come and who wants to address it then.

SL suggested that withdrawals from the Taxable and existing Roth accounts be automated and linked, but I like the idea of leaving these things in the hands of the users. So to partially accomplish his idea I added a start year to each of these withdrawal factors. So you can input a number for taxable, matching current year needs not met by income, for example, and when the Taxable account is exhausted, start withdrawals from the Roth in that year. Please note that Taxable account changes are automated anyway, in that if in a year expenses exceed income, the needed cash flow to maintain a zero result is deducted from taxable automatically, and via-a-verse for excess funds in a year.

There was a calculation error SL detected in the RMD calculation as his single status triggered it. I discovered another filing status type error in the Social Security tax calculation, and fixed that too. These would have relatively minor impact on the comparison results (an error duplicated in both sides of the model would tend to negative themselves), but users should use the new version just to verify that their result hasn't significantly changed. I have checked the model's 2012 calculation of taxes, using numerous alternatives, against TaxCaster and the resulting Federal tax exactly matches (except for a few dollars off in a minority of cases for some unknown reason).

I made a change to the example data, increasing expected investment return rates from 3% to 5% to be closer to what I've been seeing as expected real returns in coming years. The sample data indicates a 9.8% end-portfolio advantage to doing $150K of Roth conversion for the married couple, and 13.7% if a 5% tax rate increase happens across the board in 2020. In version 5.1 with the errors, the advantage was 9.7% using the same data, so a minor impact, but I would prefer no such problems!

As usual, please let me know you find any problems, or have any suggestions.

Update: A user requested an Other Income item that has an end date, such as a salary for a pre-retiree. This will be in the next release.

[SlowLane]

Two thumbs up from me! Version 5.5 is great! I like the "Start Year" for withdrawals - it is a very good solution. And the additional tax brackets make your model more robust. Great job BigFoot!

[bsteiner]

There are so many uncertainties that it's impossible to analyze the Roth conversion with precision. The best you can do is make some reasonable assumptions as to investment returns (both in the IRA and after-tax in the taxable account), and future tax rates.

However, as a general rule, assuming you have enough nonretirement assets to pay the tax on the conversion, the Roth conversion generally makes sense if the tax rate on the conversion is less than, the same as, or not too much higher than the tax rate when you (or your beneficiaries) would otherwise withdraw the money.

A simple example. Assume a constant 30% income tax rate. You have $100 in your traditional IRA and $30 in your taxable account. You convert and use your $30 taxable account to pay the income tax on the conversion. Over some period of time, your $100 IRA (which is now a Roth IRA) grows to $200, all of which is yours. Your twin brother did not convert. Over the same period of time, his $100 traditional IRA grew to $200. He withdraws the $200, pays $60 tax, and has $140 left. He also has his taxable account. However, it grew to less than $60, since he had to pay tax on the income and gains on it each year.

The way to look at your traditional IRA is that it's part yours and part the government's, and that the income and gains on your part are tax-free. In the above example, it's 70% yours and 30% the government's. Converting is the same as contributing an additional $30 to your IRA.

The complexity arises because your tax rate can change over time.

Other benefits of the Roth conversion are (i) no required distributions after age 70 1/2 (this is a major benefit), (ii) a Roth IRA is a more valuable asset to fund a credit shelter trust (less important if the estate tax exempt amount stays at a high level and portability is made permanent) or GST exempt disposition, (iii) if you leave your IRA to your children or grandchildren in trust rather than outright (to keep it out of their estates and to better protect against their potential creditors (including spouses), you avoid the compressed income tax brackets for trusts, and (iv) you avoid the double tax problem caused by the income tax deduction for the estate tax applying only to the Federal (but not the state) estate tax.

If you're in a high bracket and expect to remain in a high bracket, you should consider converting your entire IRA now (especially given the scheduled increase in income tax rates).

If you're in a high bracket now but expect to be in a lower bracket later (such as upon retirement), you may want to wait until you retire.

If you're in a low bracket but your IRA is large, and converting it all at once would put you into a high bracket, you may want to consider converting some each year, so as not to be in too high a bracket.

If you're terminally ill, you may want to accelerate your conversion.

The above is intended only as general information, and not as specific advice, for which one would need to know the details of a particular individual's situation and his/her objectives.

[kaneohe]

A simple example. Assume a constant 40% income tax rate. You have $100 in your traditional IRA and $30 in your taxable account. You convert and use your $30 taxable account to pay the income tax on the conversion. .

you might want to fix your typo here..........

[BigFoot48]

A new version of the model, 5.6, is now available. You can download it via this link: UPDATE: Newer version is now available. Find in later post.

The release includes a new feature and fixes a logic error that resulted in over-stating taxable Social Security benefits. increasing taxes on both sides of the calculation. This impacted final portfolio amounts by less than 1% in the example data, and likely had little impact in comparison for most users, and none for those that chose the married tax option and had a "becoming single" year entered, as that situation was handled correctly.

Added is a new input item allowing a person to enter a temporary income item with an ending year, allowing pre-retirees, or retirees working part time, to include this income with an ending year.

Changes:
>Added Other Income revenue item with an end date, use for temporary income like a pre-retiree salary or temporary job.
>Fixed taxable Social Security calculation error caused by selecting "married" but not having a "becoming single" factor.

[BigFoot48]

Oops, just found an error in the Existing Roth Withdrawals start year. It was using a difference factor's start year, as result of copying formulas and not modifying every cell reference that needed modifying. If you downloaded the latest model in the last few days and expect to use this element you might want to download again, otherwise you can ignore it.

I also just realized there may be a flaw in how SS benefits are handled. Users input their current benefits, or their expected benefits starting in some future year, but these future benefits are not escalated by the COLA inflation factor. (Unless the users do this themselves.) There probably should be an escalation, as almost all other items in the model are subject to an escalation factor or an annual earnings rate. I will address this in the next release. I don't think it has much if any impact on the comparison results, as both sides would be slightly understated by the same amount.

[BigFoot48]

A new version, 5.7, of the model is now available: NEW VERSION AVAILABLE. See later post.

While I regret having to release a new version so soon, I feel it is necessary because of the large impact on forecast portfolio values that escalating the SS benefit by the COLA to the chosen start date had in the Example Data. This needed change dawned on me the other day. If anyone disagrees with my thinking or its application to this model please let me know.

The Example data assumes a couple at age 60 with the spouse taking spousal benefits at 66 and the primary wage earner taking at 70. For the Example, I assumed maximum SS benefits, which is currently $30,315 starting at age 66. The primary, who is waiting until 70 to start, would get the 8% yearly bump, giving them a $39,806 benefit at 70. But wait, there's more - there's the yearly COLA that has averaged about 2.7% over the last two decades. I used 3% in the model and apply this yearly to the $39,806 for the 10 years this person will be waiting to start makes the benefit $53,757 in 2022.

This refinement has a huge impact on income and portfolio values. In the previous version of the model without the interim COLA, for both persons total SS benefits were $1,477,871. In the new version it's $1,921,717. It also impacted the purpose of this model, determining the value of a Roth Conversion. In the earlier version doing conversions resulted in an ending portfolio value (age 89) $111,393 higher, or 10.3%. It's now $125,882 higher but because of the much larger portfolio balance the percentage has dropped to 7.9%.

Previous users can decide if they want to re-run their numbers, but it would probably be worthwhile to use this more accurate model. However, for people already on SS this change will have no impact, as the COLA was always applied to their existing benefits in calculating future benefits.

While feature creep in software is often not a positive thing, I decided to add one feature to this model since all of the data was here and ready to be used. You can now compare the results of two alternative Social Security starting year and amounts. To use this, the Roth conversion data should be zero, resulting in the two models starting out with identical results, then a selection is made trigger the alternative SS numbers to be used by the Roth model. The usual results data can be then be examined, along with a new SS comparison, to see what impact the alternative SS benefits and timing had on the portfolio. Consider this an Easter Egg, the main focus remains on the Roth conversion analysis.

As usual, please report any problems, and suggestions or comments are always welcome.

Changes:
Added the COLA adjustment to SS benefit so that its starting value in a future year is more accurately included in the model's calculations.
Added a bonus option to compare two different SS benefit alternatives, such as "take at 62" and "take at 70".
Improved error messages, formatting and instructions.

[shawcroft]

BigFoot:
Excellent work- thanks. Helps a lot.
Shawcroft

[travellight]

amazing work, bigfoot!

This is an example of what is so fantastic about this site.

[BigFoot48]

I'm glad people are finding the model useful, and I'm even more happy to contribute something to this valuable forum.

The current version of the model can now be imported into Google Spreadsheet. This will allow users without Excel to use it, or as reported by one user to me, those that might have a problem using Mac PC to run it. The import to Google is largely successful, and all calculation appear to work as they do under Excel. The only thing that does not successfully transfer is the one graph on the Results page, and I notice Google has a problem in lower levels of the Input page in lining up the cursor with the correct cell. Just be careful that you are changing the correct cell on the Input page.

I had to make some text formatting changes to accommodate the Google program, but I have not created a new release. If you want to try Google Spreadsheet, download the formatting-revised model from the above link starting today.

Info on the on-line Google Spreadsheet program: https://support.google.com/drive/bin/answer.py?hl=en&answer=140784&topic=20322&rd=1

[SlowLane]

Thanks for your diligent work BigFoot! You have helped me figure out the optimal ages for SS in my circumstance.

[BigFoot48]

A new version of the Retiree Roth Conversion Model is now available. This version, 13.0, includes the 2013 tax brackets, exemptions and deductions (as best determined at this point of time), along with a number of improvements, including a refined method of calculating annual returns on average portfolio account balances, and a less-cluttered input page.

You can download the Excel model file here: NEW VERSION AVAILABLE. See below.
Mote: 2013 tax deduction and exemption were $50 low according to CCH. Model updated on 1/11 at 5PM. DIY or download.

Changing the annual income calculation to an average of beginning and ending balance resulted in slightly higher investment income in every account, while a correction to the tIRA account calculation to deduct the current year conversion amount in averaging the balance resulted in lower income in that one account. As a result of this and the tax effects, the sample data results changed from a $126k/7.9% advantage of doing a Roth conversion to one of $96k/5.9%.

I recommend all users download the current model and re-run their numbers to get what I believe is a more accurate result. (If forecasting for 30 years with guessed annual return and tax rates can be said to produce "accurate" results.)

I created this model and I'm also a user of it, and for my situation at 65 it indicates a 2.9% higher ending portfolio balance in 30 years if I maximized my 15% bracket by doing Roth conversions for the next six years. While this is a nice bump, my numbers are such with no heirs that I'm not inclined to pay the extra income taxes now, so I don't plan on doing any conversions myself - subject to periodic revisits of course!

Changes in this version:
> Starting year, new tax rates, brackets, exemptions and deductions set for 2013, as best currently determined. (Always user adjustable.)
> Added yearly marginal tax rate determination, and a chart comparing the two cases in the Results page.
> tIRA yearly calculated income which was not reflecting the impact of current year Roth conversions, thus being overstated.
> Added option to not calculate Federal and state income taxes to better understand impact of Roth and other decisions on after tax cash flow.
> Started new version numbering scheme based on the tax year.
> Moved instructions, notes and comments to title and factor comment fields to allow more space, ease of editing and readability.
> Added negative balance monitoring to automatically prevent negative IRA account balances.
> Updated, improved and added descriptions and instructions.

As usual, I welcome any comments, suggestions or problem reports. Enjoy, and I hope all find it useful in making the Roth conversion decision.

[jdjd2]

I found your thread when you commented on a thread I started
viewtopic.php?f=1&t=110999
where I was using a very unsophisticated spreadsheet to consider a Roth conversion.

Thanks for pointing me to your thread and spreadsheet, a very useful tool.

A few comments and questions:

Setup and Input Worksheet

• Row 47 - 4. Special tIRA Withdrawals

The comment states: Having a Roth account allows tax-free withdrawals for spending purposes. To more accurately compare the Base Case to the Roth Conversion, force tIRA withdrawals to be made in the Base Case at the same time Roth withdrawals are being made in the Roth Case. Use the lump-sum Expenditure field just above to match the Roth expenditure amounts, if any.”

While I don’t understand how this would “more accurately compare the Base Case to the Roth Conversion”, it appears to be a moot point if one does not need to make Roth withdraws prior to full Roth conversion. Is this correct?

• Rows 26 and 27 - Social Security Benefits

Initially, our SS benefits equal my wife’s PIA at RFA plus my spousal benefits. At age 70, I will claim my deferred benefits, and my wife will claim her spousal benefits against my PIA at my FRA. This strategy maximizes our SSI now and at age 70.

How do I reflect this change in SS benefits when we turn 70?

Roth Conversions Worksheet

• Columns R – V – Taxes By Tax Bracket

Do these or some other columns reflect the Five Tax Increases Effective In 2013 For Upper Income Earners
http://www.boston.com/business/personal ... ncrea.html, specifically:

o 0.9% Medicare surtax on wages
o 3.8% Medicare tax applied to net income.
o Phase out of itemized deductions and personal exemptions
o Increase in qualifying dividend and capital gains tax rate
o Increase in the federal income tax rate

Roth Case Worksheet

• I went through this worksheet in detail to understand the calculations. The only fields (rows) I did not understand were fields (well actually rows) F14, F15 and F18. What is the reasoning behind these calculations? It looks like values are being averaged for some reason??

Thanks! I will continue to look at your spreadsheet with the goals of understand it and then using it for my conversion decision.

[BigFoot48]

Glad you are finding it useful and thank you for the questions.

• Row 47 - 4. Special tIRA Withdrawals
Once advantage of a Roth account is the ability to make withdrawals tax-free, and this is in the model. But in order to keep the symmetry of the two models for comparison, I also give the user the ability to make equal withdrawals from their IRA in the same years, and thus provide a comparative financial result of the same potential action, like buying an RV.

If you do not plan any Roth withdrawals then this feature won't be needed.

• Rows 26 and 27 - Social Security Benefits
For more sophisticated SS benefit plans, I recommend putting in your initial choices for the calculation, then manually change the values in each model (lines 19 and 20) in the appropriate future years to the SS benefits you estimate you will receive in those years. The model will then escalate those values for remaining years. Just be sure to use the same escalation factors as the model does, and remember that formulas in those change years have been replaced by values.

• Columns R – V – Taxes By Tax Bracket
The special tax factors for high income individuals are not in the model. If there's enough demand for this I might be able to include some or all of them in a future update.

• It looks like values are being averaged for some reason??
In order to calculate yearly income for each account, I average the beginning and ending balances, and then apply in revenue factor. It's a bit crude, but as it's being done the same way in both models and then compared, some of this crudeness gets to be minimized. The best way to test the impact of this is to put in a simple starting account balance, with no deductions, and see if its calculating reasonably well for your situation.

These are my brief, quick answers. If you have more questions let me know.

[BradFreeMan]

Hi!
I am so grateful for your spreadsheet that, after many years of reading this forum, I am inspired to finally contribute. I was in the process of trying to develop a spreadsheet to analyze my personal case for Roth conversions when I stumbled upon yours. Being a retired software engineer, I'd like to avoid the hazards of re-inventing the wheel, so I have adopted you spreadsheet for my analysis.

I have a question about line #31, IRA Withdrawals Pre/Post RMD. My wife and I have different ages and the formula for line 31 is different in the Roth and Base cases. In the Roth case, the RMD age comes from line 4, spouse's age; in the Base case, the RMD age comes from line 3. Was this intentional? I'm using version 13.0 of the spreadsheet and noticed this when trying to make the Base Case match the Roth Conversion case by setting Roth Conversions to 0.

Thanks again for your fine effort and your willingness to share.

[BigFoot48]

Thank you Brad. This is indeed an error and I appreciate you reporting it although I'm sorry you were subjected to it. I have fixed it and will provide a revised version by the morning, as I am now scanning the model for any similar mistakes in use of ages, as I have seen it once before but thought it was eradicated.

Although I test the model as much as I can, by starting with minimal data, and adding factor after factor, I now realize that I have tended to have "self" and "spouse" at the same age, partially because the spouse age is not used (or supposed to be used!) for many calculations, and my wife and I are the same age so I had a prejudice toward that. Even the sample data that comes with the model has both the same age, which I will now change.

Thanks again and I'm happy you have found it useful - or will when fixed. As usual, I welcome all comments, suggestions, and error reports.

[zip605]

Great tool (v13.0). Just what I was looking for.

Is there some support in the tool to have the Roth conversion cost (tax) calculated as part of the tIRA conversion/withdrawal. In other words, the tax is deducted from the tIRA funds, and not from the taxable account(s)? What if the taxable accounts value is minimal? I think the tool just posts the cost as a negative against the taxable account (BASE tab, cells E48 F48...) If the account doesn't have sufficient funds, shouldn't something be flagged as a NEGATIVE?

I know the README says the model will not automatically adjust for this, but maybe it could flag it. Maybe a switch could be added to force the model to somehow account for the tax consequences from the tIRA withdrawal.

If I understand how this would work, each year I would need to withdraw enough from the tIRA to cover both the Roth portion and the tax portion. This withdrawal value is not constant, but depends on the amount desired to actually end up in the Roth. What I don't seem to grasp is how to get the Roth account to not include the variable amount that should go toward taxes.

Well, I hope this makes sense. In any case, the model is by far the best tool I have come across for conversion decisions.

Thanks for sharing.

zip60

[BigFoot48]

A new version of the model is now available, 13.1: https://www.dropbox.com/s/hp8nmddqxmz64zj/Retiree%20Roth%20Conversion%20Model%20v13.1.xls

This corrects an error found by BradFreeMan that had the wrong age being used to determine the start of IRA withdrawals on the Roth conversion model, causing a distortion in the comparison of the two. Unless users have the same age for self and spouse, I recommend downloading and re-running your analysis.