Retiree Portfolio Model

 is a spreadsheet developed by forum member BigFoot48 which models the impact on a portfolio of converting funds held in a traditional IRA to a Roth IRA. An additional feature analyzes the impact of starting Social Security benefits at different ages.

Use this spreadsheet to determine if a Roth IRA conversion may be worthwhile for your personal situation or how delaying, or advancing Social Security benefits may impact your income and portfolio.

Yearly results are provided (such as income, expenses, taxes, inheritances, and asset sales over a selectable time period) for both not doing conversions and doing conversions, and/or starting Social Security benefits at one age versus another. This presentation method makes comparisons of alternatives very easy to do.

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 Social Security 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 Social Security Benefits Comparison Test.
 * Roth Conversions is where the user inputs their yearly Roth conversion and withdrawal amounts, aided by instant feedback on how the conversions impact Federal taxes. This allows 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 Social Security comparison is also provided.
 * Base Case is where the calculations for the forecast of the portfolio over the selected period without Roth conversions is done. 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 withdrawal amounts, and if selected, alternative Social Security 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.