Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Following the steps listed below and create an excel model that generates the efficient frontier based of two stocks, calculates their betas and automatically
Following the steps listed below and create an excel model that generates the efficient frontier based of two stocks, calculates their betas and automatically updates when new data is input. 1. Create a blank excel file with "xls" or "xlsx" as file extensions. "Stock B" "Portfolio" "Regression Results" and "Model" Create 6 worksheets in the excel file named "Stock A" "S&P 500" . 2. Go to Yahoo Finance and download historical monthly stock price data in the past 5 years of any two stocks you like and the S&P 500 data of the same period. Copy the data in the "Stock A", "Stock B" and "S&P 500" sheets. 3. In the "Model" sheet, calculate the monthly returns using the data you downloaded. Keep in mind that you won't be able to calculate the most recent return if the most recent month has not completed or the most dated month because you don't have the closing price of the month prior to that. 4. Model portfolio returns in the "Model" sheet. Make sure use cell. references for the weights so that it is easy to change the weight of portfolios. 5. Calculate the means and standard deviations of the two stocks, and also. calculate the correlation coefficient between the stocks. 6. Create a series of portfolios ranging from 0% of weights in A and 100% in B to 100% in A and 0% in B with 5% increments. Calculate the mean and standard deviation of each portfolio. 7. Plot the portfolio means and standard deviations using a scatter plot. Make sure the x-axis marks the standard deviations and the y-axis marks the means. 8. Estimate the s of Stock A, Stock B, and the Portfolio using regressions. Put the regression results in the "Regression Results" sheet. Estimate the s again using the =SLOPE()" function and the definition(), respectively.
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