FINA3010 HW2: Empirical analysis - CAPM application (Due on Nov 27th) Try your best even if you cannot get right beta: this is a real-life application for what we learned 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, im= market rate of return, lu = 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 time te where P is the stock price.) Pe-P1 Pe- 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 retum and market return for CAPM model, so we need to use the daily price to find daily retum. Price on 2020.01.02 - Price on 2020.01.01 Return on 2020.01.02 = 100. Price on 2020.01.01 Keep solving, you will get the stock return and market retum from 2020 01 02 to 2020/11/01. (As the l-year T-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 (Radema), market return (B) and bill return (B) in same format According to the CAPM. Ri= Ref + (Rm - Rof Beta. If we want to find beta, we need run the regression between Ri -Ref and Rm - Rsf. So creating two columns, one is [Blumar-Bed another is ( R.BU Finally, using the data analysis tool in excel to run regression. You will get your stock beta!!! TITE Tristan Share SUMMARY OUTPUT 1.SE 1.55 146 153 1.55 154 151 dally return rearly Mat Date Add dall return Term T-610 RI RIREM Aile 2020/1/2 Am R: 3257 2501 2020/1/3 0.0033416 03341612 384161228501 0.00 1871 2255971 2020/1/6 235 0.01199275 1.19927528 03407247 3246 28003 0.0035 2020/1/7 -0.214824 -2.264824 3237.17995 2020/1/8 S270025 0.0223975 2020/1/9 0957241 700552174055 2020/1/10 -0.44934119791421 2020/1/1 4402997 4.40299658 287299658 2020/1/14 100S 70 0.575053221050832 2020/1/15 OL001146 0.11486 -1.654862 2020/1/16 09090354 0.09035361 1.4496464 0.006655 2020/1/17 0.010094 1008399325693993 2020/1/21 242.5.2000 0.00526427052642653 10135735 3120.78004 2020/1/22.244300003 0.0073396 0.73395966 0.8160403 3321.750.000 2020/1/23 243.059998 0.0050257 0.5025242-20575747 2325.54004 0.0011409 2020/1/24 240.3500060011149511144478 2664978 3295.469920.000 2020/1/27, 236.74000501501981.501976730319267 3241.6293 0.0157 2020/1/28 239.350006 0.01102476110247569 0.4225 1999 OK 2020/1/29 241.660004 GOTB 709 280070938 029070 0.0031 31343589 1.166566T 2020/1/80 243.770004 0.00045145 0.04514487 1.4348 -1.7709 2020/1/31 219.38999900179678 17967777 32467272 322552002 0.017705 2348536 2020/2/3 246.369/1995 0.02915241 291574252 145574252 3248.919920.0072546116 2546 2020/2/4 247.990005 0.00657552065755166 0.8224483 3297.59009 0.01494 4984342 0018054100 2020/2/5 243.8999940.018492616264531392645 2334.68914 00152506 11250 N649404 153 159 ISH TEBE 154 114 154 Repression Stathat 15 Multiple R 0.7MAS711 1.54 R Square 0.6311693 1.53 MRS05252231 1.55 Standard Em 2.4783516 154 Observation 64 1.53 15 ANOVA 1.53 1:54 Regression 1860.4522170 1.54 Real 62 502.8302361 1.56 Tel 63 11326945 154 1.55 Coefficiente ondor 155 Intercept 0.11674273 OM 0.10 159 x Variable 1103120350 1001142410 1.53 1.53 of 1.0312035 1.51 1.4 CAPM AIR 103 LAS 146 148 1:49 1.55 1.55 1.55 1.53 1.53 1.51 1.48 145 1.46 1.4 1.49 FINA3010 HW2: Empirical analysis - CAPM application (Due on Nov 27th) Try your best even if you cannot get right beta: this is a real-life application for what we learned 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, im= market rate of return, lu = 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 time te where P is the stock price.) Pe-P1 Pe- 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 retum and market return for CAPM model, so we need to use the daily price to find daily retum. Price on 2020.01.02 - Price on 2020.01.01 Return on 2020.01.02 = 100. Price on 2020.01.01 Keep solving, you will get the stock return and market retum from 2020 01 02 to 2020/11/01. (As the l-year T-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 (Radema), market return (B) and bill return (B) in same format According to the CAPM. Ri= Ref + (Rm - Rof Beta. If we want to find beta, we need run the regression between Ri -Ref and Rm - Rsf. So creating two columns, one is [Blumar-Bed another is ( R.BU Finally, using the data analysis tool in excel to run regression. You will get your stock beta!!! TITE Tristan Share SUMMARY OUTPUT 1.SE 1.55 146 153 1.55 154 151 dally return rearly Mat Date Add dall return Term T-610 RI RIREM Aile 2020/1/2 Am R: 3257 2501 2020/1/3 0.0033416 03341612 384161228501 0.00 1871 2255971 2020/1/6 235 0.01199275 1.19927528 03407247 3246 28003 0.0035 2020/1/7 -0.214824 -2.264824 3237.17995 2020/1/8 S270025 0.0223975 2020/1/9 0957241 700552174055 2020/1/10 -0.44934119791421 2020/1/1 4402997 4.40299658 287299658 2020/1/14 100S 70 0.575053221050832 2020/1/15 OL001146 0.11486 -1.654862 2020/1/16 09090354 0.09035361 1.4496464 0.006655 2020/1/17 0.010094 1008399325693993 2020/1/21 242.5.2000 0.00526427052642653 10135735 3120.78004 2020/1/22.244300003 0.0073396 0.73395966 0.8160403 3321.750.000 2020/1/23 243.059998 0.0050257 0.5025242-20575747 2325.54004 0.0011409 2020/1/24 240.3500060011149511144478 2664978 3295.469920.000 2020/1/27, 236.74000501501981.501976730319267 3241.6293 0.0157 2020/1/28 239.350006 0.01102476110247569 0.4225 1999 OK 2020/1/29 241.660004 GOTB 709 280070938 029070 0.0031 31343589 1.166566T 2020/1/80 243.770004 0.00045145 0.04514487 1.4348 -1.7709 2020/1/31 219.38999900179678 17967777 32467272 322552002 0.017705 2348536 2020/2/3 246.369/1995 0.02915241 291574252 145574252 3248.919920.0072546116 2546 2020/2/4 247.990005 0.00657552065755166 0.8224483 3297.59009 0.01494 4984342 0018054100 2020/2/5 243.8999940.018492616264531392645 2334.68914 00152506 11250 N649404 153 159 ISH TEBE 154 114 154 Repression Stathat 15 Multiple R 0.7MAS711 1.54 R Square 0.6311693 1.53 MRS05252231 1.55 Standard Em 2.4783516 154 Observation 64 1.53 15 ANOVA 1.53 1:54 Regression 1860.4522170 1.54 Real 62 502.8302361 1.56 Tel 63 11326945 154 1.55 Coefficiente ondor 155 Intercept 0.11674273 OM 0.10 159 x Variable 1103120350 1001142410 1.53 1.53 of 1.0312035 1.51 1.4 CAPM AIR 103 LAS 146 148 1:49 1.55 1.55 1.55 1.53 1.53 1.51 1.48 145 1.46 1.4 1.49