## (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 &amp; 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.

Solution details:

STATUS

QUALITY

Approved

Sep 13, 2020

EXPERT

Tutor