Please upload an excel file that has the work on one or two pages and then the answers on a separate page.

FINC 460/560: Portfolio Analysis Homework 3, due Oct. 20 40 points Value at Risk (VaR) Use any web data source to collect daily and weekly prices for PG (Procter & Gamble) and GOOG (Google) from January 1, 2015 to December 31, 2015. I recommend the historical price link at finance.yahoo.com, but you may use whatever source you prefer to gather these data. Use Excel to complete the following analysis: 1. What are the mean and standard deviation of one-day logarithmic returns (not prices!) for each stock? What is the correlation between the returns for the two stocks? Also, calculate the mean and standard deviation for a portfolio consisting of 30% PG and 70% GOOG. 2. Using the parametric method, what are the 95% and 99% 1-day VaR for a $500,000 position in each stock (so you are investing a total of $1 million, half in each)? Use this figure to calculate the 5-day VaR for each stock for the same investment amount. 3. Use your answers from #1 for the individual stocks to calculate the 95% 1-day VaR for a $1 million portfolio that consists of 30% PG and 70% GOOG. (In other words, use nave inference, ignoring the portfolio statistics.) 4. Use the portfolio descriptive statistics from #1 to calculate the maximum possible 95% 1-day VaR for a $1 million portfolio of 30% PG and 70% GOOG. (In other words, now use only the portfolio statistic numbers from #1.) Is this number the same as your answer for #3? Why or why not? 5. Use the weekly prices to calculate the 95% 5-day VaR for a $500,000 position in each stock (assume 5 days is the same thing as one week). Do you get the same results as you did in #2? Why or why not? 6. Using the historical simulation (nonparametric) method, calculate the 95% 1-day VaR for a portfolio that consists of 30% PG and 70% GOOG, using the historical two years of returns. Use the completely non-parametric method by reading off the 5% level using Excel's percentile function. Is your result the same as your figure from #4? Why or why not? 7. Conduct Monte Carlo analysis for the 95% 1-day VaR for a portfolio of 30% PG and 70% GOOG. Assume that returns of the individual stocks are normally distributed with the parameters from #1. Generate 1,000 replications; calculate descriptive statistics; and use the parametric estimation method to generate the VaR