16.1 Basics

Problem. Develop a financial plan for investment and consumption over your life-cycle (from the present

until your death). Suppose the inflation rate is 2.0% and the real return on a riskfree money market fund is

3.5%. Suppose that a risky diversified fund offers an average real return of 8.0% and a standard deviation

of 17.0%, which is equivalent to the post-World War II average real return and standard deviation on a

well-diversified portfolio of US stocks. Suppose that federal income taxes have five brackets with the

following rates: 15.0%, 28.0%, 31.0%, 36.0%, and 39.6%. For current year, the upper cutoff on the first

four brackets are $43,050, $104,050, $158,550, and $283,150 and these cutoffs are indexed to inflation.

The state tax rate = 3.0%, federal FICA-SSI tax rate on salary up to $72,600 is 6.2%, and the federal

FICA-Medicare tax rate on any level of salary is 1.45%. Suppose you are currently 25 years old and you

expect to earn a salary next year of $70,000. You currently have $0 in a retirement account and plan to

work through age 65. You will start receiving social security benefits at age 66. The current level of social

security benefits is $15, 480 per year and this is indexed to inflation. Explore the investment and

consumption impacts over the life-cycle of the following choice variables:

Savings Percentage. The percentage of your annual salary that you contribute to your

retirement fund during your working years.

Risky Diversified Fund Percentage. The percentage of your retirement fund that you put in

the risky diversified fund (vs. the riskfree money market fund).

Withdrawal Percentage. The percentage of your retirement fund that you withdraw each year

to live on during your retirement years.

Real Growth Rate in Salary. The real portion of the annual growth in your salary. You salary

also increases with inflation.

FIGURE 16.1 Spreadsheet Model for Life-Cycle Financial Planning - Basics.

Solution Strategy. Develop a spreadsheet model of investment and consumption on a year-by-year basis

over an entire lifetime. You need to choose how to divide your salary between providing consumption

now vs. savings (to provide for consumption in the future). Your savings are put in a tax-deferred

retirement account and each year you need to decide what percentage to contribute to it (or withdraw

from it). You avoid paying taxes on contributions to the retirement fund, but you suffer paying taxes

when you withdraw from it. Salary less contributions plus withdraws gives you taxable income upon

which you pay taxes. The after-tax income plus social security benefits provide for consumption each

year. You need to choose what percentage of your retirement funds to invest in the risky diversified fund.

The rest of your retirement funds will be invested in the riskfree money market fund and will grow at the

riskfree rate. Investing in the risky diversified fund will give you a higher average returns than the riskfree

money market fund, but also more risk. You also need to choose how fast to withdraw funds from your

retirement account during your retirement years. A steady state withdraw policy is one that withdraws the

average real increase in the retirement fund each year. This avoids touching the principal amount and

allows it to grow at the rate of inflation. A steady state withdraw policy can be sustained indefinitely (i.e.,

no matter how long you live). For convenience, the spreadsheet analysis is carried out to age 90. How

long will you live? Well, for today's US population of 65 year olds, average life expectancy is 82. To

determine your individual life expectancy, add to (or subtract from) 82 based your individual healthconscious

practices. Not smoking adds nine years. Aerobic exercising and getting seven to eight hours of

sleep per night adds three years. A healthy diet and maintaining a desirable weight based on your height

adds three years. A thorough annual medical exam to catch cancer and other health problems early adds

two years. The following six items add one year each: (1) daily aspirin to reduce fatal heart attacks, (2)

preventing high blood pressure, (3) avoiding accidents, (4) getting immunized against pneumonia and

influenza, (5) avoiding suicide and AIDS, and (6) avoiding heavy alcohol consumption. For the subset of

today's US 65 year olds who follow all of these health-conscious practices, life expectancy is 105! These

life expectancy figures are conservative in the sense that they do not take into account future

scientific/medical progress. For more information on the factors effecting longevity and the long-run

impact of scientific and medical progress, read "Hello Methuselah! Living to 100 and Beyond" by George

Webster, Ph.D.

