Retirement Nest Egg Model
I use a fifteen column spreadsheet as a simple model for my Retirement
Nest Egg. I use MicroSoft Excel, but other spreadsheet programs will work.
Not too many years ago, I did the same thing with pencil and paper -- it
took a lot longer.
When I started this model four years ago, it was all looking forward.
Over time I have replaced the projected values with actual values to show
the history and experience as the quarters have gone by. This has served
as a check on my estimates.
My model is done on calendar quarters. You can do it on months or years
if you would rather. Note that the point of view of the spreadsheet is to
show values at the end of each period.
You can download an Excel 7.0 template file of the model if you wish.
Click on nestegg.xls
and then save the file on your hard disk. The numbers are fictional, so
replace them with your own. I use the first three rows as headings and parameters.
The columns in this spreadsheet are the following:
- Col A: Qtr Date = the previous Qtr Date value + 365.25/4. Set the first
Qtr Date value to mid-month at the end of the quarter of interest (such
as 9/15/98). Copy the formula down to the following rows to form the spreadsheet.
I suggest you project for the next 30 years. Format this column to show
month-year (as Mar-98).
- Col B: Age = INT((Qtr Date value - $Birthdate)/365.25), which computes
your age at the end of each quarter. Put your birthdate into a free cell
at the top (such as 3/18/37 into cell B2). Reference it in the formula
as $B$2 so it remains at a constant place as you copy the formula down
through other quarters). Format the column to show integers.
- Col C: Inflation Factor = previous Inflation Factor * (1 + $CPI/4),
which computes the multiplier factor to use when adjusting for inflation
in future quarters. Put the CPI (Consumer Price Index or annual percentage
inflation rate) into a free cell (such as 4% into cell C2). Reference it
in the formula as $C$2 so it remains at a constaqnt place as you copy the
formula down through other quarters). This is used in the model to adjust
the Social Security income and the Living Expenses. You may want to vary
the percentage in different quarters over time and to set it to an actual
value as time passes and you update the model. Format the column as a number
to three decimal places.
- Col D: Nest Egg = the previous Nest Egg value + Net Change. Set the
first Nest Egg value to your estimated (or actual) Nest Egg at the end
of the first Qtr Date. Format the column as currency with zero decimal
- Col E: Rate-of-Return = annual percentage growth expected from Nest
Egg quarter by quarter, such as 7% (the calculations divide by 4 to adjust
to quarters). You may want to vary the percentage in different quarters
over time and to set it to an actual value as time passes and you update
the model. Format the column as a percentage.
- Col F: Appreciation = previous quarter Nest Egg value * (1 + Rate-of-Return/4).
This is the amount you expect your Nest Egg to grow in the quarter from
interest, dividends, and capital growth. Be conservative, a Rate-of-Return
of 3% over the inflation rate is a typical return in a well-managed account.
As time passes you can replace this calculation with the actual appreciation
at the end of each quarter. Format the column as currency with zero decimal
- Col G: Untaxed Income = gross income for the quarter from untaxed sources,
including gifts and inheritance. For instance, my kids are paying off loans
I made to them in the past. Format the column as currency with zero decimal
- Col H: Earned Income = gross taxable earned income (salary) expected
each quarter. Remember, if you are working, Earned Income for the quarter
is 3 times your monthly salary. Cashing in a life insurance policy may
produce some earned income. Format the column as currency with zero decimal
- Col I: Soc Security Income = previous quarter's Soc Security Income.
Every four quarters multiply it by a factor = (1 + Rate-of-Inflation/8).
Store the amount you expect to receive from Social Security in the first
quarter you expect to receive it; increase it by your spouse's contribution
in the quarter your spouse starts to receive Social Security. The division
by 8 assumes the COLA allowed by Congress will be one half of the inflation
rate. Format the column as currency with zero decimal places.
- Col J: Taxable Income = Earned Income + 0.2*Appreciation. This very
simple assumption is that 20% of the Appreciation from investments is taxable
in the same year. It does not provide for any impact of earned income on
Social Security. When you start withdrawing money from a tax-deferred account,
all of the withdrawal will be taxable. You may want to develop your own
formula for calculating Taxable Income that better fits your case. As time
passes, enter the actual value into each quarter. Format the column as
currency with zero decimal places.
- Col K: Taxes = Tax Rate * Taxable Income. The Tax Rate value is the
actual percentage of Taxes paid (State and Federal) to Taxable Income,
not the highest point you reached in the tax rate table. Spreading Taxes
over all four quarters is inaccurate unless you are subject to withholding
and are faithfully paying estimated taxes, but it is not a significant
amount and will be adjusted when you insert the actual amounts paid. Format
the column as currency with zero decimal places.
- Col L: Living Expenses = your base quarterly Living Expense * Inflation
Factor. You should develop a budget you use for your base quarterly Living
Expense using my previous outline. I suggest you do a good job of accounting
so you can enter the actual value at the end of each quarter. If you see
a divergence from projections as you enter actual values into this column,
you may want to check if it is caused by using the wrong Inflation Factor
or if your lifestyle has changed. Format the column as currency with zero
- Col M: Big Time Expenses = costs of upgrades and new equipment when
you expect they will occur. This will be when you purchase your next rig
in 7 to 10 years. Major repairs go into this column as well. Do you need
a laptop computer? Budget for it here. This column can be used for smaller
items, but you will do better if you try to budget all small-ticket items
in your Living Expenses. Format the column as currency with zero decimal
- Col N: Net Change = Appreciation + Untaxed Income + Earned Income +
Social Security - Taxes - Living Expenses - Big Time Expenses. This tells
you about your cash flow for each quarter. If it is positive, your Nest
Egg increased; if it is negative, you had to withdraw capital from your
Nest Egg. Format the column as currency with zero decimal places.
When you have the first few rows of the model started with all the formulas
in place, you can then copy the last row of the model down for another 100
rows or so to extend the projections out for another 25 years. When you
do this, check that the model is calculating ahead as expected. There can
be some small formula problem that keeps it from working right.
When the model is working right, look for the row in which the Nest Egg
value goes to zero or negative. The Qtr Date in that row is when the Nest
Egg runs out according to the current assumptions of the model. I call this
the Drop Dead Date.
If your Age value at the Drop Dead Date is beyond your expected life-time,
you may be able to increase the richness of your lifestyle or leave some
inheritance to your kids. If it is less than your expected life-time, you
may want to change your lifestyle or make arrangements for your kids to
take care of you in the later years.
To use the model to ask "what if?" questions, you can change
the assumptions of the model.Changing the date on which you purchase a new
rig, or changing the cost of that new rig may extend or retract the Drop
Dead Date. Change your basic living expenses. Put in some income from part-time
work. Watch how the Nest Egg changes.
The model may not show the Nest Egg going to zero; in fact, it may keep
growing. You never reach your Drop Dead Date. In that case, you initial
Nest Egg is big enough that its appreciation is faster than your Living
Expense is using it up (lucky you!). You can increase expenses, like taking
more cruises, and still be comfortable. I have found that I would need a
Nest Egg of over $500,000 to reach the point of ever-increasing Nest Egg
with the life style I expect to lead. I am not there unless some stock I
own skyrockets unexpectedly.
It is interesting to see how the Living Expense increases with inflation
over the years. We are currently in a time of low inflation; just hope it
stays that way. By the way, I generally assume my Nest Egg will appreciate
at about 3% over inflation. That assumption is historically valid for inflation
rates under 10% and with moderately agressive investing. All bets come off
if inflation runs away or Social Security craps out. Keep tuned to AARP
for how things are going.