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