How To Build This Spreadsheet Model.

1. Inputs. Enter the inputs described in the problem into the range B4:B15, the cells B18, C30,

B39, C42, and the range B43:B47. For the time being, enter 0.0001% in cell B7 as the standard

deviation rather than 17%. Lock in the left column of titles by selecting the cell B1 and clicking

on Window | Freeze Panes.

2. Date and Age. For the Date row, enter 0, 1, 2,..., 65 in the range B17:BO17. The easy way to do

this is enter 0 in cell B17, 1 in cell C17, highlight the range B17:C17, put the cursor over the

lower right corner of cell C17 until it becomes a “plus” icon, and drag the cursor all the way

across to cell BO17. For the Age row, enter =B18+1 in cell C18 and copy the cell to the range

D18:BO18.

3. Choice Variables. Each year, starting with age 26 (column C) and continuing through age 90

(column BO), you need to make certain decisions. Each decision is called a choice variable. Enter

initial values for the choice variables into the ranges C20:BO22 and D23:BO23. Figure 2 below

shows the two steps involved in implementing retirement. (1) Enter -100.0% in cell AQ23, which

is the age 66 (first retirement year) Real Growth Rate in Salary. This causes the age 66 Salary in

cell AQ30 to drop to zero. (2) Given that you have no salary, you need to withdraw a percentage

of the money in your retirement fund to live on. A Steady State Withdrawal Percentage =

Average Real Return on the Retirement Fund * (1 + Inflation Rate) = (Risky Diversified Fund

Percentage * Average Real Return on the Risk Diversified Fund + (1 - Risky Diversified Fund

Percentage) * Real Return on the Riskfree Money Market Fund) * (1 + Inflation Rate). Enter

=(AQ21*$B$6+(1-AQ21)*$B$5)*(1+$B$4) in cell AQ22 and copy it across. By the way, the

last term "(1 + Inflation Rate)" takes care of the cross-product between (1 + real return) and (1 +

inflation rate), so that the retirement fund can grow at the inflation rate.

FIGURE 16.2 Working Years vs. Retirement Years.

As you adapt this model to your own situation, it is not necessary to go from full-time work to

zero work. You could consider retiring to part-time work and then gradually tapering off. For

example, you could drop to half-time work by entering -50% in your first retirement year and

then enter -100.0% in the year that you stop working entirely.

4. Random Variables. Assume that the Real Return on the Risky Diversified Fund is normally

distributed with the mean given in cell B6 and the standard deviation given in cell B7. The Excel

function RAND() generates a random variable with a uniform distribution over the interval from

0 to 1 (that is, an equal chance of getting any number between 0 and 1). To transform this

uniformly distributed random variable into a normally distributed one, just place it inside the

Excel function NORMINV.1 Enter =NORMINV(RAND(),$B$6,$B$7) in cell C26 and copy it

across. The real return that you get depends on how much you have placed in risky vs. riskfree

funds. Real Return on Your Retirement Fund = (Risky Diversified Fund Percentage) * (Real

1 The "Transformation Method" for converting a uniform random variable x into some other random variable y based on a

cumulative distribution F is y(x) F−1(x) . See Press, W., B. Flannery, S. Teukolsky, and W. Vetterling, 1987, Numerical

Recopies: The Art of Scientific Computing, Cambridge University Press, chapter on Random Numbers, subsection on the

Transformation Method, page 201.

Return on Risky Diversified Fund) + (1 - Risky Diversified Fund Percentage) * (Real Return on

Riskfree Money Market Fund). Enter =C21*C26+(1-C21)*$B$5 in cell C27 and copy it across.

FIGURE 16.3 Old Age Years.

5. Outputs. Here are the formulas for each row:

o Salary = Last Year’s Salary * (1 + Inflation Rate) * (1 + Real Growth Rate in Salary) in

working years Enter =C30*(1+$B$4)*(1+D23) in cell D30 and copy it across.

o Less Contribution To Retirement Fund = (Savings Percentage) * (Salary). Enter

=C20*C30 in cell C31 and copy it across.

