EXCEL PROJECT AND EXCEL SOLUTION Consider the following stocks, all of which will paya liquidating dividend in a year and nothing in the interim:StockAStockBStockсStockDMarket Capitalization(5 million)800750950900Expected LiquidatingDividend ($ million)1000100010001000BetaPLEASE SHOW SOLUTIONS IN EXCEL0.771.461.251.07a. Calculate the expected return of each stock.b. What is the sign of correlation between the expected return and marketcapitalization of the stocks?In Problem 20, assume the risk-free rate is 3% and the market risk premium is 7%.a. What does the CAPM predict the expected return for each stock should be?b. Clearly, the CAPM predictions are not equal to the actual expected returns, so the CAPMdoes not hold. You decide to investigate this further. To see what kind of mistakes the CAPMis making, you decide to regress the actual expected return onto the expected returnpredicted by the CAPM.49 What is the intercept and slope coefficient of this regression?49. The Excel function SLOPE will produce the desired answersc. What are the residuals of the regression in part (b)? That is, for each stock compute thedifference between the actual expected return and the best-fitting line given by theintercept and slope coefficient in part (b).d. What is the sign of the correlation between the residuals you calculated in part (c) andmarket capitalization?e. What can you conclude from your answers to part (b) of the previous problem and part(d) of this problem about the relation between firm size (market capitalization) and returns?(The results do not depend on the particular numbers in this problem. You are welcome toverify this for yourself by redoing the problems with another value for the market riskpremium, and by picking the stock betas and market capitalizations randomly.50. The Excel command RAND will produce a random number between 0 and 1.

Note : Since you have posted too many questions, I am solving only first question with two…

Answered: EXCEL PROJECT AND EXCEL SOLUTION…

Essentials Of Investments

11th Edition

ISBN:9781260013924

Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Chapter1: Investments: Background And Issues

Section: Chapter Questions

Problem 1PS

See similar textbooks

Similar questions

Given the following data, what is the stocks expected growth rate according to the Gordon model? Dividend per share just paid: $4.5 Current market price: $40 Required rate of return: 15 Assume the stock is priced in equillibrium. Select one. a .0412 b. .0337 c. 0612 d. .0553
1. Considering the following information for stock K and stock M. Probability of state of economy State of economy Boom Normal Recession 0.3 0.5 0.2 Rate of return if state occurs K 25% 15% -12% M 18% 4% 0% 1) What is the expected return for stock K? For Stock M? 1) What is the standard deviation for Stock K" For stock M? iii) What is the coefficient of variation for Stock K? For stock M2 iv) If you invest 60% of your money in stock K and 40% in stock M, what is the expected return of the portfolio v) Find the return of your portfolio when a) economy is booming, b) economy is normal; and c) recession occurs vi) What is the standard deviation for your portfolio? (18 points in total)
Thecovarianceofthemarket'sreturnwithaStockA'sreturnis0.003andthestandarddeviation of the market's return is 0.05. Stock A's beta is ________. The risk free rate is 6% and the expected market return is 15%. Stock A's current price is $25 and will pay a $1 dividend at the end of the year. If the stock is priced at $30 at year-end, it is _______. a) 1.2; underpriced, so buy it. b) 1.5; underpriced, so buy it. c) I .2; overpriced, so sell it. d) 1.5; overpriced, so sell it. e) None of the above
The dividend-growth model may be used to value a stock: Round your answers to the nearest cent. a. What is the value of a stock if: Do = $2.30 k = 8% 9 = 5% V = Do(1+g) k-9 $ b. What is the value of this stock if the dividend is increased to $4.30 and the other variables remain constant? $ c. What is the value of this stock if the required return declines to 6 percent and the other variables remain constant? $ d. What is the value of this stock if the growth rate declines to 3 percent and the other variables remain constant? $ e. What is the value of this stock if the dividend is increased to $2.90, the growth rate declines to 3 percent, and the required return remains 8 percent? $
An analyst gathered the following information for a stock and market parameters: stock beta = 1.42; expected return on the Market = 11.41%; expected return on T-bills = 2.12%; current stock Price = $9.64; expected stock price in one year = $12.81; expected dividend payment next year = $1.54. Calculate the required return for this stock. Please share your answer as a percentage rounded to 2 decimal places.
the last question, please.
A small market consists of three stocks, A, B, and C., and their financial data and projection are presented below. Assume CAPM holds. Stock Number of shares Stock Price Beta A 750 33 1.2 B 220 51 0.8 C 450 19 1.4 Economic condition Probability A B C Boom 20% 20% 22% 23% Normal 50% 15% 10% 12% Recession 30% 2% 5% 7% Find: a) the expected return and standard deviation on this portfolio. b) the expected return on the market using portfolio beta if Rf=5%.
You expect the risk-free rate to be 4 percent and the market return to be 10 percent. You also have the following information about three stocks. Stock Beta Current Expected Expected Price Price Dividend U 1.5 $10 $11.50 $1.00 N 1.1 $27 $30 $0.00 Ο 0.8 $35 $36 $1.50 (Question 1 of 2) Based on the required rate of return estimated with the CAPM and your expected returns based on prices and dividends, select an investment strategy for each stock: Stock U Stock N [Choose ] [Choose ] Stock O [Choose ]
Consider an example. Assume a share of preferred stock with the following characteristics: Par value $100 Dividend rate 3.0% per year Payment schedule semiannual Maturity date You are analyzing this preferred stock for possible purchase. Your required rate of return on this stock is 5% per year, compounded semiannually. Draw a time line showing the expected dividends for this preferred stock. Calculate the value of this preferred stock based on the required rate of return. Assume that the current market price for this preferred stock is $75 per share. Calculate the expected return based on the market price. Should you invest in the stock? Why or why not? Be sure to use your results from BOTH parts B and C above. You are analyzing a share of XYZ…
An analyst gathered the following information for a stock and market parameters: stock beta= 1.08; • expected return on the Market = 11.97%; • expected return on T-bills = 1.55%; • current stock Price = $9.01; • expected stock price in one year = $11.14; • expected dividend payment next year = $3.23. Calculate the expected return for this stock. Please share your answer as a percentage rounded to 2 decimal places.
1
An analyst gathered the following information for a stock and market parameters: stock beta = 1.23; expected return on the Market = 9.32%; expected return on T-bills = 4.75%; current stock Price = $9.08; expected stock price in one year = $13.1; expected dividend payment next year = $3.8. Calculate the expected return for this stock. Please share your answer as a percentage rounded to 2 decimal places.

