see attach files
MGF402 S20 WU 1 Instruction for Homework Assignment (II) Question (1) Form optimal portfolio with your-choice of two stocks (Step One) Follow this instruction carefully to prepare data in a spreadsheet ready to form portfolio. Select two US publicly-traded companies of your choice that have trading data since 2012 and download the stock prices and the dividends separately for a seven-year period. Use the Excel template in the same folder and calculate holding period returns, arithmetic average return, variance and standard deviation for each stock. 1. Go to https://finance.yahoo.com, type company name, click “Historical Data”. It’s ok if you collect stock and dividends data from other resources. Make sure stock prices you’ve downloaded are not adjusted to stock split or dividends. 2. Select TWO companies that have NOT split their stocks during the past 7 years. • Select Time Period: “12/31/2012-12/31/2019” • Use “Stock Split” and “Dividends Only” to check stock spilt or dividends information. 3. Download stock price • Time Period: “12/31/2012-12/31/2019” • Show: “ Historical Prices” • Frequency: “ Daily” • “Apply” then “Download Data” • Save it as .xls or .xlsx • Use the year-end stock price (12/31/20xx or 12/30/20xx) • Use “Close Price” • Include the raw stock data in Sheet1 and Sheet2. 4. Download dividend data • Time Period: “12/31/2012-12/31/2019” • Show: “ Dividends Only” • “Apply” then “Download Data” • Save it as .xls or .xlsx • Sum up the dividends over the same calendar year • Please include the raw dividends data in the same sheet. 5. You can ONLY pick one stock without dividends history. https://finance.yahoo.com/ MGF402 S20 WU 2 6. Use the Excel template and arrange data in the following format 7. Calculate Holding Period Return Use the formula on the lecture note. ??????1 = ??????1 − ??????0 + ??????1 ??????0 Note: only HPR2013-HPR2019 8. Arrange the two stocks in the same spreadsheet side by side, and calculate the returns and risks for each stocks, and COV and CORR for the pair of stocks. Before you move on the next step, check the profiles of Return and STD. Does one dominate another one? For instance, does one stock have both higher returns and lower risk than another one? If so, I’d recommend you to find some other stock(s)…You will experience significant obstacles in next step if you insist… Year Date IBM DIV HPR 2012 12/31/2012 146.76 - 2013 12/31/2013 183.88 2.90 2014 12/31/2014 191.55 3.30 2015 12/31/2015 187.57 3.70 2016 12/31/2016 160.44 4.25 2017 12/31/2017 132.81 5.00 2018 12/31/2018 135.20 6.21 2019 12/31/2019 113.67 6.43 Year Date IBM DIV HPR MSFT DIV HPR 2012 12/31/2012 146.76 - 2013 12/31/2013 183.88 2.90 2014 12/31/2014 191.55 3.30 2015 12/31/2015 187.57 3.70 2016 12/31/2016 160.44 4.25 2017 12/31/2017 132.81 5.00 2018 12/31/2018 135.20 6.21 2019 12/31/2019 113.67 6.43 Arithmetic average return Variance Standard Deviation Covariance Correlation Dividends: Sum up the dividends paid out over the same calendar year. The TICKER of the stock you pick MGF402 S20 Wu 1 Homework Assignment (II) -- Excel Work You are required to complete this homework assignment on Excel spreadsheet and upload your work on UBlearns by due. This homework demands strong skills with Excel, skills analyze data and perform simple modelling. Please use the excel template in this folder. Before you upload, rename the file as [LASTNAME1_LASTNAME2.xlxs] Q1 (50 points): Portfolio Construction Before you start first question, make sure you’ve read the Instruction.pdf, follow the guidance, collect and arrange the data. Follow the instructions carefully and organize your answer in Excel in a clear way. 1. Rename sheet1 the TICKER of your first stock, and rename sheet 2 the TICKER of your second stock. Provide raw stock price and the dividend data of the two stocks over the 7-year window. You can calculate annual dividends in these two sheets. I will verify your results. 2. (30 points) Use sheet “Portfolio”, provide the work from Instruction (I) and construct portfolio using the two stocks by varying weights from -20% to 120% in increment of 10%. Assume short- sale is allowed. You need to provide information of the portfolios including w1, w2, E(Rp), Variance, Standard deviation, and Sharpe Ratio. All cells highlighted should be referred and linked with functions. 3. (5 points) Use X-Y scatter chart to draw the investment opportunity set in a risk-return diagram in “Ivst Opp Set” where Standard Deviation in the X-axis and Expected Returns in the Y-axis. 4. (5 points) Construct Minimum-variance portfolio • Use Solver to construct the minimum-variable portfolio in an additional row. • Mark this portfolio in the “Ivst Opp Set” diagram. • What are the expected return, standard deviation and Sharpe ratio of your minimum- variance portfolio? 5. (10 points) Construct Optimal risky portfolio • Use Solver to construct the optimal risky portfolio in an additional row. • Mark this portfolio in the “Ivst Opp Set” diagram. • What are the expected return, standard deviation and Sharpe ratio of the optimal risky portfolio? • Draw a Capital Allocation Line in sheet “Ivst Opp Set” that connects risk-free rate (assume 3%) to the optimal risky portfolio (CALo). You may extend the CAL to allow short-sale. Do NOT manually insert a straight line. • What is the equation of this CALo? Please type the equation. MGF402 S20 Wu 2 Q2 (15 points): CAPM Apply CAPM and value Amazon (AMZN) and JC Penny (JCP) using the monthly stock prices during the past 10-years. 1. Calculate monthly holding period returns and return premium for both stocks and the market portfolio. Then calculate the return premium (Column F,G,H,J,K,L and Row 123). 2. Estimate Beta of each company and Expected Returns E(Ri) suggested by CAPM. Calculate investor’s abnormal returns. 3. For each stock, compare investor’s realized returns with expected returns and make fair assessment, including investment suggestion. Q3 (35 points): Bond Portfolio Tim is a recent retiree who is interested in investing some of his savings in corporate bonds. His financial planner has suggested the following bonds. Bond A Bond B Bond C Coupon rate 7% 10% 12% Maturity (Yrs) 15 15 15 Payment frequency 1 1 1 Redemption value $ 1,000 $ 1,000 $ 1,000 Yield to Maturity 10% 10% 10% 1. Calculate the price, Macaulay duration, modified duration, and convexity of each of the three bonds, and determine the type of each bond (use the Drop down list). You may verify your answer using the excel function, but there is no need to report. 2. Calculate the current yield for each bonds. 3. If the yield to maturity stays at 10%, what will be the price of each bond 1 year from now (you may use the PRICE function)? What is the expected capital gains/loss yield for each bond? What is the expected holding period return over one year for each bond? 4. Determine the relationship among coupon rate, YTM, and current yield for Bond A and Bond C (use the Drop down list). 5. Determine the percentage changes in the bond price to a 25 basis-point (i.e. 0.25%) increase in the yield based on only duration. Which bond reacts the most to the interest rate fluctuation (use the Drop down list)? 6. Determine the percentage changes in the bond price to 150 basis points (i.e. 1.50%) decrease in yield based on duration and convexity? 7. Suppose investor considers to hold one 6% yield zero coupon bond with 6 year maturity and with $1,000 par value in addition to each of the three bonds. Calculate the portfolio duration.