EXERCISE 1 BOZO RETIRES

This easy exercise has two purposes. First, it will give you practice with some key financial functions in Excel. The functions you will need to use are PMT and NPV. These are two of the three most commonly used functions in the course. The other is IRR. Second, the exercise gives you a sense for what it will take in terms of savings and investment returns to achieve a retirement goal in terms of timing and standard of living. Once it is set up, you can change assumptions regarding date of retirement, income level during retirement, rate of saving, etc., and analyze your options. I keep my plan on my computer at all times. It helps me to abide.

To start, you need several parameters:

1. Income per year during retirement. You may assume an amount, or you may develop a detailed budget, based on the standard of living you desire to maintain. If you want to be more realistic, you can subtract social security cash payments and any defined benefits retirement income you expect. The difference will be the living expenses you need to cover by this retirement plan;

2. Number of years you expect to be in retirement mode. This number depends on two ages, your age at time of retirement and your age at time of demise;

3. The amount of money you have saved for retirement now. This amount may of course be zero. It may even be negative for those of you with substantial debt. Icky-poo;

4. The rate of return you expect to earn on your retirement investments during your working years. This rate depends on your investment options and strategy, and especially your tolerance for risk;

5. The rate of return on your retirement investments during your retirement years. This rate will generally be lower than that during years, as you will likely want to put your funds into assets of lower risk;

6. An inflation rate that is applied to current dollars to calculate nominal (inflation-inclusive) dollars.

Think about how you will put the values of these parameters together, using the financial functions, to determine how much you must save per year during your working years to achieve your goals. The exercise is best done in two steps. First, calculate the size of the nest egg (note the chicken reference) necessary to fund your retirement income. This step will entail applying the NPV function to yearly nominal income amounts during retirement. Second, calculate the amount you must save per year during your working years. This step will entail applying the PMT function to the next egg amount. Once you have done the analysis, consider the feasibility of saving the amount given by the PMT function. If it seems infeasible, you may want to consider changing one or more of the parameters.

I have included a file as a start to this exercise. You may use it as a template. The best submissions will: show a clearly feasible plan (which need not be yours, if you do not want to share your personal information with me); demonstrate some thought about an expense budget in retirement; and demostrate some thought about other parameters in the model. As noted, the exercise is intended to be a pretty simple introduction to some important concepts and analytic processes in the course. Subsequent assignments will be more challenging, I promise. Learning Points: 1. NPV and PMT functions 2. Interest rate effects 3. Retirement planning 4. Nominal versus real cash flows and rates

50000 Exercise 1 Sample Analysis income/yr in retirement age at retirement age at demise current retirement savings interest rate during work years interest rate during retirement years age now inflation rate 01 jack: you could start with a retirement expense budget, covering food, housing, travel, etc. Then subtract any defined benefits retirement income. The difference is what you must fund per year (in real terms) from this 401-type retirement plan. jackwhee: in this col. Put nominal inflation-adjusted) income in retirement. jackwhee: note there is not yet any inflation adjustment to income in retirement. jackwhee: nominal 65 85 jackwhee: 200000/ nominal 0.081 0.06 Jack: end of year for all ages 51 0.03 real cash flows nominal cash flows year (age in retirement) income/yr in retirement income/yr in retirement 66 50000 77898 67 50000 80235 68 50000 82642 69 50000 85122 70 50000 87675 71 50000 90306 72 50000 93015 73 50000 95805 74 50000 98679 75 50000 101640 76 50000 104689 77 50000 107830 78 50000 111064 79 50000 114396 80 50000 117828 81 50000 121363 82 50000 125004 83 50000 128754 84 50000 132617 85 50000 136595 nest egg $1,134,320 amount to save per year ($18,411) jackwhee: use NPV function. Result is amount that must be saved by year end age 65. Jack Wheeler: use PMT function. Negative result is amount that must be saved per year, for ages 51-65. 50000 Exercise 1 Sample Analysis income/yr in retirement age at retirement age at demise current retirement savings interest rate during work years interest rate during retirement years age now inflation rate 01 jack: you could start with a retirement expense budget, covering food, housing, travel, etc. Then subtract any defined benefits retirement income. The difference is what you must fund per year (in real terms) from this 401-type retirement plan. jackwhee: in this col. Put nominal inflation-adjusted) income in retirement. jackwhee: note there is not yet any inflation adjustment to income in retirement. jackwhee: nominal 65 85 jackwhee: 200000/ nominal 0.081 0.06 Jack: end of year for all ages 51 0.03 real cash flows nominal cash flows year (age in retirement) income/yr in retirement income/yr in retirement 66 50000 77898 67 50000 80235 68 50000 82642 69 50000 85122 70 50000 87675 71 50000 90306 72 50000 93015 73 50000 95805 74 50000 98679 75 50000 101640 76 50000 104689 77 50000 107830 78 50000 111064 79 50000 114396 80 50000 117828 81 50000 121363 82 50000 125004 83 50000 128754 84 50000 132617 85 50000 136595 nest egg $1,134,320 amount to save per year ($18,411) jackwhee: use NPV function. Result is amount that must be saved by year end age 65. Jack Wheeler: use PMT function. Negative result is amount that must be saved per year, for ages 51-65