In this exercise you will familiarize yourself with index models, beta and CAPM estimation. Download the spreadsheet data_question3.xlsx from Sakai and use the data contained therein to answer Question 3. Spreadsheet data_question3.xlsx contains monthly stock returns for AT&T, Ford, Google and Exxon Mobile. Additionally, it contains monthly returns on Treasury securities and a broad market index. Be careful to calculate monthly excess returns over Treasuries as this is what you will need for estimating single index models.

a)  In class we have discussed how index models can be  used to separate firm-specific risk and market risk inherent in a firm's expected returns. What is the analytical formula to separate a security's risk into market risk and firm specific risk?
 
b)  Using excess returns over Treasuries, estimate the beta coefficients for AT&T, Ford, Google and Exxon Mobile without running a regression model. Additionally, using your result from part (a) estimate the  standard deviation for each firm's expected return and estimate each  firm's market risk component and firm-specific risk component. (Again use excess returns over Treasuries) Use the Excel functions STDEV.S and COVARIANCE.S for this exercise. Tabulate your results in the write-up as follows:

c)  Describe and interpret your results in the single-index model sense. i.e. how each stock moves with the overall market and which stock is the riskiest for a diversified investor? How does beta relate to the market risk component and how does it relate to the firm specific component?
 
d)  Suppose you want to construct a portfolio consisting of AT&T, Ford, Google and Exxon that exhibits minimal movement with the overall market, i.e.  = 0. Using Microsoft Excel Solver calculate the weights such that  is minimized under the constraint

β = 0. (Use excess returns over Treasuries for your calculations). Break  up into market risk and firm-specific risk. Would you have eliminated all risk by only holding this portfolio of stocks?

Posted Date: 2/28/2013 6:02:53 AM | Location : United States

Ask an Expert

Related Discussions:- Calculate monthly excess returns, Assignment Help, Ask Question on Calculate monthly excess returns, Get Answer, Expert's Help, Calculate monthly excess returns Discussions

Write discussion on Calculate monthly excess returns
Your posts are moderated

Write your message here..

Related Questions

Cash cycle, The Cash Cycle: so as to deal with the problem of cash managem... The Cash Cycle: so as to deal with the problem of cash management we should have a concept about the flow of cash by a firm's accounts. The entire process of such cash flow is ide

Show the objective of accounting standards, A sound foundation is necessary... A sound foundation is necessary for success in any task from building a house to putting on make up. In terms of U.S Accounting standards it is necessary to have a sound foundatio

Determine the ratio of the sales, 1) Jodie received a $2000 college entranc... 1) Jodie received a $2000 college entrance scholarship. Nine month later Brian was awarded a $2100 academic proficiency scholarship for his outstanding grades in the first year of

Calculate the annual profit from the farm , Xander Harris is considering wh... Xander Harris is considering whether to buy a corn and soybean farm in Iowa. The farm will cost $800,000, and Xander will be able to pay this from profits his recently deceased mot

Commodities to stock, Commodities to Stock Employ Material Req... Commodities to Stock Employ Material Requirement Planning From the Master Production Schedule the manager has determined such the products to be produced. A

Tax depreciation affect the cash flows statement?, What type of activity co... What type of activity could a company engage in to improve their cash flows in their Cash Flows Statement? Is this ethical? Could borrowing money make the cash from operations be

Example of job order costing, Example of Job Order Costing The giv... Example of Job Order Costing The given transactions were made by a company in the month of December. Direct Materials a) 8,000/- was bought on credit, out of these

Basic, sir i sir i

What is maximax and maximin approach, Build-Rite construction has received ... Build-Rite construction has received favorable publicity from guest appearances on a public TV home improvement program. Public TV programming decisions seem to be unpredictable, s

Credit sales method, What are the journal entries to recognize each of the ... What are the journal entries to recognize each of the below events. a.   The firm records bad debt expense of 5% of credit sales, which were $300.  The firm uses the Percentage