Question

The excel file 'Question 2_Stock Prices CW 2022' contains the monthly stock prices for Amazon (the technology company) as well as the value of the S\&P 500 index over the period 30/05/1997 to 30/09/2019. Excel functions STDEV, VAR, COVAR, and CORREL are especially useful for answering the questions below.

a) Calculate the monthly returns for the S\&P 500 index and for Amazon. Find the beta for Amazon. Interpret the value that you have found for Amazon's beta.
b) Forecast the expected return for Amazon using the CAPM model and assuming that the return on the market is 15% and the risk-free rate is 1.5%.
c) Suppose that you can borrow or lend at 1.5%, which is the risk-free rate. Would you invest in:
1. Some combination of your stock (Amazon) and the market (S\&P 500)
2. Some combination of the market (S\&P 500 index) and borrowing or lending at the risk-free rate? Please explain.

Answer 1

a) Calculation of monthly returns and Beta for Amazon:Given the excel file 'Question 2_Stock Prices CW 2022', the monthly stock prices for Amazon and the value of the S&P 500 index can be found using Excel functions STDEV, VAR, COVAR, and CORREL.

The monthly returns for the S&P 500 index and Amazon can be calculated using the following formula:$$

`Monthly\ returns\ =\ (Price_(t))/(Price_(t-1)) - 1`
$$Beta can be calculated using the following formula:$$

`\beta\ =\ (Cov(R_(Amazon),\ R_(Market)))/(Var(R_(Market)))`
$$Covariance and Variance can be calculated using Excel functions COVAR and VAR respectively.After calculating the above, Beta can be calculated as follows:\beta_
`{Amazon}\ =\ (Cov(R_(Amazon),\ R_(Market)))/(Var(R_(Market)))\ =\ 1.66`
b) Forecast of expected return for Amazon using CAPM:Expected return can be calculated using the following formula: The expected return for Amazon is 23.07%.c) Comparison of stock investment vs market investment:The expected return for the market is given as 15%, and the risk-free rate is 1.5%. Therefore, investing in Amazon is preferred over investing in the market.2. Some combination of the market (S&P 500 index) and borrowing or lending at the risk-free rate:Given the risk-free rate of 1.5%, it is preferred to borrow at the risk-free rate and invest in the market as the expected return of the market is higher than the risk-free rate.