o Plus Withdrawal From Retirement Fund = (Withdrawal Percentage) * (Last Period’s

Retirement Fund). Enter =C22*B39 in cell C32 and copy it across.

o Taxable Income = Salary - (Contribution To Retirement Fund) + (Withdrawal From

Retirement Fund).

Enter =C30-C31+C32 in cell C33 and copy it across.

o Taxes = (Bracket 1 Tax Rate) * MIN(Taxable Income, Bracket 1 Cutoff)

+ (Bracket 2 Tax Rate) * MAX(MIN(Taxable Income, Bracket 2 Cutoff)

- Bracket 1 Cutoff, 0)

+ (Bracket 3 Tax Rate) * MAX(MIN(Taxable Income, Bracket 3 Cutoff)

- Bracket 2 Cutoff, 0)

+ (Bracket 4 Tax Rate) * MAX(MIN(Taxable Income, Bracket 4 Cutoff)

- Bracket 3 Cutoff, 0)

+ (Bracket 5 Tax Rate) * MAX(Taxable Income - Bracket 4 Cutoff, 0)

+ (Federal FICA-SSI Tax Rate) * MIN(Salary, Federal FICA-SSI Wage Cap)

+ (Federal FICA-Medicare Tax Rate) * Salary

+ (State Income Tax Rate) * Taxable Income

Enter =$B$8*MIN(C33,C43)

+$B$9*MAX(MIN(C33,C44)-C43,0)

+$B$10*MAX(MIN(C33,C45)-C44,0)

+$B$11*MAX(MIN(C33,C46)-C45,0)

+$B$12*MAX(C33-C46,0)

+$B$13*MIN(C30,C47)

+$B$14*C30

+$B$15*C33 in cell C34 and copy it across.

o After-Tax Income = Taxable Income - Taxes. Enter =C33-C34 in cell C35 and copy it

across.

o Plus Social Security Benefits = 0 in working years

= Social Security Benefit Level in retirement year

o Enter 0 in cell C36 and copy the cell to the range D36:AP36. Enter =AQ42 in cell AQ36

and copy it across.

o Consumption = After-Tax Income + Social Security Benefits. Enter =C35+C36 in cell

C37 and copy it across.

o Retirement Fund = Last Year's Retirement Fund * (1 + Inflation Rate) * (1 + Real

Return on Your Retirement Fund) + Contribution to the Retirement Fund - Withdrawal

from the Retirement Fund. Enter =B39*(1+$B$4)*(1+C27)+C31-C32 in cell C39 and

copy it across.

o Real Consumption = (Nominal Consumption) / ((1 + Inflation Rate) ^ Number of

periods)

Enter =C37/((1+$B$4)^C17) in cell C40 and copy it across.

o Difference in Real Consumption (Post-Pre) = Real Consumption in Post-Retirement -

Real Consumption in Pre-Retirement. Enter =AQ40-AP40 in cell B41.

o Social Security Benefit Level = Last Year's Social Security Benefit Level * (1 +

Inflation Rate).

Enter =C42*(1+$B$4) in cell D42 and copy it across. To check your social security

eligibility and benefit level, surf the Social Security Administration's web site

http://www.ssa.gov/OACT/ANYPIA/.

o Federal Income Tax Bracket Cutoffs. = Last Year's Federal Income Tax Bracket Cutoff

* (1 + Inflation Rate). Enter =B43*(1+$B$4) in cell C43 and copy the cell to the range

C43:BO46.

o Federal FICA-SSI Wage Cap. = Last Year's Federal FICA-SSI Wage Cap * (1 +

Inflation Rate).

Enter =B47*(1+$B$4) in cell C47 and copy it across.

6. Graph Real Consumption Over The Life-Cycle. Highlight the range B18:BO18, then hold

down the Control button and (while still holding it down) select the range B40:BO40. Next

choose Insert | Chart from the main menu. Select an XY(Scatter) chart type and make other

selections to complete the Chart Wizard. Place the graph in the range C2:G16.

