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
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.