Financial Management Data Processing Project

Financial Management Assignment Part1. Data Processing This data processing is needed to answer the questions in Part 2. FM_ASGN_Data contains monthly prices of 10 stocks from January 2016 to December 2020 (5 years), the S&P 500, and the risk-free rate (1-month treasury rates).

- First, calculate monthly returns (excluding the risk-free rate) for each item, including the S&P 500 as the following Return(t) = [Price(t) – Price(t-1)] / Price(t-1)

- The risk-free rate (1-month treasury) is in annual percentage. Convert them into

monthly rates using Monthly rate = (1+Annual rate/100)1/12 – 1 Note: The risk-free rates during this period are very low. You will need to adjust the excel so it shows at least 4 decimals.

Assumptions/guidelines are as follows: 1. The S&P 500 is representative of the entire stock market. 2. Except for Q5, all answers should be in monthly returns or standard deviations. 3. These Excel functions will be helpful. Average: ‘=average(x1:x2)’ Standard deviation: ‘=stdev.s(x1:x2)’ Variance: ‘=var.s(x1:x2)’ Covariance: ‘=covariance.s(y1:y2,x1:x2)’ Correlation: ‘=correl(y1:y2,x1:x2)’

Beta: ‘=covariance.s(y1:y2,x1:x2)/var.s(y1:y2)’ where y is the market index and x is the individual stock

Part2. Questions Q1. From January 2016 to December 2020, what are the average returns on the risk-free treasury bill, the S&P 500, and each security? What is each asset’s volatility of returns? Q2. Calculate the monthly returns of a portfolio that invests equal amounts in each of the 10 individual securities. Is this a diversified portfolio? – i.e. how does the risk of the portfolio compare to those of individual securities? Q3. Plot the volatility-return relation. Is there a clear relation between risk and return? Q4. Which asset (including the S&P 500 and the equal-weighted portfolio in Q2) has the highest Sharpe ratio?

- Sharpe Ratio = Excess Return/Volatility = (average return – risk-free rate)/standard deviation

Q5. Convert the monthly returns and volatilities from Q1 into annual numbers.

- Annual return = (1+Monthly return)12 – 1 - Rough approximation of volatility annualization: SDyear = SDmonth*(12)1/2

Q6. Calculate the betas for each security, using the CAPM. Which stock has the highest beta? Which has the lowest beta? How does the beta relate to the industry the stock belongs to? Q7. Plot the beta and expected return relation. Does the relation fit the SML? What do you conclude about the validity of the CAPM?