financial modeling – Cheap Research Essays

financial modeling
Problem 1
In Model 4 of Chapter 15 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.)

Problem 2
Start with the input data provided for Model 16.1. 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.
Plot the nominal and real values of the portfolio in a chart similar to Figure 16.2.
Assume that the amount of quarterly dividend paid at the end of March equals dividends shown for Jan, Feb., and March and is reinvested on April 1 at the price shown for that day, and so on.

Problem 3
Using the data provided for Model 16.4, 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.
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.

READ ALSO : Examples of AntiTrust Law

Problem 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.
a) If you were to invest a lump sum today to cover her college costs, how much would you have to invest?
b) 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?
c) How much will you have to save today as the first installment if you were to save annually as in (b) but plan to save 3% more each year (relative to the year before)?
d) How will the answer for (c) 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.
e) Build a model for case (c) 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. (50 points)
Problem 6: Calculating growth of investment and returns earned on an investment
Download the daily price and volume data provided in Yahoo Finance for the ETF (Exchange Traded Fund) SPY for the period 12/31/2007 to 12/31/2012. Make sure that you clearly understand what each column of data represents. Use the Help at the top of the Yahoo Finance page if necessary.
(a) An investor invests $1,000 in SPY at the closing price of 12/31/2007. Assuming that he withdraws any 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.
(b) 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.
(c) Compare your results from (b) with the results you get if you use the adjusted close prices. Explain concisely why your results for (b) and (c) are the same or different
In your submission show the results for only the first and last few dates of the period and hide the other dates so that you can present your results in one page (using a readable font size.)
Problem 7: 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 at least two different models using different approaches to calculate how much fine, if any, has accrued on a particular date for a business given the initial installation date for the equipment. (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 30 days or 31 days 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 earlier) 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.
Show your results using initial equipment installation date of 5/11/2006 and the date when you are doing your calculation in 11/7/2015. Include some details (formulas, descriptions or whatever you want) so that a user can understand how your models do the calculations and that you have created two distinct models.

Planning for Retirement in Nominal Dollars Chapter Model 15

Return on fixed income 6%
Return on stocks 12%
Tax rate (oridnary) 25%
Tax rate (cap. gains) 15%
Inflation rate 3%
Initial balance $1,300,000
Initial annual withdrawal $65,000
Security horizon (years) 15

Year Total Bal. FI Holding After-tax Stock Hold. Annual Int Income Return on Stock Hold. FI Holding Total Bal. Tax basis Basis CG Tax Check on
Year-beg. Year-beg Stock Sales Year-beg Withdrawal After Tax Stock Year-end Year-end Year-end of Stock Used Paid Tax paid

1 $1,300,000 $817,873 $417,127 $65,000 $36,804 $50,055 $467,182 $854,677 $1,321,860 417,127
2 $1,320,966 $842,409 ($54,682) $411,607 $66,950 $37,908 $49,393 $461,000 $880,317 $1,341,318 367,507 49,621 $893 $893
3 $1,339,551 $867,681 ($56,322) $402,911 $68,959 $39,046 $48,349 $451,260 $906,727 $1,357,987 321,198 46,309 $1,767 $1,767
4 $1,355,366 $893,712 ($58,012) $390,627 $71,027 $40,217 $46,875 $437,502 $933,929 $1,371,431 278,040 43,158 $2,621 $2,621
5 $1,367,975 $920,523 ($59,752) $374,294 $73,158 $41,424 $44,915 $419,209 $961,947 $1,381,156 237,870 40,170 $3,456 $3,456
6 $1,376,885 $948,139 ($61,545) $353,394 $75,353 $42,666 $42,407 $395,801 $990,805 $1,386,606 200,525 37,345 $4,271 $4,271
7 $1,381,539 $976,583 ($63,391) $327,343 $77,613 $43,946 $39,281 $366,624 $1,020,529 $1,387,154 165,842 34,683 $5,066 $5,066
8 $1,381,310 $1,005,880 ($65,293) $295,488 $79,942 $45,265 $35,459 $330,946 $1,051,145 $1,382,091 133,664 32,179 $5,844 $5,844
9 $1,375,487 $1,036,057 ($67,252) $257,090 $82,340 $46,623 $30,851 $287,941 $1,082,679 $1,370,620 103,834 29,829 $6,604 $6,604
10 $1,363,272 $1,067,138 ($69,269) $211,323 $84,810 $48,021 $25,359 $236,682 $1,115,160 $1,351,842 76,205 27,629 $7,348 $7,348
11 $1,343,764 $1,099,153 ($71,347) $157,257 $87,355 $49,462 $18,871 $176,128 $1,148,615 $1,324,742 50,633 25,573 $8,078 $8,078
12 $1,315,948 $1,132,127 ($73,488) $93,846 $89,975 $50,946 $11,261 $105,107 $1,183,073 $1,288,180 26,978 23,654 $8,794 $8,794
13 $1,278,681 $1,166,091 ($75,693) $19,916 $92,674 $52,474 $2,390 $22,306 $1,218,565 $1,240,871 5,112 21,866 $9,499 $9,499
14 $1,230,678 $1,201,074 ($77,963) -$65,851 $95,455 $54,048 ($7,902) ($73,753) $1,255,122 $1,181,369 -15,091 20,203 $10,193 $10,193
15 $1,170,491 $1,237,106 ($80,302) -$164,933 $98,318 $55,670 ($19,792) ($184,725) $1,292,776 $1,108,050 -33,749 18,657 $10,879 $10,879
16 $1,096,493 $1,274,219 ($82,711) -$278,994 $101,268 $57,340 ($33,479) ($312,473) $1,331,559 $1,019,086 -50,971 17,222 $11,557 $11,557
17 $1,006,857 $1,312,446 ($85,193) -$409,895 $104,306 $59,060 ($49,187) ($459,082) $1,371,506 $912,423 -66,863 15,892 $12,230 $12,230
18 $899,525 $1,351,819 ($87,748) -$559,729 $107,435 $60,832 ($67,167) ($626,897) $1,412,651 $785,754 -81,521 14,659 $12,898 $12,898
19 $772,190 $1,392,374 ($90,381) -$730,842 $110,658 $62,657 ($87,701) ($818,543) $1,455,030 $636,488 -95,038 13,517 $13,564 $13,564
20 $622,259 $1,434,145 ($93,092) -$925,864 $113,978 $64,537 ($111,104) ($1,036,968) $1,498,681 $461,714 -107,499 12,461 $14,229 $14,229
21 $446,819 $1,477,169 ($95,885) -$1,147,747 $117,397 $66,473 ($137,730) ($1,285,477) $1,543,642 $258,165 -118,983 11,484 $14,894 $14,894
22 $242,604 $1,521,484 ($98,762) -$1,399,799 $120,919 $68,467 ($167,976) ($1,567,775) $1,589,951 $22,176 -129,565 10,582 $15,561 $15,561
23 $5,945 $1,567,129 ($101,724) -$1,685,731 $124,547 $70,521 ($202,288) ($1,888,019) $1,637,650 ($250,369) -139,313 9,748 $16,231 $16,231
24 ($267,274) $1,614,143 ($104,776) -$2,009,700 $128,283 $72,636 ($241,164) ($2,250,864) $1,686,779 ($564,085) -148,292 8,979 $16,905 $16,905