7. Adjust Savings Percentage To Smooth Real Consumption Over The Life-Cycle. It doesn't

make any sense to live like a king in your working years and the live in poverty in your

retirement years. Similarly, it doesn't make sense to live in poverty in your working years and live

like a king in your retirement years. The key idea is that you want to have a smooth pattern of real

consumption over the life-cycle. The easiest way to get a smooth consumption pattern is to adjust

the savings percentage. The easiest way to do this is to have a constant savings percentage during

your working years by tying this savings percentage to a single cell and then manually adjust this

cell. Enter =C20 in cell D20 and copy it across. Then manually adjust cell C20 up or down in

small increments until the Difference in Real Consumption (Post-Pre) in cell B41 is reasonably

close to zero.

Looking at the big picture, the retirement fund starts at $0 and rises smoothly to $4,072,321 in the

first retirement year and then increases at the rate of inflation each year after that. Focusing on the

graph of real consumption over the life-cycle, we see that real consumption (in current dollars)

starts out at $43,864 and rises smoothly to $92,511 at retirement and then stays constant at that

level throughout retirement -- a comfortable lifestyle!

8. Adjust The Standard Deviation and View The Risk Involved. Now change the standard

deviation to a realistic figure. Enter 17.0000% in cell B7. The random variables in rows 26 and

27 will spring to life and the graph of real consumption over the life-cycle will reflect the high or

low realizations of the risky diversified fund. Press the F9 Recalculation key several times and

you will see the real consumption rate dance all over the graph. Figure 4 shows a low

consumption case due to low real returns. Figure 5 shows a medium consumption case due to

medium real returns Figure 6 shows a high consumption case due to high real returns.

FIGURE 16.4 A Low Consumption Case Due To Low Real Returns in the Risky Diversified Fund.

FIGURE 16.5 A Medium Consumption Case Due To Medium Real Returns in the Risky

Diversified Fund.

FIGURE 16.6 A High Consumption Case Due To High Real Returns in the Risky Diversified Fund.

These three graphs are "representative" of the risk you face from investing in the risky diversified fund. In

the low case, real consumption drops to about $40,000. In the medium case, real consumption fluctuates

between $75,000 and $100,000. In the high case, real consumption fluctuates between $125,000 and

$160,000. Clearly, there is substantial risk from being so heavily exposed to the risky diversified fund.

There is a direct connection between risk and return. A high percentage in the risky diversified fund

percentage gives you a high average return and high risk. Whereas a low percentage in the risky

diversified fund percentage gives you a low average return and low risk. The choice is up to you.

Problems

Skill-Building Problems.

1. Suppose the inflation rate is 2.4% and the real return on a riskfree money market fund is 3.8%.

Suppose that a risky diversified fund offers an average real return of 7.2% and a standard

deviation of 19.3%. Suppose that federal income taxes have five brackets with the following

rates: 15.0%, 28.0%, 31.0%, 36.0%, and 39.6%. For current year, the upper cutoff on the first

four brackets are $43,050, $104,050, $158,550, and $283,150 and these cutoffs are indexed to

inflation. The state tax rate = 4.5%, federal FICA-SSI tax rate on salary up to $72,600 is 6.2%,

and the federal FICA-Medicare tax rate on any level of salary is 1.45%. Suppose you are

currently 35 years old and you expect to earn a salary next year of $90,000. You currently have

$40,000 in a retirement account and plan to work through age 70. You will start receiving social

security benefits at age 71. The current level of social security benefits is $15, 480 per year and

this is indexed to inflation. Develop a financial plan for investment and consumption over your

life-cycle.

Skill-Extending Problems.

2. Extend the Life-Cycle Financial Planning model by converting the spreadsheet into a Dynamic

Chart by adding spinners to drive the inputs. See Black Scholes Option Pricing - Dynamic Chart

for details on how to implement spinners. After you click on the spinners to change the inputs,

then adjust the Savings Percentage to have a smooth consumption pattern.

Live In-class Problems.

3. Given the partial Basics spreadsheet LifebasZ.xls, do step 3 Choice Variables.

