The current version of the model can be downloaded from Dropbox using this link: Download the current version 14.1 - 3/6/2014: https://www.dropbox.com/s/ozx2eg6o66pljgf/Retiree%20Roth%20Conversion%20Model%20v14.1.xlsThe 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.)
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 an Excel 2003 model (following the advice of many to "just create a 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 situation. After creating and using it, I thought I could get some beta testers by adding some instructions and user-friendly features and sharing it, and that has worked out well as users have found some errors and have made useful suggestions for improving it. So I have continued to provide it as a service to my fellow Bogleheads, and others that may stumble upon it.
The model I'm providing is my personal model that I use periodically, entering my own portfolio, estimate of earnings, expenses and anticipated changes we think will happen, to look at the conversion decision. (I've got another five years until RMD and the related taxes kick in and any conversion benefit goes away for good.) In addition to the conversion analysis, and since the model includes Social Security benefits as an income source, I did add one feature that I can't use - a way to quantify the "when should I start Social Security benefits" decision. (We started at 62.) That feature also quantifies the SS decision process, and could be useful to many.
So, as my personal model, use it at your own risk. It may help you or it may not. Your situation may be more complex and the model be of only limited value. Your data may cause it to make bad calculations. It may indicate a conversion advantage because you used a 12% earnings rate. Lots of things can be indicated from the data entered, so use it as a start in your conversion decision, or SS benefits decision, and when in doubt, create a model! (Above updated Dec 2013)
What will this model do?
> Quantify the impact on a portfolio balances from doing Roth conversions, providing information that might help determine if it's worth doing.
> Calculate yearly results, including earnings, expenses, taxes, inheritances, and asset sales in a portfolio for a limited time period, 1 to 40 years.
> Test impact of common events, like the death of a spouse, receiving an inheritance, selling a home, buying an RV, etc., or experiencing a large tax rate increase.
> Show how conversions will impact average and marginal taxes each year, allowing for adjusting conversion amounts to achieve the best tax outcome.
> Provide a Federal tax calculation using simplified input factors that includes determining taxable Social Security benefits, but excludes many tax provisions for higher income taxpayers.
> Use two almost identical models, with the primary difference being Roth conversion and expenditure amounts, to focus on the real impact of conversions.
> Provide a free, unprotected, fully transparent Roth conversion model for those needing such a tool, but unable or not interested in preparing their own.
> Bonus: Compare two different options for starting Social Security benefits using different starting ages, e.g. spousal at 66, self at 70, or spouse at 62, self at 66, etc.
What will this model won't do?
> Accurately predict the future. All input factors are under the user's control and the future will likely provide different results than the model calculates.
> Recognize or adjust for non-deductible (after-tax) contributions to your IRA. All IRAs are assumed to be
"tax-deductible" when funded. All IRA withdrawals will be taxed as ordinary income in this model.
> Calculate pre-59 1/2 IRA withdrawal penalties, or the penalty on withdrawing Roth conversion amounts within five years of conversion.
> Calculate the impact of conversions on taxes applicable to high income taxpayers, like the Medicare surtax and higher premium, the phase-out of deductions and personal exemptions, higher qualified dividend and cap gain rates, tax gain harvesting, the AMT, various tax credits, etc.
> Automatically correct for all negative account balances. The user needs to review the yearly schedule and make changes to keep accounts out of the red.
Please note this is a simple model, designed for married or single retirees, with limited selections for asset, earnings, revenue and expense items. While I believe it is operating correctly as designed, it may not be, and users may find errors or unusual results as a result of unusual data sets, 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, and let me know of any problems found or ideas for improving it!