Case Study Easy and Simple

FIN 5130 Mini-case 1Real-world Application: Risk and Return
Dr. Darshana Palkar
This assignment will require you to analyze time series of monthly returns for different securities.
[5 points]
1) Pick three companies as follows from Yahoo! Finance website:
i.
SPDR S&P 500 ETF Trust (ticker = SPY) as our proxy for the market portfolio
Note that ideally, we would use the S&P 500 Index (ticker=^GSPC) to proxy
for the market portfolio. However, since Yahoo! Finance has disabled the
download button on their website, we can use this S&P 500 ETF (SPY) which
benchmarks the S&P 500 Index.
ii.
Pick Company 1, a company of your choice and note the “Sector” and
“Industry” to which it belongs.
Hint: Go to Yahoo! Finance website, enter the ticker symbol under Quote
Lookup. Then, click on “Profile” to check the sector/industry it belongs to).
iii.
Pick Company 2 that belongs to a different sector or industry than Company 1.
Enter the name of the two companies you selected, along with their ticker
symbol and sectors, under “Mini-case 1 (State names of selected companies)”
Forum.
You need to pick companies not selected by your classmates.
[5 points]
2) Download the MONTHLY price data for the 01/01/2019 – 12/31/2023 period for the
three securities.
Instructions on how to download price data from Yahoo! Finance website:
i.
Go to Yahoo! Finance website, enter the security’s ticker symbol in “Quote
Lookup” box. Then, click on “Historical Data”. Enter “Time Period” as given
above. For “Frequency”, make sure “Monthly “is selected and then click on
“Apply”. Next, click on “Download”. A ticker.csv file will be downloaded.
ii.
Save your file as a .xls or .xlsx file before you start anything – very important
step! Comma delimited (.csv) files do not retain the formulae and cell
references after closing the file. You will receive a grade of zero if your excel file
does not contain cell references and formulae that show how you arrived at the
various answers.
iii.
You only need the “Date” and “Adj Close” columns. Copy over the “Date” and
“Adj Close” columns of the three securities into a single excel so you can do the
analysis. (Note: the “Adj Close” column in Yahoo! Finance has already adjusted
the prices for dividends and stock splits so you do not have to adjust for it
again.
[15 points]
3) Calculate the MONTHLY return data for the 01/01/2019 – 12/31/2023 period for
the selected three securities.
Note: Return for Month 2 = (Adj Close for Month 2/Adj Close for Month 1) – 1
Page 1 of 2
FIN 5130 Mini-case 1
Real-world Application: Risk and Return
Dr. Darshana Palkar
[15 points]
4) Calculate the average arithmetic monthly return, variance, and standard deviation
of returns for 01/01/2019 – 12/31/2023 period for the selected three securities.
Explain your findings so far based on the risk-return profile of each security.
[10 points]
5) Calculate the covariance and correlation of returns between Company A and
Company B for the 01/01/2019 – 12/31/2023 period.
[10 points]
6) Calculate the stock betas for Company A and Company B for the 01/01/2019 –
12/31/2023 period. What do the betas indicate?
Hint: Remember that we have used SPDR S&P 500 ETF Trust (ticker = SPY) as our
proxy for the market portfolio. What should go in the known-xs array and what
should go in the known-ys array?
[40 points]
7) Suppose you invest 50% in Company A and 50% in Company B. Answer the
following:
i.
What is your portfolio average return?
ii.
What is the weighted average of the standard deviations of Company A and
Company B? (w1 * Stdev1 + w2*Stdev2)
iii.
What is your portfolio standard deviation?
iv.
What is the reason for the difference between your answers for parts (ii) and
(iii)? In your explanation, discuss the role correlation coefficient plays in
diversification.
v.
What is your portfolio beta?
vi.
Is your portfolio more or less risky than the market portfolio? How did you
determine that?
Page 2 of 2