Problem. Develop a financial plan for investment and consumption over your life-cycle (from the present

until your death). Suppose the inflation rate is 2.0% and the real return on a riskfree money market fund is

3.5%. Suppose that a risky diversified fund offers an average real return of 8.0% and a standard deviation

of 17.0%, which is equivalent to the post-World War II average real return and standard deviation on a

well-diversified portfolio of US stocks. Suppose that federal income taxes have five brackets with the

following rates: 15.0%, 28.0%, 31.0%, 36.0%, and 39.6%. For current year, the upper cutoff on the first

four brackets are $43,050, $104,050, $158,550, and $283,150 and these cutoffs are indexed to inflation.

The state tax rate = 3.0%, federal FICA-SSI tax rate on salary up to $72,600 is 6.2%, and the federal

FICA-Medicare tax rate on any level of salary is 1.45%. Suppose you are currently 25 years old and you

expect to earn a salary next year of $70,000. You currently have $0 in a retirement account and plan to

work through age 65. You will start receiving social security benefits at age 66. The current level of social

security benefits is $15, 480 per year and this is indexed to inflation. Explore the investment and

consumption impacts over the life-cycle of the following choice variables:

Savings Percentage. The percentage of your annual salary that you contribute to your

retirement fund during your working years.

Risky Diversified Fund Percentage. The percentage of your retirement fund that you put in

the risky diversified fund (vs. the riskfree money market fund).

Withdrawal Percentage. The percentage of your retirement fund that you withdraw each year

to live on during your retirement years.

Real Growth Rate in Salary. The real portion of the annual growth in your salary. You salary

also increases with inflation.

FIGURE 16.1 Spreadsheet Model for Life-Cycle Financial Planning - Basics.

Solution Strategy. Develop a spreadsheet model of investment and consumption on a year-by-year basis

over an entire lifetime. You need to choose how to divide your salary between providing consumption

now vs. savings (to provide for consumption in the future). Your savings are put in a tax-deferred

retirement account and each year you need to decide what percentage to contribute to it (or withdraw

from it). You avoid paying taxes on contributions to the retirement fund, but you suffer paying taxes

when you withdraw from it. Salary less contributions plus withdraws gives you taxable income upon

which you pay taxes. The after-tax income plus social security benefits provide for consumption each

year. You need to choose what percentage of your retirement funds to invest in the risky diversified fund.

The rest of your retirement funds will be invested in the riskfree money market fund and will grow at the

riskfree rate. Investing in the risky diversified fund will give you a higher average returns than the riskfree

money market fund, but also more risk. You also need to choose how fast to withdraw funds from your

retirement account during your retirement years. A steady state withdraw policy is one that withdraws the

average real increase in the retirement fund each year. This avoids touching the principal amount and

allows it to grow at the rate of inflation. A steady state withdraw policy can be sustained indefinitely (i.e.,

no matter how long you live). For convenience, the spreadsheet analysis is carried out to age 90. How

long will you live? Well, for today's US population of 65 year olds, average life expectancy is 82. To

determine your individual life expectancy, add to (or subtract from) 82 based your individual healthconscious

practices. Not smoking adds nine years. Aerobic exercising and getting seven to eight hours of

sleep per night adds three years. A healthy diet and maintaining a desirable weight based on your height

adds three years. A thorough annual medical exam to catch cancer and other health problems early adds

two years. The following six items add one year each: (1) daily aspirin to reduce fatal heart attacks, (2)

preventing high blood pressure, (3) avoiding accidents, (4) getting immunized against pneumonia and

influenza, (5) avoiding suicide and AIDS, and (6) avoiding heavy alcohol consumption. For the subset of

today's US 65 year olds who follow all of these health-conscious practices, life expectancy is 105! These

life expectancy figures are conservative in the sense that they do not take into account future

scientific/medical progress. For more information on the factors effecting longevity and the long-run

impact of scientific and medical progress, read "Hello Methuselah! Living to 100 and Beyond" by George

Webster, Ph.D.

How To Build This Spreadsheet Model.

