C5: Capital Asset Pricing

Calculation Problems

1. Please use the Regression function in Data Analysis ToolPak in Excel to find the beta of the stock you are tracking.

(Note: Steps to add in the Data Analysis Toolpak: File tab > Options > Add-ins > Excel Add-ins Go > choose Analysis ToolPak.)

Please calculate the beta using both daily and monthly returns and fill in the corresponding information in the tables below. If there is no Data Analysis ToolPak available in your Excel software, you can simply list the data series of daily and monthly returns of SP500 and your companys stock, and then upload your Excel file.

Company Name ____________

Beta based on daily returns

Number of Observations
R Square
	Coefficients	t Stat
Intercept (alpha)
beta

Beta based on monthly returns

Number of Observations
R Square
	Coefficients	t Stat
Intercept (alpha)
beta

2. Assume risk-free rate = 1% and market risk premium = 7%, based on the beta number you calculated using monthly returns, what is the expected return of the stock you are tracking?