Reference no: EM132294174
Assignment - Stock Project
One of Financial Engineers Corp.'s personal clients is William Burns. William would like to invest $10,000 on July 1, 2019, and he intends to end his investment on June 30, 2022. William is deciding whether to buy stock in Amazon or in Duke Energy Corporation (a large investor-owned electric utility). He has asked Financial Engineers Corp. for advice which stock to purchase.
For the purpose of this project, you must recommend one or the other (or neither)-you cannot diversify the investment of this $10,000. Because William is adopting a value-based buy-and-hold strategy, if he buys stock in one of these companies on July 1, 2019, he will hold it for three years before selling all of the stock.
Historical Stock Prices -
You can use the following links to access Duke and Amazon. You can select "Historical Data" to see how closing stock prices were estimated, but do not attempt to determine them yourself.
You will want to use the "Historical Data" feature to determine historical dividends for Duke. Click on "Show: Dividends Only" to view the dividends and the dates. Also click Apply (see Figure 1). The number represents how much dividends were paid to shareholders per share.
|
Table 1 Historical stock prices
|
|
Date
|
AMZN
|
DUK
|
|
9/30/2015
|
511.89
|
71.94
|
|
12/31/2015
|
675.89
|
71.39
|
|
3/31/2016
|
593.64
|
80.68
|
|
6/30/2016
|
715.62
|
85.79
|
|
9/30/2016
|
837.31
|
80.04
|
|
12/31/2016
|
749.87
|
77.62
|
|
3/31/2017
|
886.54
|
82.01
|
|
6/30/2017
|
968.00
|
83.59
|
|
9/30/2017
|
961.35
|
83.92
|
|
12/31/2017
|
1169.47
|
84.11
|
|
3/31/2018
|
1447.34
|
77.47
|
|
6/30/2018
|
1699.80
|
79.08
|
|
9/30/2018
|
2003.00
|
80.02
|
|
12/31/2018
|
1501.97
|
86.30
|
|
3/31/2019
|
1780.75
|
90.00
|
|
6/30/2019
|
??
|
??
|
Table 1 depicts the historical stock closing prices for this project. You can see there are a total of 15 prices over 15 quarters. The estimated closing price on June 30, 2019 will be the purchase price of the stock on July 1 (which is quarter 0 in the Excel sheet template "Project 4 solutions.xlsx").
Estimating stock prices for Amazon -
The stock price for Amazon has uncertainty. We are going to assume that Amazon's stock price in one quarter obeys the following equation
Xt = Xt-1 * Zt
where Xt is the price of the stock at quarter t and Zt is a random variable that follows a lognormal distribution.
For each quarter 0 through 12 in spreadsheet you should simulate a lognormal random variable Zt in row 10. In the "Lognormal" dialog box, set the "50th %ile" equal to 1.035 and "0.9th %ile" equal to 1.3. This means there is a 50% probability the price of the stock will increase in the next quarter by 3.5% or less and there is a 90% probability the price of the stock will increase by 30% or less. Repeat this process for all 13 quarters (quarter 0 to 12). Each quarter should have a different lognormal random variable. There should NOT be the same values in each quarter!
The stock price in a quarter is the stock price in the previous quarter multiplied by the lognormal random variable. To calculate the stock price in quarter 0, multiply the stock price on March 31, 2019 (which is $1780.75 from Table 1) by the lognormal random variable. For example, if the lognormal random variable for quarter 0 equals 1.16, then the price in quarter 0 equals $1780.75*1.16 = $2065.51.
The stock price in quarter 0 equals the buying price. It should automatically change in Excel cell B7 based on the calculation in cell D11. The number of shares that William will purchase will be based on the buying price assuming the entire amount of $10,000 is used to purchase Amazon stock. It is acceptable to purchase fractions of shares of stock.
Repeat the process of calculating the stock price in each quarter for the next 12 quarters by multiplying the price in the previous quarter by the lognormal random variable. At the end of quarter 12, William will sell this stock at the price in quarter 12.
Although you are calculating the stock price for each quarter, there is 0 cash flow in quarters 1 through 11 because William is not selling or buying additional stocks. There are no dividends for Amazon.
Estimating stock prices for Duke -
The stock price for Duke has uncertainty but the randomness follows a different formula than for Amazon's stock. We are going to assume that Duke's stock price in one quarter obeys the following equation
Xt = Xt-1 + Yt
where Xt is the price of the stock at quarter t and Yt is a random variable that follows a normal distribution.
For each quarter 0 through 12 in spreadsheet you should simulate a normal random variable Yt in row 20. To calculate the mean of the random variable Yt, calculate the differences in Duke's opening prices between adjacent quarters. For example, the difference between 12/31/2015 and 9/30/2015 is 71.39-71.94 = -0.55, and the difference between 3/31/2016 and 12/31/2015 is 80.68-71.39 = 9.29. There should be a total of 14 differences, and use the average of those differences as the mean of the random variable Yt. Assume the standard deviation of the normal random variable Yt is 1.25.
Each of the 13 quarters should have a different normal random variable. There should NOT be the same values in each quarter!
Duke's stock price in a quarter is the stock price in the previous quarter added to the normal random variable. To calculate the stock price in quarter 0, add the stock price on March 31, 2019 (which is $90.00 from Table 1) to the normal random variable. For example, if the normal random variable for quarter 0 equals 1.32, then the price in quarter 0 equals $90.00+1.32 = $91.32.
The stock price in quarter 0 equals the buying price. It should automatically change in Excel cell B17 based on the calculation in cell D21. The number of shares bought is based on the buying price assuming the entire amount of $10,000 is used to purchase Duke Stock. It is acceptable to purchase fractions of shares of stock.
Repeat the process of calculating the stock price in each quarter for the next 12 quarters by adding the price in the previous quarter by the normal random variable. At the end of quarter 12, William will sell this stock at the price in quarter 12.
Dividend information -
Dividends will come into play for Duke stock. You will see from historical data that dividends often occur quarterly, and that their value often remains the same for 4 quarters before going up. For evaluating the future cash flows, always assume that dividends remain constant for 4 consecutive quarters. Assume the dividends will rise by $0.03 after remaining constant for 4 consecutive quarters. This is close to the historical change. Assume the dividends on November 15, 2018 and August 16, 2018 were 0.928 (rounded up from 0.9275).
Assume all future dividends will be paid at the end of the quarter. For example, if a dividend is actually paid on Feb 14, 2019, you can assume that dividend occurs on March 31, 2019.
Dividends are paid on a per share basis. For example, if dividends for a quarter are $0.40 and William has 100 shares of stock, total dividends for that quarter would be $40. Treat dividends as income at the end of each quarter, and calculate taxes on those dividends for each quarter at the applicable tax rate.
William will not receive a dividend in quarter 0 although you should consider the dividend in quarter 0 as 1 of the 4 consecutive quarters. William will receive a dividend in quarter 12 if he purchases Duke Stock.
Tax information -
When an individual sells a stock, the individual pays taxes on gains only. For example, if William buys stocks for $10,000 and sells them for $10,000, there is no tax. If he sells the stocks for less than $10,000, there will be a tax refund.
For individuals, long-term capital gains and qualified dividends (those received after one year) are taxed at 15%. For the first year though (i.e., July 1, 2019 through June 30, 2020), they are taxed as ordinary income at 24%. For the second and third years, they are taxed at 15%. When William sells the stock at the end of quarter 12, the gains will be taxed at a rate of 15%.
The state of Iowa income tax rate is 8.98%. The state of Iowa does not provide any dividend or capital gains tax break. The state of Iowa does allow an individual to deduct what he or she pays in federal income taxes. Thus, the total amount paid in taxes = income * federal tax rate + income * (1-federal tax rate) * state tax rate. Or, you can use the combined rate formula covered in class, as they provide the same answer. Refer to chapter 9 of how to calculate the combined tax rate.
Compute taxes and dividends every quarter. It makes things much easier to do so. If you have a stock that is projected to receive dividends in all 12 quarters, then all 12 columns will have values contained in the "NET" row.
Present worth analysis -
All dollar amounts are given in actual dollars here. The investment, dividends, taxes, and projected prices are based on actual dollars.
Assume the average annual (general) inflation rate is 1.5% and will remain at 1.5% for the next 3 years.
William Burn's inflation-free MARR' (or inflation-free interest rate i') is 4%.
To conduct present worth analysis, use the annual market interest rate i based on the inflation-free interest rate (MARR') and the inflation rate. To calculate the discounted cash flow for both stocks, use the following formula
Pt = At/(1+i)t⁄4
where At is the cash flow that occurs in t quarters after quarter 0 and Pt is the discounted (present-value) cash flow. Quarter 0 is July 1, 2019. Use the discounted cash flows to compute the net present worth for each stock. The net present worth in cells S15 and S16 are random and should be the output cells for SIPMath.
Deliverable -
You should calculate the net present worth for the Amazon stock and the Duke stock. Conduct 100,000 trials in SIPMath. Use the Excel sheet "Project 4.xlsx" for help with the calculation. You should concentrate of filling in the yellow-shaded cells.
You should write a professional memorandum (memo) providing William Burns with your financial analysis for each stock. The Excel sheet contains suggested metrics for the results of the financial analysis. See the attached Word document "Writing a professional memo.docx" for expectations and guidelines on writing a professional memo.
The memo should contain 4-5 paragraphs. The first paragraph should be an executive summary that summarizes your analysis. One paragraph should discuss the financial analysis of purchasing the Amazon stock, and the other paragraph should discuss the financial analysis of purchasing the Duke stock. Each paragraph should provide the advantages and disadvantages of purchasing that particular stock.
You may also want to include graphs (e.g., histograms, cumulative density plots) in your memo to illustrate the uncertainty in the net present worth of each stock. You can generate these plots by clicking on "Graphs" in the SIPmath Modeler Tools toolbar. In order to generate these plots, the cell must be defined as an Output cell. The number of bins for the histogram should be set to 100. You should also spend some time formatting the plot (e.g., adding axis titles, changing the chart title, making sure the font on the graph is large enough) so that it looks professional and readable in the memo.
The final one or two paragraphs should briefly compare between Amazon and Duke Stock. You should explain which stock William should purchase if he does not want to take on a lot of risk (e.g., risk averse) and which stock William should purchase if he is willing to take more risk. Finally, you should make a recommendation of which stock is better and on what basis you are making your recommendation. There is not a single correct answer, and either stock could be recommended.
Attachment:- Assignment Files.rar