Project #154919 - Financial Modeling

Business Tutors

Subject Business
Due By (Pacific Time) 11/23/2016 12:00 am

Problem 1: 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.


Problem 2: Calculating late penalty over a period of time

A local law requires that certain businesses install a safety equipment and then replace it with a new one every six years. If it is not replaced on time, fines described later are assessed.

Develop a model to calculate how much total fine, if any, has accrued on a particular date for a business given the initial installation date for the equipment and the date when the calculations are being done. (The two dates are the independent variables of the problem and the user should be able to enter any two dates and get the correct answer.)

The rules for the fine are as follows:

  • A business gets a 2 month grace period after the six year period from the original installation date is up before fines start to accrue.
  • The fine is $25 per month for the 1st and 2nd months once it starts to accrue and then it escalates by $25/mo every two months (i.e., the fine is $50/mo for the 3rd and 4th months and so on).
  • Months are not calendar month and are also not based on a uniform 30 day or 31 day months. They are based on dates. For example, 3/14/15 to 4/13/15 is one month.
  • If the last month is not a full month (based on dates as explained before) then the fine for that month will be prorated based on the number of days since the last full month and assuming that the last month is 30 days. Also, the fine for the last fractional month should be calculated based on the monthly fine that would have applied to that month. Fines should be calculated through the day before the date when the calculation is being made.

Your model should work for all dates. Using your model calculate and show the total fine for the following two cases: (a) initial equipment installation date of 5/11/2006 and the date when you are doing your calculation is 11/7/2015 and (b) initial equipment installation date of 9/14/1996 and the date when you are doing your calculation is 3/5/2016. Note that unless your model is exactly right, you may get the right answer for part (a) but not for part (b). So check your model and answers carefully.

Include one or more clear decision tree(s)—in Excel or some other printed form, not hand drawn or written--for your model and lay out the model with proper labels, etc. in a way that someone with knowledge of Excel will be able to understand your model and check it.


(Each problem uses Excel to do.)


out of 1971 reviews

out of 766 reviews

out of 1164 reviews

out of 721 reviews

out of 1600 reviews

out of 770 reviews

out of 766 reviews

out of 680 reviews