The current version of the model can be downloaded from Dropbox using this link: Download the current version 14.9 - 10/9/2014: https://www.dropbox.com/s/h2v6bzutq3frc ... 9.xls?dl=0Updated 10/12: Fixed age checking rule in SS section that didn't allow values over 70. This was fixed and anyone already receiving benefits from 62 and up can include them. Note "Benefits have started" should be set to "y" if 70 or older. (Many thanks to BH earlyout for reporting this to me.)
Oct 15: Fixed false first year RMD for traditional IRA 2 that appeared if IRA2 amount was $0 and RMD amount if that was the RMD start year for IRA 2, i.e. age = 71.
Changes in this version:
Several corrections to the Social Security benefits calculations were made.
1) The end age for spouse benefits was using the spouse's age rather than the "you" age and resulted in one extra year of spouse benefits than intended. Using the provided example data, this correction reduced SS benefits by about $45,000 at age 85 and reduced both case's final portfolio balances by about $58,000. Users may see similar changes depending upon the ages of "you" and "spouse".
2) The formula used to calculate spousal benefits taken before FRA was incorrect, overstating the benefit. In the example data, if the supplied spousal benefit amount is zero and the automatic calculation of benefits used, age 62 benefits were $12,200 (using the formula of "Your" FRA x 50% x 75% + 2.5% COLA) but the correct amount is $11,400 (using "Your" FRA x 35% + 2.5% COLA).
3) The alternative SS benefit option was not including automated spousal benefits in yearly forecasts, potentially underestimating income when this option was used.
Improved and updated menus, instructions and cell help comments.
Note the model has been renamed from Retiree Roth Conversion Decision Model to Retiree Portfolio Model (RPM).
Some user comments: "Great tool - excellent - a gem - useful tool - truly wonderful spreadsheet - very good tool - Two thumbs up - Excellent work - amazing work - a very useful tool - Great tool - by far the best tool I have come across for conversion decisions."The Excel 2003 model can used with the free program LibreOffice Calc [recommended!], available here: http://www.libreoffice.org/default/
and Google Sheets and Excel Online.
Boglehead Wiki article: http://www.bogleheads.org/wiki/Retiree_Portfolio_Model
Background (original 2012 post):
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!