This is a portfolio forecasting spreadsheet model designed for a person or couple nearing or in retirement. Boglehead Wiki article: http://www.bogleheads.org/wiki/Retiree_Portfolio_Model
The current version of the model can be downloaded from Dropbox via this link: Download the current version 15.1 - 2/4/2015: https://www.dropbox.com/s/z441l17guxnh4 ... 1.xls?dl=0
Changes (some of these were added to 15.0 in the last few weeks):
> Fixed SS Restricted Application logic to allow spousal benefits at 70. NOTE: This function still under re-development to allow total flexibility.
> Fixed Protect Sheet setting to allow use of sheet format macros without unprotecting the page.
> Fixed spousal benefits ending date which was not consistent nor based on other person's end date.
> Improved SS benefit input error messages. Added start years to spousal and special amounts.
> Fixed Clear Entries function to skip locked calculated cells that were creating a halt to the macro process and an error message.
Version 15.0 changes:
> 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. Note: some macros won't work in LibeOffice Calc.
> 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.
> 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.
> 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.
> Example data story moved to cell comments in far left section of green section title labels.
> 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 a 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. (Note: some macros will not work using Calc and selecting them will result in an error and the macro editor opening. If this happens, close the editor and don't use that macro.)
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!