We provide two simple Excel spreadsheets in this website for the purpose of helping you develop your annual spending budget in retirement as part of what we call “The Actuarial Approach.” The spreadsheets are located in the “Articles/Spreadsheet” section. The first one (Excluding Social Security V 2.0) generally applies if you are currently receiving Social Security benefits and develops a total spending amount (from accumulated savings and any immediate or deferred annuity or pension income for which you may be eligible). To obtain your total spending budget for the year, you take the result from this spreadsheet (or a smoothed result based on our recommended smoothing algorithm or some other smoothing algorithm) and add income you expect to receive from other sources in the next year such as your Social Security benefit, other inflation-indexed annuity/pension benefits, expected earnings from employment, etc.
The second spreadsheet (Social Security Bridge) applies if you are not currently receiving Social Security benefits but would like to coordinate your current retirement spending with Social Security benefits you expect to receive in the future.
The Total Spendable Amount shown in the Results section of the Input tab is the answer to the mathematic problem, "What total spendable amount (from accumulated savings and annuities) may be spent in the current year, to be increased each subsequent year by a constant percentage so that accumulated assets will exactly equal the amount desired to be left to heirs at the end of the expected payout period?"
Please be patient when downloading the Excel spreadsheets. Sometimes it can take a while. If you are requested to provide a password, simply click "cancel" and the spreadsheet should appear.
If you are having trouble seeing all the cells in the spreadsheet once it has downloaded (or you want to see a year-by-year runout of your accumulated savings based on the input items), be sure to maximize the spreadsheet window (by clicking on the “maximize” box in the top right-hand corner of the spreadsheet).
The Excluding Social Security V 2.0 has three tabs in the spreadsheet. The Social Security Bridge spreadsheet only has two tabs. As noted above, if you can’t see the tabs at the bottom of the spreadsheets, you need to maximize the spreadsheet window. After you do this, you should see the tabs labeled, “Input”, “Runout” and “Inflation-adjusted Runout.” The Social Security Bridge spreadsheet does not have an inflation-adjusted runout tab.
The input sheet allows you to enter your data and assumptions used in the calculations. Once you enter these items, results are shown in the bottom portion of the spreadsheet.
The Runout tab shows results in nominal (non-inflation-indexed) dollars. The first year of the Runout spreadsheet shows the beginning of year balance of accumulated savings that you entered, the amount to be withdrawn (payment) from accumulated savings for that year, expected investment return (interest) on the beginning of year balance, any immediate life annuity income for the year, the total spendable amount (from accumulated savings and pension/annuity) for the year and the accumulated savings withdrawal as a percentage of the beginning of year balance. Similar amounts are shown for each subsequent year based on the expected payout period you entered in the input sheet. The run-out is based on the assumptions discussed at the top of the spreadsheet. If you want, you can check to see that the end of year accumulated savings is equal to the beginning of year accumulated savings minus the amount withdrawn during the year plus the expected investment return for the year. Note that the Runout spreadsheet is somewhat self-checking. At the end of the expected payout period you entered, the end of year balance for that year should exactly match the amount you entered in the input spreadsheet for the “desired amount of savings to be left at death.”
The inflation-adjusted Runout sheet (in Excluding Social Security V 2.0 only) adjusts the expected year by year results shown in the Runout sheet for inflation based on the assumption for inflation that you input as the last input item on the input sheet. If you input the same rate for “annual desired increase in payments” and “expected annual rate of inflation”, the total inflation-adjusted spendable amount shown in Column L should remain the same throughout the expected payment period.
Below are screen shots from the Excluding Social Security V 2.0 spreadsheet showing the input, and its two Runout sheets. The data and assumptions used are shown in the Input sheet.
As discussed above, the Runout sheet for this example shows an end of year accumulated savings balance of $10,000 at the end of the 30th year (the amount inputted for desired savings remaining at death), and the Inflation-Adjusted Runout sheet shows a total inflation adjusted spending amount that remains constant throughout the 30-year expected payment period of $40,259 per year.
Input
(click to enlarge)
Runout
(click to enlarge)
Inflation-adjusted Runout
 |
| (click to enlarge) |
