Answered step by step
Verified Expert Solution
Link Copied!

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

image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Derivatives Markets

Authors: Robert McDonald

3rd Edition

978-9332536746, 9789332536746

More Books

Students also viewed these Finance questions

Question

How many countries have adopted DRGs? Why?

Answered: 1 week ago