Excel Assignment 1

This assignment is a practice using excel (or other software or programming language of your choice) to examine the empirical distribution of a stock’s daily return, as well as construct the security characteristics line.

 

What to submit?

Excel file: this should include your original data and calculations using the data.

 

Part I: return distribution

Choose a US listed stock to examine its return distribution.

 

Step 1: collecting data. Download the daily adjusted closing price for the past 5 years (or any in-sample period you choose) from finance.yahoo.com.

 

Step 2: calculate daily return for the stock using holding period return and log return. Then calculate statistics for the returns: mean, variance, standard deviation, covariances, correlation coefficients, (optional: skewness, and kurtosis). Draw histograms for the returns.

 

Step 3: Which of the return (HPR or log return) has a distribution closer to a Normal Distribution.

 

Part II: estimate CAPM beta

Step 1: Collecting data. You can use the same stocks you have chosen for Part I. Download the Monthly adjusted closing price the past 6 years (5 years in-sample period, 1-year out-of-sample period). Download your choice of market portfolio (index) monthly closing price for the same sample period. Download the 1-month Treasury Bill rate (need to divide the raw data by 1200) for the same sample period plus a month ahead (available monthly from https://fred.stlouisfed.org/series/TB4WK).

 

Step 2: calculate monthly return for each of the risky securities and market index. Then calculate excess returns = return – risk-free rate.

 

Step 3: Security Characteristics Line SCL regressions for each stock using 5 years of in-sample period data to estimate the beta of the stock. Show the regression results and SCL graph.

 

(Optional) Step 4. Use the beta estimate on out-of-sample data to calculate abnormal return for each month in the out of sample period. Calculate the mean of the abnormal return (which is alpha) and standard deviation of the abnormal return. Perform t-test to see if alpha is statistically significant from zero.