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.
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.
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).
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):
Likewise, for each Life Event up to three monthly expense values can be entered (in today dollars):
- Social Security
- 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.