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:

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.