(solution) Please upload an excel file that has the work on one or two pages

(solution) Please upload an excel file that has the work on one or two pages

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 naïve
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.