1. Inputs. Enter the inputs described in the problem into the range B4:B15, the cells B18, C30,

B39, C42, and the range B43:B47. For the time being, enter 0.0001% in cell B7 as the standard

deviation rather than 17%. Lock in the left column of titles by selecting the cell B1 and clicking

on Window | Freeze Panes.

2. Date and Age. For the Date row, enter 0, 1, 2,..., 65 in the range B17:BO17. The easy way to do

this is enter 0 in cell B17, 1 in cell C17, highlight the range B17:C17, put the cursor over the

lower right corner of cell C17 until it becomes a “plus” icon, and drag the cursor all the way

across to cell BO17. For the Age row, enter =B18+1 in cell C18 and copy the cell to the range

D18:BO18.

3. Choice Variables. Each year, starting with age 26 (column C) and continuing through age 90

(column BO), you need to make certain decisions. Each decision is called a choice variable. Enter

initial values for the choice variables into the ranges C20:BO22 and D23:BO23. Figure 2 below

shows the two steps involved in implementing retirement. (1) Enter -100.0% in cell AQ23, which

is the age 66 (first retirement year) Real Growth Rate in Salary. This causes the age 66 Salary in

cell AQ30 to drop to zero. (2) Given that you have no salary, you need to withdraw a percentage

of the money in your retirement fund to live on. A Steady State Withdrawal Percentage =

Average Real Return on the Retirement Fund * (1 + Inflation Rate) = (Risky Diversified Fund

Percentage * Average Real Return on the Risk Diversified Fund + (1 - Risky Diversified Fund

Percentage) * Real Return on the Riskfree Money Market Fund) * (1 + Inflation Rate). Enter

=(AQ21*$B$6+(1-AQ21)*$B$5)*(1+$B$4) in cell AQ22 and copy it across. By the way, the

last term "(1 + Inflation Rate)" takes care of the cross-product between (1 + real return) and (1 +

inflation rate), so that the retirement fund can grow at the inflation rate.

FIGURE 16.2 Working Years vs. Retirement Years.

As you adapt this model to your own situation, it is not necessary to go from full-time work to

zero work. You could consider retiring to part-time work and then gradually tapering off. For

example, you could drop to half-time work by entering -50% in your first retirement year and

then enter -100.0% in the year that you stop working entirely.

4. Random Variables. Assume that the Real Return on the Risky Diversified Fund is normally

distributed with the mean given in cell B6 and the standard deviation given in cell B7. The Excel

function RAND() generates a random variable with a uniform distribution over the interval from

0 to 1 (that is, an equal chance of getting any number between 0 and 1). To transform this

uniformly distributed random variable into a normally distributed one, just place it inside the

Excel function NORMINV.1 Enter =NORMINV(RAND(),$B$6,$B$7) in cell C26 and copy it

across. The real return that you get depends on how much you have placed in risky vs. riskfree

funds. Real Return on Your Retirement Fund = (Risky Diversified Fund Percentage) * (Real

1 The "Transformation Method" for converting a uniform random variable x into some other random variable y based on a

cumulative distribution F is y(x) F−1(x) . See Press, W., B. Flannery, S. Teukolsky, and W. Vetterling, 1987, Numerical

Recopies: The Art of Scientific Computing, Cambridge University Press, chapter on Random Numbers, subsection on the

Transformation Method, page 201.

Return on Risky Diversified Fund) + (1 - Risky Diversified Fund Percentage) * (Real Return on

Riskfree Money Market Fund). Enter =C21*C26+(1-C21)*$B$5 in cell C27 and copy it across.

FIGURE 16.3 Old Age Years.

5. Outputs. Here are the formulas for each row:

o Salary = Last Year’s Salary * (1 + Inflation Rate) * (1 + Real Growth Rate in Salary) in

working years Enter =C30*(1+$B$4)*(1+D23) in cell D30 and copy it across.

o Less Contribution To Retirement Fund = (Savings Percentage) * (Salary). Enter

=C20*C30 in cell C31 and copy it across.

