Please choose a publicly traded (on NYSE or NASDAQ for at least10years) industrialUSfirmYouwill complete a historical risk and return analysis on your stock and create an efficient frontier. Please...

Please choose a publicly traded (on NYSE or NASDAQ for at least10years) industrialUSfirmYouwill complete a historical risk and return analysis on your stock and create an efficient frontier.

Please download themonthlyprice dataforyour companyfrom Yahoo Finance or a similar source, for Newmont Mining Corp (Ticker symbol: NEM)and the S&P 500 Stock Index(Ticker symbol: ^GSPC)betweenSeptember 2010-September 2020.

1.Calculate themonthlyholding period returnsfor each in Excel using thesplit-adjustedclosingprices thatare provided. Beware: returns calculated from beginning to closing prices will not be split/dividendadjusted, so make sure you calculate them fromthe adjustedclosing price of the previous month to theadjustedclosing price of the current month.2.Find the average historical return (Tip: useexcel’s“average” function) and historical standard deviation (Tip: use Excel’s “stdev” function) of the returns of all three securities.3.Create a covariance matrix and a correlation matrix between these three securities. (Tip: Use AnalysisToolpack)4.Using the average monthly returns and standard deviations calculated above, create portfolios made up of your company and NEM with alternately 0%, 5%, 10%, 15% ….., 95%, 100% weight in your company and the rest in NEM.a.Calculate the expected returns and standard deviations for the returns of these portfolios between your company and NEM. (Tip: Use thecorrelationcoefficient from #3 between your company and NEM for the standard deviation calculations. Use the formula below for the standard deviation of a two stock portfolio. b.Graph this portfolio return and standard deviation for all possible portfolios on a graph with return on theverticalaxis and standard deviation on thehorizontalaxis. (hint: Use Scatterplot. Your graph should look like the efficient frontier from portfolio theory lectures.)c.Find the portfolio with the lowest standard deviation. i.e. calculate the portfolio weight you need to put in your company so you can end up with the minimum standard deviation portfolio (hint: try using solver or manual trial and error); show to several decimal places.5.Run two regressions to find the betas (β) of your company andthatof NEM. (Use the SP 500 returns as a proxy for market returns.)a.Can you have a negative beta? What does a negative beta mean?b.Compare your betas for your company and NEM from the regressions to the betas reported for these companies in Bloomberg. Are they the same? If not, how can you explain the discrepancy between your numbers and those calculated by Yahoo!? What can be potential reasons for the difference?6.Using the average historical return on the S&P 500 as the market return (rM) and the 10-yr treasury bond yield as the risk-free rate of return (rRF), calculate the required returns for your company and NEM using the CAPM. (Tip: Do not forget that all your returns here are monthly but the yield on the T-bond will be annual yield. You will need to convert that into a monthly yield in order to apply the formula.)7.Compare your required returns from #6 to the average historical returns calculated in #2. a.Is your company over-priced or underpriced? Why? Would you recommend a buy or hold or sell for your company?b.Is NEM over-priced orunder-priced? Why? Would you recommend a buy or hold or sell for NEM?