Retiree Portfolio Model

From Bogleheads
Revision as of 13:04, 4 August 2014 by BigFoot48 (talk | contribs) (Updates)
Jump to navigation Jump to search

The Retiree Portfolio Model is a downloadable Excel spreadsheet which models portfolio accounts, retirement income, expenses and taxes over a selectable period of up to 40 years. A feature of this model allows the user to compare their normal portfolio results with one that includes alternative choices, such as doing Roth IRA conversions, choosing alternative Social Security starting ages and benefits, or buying a Single Premium Immediate Annuity. The model was developed by forum member BigFoot48.

Methodology

The user inputs ages, account balances, earnings rates, income sources, first year expense and escalation rate, and some common retirement events, such as inheritances, large expenditures, and the passing of a spouse. The model calculates the after-tax results of these amounts and choices and provides summarizes of results, including graphs, and the complete details. The alternative events can be selected to be included in a second model, otherwise identical to the default one, for a quick and easy comparison and analysis of these options.

A brief overview of the individual tabs and worksheets:

  • Readme contains general information about the model, including information on Roth conversions, with links to some external sources of additional data, along with an quick start guide, links to other retirement forecasting models, and details of the history of changes to the model.
  • Setup is where the user's data is entered that is used by the model to calculate future results. Major sections include Age and Year Factors, Portfolio Balance, Income including two alternative Social Security benefit starting ages and amounts and an immediate annuity, including the File and Suspend strategy, Expenses and IRA Withdrawals, Lump Sum Events (inheritances, home sales,, etc.), Income Taxes (joint or single), future Federal Tax Rate Changes, and Roth Conversions and Withdrawals. The model comes with a hypothetical couples' example data, illustrating results and the impact of the special items. A narrative of this data is also provided.
  • Summary shows key elements of the Full and Base cases.
  • Results contains key comparisons of data from the Base and Compare cases, along with graphs of some 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. Detailed data on Roth conversions and alternative Social Security benefits is also provided. A section summarizing key data along with all the selected input factors is provided for storing results
  • Base Case is where the calculations for the forecast of the portfolio over the selected period without Roth conversions and with the normal Social Security benefits are 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.
  • Full Case is otherwise identical to the Base Case, except it includes the calculated or input Roth conversion and withdrawal amounts, alternative Social Security benefit starting years and amounts, and other alternative Setup amounts such as traditional IRA withdrawals.
  • Comparison shows the differences between the Base and Compare cases, with all rows shown.
  • 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.

The spreadsheet model

This model was created using Excel 2003 and is compatible with LibreOffice Calc and possibly Google Sheets. All formulas and results are completely viewable and can be unprotected, allowing user customization.

The current version of the spreadsheet can be downloaded from this forum thread: Retiree Portfolio Model

Screenshots

Setup: Retiree Portfolio Model Setup.jpeg

Base case: RPM Base.png

Results: RPM Results.png

See also

External links