6 questions

I have 6 questions I need you to answer in an excel spreadsheet. I attached the data you need to use. answer all question. also other than the excel sheet attach a word document that include all relevant tables, figures, and commentary
that answer all questions. show everything.

data:

contains the monthly returns of value– weighted equity indexes divided into various groups:
• North America — Canada and the United states
• Japan
• Asia Pacific — Australia, Hong Kong, New Zealand, and Singapore
• Europe — Austria, Belgium, Denmark, Finland, France, Germany, Greece, Ireland, Italy, the
Netherlands, Norway, Portugal, Spain, Sweden, Switzerland, and the United Kingdom
• Global – All of the above countries
All index returns are in US dollar terms. In addition, the file contains the 1–month US risk–free rate.

questions
1.) On a single chart, plot the value of $1 invested in each of the five indexes over time. I.e., for all
, plot the cumulative return series for each index:
= (1 + 1)(1 + 2) … (1 + )
What patterns do you observe? (10 points)

2.) Plot a histogram of only the Global index returns. Does the distribution look normal? (5 points)

3.) Estimate the following for each of the indices. In calculating the statistics, “monthly” can be
interpreted as “not annualized”. (30 points)

a. Arithmetic average of monthly returns, and annualized arithmetic return using the APR
method
b. Geometric average of monthly returns, and annualized geometric return using the EAR
method. Why does the geometric average differ from the arithmetic average?
c. Standard deviation of monthly returns, and annualized standard deviation
d. Sharpe Ratio of monthly returns, and annualized Sharpe Ratio
e. Skewness of monthly returns
f. Kurtosis of monthly returns
g. 5% Value at Risk (VaR) of monthly returns
h. 5% Expected Shortfall of monthly returns
i. Only for the Global index: based on your answers for (e)–(h), what do each indicate
about using just the standard deviation to estimate risk?

4.) Suppose you have a risk–aversion coefficient of 3.0, and utility function of,
= [ ] − 1
2 2,
that uses your estimate of annual arithmetic returns (APR) and variance. Which index would
you invest in? (5 points)

5.) Assume that only a single risky asset exists (the Global index), and a single risk–free rate (the 1–
month US risk–free rate) at which investors can borrow and lend. Assume the risk–free rate is
equal to its time–series average and has zero variance. Also assume the utility function
provided in the previous problem, with a risk–aversion coefficient of 3.0. For all parts use your
estimate of annualized arithmetic average returns (APR) and annualized variance.

a. Plot the capital allocation line (CAL) in the expected return–standard deviation plane.
What does moving up and down the CAL represent about the allocation between the
two assets? What is the intercept of the line? What is its slope? What is the economic
significance of the intercept and slope?

b. Plot the investor’s utility of the complete portfolio as a function of the allocation to the
risky asset. What does the curve suggest about the investor’s approximate optimal
allocation to the risky asset?

On the same plot as the CAL in (a), plot the indifference curve in the expected return–
standard deviation plane corresponding to a utility level of 0.03 for an investor with a
risk aversion coefficient of 3. Plot a second indifference curve corresponding to the
utility level suggested by (b). How does the second indifference curve differ from the
first? What is the relation of the second indifference curve with respect to the CAL?

d. What is the exact allocation to the risky asset in the complete portfolio that maximizes
the utility of the investor?

6.) Imagine that you are an investor on January 1, 2004, using the historical data up to that date.
I.e., unless otherwise indicated only use the data prior to 2004. Assume that the Global index
cannot be included in your portfolio. I.e., only use the returns for North America, Japan, Asia
Pacific, and Europe.

a. Plot the efficient frontier for the risky assets assuming that short–selling is allowed.
What does the efficient frontier represent?

b. What are the weights on the maximum Sharpe Ratio (SR) portfolio assuming short–
selling is allowed? If you applied these weights to your portfolio on January 1, 2004,
what would the SR of your portfolio be over the next 10 years (i.e., an out–of–sample
test from 1/1/2004 through 12/31/2013)?

c. What are the weights on the maximum SR portfolio assuming short–selling is NOT
allowed? If you applied these weights to your portfolio, what would the SR of your
portfolio be over the next 10 years?

d. Comparing the performance of the two approaches in (b) and (c), which one generated
better out–of–sample performance? Explain.

e. What are the weights on the minimum global variance portfolio? If you applied these
weights to your portfolio, what would the SR of your portfolio be over the next 10
years?