SEE MORE QUESTIONS

Recommended textbooks for you

Essentials Of Investments

Finance

ISBN:

9781260013924

Author:

Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:

Mcgraw-hill Education,

FUNDAMENTALS OF CORPORATE FINANCE

Finance

ISBN:

9781260013962

Author:

BREALEY

Publisher:

RENT MCG

Financial Management: Theory & Practice

Finance

ISBN:

9781337909730

Author:

Brigham

Publisher:

Cengage

Essentials Of Investments

Finance

ISBN:

9781260013924

Author:

Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:

Mcgraw-hill Education,

FUNDAMENTALS OF CORPORATE FINANCE

Finance

ISBN:

9781260013962

Author:

BREALEY

Publisher:

RENT MCG

Financial Management: Theory & Practice

Finance

ISBN:

9781337909730

Author:

Brigham

Publisher:

Cengage

Foundations Of Finance

Finance

ISBN:

9780134897264

Author:

KEOWN, Arthur J., Martin, John D., PETTY, J. William

Publisher:

Pearson,

Fundamentals of Financial Management (MindTap Cou…

Finance

ISBN:

9781337395250

Author:

Eugene F. Brigham, Joel F. Houston

Publisher:

Cengage Learning

Corporate Finance (The Mcgraw-hill/Irwin Series i…

Finance

ISBN:

9780077861759

Author:

Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor

Publisher:

McGraw-Hill Education

SEE MORE TEXTBOOKS