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