Project #155766 - Financial Modeling

Business Tutors

Subject Business
Due By (Pacific Time) 11/27/2016 11:59 pm

1. In the book, the stock holdings become negative from year 14 onwards implying that the retiree will sell more stocks than he owns. This, of course, is not possible. (Ask yourself why this cannot imply short selling.)

Modify the model in the book so that in year 14 the retiree will sell all his remaining stocks (so that stock holdings will go to zero) and draw down any additional money he needs for that year from fixed income holdings. In the years after that he will have to draw down (i.e., take money out of) only fixed income holdings to meet his annual expenses, and stock holdings will remain zero. The model will stop in the year the retiree runs out of money.


For this version, assume that all assumptions and values of all input variables will remain the same as in the book model so that all numbers in the table will remain unchanged through year 13 and stock holdings will run out in year 14. (Your model must show all the columns that are in the book model except that, if you prefer, you can omit the column labeled “Check on Tax Paidâ€Â and add other properly labeled columns.)


2.Start with the input data provided from book. Assume that all the dividends for a quarter are paid at the end of the quarter (instead of at the ends of the months). Calculate the monthly nominal and real values of a portfolio for the period 1/1/90 to 1/1/96 starting with a $1,000 investment on 1/1/90. Also calculate the cumulative and annualized nominal and real returns for the period.


Assume that the amount of quarterly dividend paid at the end of March equals the total of dividends shown for Jan, Feb., and March and is reinvested on April 1 at the price shown for that day, and so on.


3.Using the data provided from book, calculate 10-year rolling nominal and real returns (both cumulative and annualized) for the period 1/1/50 to 12/31/02 and plot them in bar charts similar to Figures 16.5 and 16.6. (Charts should be properly labeled.) Also calculate the maximum, minimum, mean and median values as well as the standard deviations for the two types of returns.

4. 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 analysis input variables as well.


1) 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.

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


3)  Create a model to calculate how much money you will have in your taxable account 35 years from today and what will be the tax basis for the stock mutual fund holding in your account at that point.


5. Your 5 year old daughter has just announced that she would like to attend college after finishing high school. Your best guess is that today it costs approximately $25,000 per year (for four years) in tuition, books, rent, etc., to attend State College, but the cost will increase at the rate of 4% per year. Your daughter will start college 13 years from now, and the full cost for each year will have to be paid out at the beginning of that year You believe that you can invest all the savings for college in a 529 account to earn 9% return per year and you won't have to pay any taxes on any of the returns.

1)If you were to invest a lump sum today to cover her college costs, how much would you have to invest?

2) If you decide to save (in equal installments) and invest annually starting today and ending at the beginning of her last year of college, how much will you have to save each year?

3)How much will you have to save today as the first installment if you were to save annually as in (2) but plan to save 3% more each year (relative to the year before)?

4)How will the answer for (3) change if you expect four contributions of $5,000 each from your parents towards your daughter’s college funds at five year intervals with the first contribution received today. (You will invest this money the same way as the other savings for college.)

Build four separate models for the above four cases. In each model, make all the inputs other than the time (that is, the variables measured in years) input variables so that a user can change the assumptions to answer “what ifâ€Â questions.


5)  Build a model for case (3) where the number of years from now to when your daughter will go to college can be varied between 10 and 20 and how many years of college she will attend can be varied between 2 and 8. (This means you will build a model that will work with any positive whole numbers for these variables, but you will use data validation to constrain the user to those ranges of values for those variables.) The model should also allow the user to enter a lump sum amount that he has available today (over and above the first installment) to contribute to the college fund. Use the model to calculate the required fist installment of savings today if the initial lump sum amount (as explained before) is $20,000, your daughter will start college 15 years from now and she will attend college for 6 years.



6. For this problem use the historical price, volume and dividend data for the ETF SPY (Exchange Traded Fund for S&P 500) provided for the period 12/31/2007 to 12/31/2012. This data is downloaded from Yahoo Finance. Make sure that you clearly understand what each column of data represents, especially Adjusted Close (which is defined as close price adjusted for dividends and splits). Do not download the data from Yahoo; use what is provide in the data workbook for this problem.)

1) An investor invests $1,000 in SPY at the closing price of 12/31/2007. Assuming that he withdraws all dividends received and does not reinvest them, calculate the value of his investment at the end of each trading day through the end of 12/31/12. Calculate his annualized rate of return over the 5 year period.

2) Now assume that the investor reinvests in SPY any dividend he receives along the way. Reinvesting means that he uses any dividend he receives to buy additional shares of SPY at the closing price of SPY on the day he receives the dividend. Calculate the value of his investment at the end of each trading day through the end of 12/31/12 and then his annualized rate of return over the 5 year period.

3) Compare your results from (2) with the results you get if you use the adjusted close prices. Explain concisely why your results for (2) and (c) are the same or different


7. 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: (1) initial equipment installation date of 5/11/2006 and the date when you are doing your calculation is 11/7/2015 and (2) initial equipment installation date of 9/14/1996 and the date when you are doing your calculation is 3/5/2016.


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