This is a retirement financial forecasting model.
The current version of the model can be downloaded from Dropbox via this link: Download the current version 15.0 - 1/1/2015: https://www.dropbox.com/s/3z9sv0v1hvudp ... 0.xls?dl=0This was version updated Jan 5 to fix a Restrict Application glitch as discussed in a Jan 5 2014 post, and a Sheet Protection setting that didn't allow the sheet formatting macros to work without unprotecting the sheet. If either of these may affect you, re-download it. UPDATE: The actual spousal benefit amount using the Restricted Application option may not be correct depending on ages entered. As of Jan 12 still working on this.
Changes from the prior version include:
> Added macros. Macros are clickable buttons that perform various housekeeping tasks, such as clearing all setup data, copying and pasting results for saving, navigating, and showing or hiding gridlines, headers and rows. The model can be used without activating macros.
> Added the Social Security Restricted Application scenario as part of an update to the SS calculation section.
> Example case data has been expanded to use every user input item to increase the possibly of revealing programming errors. A macro is now available to clear nearly all input items for a clean start.
> 2015 tax rates and factors are used. Changed default tax escalation rate to 2.5% from 2.7% to better reflect recent historic and average since the last tax rate change in 2003.
> Added to the Setup page a beta test of a Cash Flow diagram (suggested by BH keith simpson).
> Example data story moved to cell comments in far left section of green section title labels.
> Tied the end of SS benefits due to a spouse passing, and the change to the single tax rate, to the Age section entry to eliminate the need to multiple post this entry.
> Add Restricted Application strategy to SS benefits calculation. Also insured largest SS benefit is used by default after a spouse's passing. Fixed issue with File & Suspend that might have affected the calculation for different aged spouses.
> Documented that annual percent changes in retirement income and IRA withdrawals can either increase or decrease the result.
> Added areas on the right side of the Setup page for the user to make notes.
> Added print areas to each page that allows printing of the primary data on the page. Adjust sizing etc. for your own printer.
> Complete review and update of Setup page cell help comments. Improved error messages. Example story moved to left-most cell in each section's title bar.Download the previous version 14.93 - 10/24/2014: https://www.dropbox.com/s/3vk9ag0crqalf ... 3.xls?dl=0
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!