GBIC >> Retirement >> Retirement Financial Model

Retirement Financial Model
Managing finances is perhaps the most critical activity that must be pursued during retirement. Many retirees create a model which describes their projections of income and expenses and which may be used throughout retirement to monitor their net worth and to guide their need to produce income or to adjust their expenses to stay within their retirement financial plan.

This page provides an Excel spreadsheet to help retirees with these calculations. The first sheet is an itemized list of expenses that most retirees will see. The sheet is pre-loaded with sample expenses for a $6K monthly budget. The second sheet allows the retiree to load expense and income estimates and to calculate net worth throughout the retirement period, including the ability to vary interest, taxes and inflation over time.

Expenses Model Net Worth Model Download (Ver4.0)

Return to top of document

Retirement Expenses Model

Even though retiree expenses will vary greatly from person to person, the basic expense items are very similar from person to person. The big difference tends to be in how much a retiree spends on a particular expense item. Starting with a an expense model with pre-defined budgetary items such as the one provided below can provide two key benefits.

First, a pre-defined model contains a list of expense items which has been thought out in detail. For retiree's just beginning to analyze their expenses a thorough listing of potential expenses items will help the retiree make sure that no significant expense items have been missed.

Second, a pre-defined model that contains estimates for each of the expense items can be used by the retiree to compare against his/her own cost estimates. If the numbers in the model are higher or lower than the retiree's expectations, justification can be worked out to the retiree's satisfaction before committing to his/her own budget estimates.

The Retirement Expenses Model below is already filled out with estimates for a $6000 budget (pre-tax) at the start of retirement. It also suggests how those expenses might vary over time as various events occur during retirement (see the discussion on Life Events elsewhere on this site).

Here's an image of the Retirement Expenses Model (click on the image to view a full size image).

The tax line item at the top of the spreadsheet includes both State and Federal taxes. State taxes vary greatly so special attention is needed for that line item. Likewise, insurance costs also vary greatly and special attention should be paid to getting valid estimates for the state in which the retiree lives.

Remember - you're supposed to fill in your own estimated expenses. The numbers already in the template are there just to show one allocation of expenses that would meet the total budget, which in this example is $6000 per month.

The "Show Then$" button exposes additional columns which calculate the expenses for the age of the Life Event, taking into account inflation starting from the Current Age as entered on the spreadsheet.

The Inflation column allows you to apply different inflation rates to each expense item. This feature is particularly useful in two areas:

House Payment
If your house payment is for a fixed interest then inflation for it would be zero.
Medical
Unfortunately medical expenses are much higher than the overall inflation rate of the economy. Recent inflation for medical expenses have been as high at 15% and are expected to continue well above the general inflation rate for several years.

Return to top of document

Retirement Net Worth Model

All retirement models will consist of pretty much the same basic elements - income and expenses, along with some other basic factors such as investment rates of return, taxes, and inflation. A model typically combines the various elements to estimate the retiree's net worth over time. Such models are valuable in that the retiree can adjust the various parameters to help make financial and lifestyle decisions throughout retirement. A model can be used not only to make the decision to retire, but also to help monitor finances throughout retirement.

For example, retirees can evaluate how raising or lowering their standard of living (expenses) affects the ability of their funds to last throughout retirement. A model will also allow a retiree to can evaluate the impact of a state's tax rate or the impact of inflation rates on retirement funding. Or a model can allow a retiree to calculate how much part-time income is needed to maintain a particular standard of living.

Many online retirement financial models are very simple. They assume a net worth at the start of retirement and calculate how long the net worth will last at specified levels of yearly expenses. These models are excellent for pre-retirement estimates of the net worth a retiree might need during retirement but at some point, as retirement approaches a more detailed model is needed to help manage finances.

The Retirement Net Worth Model provided here allows the retiree to adjust income, expenses, and other assumptions for each year (or block of years) of retirement. It also supports the inclusion of lump sum events such as sale of a property (income) or a daughter's wedding (expenses), again on a yearly basis. An image of the Retirement Net Worth Model is provided next, followed by discussion of the various sections of the model (click on the image to view a full size image).

Basic Assumptions
As was discussed in the Life Events section of this site, finances are seldom constant throughout retirement. Interest, tax, and inflation rates may vary over time, usually as a result of a particular event (such as the start of Social Security income or a decision by the retiree to quit part-time work). After a Life Event, income and expenses remain generally constant until the next Life Event.

Additionally, some income or expenses occur in lump sum events throughout retirement - such as the sale of a house or the purchase of a boat.

The Net Worth model provided here supports both Life Events (income/expenses constant over a fixed number of years) as well as lump sum events (income/expenses which happen once in a specific year).

The model allows the entry of some basic information (upper left corner of the model):

Current age
Inflation (pre-retirement) expected up to the age of retirement
Inflation (alternate pre-retirement) covering selected expenses up to the age of retirement
Net Worth at the start of retirement

The model allows entry of up to seven Life Events, with the following parameters:

Description of the event (first event must be Retirement)
Age at which the Life Event begins
Inflation Rate
Interest Rate

Also for each Life Event up to three monthly income values can be entered (in today dollars):

Social Security
Work
Other

Likewise, for each Life Event up to three monthly expense values can be entered (in today dollars):

Standard expenses
Alternate expenses, which will have the alternate inflation rate applied
Alternate Inflation Rate to be applied to expenses (such as medical) to which the standard inflation rates will not apply

The model also allows lump sum events to be entered. For each lump sum, the following variables can be entered:

Description of the expense/income
Age at which the event takes places (model assumes the start of the year)
Amount of the expense/income

All values can be entered manually, but to help make quick changes the model includes several sets of buttons labelled + or -. The buttons above the inflation and interest entries will change the values in that column by 0.5%. The buttons near the Net Worth @ Retirement will change the value by $50,000. Buttons near expenses will change the table values by $100.

Finally, the output of the model (net worth/income/expenses for each year, in then dollars), is plotted and placed over the data used to make the plot. You can hide/show the data by pressing the button next to the plot. You can also hide/show additional interim data used by the spreadsheet to generate the results and chart.