The CAPM Method for the expected return I have given you monthly returns for the SPY,...

Transcribed Image Text:

The CAPM Method for the expected return I have given you monthly returns for the SPY, Tesla, and the three-month treasury (risk-free rate). 1) Run the regression to estimate the beta of Tesla and report the ouput in a new worksheet. 2) Draw the security characteristic line equation (SCL) using the regression output and write the SCL. 3) Suppose that the risk free rate is 0.23% and the market expected return is 1.52%. Using the beta from Question 1), figure out the expected return for Tesla based on the CAPM framework. Monthly Returns TSLA Return Date SPY Return Risk-Free Rate 11/1/2016 0.01429 0.12825 0.0016 12/1/2016 0.02389 0.17895 0.0016 1/1/2017 0.03929 -0.00770 0.0021 2/1/2017 -0.00309 0.11324 0.0021 3/1/2017 0.01432 0.12853 0.0024 4/1/2017 0.01411 0.08578 0.0023 5/1/2017 0.00149 0.06041 0.0024 6/1/2017 0.02553 -0.10547 0.003 7/1/2017 0.00292 0.10026 0.0029 8/1/2017 0.01511 -0.04158 0.0027 9/1/2017 0.02864 -0.02806 0.0031 10/1/2017 0.03057 -0.06841 0.0032 11/1/2017 0.00698 0.00809 0.0035 12/1/2017 0.06176 0.13798 0.0034 1/1/2018 -0.03636 -0.03175 0.0037 2/1/2018 -0.03129 -0.22425 0.0036 3/1/2018 0.00919 0.10435 0.0043 4/1/2018 0.02431 -0.03120 0.004 5/1/2018 0.00126 0.20447 0.004 6/1/2018 0.04170 -0.13066 0.0042 7/1/2018 0.03192 0.01181 0.0041 8/1/2018 0.00141 -0.12229 0.0041 9/1/2018 -0.06489 0.27401 0.0042 10/1/2018 0.01855 0.03901 0.004 11/1/2018 -0.09334 -0.05045 0.0044 12/1/2018 0.08637 -0.07746 0.0038 1/1/2019 0.03242 0.04189 0.0043 2/1/2019 0.01364 -0.12511 0.0044 3/1/2019 0.04544 -0.14711 0.0041 4/1/2019 -0.06377 -0.22427 0.004 5/1/2019 0.06441 0.20685 0.004 6/1/2019 0.02006 0.08122 0.0042 7/1/2019 -0.01674 -0.06622 0.0032 8/1/2019 0.01477 0.06764 0.0032 9/1/2019 0.02682 0.30743 0.0034 10/1/2019 0.03620 0.04769 0.0027 11/1/2019 0.02402 0.26790 0.0021 12/1/2019 0.00451 0.55516 0.0013 1/1/2020 -0.07917 0.02678 0.0017 2/1/2020 -0.12999 -0.21556 0.0017 3/1/2020 0.13361 0.49214 0.0017 4/1/2020 0.04765 0.06794 0.0017 5/1/2020 0.01328 0.29319 0.0015 6/1/2020 0.06355 0.32501 0.0012 7/1/2020 0.06980 0.74145 0.0015 8/1/2020 -0.04128 -0.13909 0.0008 9/1/2020 -0.02103 -0.09550 0.0002 10/1/2020 0.10878 0.46274 0.0009 11/1/2020 0.03265 0.24325 0 12/1/2020 -0.00597 0.12451 0.0001 1/1/2021 0.02781 -0.14874 0.0001 2/1/2021 0.04199 -0.01121 0.0001 3/1/2021 0.05636 0.06215 0 4/1/2021 0.00657 -0.11871 0 5/1/2021 0.01909 0.08714 0.0001 6/1/2021 0.02776 0.01103 0.0001 7/1/2021 0.02976 0.07061 8/1/2021 -0.04965 0.05404 9/1/2021 0.06494 0.32158 10/1/2021 0.02917 0.06343 O O O O 0 0 The CAPM Method for the expected return I have given you monthly returns for the SPY, Tesla, and the three-month treasury (risk-free rate). 1) Run the regression to estimate the beta of Tesla and report the ouput in a new worksheet. 2) Draw the security characteristic line equation (SCL) using the regression output and write the SCL. 3) Suppose that the risk free rate is 0.23% and the market expected return is 1.52%. Using the beta from Question 1), figure out the expected return for Tesla based on the CAPM framework. Monthly Returns TSLA Return Date SPY Return Risk-Free Rate 11/1/2016 0.01429 0.12825 0.0016 12/1/2016 0.02389 0.17895 0.0016 1/1/2017 0.03929 -0.00770 0.0021 2/1/2017 -0.00309 0.11324 0.0021 3/1/2017 0.01432 0.12853 0.0024 4/1/2017 0.01411 0.08578 0.0023 5/1/2017 0.00149 0.06041 0.0024 6/1/2017 0.02553 -0.10547 0.003 7/1/2017 0.00292 0.10026 0.0029 8/1/2017 0.01511 -0.04158 0.0027 9/1/2017 0.02864 -0.02806 0.0031 10/1/2017 0.03057 -0.06841 0.0032 11/1/2017 0.00698 0.00809 0.0035 12/1/2017 0.06176 0.13798 0.0034 1/1/2018 -0.03636 -0.03175 0.0037 2/1/2018 -0.03129 -0.22425 0.0036 3/1/2018 0.00919 0.10435 0.0043 4/1/2018 0.02431 -0.03120 0.004 5/1/2018 0.00126 0.20447 0.004 6/1/2018 0.04170 -0.13066 0.0042 7/1/2018 0.03192 0.01181 0.0041 8/1/2018 0.00141 -0.12229 0.0041 9/1/2018 -0.06489 0.27401 0.0042 10/1/2018 0.01855 0.03901 0.004 11/1/2018 -0.09334 -0.05045 0.0044 12/1/2018 0.08637 -0.07746 0.0038 1/1/2019 0.03242 0.04189 0.0043 2/1/2019 0.01364 -0.12511 0.0044 3/1/2019 0.04544 -0.14711 0.0041 4/1/2019 -0.06377 -0.22427 0.004 5/1/2019 0.06441 0.20685 0.004 6/1/2019 0.02006 0.08122 0.0042 7/1/2019 -0.01674 -0.06622 0.0032 8/1/2019 0.01477 0.06764 0.0032 9/1/2019 0.02682 0.30743 0.0034 10/1/2019 0.03620 0.04769 0.0027 11/1/2019 0.02402 0.26790 0.0021 12/1/2019 0.00451 0.55516 0.0013 1/1/2020 -0.07917 0.02678 0.0017 2/1/2020 -0.12999 -0.21556 0.0017 3/1/2020 0.13361 0.49214 0.0017 4/1/2020 0.04765 0.06794 0.0017 5/1/2020 0.01328 0.29319 0.0015 6/1/2020 0.06355 0.32501 0.0012 7/1/2020 0.06980 0.74145 0.0015 8/1/2020 -0.04128 -0.13909 0.0008 9/1/2020 -0.02103 -0.09550 0.0002 10/1/2020 0.10878 0.46274 0.0009 11/1/2020 0.03265 0.24325 0 12/1/2020 -0.00597 0.12451 0.0001 1/1/2021 0.02781 -0.14874 0.0001 2/1/2021 0.04199 -0.01121 0.0001 3/1/2021 0.05636 0.06215 0 4/1/2021 0.00657 -0.11871 0 5/1/2021 0.01909 0.08714 0.0001 6/1/2021 0.02776 0.01103 0.0001 7/1/2021 0.02976 0.07061 8/1/2021 -0.04965 0.05404 9/1/2021 0.06494 0.32158 10/1/2021 0.02917 0.06343 O O O O 0 0