Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Download the historical stock price data of a firm (you can choose any firm you like) and S&P500 (which is the average market price) from

image text in transcribed

Download the historical stock price data of a firm (you can choose any firm you like) and S&P500 (which is the average market price) from Yahoo finance or other public source. Download the dairy yield of 1 year (US T-bill rate) from the Department of US Treasury. You could choose any length of period, but it must be longer than three months. Using those data to estimate the CAPM beta of the firm you choose and clearly state your CAPM model equation. Upload the EXCEL worksheet and your explanation or other work that you think helps. Hint: To solve the CAPM, you should get = firm rate of return, rm = market rate of return, la = risk- free rate or T-bill rate. Then use those rates to run regression in excel and find the beta. And return of stock on timet: rapp, where P is the stock price.) For example: You want to estimate Market risk (beta) for Lululemon in using current year (2020) data. Before you are starting, you should get the three types of data (in bold): Lululemon stock daily price from 2020/01/01 to 2020/11/01; S&P 500 daily price (market price) from 2020/01/01 to 2020/11/01; 1-year T-bill rate from 2020/01/01 to 2020/11/01. As we need to find stock return and market retum for CAPM model, so we need to use the daily price to find daily return. Return on 2020.01.02 = 100* Price on 2020.01.02 Price on 2020.01.01 Price on 2020.01.01 Keep solving, you will get the stock return and market return from 2020/01/02 to 2020/11/01. (As the 1-year 7-bill rate is the rate of return in percentage format. To keep consistent-- daily stock or market return should time 100.) For now you will have stock return (Bululemon), market return (Burkind and T-bill return (Bel) in same format According to the CAPM, Ri = Rf + (Rm - RD * Beta. If we want to find beta, we need run the regression between Ri -Rof and Rm- Ref. So creating two columns, one is [Bululemon - Rod; another is [Boudu - Bul. Finally, using the data analysis tool in excel to run regression. You will get your stock beta!!! Download the historical stock price data of a firm (you can choose any firm you like) and S&P500 (which is the average market price) from Yahoo finance or other public source. Download the dairy yield of 1 year (US T-bill rate) from the Department of US Treasury. You could choose any length of period, but it must be longer than three months. Using those data to estimate the CAPM beta of the firm you choose and clearly state your CAPM model equation. Upload the EXCEL worksheet and your explanation or other work that you think helps. Hint: To solve the CAPM, you should get = firm rate of return, rm = market rate of return, la = risk- free rate or T-bill rate. Then use those rates to run regression in excel and find the beta. And return of stock on timet: rapp, where P is the stock price.) For example: You want to estimate Market risk (beta) for Lululemon in using current year (2020) data. Before you are starting, you should get the three types of data (in bold): Lululemon stock daily price from 2020/01/01 to 2020/11/01; S&P 500 daily price (market price) from 2020/01/01 to 2020/11/01; 1-year T-bill rate from 2020/01/01 to 2020/11/01. As we need to find stock return and market retum for CAPM model, so we need to use the daily price to find daily return. Return on 2020.01.02 = 100* Price on 2020.01.02 Price on 2020.01.01 Price on 2020.01.01 Keep solving, you will get the stock return and market return from 2020/01/02 to 2020/11/01. (As the 1-year 7-bill rate is the rate of return in percentage format. To keep consistent-- daily stock or market return should time 100.) For now you will have stock return (Bululemon), market return (Burkind and T-bill return (Bel) in same format According to the CAPM, Ri = Rf + (Rm - RD * Beta. If we want to find beta, we need run the regression between Ri -Rof and Rm- Ref. So creating two columns, one is [Bululemon - Rod; another is [Boudu - Bul. Finally, using the data analysis tool in excel to run regression. You will get your stock beta

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Personal Finance

Authors: Jack Kapoor

13th Edition

1260799735, 9781260799736

More Books

Students also viewed these Finance questions

Question

3 6 .

Answered: 1 week ago

Question

Compute the derivative of f(x)cos(-4/5x)

Answered: 1 week ago

Question

Discuss the process involved in selection.

Answered: 1 week ago

Question

Differentiate tan(7x+9x-2.5)

Answered: 1 week ago

Question

Explain the sources of recruitment.

Answered: 1 week ago