You are the financial manager of Apple Inc. (symbol: AAPL). You are considering the purchase of a promising new software company in California. Your objective is to determine the present value of the future cash flows that this company is expected to generate, thus giving you an approximate idea of the maximum price you would be willing to pay for this company. AAPL has determined that the hypothetical cash flows generated by this company would be of similar riskiness to its own asset cash flows. Therefore, AAPL will use its expected return on assets to discount the future net cash flows that it expects from the software company. a) Calculate AAPL's equity beta as follows. First, download monthly price data for AAPL and SPY from the Supplementary Data folder on the Blackboard course site. SPY is an exchange-traded fund that tracks the S&P 500, and thus provides a good representation of the market portfolio. The Supplementary Data folder is found on the Problem Sets page. Second, calculate monthly returns for AAPL and SPY using the following equation: 14 = (P. - P-)/Pt-1, where r is the return in month t, and P is the adjusted closing price in month t. Convert all of your returns into excess returns (rt - rp) by subtracting the monthly risk-free rate, which we will assume as 0.0204/12. You will not have a return observation for the first month of your time series. Third, run a linear regression of AAPL excess returns (your Y variable) on SPY excess returns (your X variable). You can find "Regression" in Microsoft Excel in "Data Analysis" under the "Data" tab. Report the X-variable coefficient and t-statistic. The X-variable coefficient is the equity beta for AAPL. A t-statistic above 1.96 indicates that we are 95% confident that AAPL monthly returns are positively correlated with S&P 500 monthly returns. b) Calculate the asset beta for AAPL. Assume that the market value of AAPL equity is $2.6 trillion, the market value of AAPL debt is $290 billion, and the beta of AAPL debt is 0.05. c) Using the CAPM, calculate and report the expected return on assets for AAPL. Assume a market risk premium of 5.0% and a risk-free rate of 2.04% (the approximate yield on 30-year U.S. treasury securities). d) The software company is expected to generate free cash flows of $150 million per year for the next thirty years. What is the present value of the future cash flows generated by this company? H B C D E F G G 1 1 Date AAPL Adj. Closing Pri SPY Adj. Closing Pri AAPL Monthly Retur SPY Monthly Retur AAPL Monthly Excess Retu SPY Monthly Excess Return 2 1/1/2019 40.39 256.64 3 2/1/2019 42.02 264.96 4 3/1/2019 46.30 268.57 5 5 4/1/2019 48.91 280.77 6 5/1/2019 42.67 262.87 7 6/1/2019 48.43 279.80 8 7/1/2019 52.13 285.41 9 8/1/2019 51.07 280.63 10 9/1/2019 55.01 284.78 11 10/1/2019 61.10 292.41 12 11/1/2019 65.64 303.00 13 12/1/2019 72.34 310.28 14 1/1/2020 76.24 311.68 15 2/1/2020 67.34 287.00 16 3/1/2020 62.79 249.70 17 4/1/2020 72.55 283.06 18 5/1/2020 78.51 296.54 19 6/1/2020 90.32 300.48 20 7/1/2020 105.24 319.58 21 8/1/2020 127.80 341.88 22 9/1/2020 114.90 327.77 90.32 300.48 105.24 319.58 341.88 327.77 127.80 114.90 108.01 118.12 131.88 131.15 320.88 355.78 367.40 365.20 120.52 19 6/1/2020 20 7/1/2020 21 8/1/2020 22 | 9/1/2020 23 10/1/2020 24 11/1/2020 25 12/1/2020 26 1/1/2021 27 2/1/2021 28 3/1/2021 29 4/1/2021 30 5/1/2021 31 6/1/2021 32 7/1/2021 33 8/1/2021 34 9/1/2021 35 10/1/2021 36 11/1/2021 37 12/1/2021 1/1/2022 39 40 121.58 130.85 124.03 136.56 145.43 151.38 141.29 149.58 165.06 177.57 159.78 375.36 391.12 413.16 415.87 423.81 435.58 448.54 426.27 457.64 453.97 473.30 434.47 38 You are the financial manager of Apple Inc. (symbol: AAPL). You are considering the purchase of a promising new software company in California. Your objective is to determine the present value of the future cash flows that this company is expected to generate, thus giving you an approximate idea of the maximum price you would be willing to pay for this company. AAPL has determined that the hypothetical cash flows generated by this company would be of similar riskiness to its own asset cash flows. Therefore, AAPL will use its expected return on assets to discount the future net cash flows that it expects from the software company. a) Calculate AAPL's equity beta as follows. First, download monthly price data for AAPL and SPY from the Supplementary Data folder on the Blackboard course site. SPY is an exchange-traded fund that tracks the S&P 500, and thus provides a good representation of the market portfolio. The Supplementary Data folder is found on the Problem Sets page. Second, calculate monthly returns for AAPL and SPY using the following equation: 14 = (P. - P-)/Pt-1, where r is the return in month t, and P is the adjusted closing price in month t. Convert all of your returns into excess returns (rt - rp) by subtracting the monthly risk-free rate, which we will assume as 0.0204/12. You will not have a return observation for the first month of your time series. Third, run a linear regression of AAPL excess returns (your Y variable) on SPY excess returns (your X variable). You can find "Regression" in Microsoft Excel in "Data Analysis" under the "Data" tab. Report the X-variable coefficient and t-statistic. The X-variable coefficient is the equity beta for AAPL. A t-statistic above 1.96 indicates that we are 95% confident that AAPL monthly returns are positively correlated with S&P 500 monthly returns. b) Calculate the asset beta for AAPL. Assume that the market value of AAPL equity is $2.6 trillion, the market value of AAPL debt is $290 billion, and the beta of AAPL debt is 0.05. c) Using the CAPM, calculate and report the expected return on assets for AAPL. Assume a market risk premium of 5.0% and a risk-free rate of 2.04% (the approximate yield on 30-year U.S. treasury securities). d) The software company is expected to generate free cash flows of $150 million per year for the next thirty years. What is the present value of the future cash flows generated by this company? H B C D E F G G 1 1 Date AAPL Adj. Closing Pri SPY Adj. Closing Pri AAPL Monthly Retur SPY Monthly Retur AAPL Monthly Excess Retu SPY Monthly Excess Return 2 1/1/2019 40.39 256.64 3 2/1/2019 42.02 264.96 4 3/1/2019 46.30 268.57 5 5 4/1/2019 48.91 280.77 6 5/1/2019 42.67 262.87 7 6/1/2019 48.43 279.80 8 7/1/2019 52.13 285.41 9 8/1/2019 51.07 280.63 10 9/1/2019 55.01 284.78 11 10/1/2019 61.10 292.41 12 11/1/2019 65.64 303.00 13 12/1/2019 72.34 310.28 14 1/1/2020 76.24 311.68 15 2/1/2020 67.34 287.00 16 3/1/2020 62.79 249.70 17 4/1/2020 72.55 283.06 18 5/1/2020 78.51 296.54 19 6/1/2020 90.32 300.48 20 7/1/2020 105.24 319.58 21 8/1/2020 127.80 341.88 22 9/1/2020 114.90 327.77 90.32 300.48 105.24 319.58 341.88 327.77 127.80 114.90 108.01 118.12 131.88 131.15 320.88 355.78 367.40 365.20 120.52 19 6/1/2020 20 7/1/2020 21 8/1/2020 22 | 9/1/2020 23 10/1/2020 24 11/1/2020 25 12/1/2020 26 1/1/2021 27 2/1/2021 28 3/1/2021 29 4/1/2021 30 5/1/2021 31 6/1/2021 32 7/1/2021 33 8/1/2021 34 9/1/2021 35 10/1/2021 36 11/1/2021 37 12/1/2021 1/1/2022 39 40 121.58 130.85 124.03 136.56 145.43 151.38 141.29 149.58 165.06 177.57 159.78 375.36 391.12 413.16 415.87 423.81 435.58 448.54 426.27 457.64 453.97 473.30 434.47 38