o Plus Withdrawal From Retirement Fund = (Withdrawal Percentage) * (Last Period’s

Retirement Fund). Enter =C22*B39 in cell C32 and copy it across.

o Taxable Income = Salary - (Contribution To Retirement Fund) + (Withdrawal From

Retirement Fund).

Enter =C30-C31+C32 in cell C33 and copy it across.

o Taxes = (Bracket 1 Tax Rate) * MIN(Taxable Income, Bracket 1 Cutoff)

+ (Bracket 2 Tax Rate) * MAX(MIN(Taxable Income, Bracket 2 Cutoff)

- Bracket 1 Cutoff, 0)

+ (Bracket 3 Tax Rate) * MAX(MIN(Taxable Income, Bracket 3 Cutoff)

- Bracket 2 Cutoff, 0)

+ (Bracket 4 Tax Rate) * MAX(MIN(Taxable Income, Bracket 4 Cutoff)

- Bracket 3 Cutoff, 0)

+ (Bracket 5 Tax Rate) * MAX(Taxable Income - Bracket 4 Cutoff, 0)

+ (Federal FICA-SSI Tax Rate) * MIN(Salary, Federal FICA-SSI Wage Cap)

+ (Federal FICA-Medicare Tax Rate) * Salary

+ (State Income Tax Rate) * Taxable Income

Enter =$B$8*MIN(C33,C43)

+$B$9*MAX(MIN(C33,C44)-C43,0)

+$B$10*MAX(MIN(C33,C45)-C44,0)

+$B$11*MAX(MIN(C33,C46)-C45,0)

+$B$12*MAX(C33-C46,0)

+$B$13*MIN(C30,C47)

+$B$14*C30

+$B$15*C33 in cell C34 and copy it across.

o After-Tax Income = Taxable Income - Taxes. Enter =C33-C34 in cell C35 and copy it

across.

o Plus Social Security Benefits = 0 in working years

= Social Security Benefit Level in retirement year

o Enter 0 in cell C36 and copy the cell to the range D36:AP36. Enter =AQ42 in cell AQ36

and copy it across.

o Consumption = After-Tax Income + Social Security Benefits. Enter =C35+C36 in cell

C37 and copy it across.

o Retirement Fund = Last Year's Retirement Fund * (1 + Inflation Rate) * (1 + Real

Return on Your Retirement Fund) + Contribution to the Retirement Fund - Withdrawal

from the Retirement Fund. Enter =B39*(1+$B$4)*(1+C27)+C31-C32 in cell C39 and

copy it across.

o Real Consumption = (Nominal Consumption) / ((1 + Inflation Rate) ^ Number of

periods)

Enter =C37/((1+$B$4)^C17) in cell C40 and copy it across.

o Difference in Real Consumption (Post-Pre) = Real Consumption in Post-Retirement -

Real Consumption in Pre-Retirement. Enter =AQ40-AP40 in cell B41.

o Social Security Benefit Level = Last Year's Social Security Benefit Level * (1 +

Inflation Rate).

Enter =C42*(1+$B$4) in cell D42 and copy it across. To check your social security

eligibility and benefit level, surf the Social Security Administration's web site

http://www.ssa.gov/OACT/ANYPIA/.

o Federal Income Tax Bracket Cutoffs. = Last Year's Federal Income Tax Bracket Cutoff

* (1 + Inflation Rate). Enter =B43*(1+$B$4) in cell C43 and copy the cell to the range

C43:BO46.

o Federal FICA-SSI Wage Cap. = Last Year's Federal FICA-SSI Wage Cap * (1 +

Inflation Rate).

Enter =B47*(1+$B$4) in cell C47 and copy it across.

6. Graph Real Consumption Over The Life-Cycle. Highlight the range B18:BO18, then hold

down the Control button and (while still holding it down) select the range B40:BO40. Next

choose Insert | Chart from the main menu. Select an XY(Scatter) chart type and make other

selections to complete the Chart Wizard. Place the graph in the range C2:G16.

