Question
I am working on a project for my financial modeling class on portfolio optimization. I have gotten parts A-D but am stuck on the next
I am working on a project for my financial modeling class on portfolio optimization. I have gotten parts A-D but am stuck on the next few parts. Any help is appreciated.
I have chosen the stocks:
KO | HD | IBM | AAPL | FB | NFLX | BUD | DAL | S&P |
A) Download and calculate daily returns for the portfolio for the following time period: 6/15/2016-6/15/2017.
B) Calculate the average returns, average daily variance, and average daily standard deviation for each asset.
C) Calculate the yearly equivalent for average, variance, and standard deviation.
D) Calculate the betas for all stocks. Use daily returns on the S&P 500 as a proxy for the market portfolio.
E) Calculate the required return on each stock for the upcoming year ASSUMING a market portfolio risk premium of 6%. Use CAPM. (You will need to find a current value for the risk-free rate). Use the current 90-day T-bill for the risk free rate.
F) Create the daily variance-covariance matrix for your returns (Omit the S&P 500 for this section). Keep in mind that this is a daily risk assessment. Scale this up to an annual basis. You may assume 252 trading days per year. (YOU WILL NEED TWO MATRICES HERE..ONE FOR DAILY, ONE CONVERTED TO YEARLY)
G) Calculate portfolio return and risk for an equally weighted portfolio using expected returns for the upcoming year. (you may want to copy and paste these results to another sheet. It is a deliverable for the project). Returns are forward looking so use the CAPM model.
H) Find the minimum variance portfolio. Short-selling is allowed.
I) Using SOLVER, create the efficient frontier.
J) Plot the 9 securities on your graph.
K) Use Solver to find the market portfolio (composed of 8 stocks. Again ignore the S&P). Plot the market portfolio on your graph.
L) Plot the Capital Market Line on your graph.
***Keep in mind that we need to SEE the solution to part G. So, you may want to copy your sheet to another blank sheet before continuing to part H.
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