ZuoTi.Pro
Question

NEED EXCEL SOLUTION PLEASE: (EXCEL FORMULA) Suppose we have the following returns for the market and...

NEED EXCEL SOLUTION PLEASE: (EXCEL FORMULA)

Suppose we have the following returns for the market and a stock:
Year Market return Stock return
1 18% 7%
2 27% 25%
3 5% 21%
4 13% 4%
5 -17% -16%
6 6% 19%
7 -21% -38%
8 34% 29%
9 19% 15%
10 11% 16%

What is the covariance and correlation of the returns between this stock and the market?
Covariance: Use Covar Function
Correlation: Use Correl Function
Beta: Use Slope Function

Please provide solutions for the above: Covariance, Correlation, and Beta using the listed functions (EXCEL Solution)

business   finance   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Answer:

Calculation of Correlation, and Beta using the listed functions (EXCEL Solution):

Suppose we have the following returns for the market and a stock Stock return Market return 18% 27% 5% 13% 17% 5% 21% 34% 19% 11% Year 25% 21% 16% 19% 38% 20% 15% 16% 10 What is the covariance and correlation of the returns between this stock and the market? Covariance: Correlation: Beta 0.0281 0.864774 1.022376

The above excel with 'show formula' for the formula used:

1 Suppose we have the following returns for the market and a stock: 2 Year Market return Stock return 0.18 0.27 0.05 0.13 0.17 0.06 0.21 0.34 0.19 0.11 0.07 0.25 0.21 0.04 0.16 0.19 0.38 0.29 0.15 0.16 4 2 10 8 11 9 12 10 13 What is the covariance and correlation of the returns between this stock and the market? 14 Covariance: 15 Correlation: 16 Beta: 17 CORREL(B3:B12,C3:C12) SLOPE(C3:C12,B3:B12)

0 0
Add a comment
Know the answer?
Add Answer to:
NEED EXCEL SOLUTION PLEASE: (EXCEL FORMULA) Suppose we have the following returns for the market and...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • You are given the following information concerning a stock and the market Returns Market Stock Year...

    You are given the following information concerning a stock and the market Returns Market Stock Year 15% 27% 19 2011 2012 19 2013 25 5 2014 -12 -22 2015 35 16 2016 15 27 a. Calculate the average return and standard deviation for the market and the stock. (Use Excel to complete the problem. Do not round intermediate calculations. Enter your answers as a percent rounded to 2 decimal places.) Market Stock % Average return Standard deviation b. Calculate the...

  • no excel work. please provide formula. You have done your research for the following investments and...

    no excel work. please provide formula. You have done your research for the following investments and your friend has provided their expectations for the markets for next year. Probability of State of State of Stock Stock Economy Economy A B TSX Boom .30 30% -9% 18% Normal .40 16% 12% 10% Recession .30 -10% 20% -10% Remember to show all of your work * e. Calculate the risk for stock B. (2 marks) f. Calculate the risk for the TSX....

  • no excel work, show formula please You have done your research for the following investments and...

    no excel work, show formula please You have done your research for the following investments and your friend has provided their expectations for the markets for next year. Probability of State of State of Stock Stock Economy Economy A B TSX Boom .30 30% -9% 18% Normal .40 16% 12% 10% Recession .30 -10% 20% -10% Remember to show all of your work * h. Calculate the covariance and correlation of the returns for stock A and the TSX. (2...

  • Consider the following time-series observations on realized returns on the stocks of company A, and company...

    Consider the following time-series observations on realized returns on the stocks of company A, and company B, as well as the returns on the Market portfolio I'm respectively. Table 1: Realized percentage returns on alternative assets market portfolio stock A stock B I'm TA TB 5% - 2% -3% 7% 1% 2% -15% -50% -1.5% 12% 1.5% 2% -3% 2.5% 1.7% 5% - 1% 2.5% 6% 2.5% -1.5% --2% 1 -0.5% 5% 1. Compute the mean and the standard deviation...

  • You are given the following information concerning a stock and the market: Returns Year Market Stock...

    You are given the following information concerning a stock and the market: Returns Year Market Stock 2011 15 % 27 % 2012 14 30 2013 15 6 2014 −14 −24 2015 37 16 2016 15 25 a. Calculate the average return and standard deviation for the market and the stock. (Use Excel to complete the problem. Do not round intermediate calculations. Enter your answers as a percent rounded to 2 decimal places.) b. Calculate the correlation between the stock and...

  • 4. You have observed the following returns for Market, Apple, and Microsoft for last 3 vearS:...

    4. You have observed the following returns for Market, Apple, and Microsoft for last 3 vearS: Period Market 0.12 0.10 0.09 0.15 0.10 0.13 Microsoft 0.07 0.05 0.03 a) Calculate the market's return and standard deviation. Write out all the setups for each calculation. Use sample standard deviation formula. b) Calculate both the sample covariance and correlation coefficient between the market and Apple, and between the market and Microsoft. Write out all the setups for each calculation Calculate the betas...

  • You are given the following information concerning a stock and the market: Returns Year Market Stock...

    You are given the following information concerning a stock and the market: Returns Year Market Stock 2011 20 % 32 % 2012 10 10 2013 20 4 2014 −15 −29 2015 37 16 2016 15 16 a. Calculate the average return and standard deviation for the market and the stock. (Use Excel to complete the problem. Do not round intermediate calculations. Enter your answers as a percent rounded to 2 decimal places.) b. Calculate the correlation between the stock and...

  • NEED EXCEL SOLUTION: EXCEL FORMULA ONLY PLEASE 1. Cost of Equity: Stock quote link Stock price:...

    NEED EXCEL SOLUTION: EXCEL FORMULA ONLY PLEASE 1. Cost of Equity: Stock quote link Stock price: $                                           65.95 Dividend: $                                             1.66 Key statistics link Beta:                                                  1.43 Shares outstanding:                                   148,610,000 Analysts' estimates link 5-year dividend growth: 6.85% Bond center link: Risk-free rate: 0.13% Market Risk Premium: 7.00% Cost of Equity: Use DDM: r = D/P + g Cost of Equity: Use CAPM: r = rf + B * (Rm - Rf) Please provide Excel solutions for the above two...

  • NEED EXCEL SOLUTION PLEASE: EXCEL FORMULA ONLY Calculating returns in Excel is a relatively simple matter...

    NEED EXCEL SOLUTION PLEASE: EXCEL FORMULA ONLY Calculating returns in Excel is a relatively simple matter since we only need to input basic equations. Consider the information from the Video Concepts Company: Shares:                  100 Beginning price: $           37.00 Ending price: $           40.33 Dividend per share: $             1.85 With this information, we can calculate the dollar returns and percentage returns as: Total dollar capital gains: Dividend income: Total dollar return: Capital gains return: Dividend yield: Total return:

  • step by step solutions please. no excel solutions 2. Using the CAPM, answer the following problems:...

    step by step solutions please. no excel solutions 2. Using the CAPM, answer the following problems: a. A stock has a beta of 1.2, the expected return on the market is 17 percent, and the risk-free rate is 8 percent. What must the expected return on this stock be? b. A stock has an expected return of 14 percent, the risk-free rate is 4 percent, and the market risk premium is 6 percent. What must the beta of this stock...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT