Retiree Portfolio Model
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 nearly all of the user's data is entered that is used by to calculate and model the future results. Major sections include Age and Year Factors, Portfolio Balances, 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 set for a married couple, illustrating typical results and how Roth conversions, or alternative SS starting ages, will impact portfolio results. A narrative of the example data is also provided.
- Summary shows key elements of the Full and Base cases.
- Results contains key comparisons of data from the default (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
- Details is the default yearly model (full case) of the input data, which optionally includes Roth conversion and withdrawal amounts, and alternative Social Security benefits. Using starting account balances and input factors for income, expenses, earnings rates, and typical changes that occur during retirement, it will 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.
- Base Case is the model of the user's input data without Roth conversions or the alternative Social Security benefits option. It is used to provide an instant analysis of doing Roth Conversions and/or choosing alternative SS benefits. If these features are not used in the default case, this portfolio results will be identical to the default (full) case. A comparison section shows the differences between the default and base cases is also provided on this page.
- 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:
Base case:
Results:
Graphs:
See also
External links
- Roth IRA conversion rules, on Wikipedia
| Planning |  | |
|---|---|---|
| Retirement plans | ||
| Social Security | ||
| Withdrawal rates | ||
| Withdrawal strategies | ||