FIN 5130 Mini-case 1 Real-world Application: Risk and Return Dr. Darshana Palkar
Page 1 of 3
This assignment will require you to analyze time series of monthly returns of different securities.
[5 points] 1) Pick FOUR companies as follows from Yahoo! Finance website:
i. For each company of your choice, go to Yahoo! Finance website, enter the
ticker symbol in “Quote Lookup” box. Then, click on “Profile” and identify the
Sector to which the company belong.
ii. The companies must belong to a different Sector.
iii. Enter the name of all companies you selected, along with their ticker symbol
and sectors, under “Mini-case 1 (State names of selected companies)” Forum.
• You must pick companies that are not already selected and posted by your
classmates “Mini-case 1 (State names of selected companies)” Forum. If you pick
companies already selected by your classmates, then you will not score any points
for your Mini-case, and you may have to re-do your Mini-case 1 all over again with
new companies not selected by your classmates.
• If your chosen company went public after January 2021, you would not have price
data from the entire period of the Mini-case analysis. In that case, simply select
another company and update your choice under “Mini-case 1 (State names of
selected companies)” Forum.
[5 points] 2) Download the MONTHLY price data for the 01/01/2021 – 12/31/2025 period for
each company.
Yahoo! Finance has locked the Download file button as of 09/06/2024. It is no
longer available for free. I have created a video showing how we can still download
the price data for free from Yahoo! Finance website. The video to the link is
provided below:
Remember to enter the start date as 09/01/2020 and the end date as 08/31/2025.
Beyond this step, all values should be cell referenced.
[15 points] 3) Calculate the monthly return for all four companies.
Formula:
Return for each month = (Current Adj Close Price- Previous Adj Close Price/Previous
Adj Close Price
• Use the above formula to obtain the monthly return. The “Adj Close Price” in
Yahoo! Finance website is already adjusted for dividends and stock splits so we do
not need to include dividends again in the formula.
• Think about this: what do we do about the return for the first month?
FIN 5130 Mini-case 1 Real-world Application: Risk and Return Dr. Darshana Palkar
Page 2 of 3
[15 points] 4) Calculate and prepare a summary table as shown below.
Company 1 Company 2 Company 3 Company 4
Average arithmetic
monthly return
Average geometric
monthly return
Variance of
monthly returns
Standard deviation
of monthly returns
• Reminder: make sure all values are cell referenced.
[20 points] 5) Describe your findings from Question #4. As a financial analyst, we need to be able
to explain our work. Be precise in your responses.
i. Which company has the highest/lowest return over the period? In your
explanation, also describe what arithmetic and geometric return measure and
how they are different from each other.
ii. Which company has the lowest/highest risk? In your explanation, also state
your measure of risk.
iii. Out of your four companies and based on your results, which stock would you
prefer to hold and why? Explain clearly.
Some hints to frame your answer: We need to look at both risk and return
dimensions to arrive at our decision. Among the four stocks, say if there is a
stock that provided the highest return for the lowest risk, then it performed
the best (clear winner). On the other hand, if a stock provided the lowest
return for the highest risk, then it performed the worst (clear loser) – you
are better off holding other stocks. Or you may see a pattern where riskier
stocks yielded higher returns and less risky stocks yielded lower returns. In
this case, the decision depends on the investor’s attitude towards risk – a
risk lover would prefer to hold the risky stock with the expectation of
earning higher returns, whereas a risk averse investor would go with the
least risky stock.
[5 points] 6) Calculate the correlation of returns as follows and answer the following.
i. Correlation between Company 1 and Company 2.
ii. Correlation between Company 1 and Company 3.
iii. Correlation between Company 1 and Company 4.
iv. Correlation between Company 2 and Company 3.
v. Correlation between Company 2 and Company 4.
vi. Correlation between Company 3 and Company 4.
vii. What does the correlation indicate?
viii. From the above, which set of companies had the highest correlation?
ix. From the above, which set of companies had the lowest correlation?
FIN 5130 Mini-case 1 Real-world Application: Risk and Return Dr. Darshana Palkar
Page 3 of 3
For a similar problem, refer to Week 3 handout, Problem 7.
[10 points] 7) Using the SLOPE() function, calculate the beta for each of the four companies and
answer the following questions.
i. What does beta measure?
ii. List the companies whose beta is riskier than the market portfolio.
iii. List the companies whose beta is less risky than the market portfolio?
• Below is how you can get the monthly market portfolio return:
SPDR S&P 500 ETF Trust (ticker = SPY) closely tracks the S&P 500 index. So, we can
use SPY as our proxy for the market portfolio. So, first get the monthly price data
for the same 01/01/2021 – 12/31/2025 period for SPY. Then convert the monthly
price data into monthly return data using the same formula given in Part 3.
For a similar problem, refer to Week 3 handout, Problem 8.
[25 points] 8) Suppose you invest 25% in each company. Compute the following:
i. Calculate the portfolio arithmetic average return.
ii. Calculate the weighted average of the arithmetic average returns of Companies
A, B, C, and D? (w1 * Avg Ret1 + w2*Avg Ret2 + w3 * Avg Ret3 + w4*Avg Ret4)
iii. What do you observe for your answers for parts a) and b)?
iv. Using the STDEV() function, calculate the portfolio standard deviation.
v. Calculate the weighted average of the standard deviations of Companies A, B,
C, and D? (w1 * Stdev1 + w2*Stdev2 + w3 * Stdev3 + w4*Stdev4).
vi. If you have calculated correctly, you should find that your answer for part d) is
lower than your answer for part e). Why do you think the answer for part d) is
lower than the answer for part e)?
vii. Calculate the portfolio beta.
viii. Calculate the weighted average of the betas of Companies A, B, C, and D? (w1 *
Beta1 + w2*Beta2 + w3 * Beta3 + w4*Beta4)
ix. What do you observe for your answers for parts g) and h)?
x. Is your portfolio more or less risky than the market portfolio? How did you
determine that?
For a similar problem, refer to Week 3 handout, Problem 11.
Notes:
1. Make sure your work is well organized. 10% of the points will be deducted otherwise.
2. Use cell references and Excel calculations to avoid receiving a grade of zero.
3. You can provide your explanation in the same Excel file by using Text Box (In your Excel file, click
on Insert in the top panel and then click on Text Box).