7. Adjust Savings Percentage To Smooth Real Consumption Over The Life-Cycle. It doesn't

make any sense to live like a king in your working years and the live in poverty in your

retirement years. Similarly, it doesn't make sense to live in poverty in your working years and live

like a king in your retirement years. The key idea is that you want to have a smooth pattern of real

consumption over the life-cycle. The easiest way to get a smooth consumption pattern is to adjust

the savings percentage. The easiest way to do this is to have a constant savings percentage during

your working years by tying this savings percentage to a single cell and then manually adjust this

cell. Enter =C20 in cell D20 and copy it across. Then manually adjust cell C20 up or down in

small increments until the Difference in Real Consumption (Post-Pre) in cell B41 is reasonably

close to zero.

Looking at the big picture, the retirement fund starts at $0 and rises smoothly to $4,072,321 in the

first retirement year and then increases at the rate of inflation each year after that. Focusing on the

graph of real consumption over the life-cycle, we see that real consumption (in current dollars)

starts out at $43,864 and rises smoothly to $92,511 at retirement and then stays constant at that

level throughout retirement -- a comfortable lifestyle!

8. Adjust The Standard Deviation and View The Risk Involved. Now change the standard

deviation to a realistic figure. Enter 17.0000% in cell B7. The random variables in rows 26 and

27 will spring to life and the graph of real consumption over the life-cycle will reflect the high or

low realizations of the risky diversified fund. Press the F9 Recalculation key several times and

you will see the real consumption rate dance all over the graph. Figure 4 shows a low

consumption case due to low real returns. Figure 5 shows a medium consumption case due to

medium real returns Figure 6 shows a high consumption case due to high real returns.

FIGURE 16.4 A Low Consumption Case Due To Low Real Returns in the Risky Diversified Fund.

FIGURE 16.5 A Medium Consumption Case Due To Medium Real Returns in the Risky

Diversified Fund.

FIGURE 16.6 A High Consumption Case Due To High Real Returns in the Risky Diversified Fund.

These three graphs are "representative" of the risk you face from investing in the risky diversified fund. In

the low case, real consumption drops to about $40,000. In the medium case, real consumption fluctuates

between $75,000 and $100,000. In the high case, real consumption fluctuates between $125,000 and

$160,000. Clearly, there is substantial risk from being so heavily exposed to the risky diversified fund.

There is a direct connection between risk and return. A high percentage in the risky diversified fund

percentage gives you a high average return and high risk. Whereas a low percentage in the risky

diversified fund percentage gives you a low average return and low risk. The choice is up to you.

Problems

Skill-Building Problems.

1. Suppose the inflation rate is 2.4% and the real return on a riskfree money market fund is 3.8%.

Suppose that a risky diversified fund offers an average real return of 7.2% and a standard

deviation of 19.3%. Suppose that federal income taxes have five brackets with the following

rates: 15.0%, 28.0%, 31.0%, 36.0%, and 39.6%. For current year, the upper cutoff on the first

four brackets are $43,050, $104,050, $158,550, and $283,150 and these cutoffs are indexed to

inflation. The state tax rate = 4.5%, federal FICA-SSI tax rate on salary up to $72,600 is 6.2%,

and the federal FICA-Medicare tax rate on any level of salary is 1.45%. Suppose you are

currently 35 years old and you expect to earn a salary next year of $90,000. You currently have

$40,000 in a retirement account and plan to work through age 70. You will start receiving social

security benefits at age 71. The current level of social security benefits is $15, 480 per year and

this is indexed to inflation. Develop a financial plan for investment and consumption over your

life-cycle.

Skill-Extending Problems.

2. Extend the Life-Cycle Financial Planning model by converting the spreadsheet into a Dynamic

Chart by adding spinners to drive the inputs. See Black Scholes Option Pricing - Dynamic Chart

for details on how to implement spinners. After you click on the spinners to change the inputs,

then adjust the Savings Percentage to have a smooth consumption pattern.

Live In-class Problems.

3. Given the partial Basics spreadsheet LifebasZ.xls, do step 3 Choice Variables.