Retiree Portfolio Model

 is a spreadsheet developed by forum member BigFoot48 for the purpose of forecasting the impact on a user's portfolio from converting funds held in a traditional IRA to a Roth IRA. A secondary feature can be selected to forecast the impact of alternative Social Security benefit starting years.

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 them (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 of 1 to 40 years.

Roth conversion spreadsheet
The spreadsheet can be downloaded from the link on 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 Ages and Year Factors, Portfolio Balance, Income, Expenses and Withdrawals, Lump Sum Events, Income Taxes, Federal Tax Rate Changes, amd 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 input, alternative SS benefit starting years and amounts.includes the returns of various funds from 1985-2013 (to include many of the funds that were started in 1984)
 * 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.