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 a
ange 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. A
ange the two stocks in the same spreadsheet side by side, and calculate the
eturns 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/ XXXXXXXXXX -
2013 12/31/ XXXXXXXXXX
2014 12/31/ XXXXXXXXXX
2015 12/31/ XXXXXXXXXX
2016 12/31/ XXXXXXXXXX
2017 12/31/ XXXXXXXXXX
2018 12/31/ XXXXXXXXXX
2019 12/31/ XXXXXXXXXX
Year Date IBM DIV HPR MSFT DIV HPR
2012 12/31/ XXXXXXXXXX -
2013 12/31/ XXXXXXXXXX
2014 12/31/ XXXXXXXXXX
2015 12/31/ XXXXXXXXXX
2016 12/31/ XXXXXXXXXX
2017 12/31/ XXXXXXXXXX
2018 12/31/ XXXXXXXXXX
2019 12/31/ XXXXXXXXXX
Arithmetic average return
Variance
Standard Deviation
Covariance
Co
elation
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,
ename 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 a
ange 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 refe
ed 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 XXXXXXXXXX
Payment frequency 1 1 1
Redemption value $ XXXXXXXXXX,000 $ XXXXXXXXXX,000 $ XXXXXXXXXX,000
Yield to Maturity 10% 10% 10%
1. Calculate the price, Macaulay duration, modified duration, and convexity of each of the three
onds, 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 cu
ent 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 cu
ent 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.