Estimate the beta for only one of the stocks above (choose AAPL or CTC-A.TO). In order to do so, use monthly returns for the AAPL or CTC-A.TO calculated in part 1 above. In addition, retrieve the values of market portfolio: use S&P 500 index (ticker symbol ^GSPC). Using monthly levels of the S&P 500 index, compute monthly holding period returns for "market portfolio" (see calculating holding period returns for CTC-A.TO and AAPL in part 1). Estimate beta for chosen stock (CTC-A.TO or AAPL), by running the regression of this stock's monthly excess returns2 on the "market portfolio" excess returns (In Excel go to "Tools / Data Analysis / Regression"; make sure that Data Analysis add-in is installed on your computer). Is the beta positive or negative? Is it statistically different from zero? How can you tell (why)? Use this data. Historical monthly rates on T-bills (six months to maturity) Unit: Percent:_Per_Year Source: www.federalreserve.gov Time Period Annual rate 2015-01 0.08 2015-02 0.07 2015-03 0.11 2015-04 0.09 2015-05 0.08 2015-06 0.09 2015-07 0.12 2015-08 0.22 2015-09 0.18 2015-10 0.11 2015-11 0.33 2015-12 0.5 2016-01 0.43 2016-02 0.45 2016-03 0.47 2016-04 0.37 2016-05 0.42 2016-06 0.4 2016-07 0.4 2016-08 0.45 2016-09 0.47 2016-10 0.48 2016-11 0.58 2016-12 0.64 2017-01 0.62 2017-02 0.65 2017-03 0.89 2017-04 0.95 2017-05 1.04 2017-06 1.11 2017-07 1.13 2017-08 1.13 2017-09 1.17 2017-10 1.25 2017-11 1.39 2017-12 1.5 2018-01 1.62 2018-02 1.79 2018-03 1.92 2018-04 1.98 2018-05 2.07 2018-06 2.11 2018-07 2.17 2018-08 2.24 2018-09 2.34 2018-10 2.46 2018-11 2.52 2018-12 2.54 2019-01 2.51 2019-02 2.5 2019-03 2.51 2019-04 2.46 2019-05 2.42 2019-06 2.17 2019-07 2.08 2019-08 1.93 2019-09 1.89 2019-10 1.67 2019-11 1.53 2019-12 1.52 2020-01 1.53 2020-02 1.54 2020-03 0.36 2020-04 0.15 2020-05 0.14 2020-06 0.18 2020-07 0.17 2020-08 0.12 2020-09 0.12 2020-10 0.12 2020-11 0.1 2020-12 0.08 ABC Technologies has been growing quite rapidly in recent years, with earnings and dividends increasing at a rate of 30% per annum. This has been achieved by a relatively high level of retention in conjunction with high rates of return on investment. Retentions have been kept at a level of 60%. The company plans to continue with retentions at this level for another year, and then allow retentions to fall to 40% and 25% in the subsequent two years as good investment opportunities become more difficult to find. Earnings for the next year are expected to be $8,000,000. The expected rate of return on the investment to be undertaken next year is 30% and this is expected to fall to 25% the following year, and to 20% - which is the shareholder's required rate of return - the year after that. a. Provide an estimate of the firm's value today. b. Determine the rate of return the firm must have been earning to grow at 30% given the level of its retentions. Hint: use excel and please provide with formulas used on spreadsheets