FAN

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: https://sharkmedia.nova.edu/id/1_ijsf3n57 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).