Financial modeling

Financial modeling

Problem 4: Saving and withdrawing for retirement
You want to compare three alternatives for saving for retirement. (1) a Roth IRA,

(2) a 401(k) and (3) a taxable account. The tax consequences of the three

alternatives are as follows:

In a Roth IRA you make contributions from after-tax money, that is, money on which

you have already paid income tax. Over the years the money will earn investment

returns tax-free, that is, you do not have to pay any taxes on any investment

returns. Also, when you withdraw any money from this account after retirement, you

don’t have to pay any taxes.
In a 401(k), any money you put in is deductible from your income in the year you

make a contribution. (This is the same as saying that you make the contributions

from your pre-tax income.) No taxes are due on any investment returns (dividend,

interest, or capital gains) until you start to make withdrawals. But when you

withdraw money during retirement, you have to pay income taxes on the full amount

of any withdrawal at your ordinary income tax rate at that time.
In a taxable account, you make contributions from after-tax money, that is, money

on which you have already paid income tax. Over the years you have to pay taxes at

the dividend tax rate on any dividends you receive, at the long-term capital gains

tax rate on any long-term capital gains you realize, and at the ordinary income tax

rate on any interest income. (We are assuming you will not realize any short-term

capital gains.)
You plan to make annual contributions increasing at the rate of 3% (that is, your

second contribution will be 3% more than the first contribution and so on). You

will make a total of 35 contributions starting with a $10,000 pre-tax contribution

today (or its equivalent, i.e., $6,500 in the first year for a Roth IRA or taxable

account when the marginal ordinary income tax rate is 35%).

During retirement you want to make annual withdrawals, which after-taxes, if any,

will increase at the rate of 4% per year (that is, if your first withdrawal is

$1,000, the second one will be $1,040, and so on). You will make a total of 25 such

withdrawals starting 35 years from today.

Assume that both before and during retirement all monies will be invested in a

stock mutual fund that provides an annual total return of 8% of which 2% is

dividend. Ordinary income tax rate is 35% before retirement and 25% during

retirement. Long-term capital gains and dividend tax rates are both 15% before and

after retirement. But make all these tax rates variables so that the user can

change any one of these. Make all other values that you may want to change to do

“what if” analysis input variables as well.

Do and submit the following:

a)     Create a model to calculate how much after-tax withdrawal you will be able

to make in the first year of withdrawal if you are using a Roth IRA assuming that

the withdrawal rate in each case will be such that all of the money will be used up

after the 25th withdrawal. Your model should have two parts: one for the

accumulation phase and another for the withdrawal phase. Include an explanation of

how you checked to make sure that your answer is correct.

b)    Repeat Prob. (a) for using a 401(k) instead of Roth IRA account.

c)     Create a model to calculate how much money you will have in your taxable

account (if you use a taxable account) 35 years from today (before any withdrawals)

and what will be the tax basis for the stock mutual fund holding in your account at

that point.