Retiree Portfolio Model

 is a spreadsheet developed by forum member BigFoot48 which predicts the impact on a portfolio by converting funds held in a traditional IRA to a Roth IRA. Another feature analyzes the impact of alternative starting years for Social Security benefits.

Use this spreadsheet to determine if a Roth IRA conversion may be worthwhile for your personal situation.

Yearly results are provided (such as income, expenses, taxes, inheritances, and asset sales over a selectable time period) for both doing conversion, and not doing the conversion, so an easy comparison can be made.

Methodology
The spreadsheet analyzes the impact of conversions by comparing two models of the user's portfolio; a Base Case without conversions, and a Roth Case with conversions (or with the alternative SS benefit data).

The portfolio is modeled from data supplied by the user; including beginning balances in various asset accounts, earning rates, income, expenses, special events, and tax status. These amounts are then used to calculate after-tax net cash flow and resulting portfolio changes over a user selected period from 1 to 40 years.

Roth conversion spreadsheet
The spreadsheet can be downloaded from this forum thread: Retiree Roth Conversion Decision Model

A brief overview of the individual tabs and worksheets:


 * Readme contains general information about Roth conversions, with links to some external sources of additional data, along with a description of the model, how to use it, links to other retirement forecasting models, and details of the history of changes to the model.
 * Setup and Input is where the user's data is entered that is used by the model to forecast future results. Major sections include Age and Year Factors, Portfolio Balance, Income, Expenses and Withdrawals, Lump Sum Events, Income Taxes, Federal Tax Rate Changes, and SS Benefits Comparison Test.
 * Roth Conversions is where the user inputs their yearly Roth conversion amounts, aided by instant feedback on how they impact Federal taxes, allowing the user to adjust conversions to maximize the use of lower tax brackets.
 * Results contains key comparisons of data from the Base and Roth cases, along with graphs of the data. A Summary shows the portfolio balance in both cases at the end of the selected period, along with important factors for each case such as Total Income after Taxes, Federal Taxes and IRA Required Minimum Distribution amounts. Yearly amounts of key data from the cases is also shown. Data on the SS comparison is also provided.
 * Base Case is the forecast of the portfolio over the selected period without Roth conversions and with the input Social Security benefit starting years and amounts. Major sections include Account Balances, Account Income and Earnings, Account Changes, Expenses, Tax Calculations, Summary of Account Changes, and Account Balances at End of Year.
 * Roth Case is otherwise identical to the Base Case, except it includes the input Roth conversion and expenditure amounts, and if selected, alternative SS benefit starting years and amounts.
 * Tax Tables contains the current year tax rates and brackets, exemptions and deductions, for filing jointly or single. Future year tax rates and brackets are shown, calculated using an average escalation factor. Also included in this tab are other data tables, such as Life Expectancy, used in the modeling process.