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 places.
  
  
• 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 places.
  
  
• 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 places.
  
  
• 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 places.
  
  
• 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 decimal places.
  
  
• 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 places.
  
  
• 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.