Question
You want to form a portfolio between two stocks: Canadian Tire and the Apple Inc. Download the monthly price data from Yahoo! Finance pages for
You want to form a portfolio between two stocks: Canadian Tire and the Apple Inc. Download the monthly price data from Yahoo! Finance pages for Canadian Tire (ticker symbol: CTC-A.TO) and Apple (ticker symbol: AAPL) from November 1, 2018 to November 1, 2023. Calculate the monthly holding period returns for each stock in Excel using the split-adjusted prices that Yahoo provides . Use these data and Excel to answer the following questions:
What are the average monthly return and standard deviation of returns for Canadian Tire? What are the average monthly return and standard deviation of returns for the Apple? Does risk-return relationship (trade-off) hold between these two stocks?
Using these values, calculate the portfolio return and standard deviation for various weights in Canadian Tire and Apple:
Calculate the portfolio return and standard deviation for a portfolio with alternately 0%, 5%, 10%, 15% .., 95%, 100% weight in Apple and the rest in Canadian Tire.
Graph this portfolio return and standard deviation for all possible portfolios on a graph with Return on the vertical axis and Standard deviation on the horizontal axis. (hint: Use Scatter Plot type of graph)
Calculate the Canadian Tires weight in the portfolio that gives the minimum standard deviation (hint: try using solver or manual trial and error); show this portfolio on a graph built above.
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 stocks monthly excess returns1 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, negative? Is it statistically different from zero? How can you tell (why)?
Please solve and show all calculations, graphs and charts. Please provide step-by-step explanations for each solved answer.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started