Retiree Portfolio Model

From Bogleheads
Jump to: navigation, search

The Retiree Portfolio Model is a downloadable Excel spreadsheet created by a retiree for retirees. It models the most common financial aspects of a retiree and their spouse's lives, including pensions, Social Security benefits, living expenses, IRA Required Minimum Distributions, purchase of an annuity, sale of a house, and many other items including Federal and state income taxes. All of this data is used to create a model of their accounts over a period of 1 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.

Downloading and support

The first post in this Bogleheads® forum topic: Retiree Portfolio Model contains the link for downloading the spreadsheet. Also use this topic to receive assistance or ask questions.

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.

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, Expenses and IRA Withdrawals, Special 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 example data for a married couple, illustrating results and how Roth conversions or alternative SS starting ages, will impact portfolio values. 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 Full and Base 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
  • Full Case is the model of the input data which optionally includes Roth conversion and withdrawal amounts and alternative Social Security benefits. If these features are not used or turned off, the Full case is identical to the Base case.
  • Base Case is the model of the user's input data. Using starting account balances and input factors for income, expenses, earnings rates, and typical changes that occur during retirement, to calculate yearly income, expenses, taxes and portfolio balances during the selected modeling period. 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.
  • Comparison shows the differences between the Full and Base 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.
  • ORP Comparison is a page allowing a user to compare the results of the RPM model to The Optimal Retirement Planner.

Screenshots

Setup: Setup 16.png

Base case: Base Case 16.png

Results: Results 16.png

Graphs: Graphs 16.png

See also

External links