Posted: September 16th, 2017

Caseʹ2

Caseʹ2
You have also been approached by a friend who is evaluating an investment problem. Knowing that
you have studied finance, you are asked to help him answering the below questions.
Need a Professional Writer to Work on this Paper and Give you an A+ 100 % Original Paper? CLICK HERE TO GET THIS PAPER WRITTEN
Assume the following expected data on two assets:

Return
State Probability state Bond Stock
Recession 25% 20% -15%
Normal 50% 10% 0%
Boom 25% 5% 20%

The stated probabilities shall be changeable as well as the returns in the different states. Build a
spreadsheet model to answer the following question:
x What is the expected return of the assets?
x What is the risk (variance and volatility) for the assets?
x What is the correlation between the two asset’s returns?
x Assume a portfolio of the two assets. What is the expected return on this portfolio (the user
shall be able to change the percentage weight of the assets in the portfolio)? Use a 50%/50%
weights to answer this question.
x What is the variance and volatility for this 50%/50%-portfolio?
x What is the efficient frontier for the two assets given the information in the table above?
(Create a suitable graph depicting this.)
x What is the minimum variance portfolio and what is the return of this portfolio, i.e. what
fraction of the stock and the bond is it made up of? Use the information in the table above
for this question. Hint: Use the solver in excel (include solver results) if you cannot solve this
algebraically.

N.B. Since the client wants to be able to use this spreadsheet in the future for other projects as well
you need to use cell references in the formulas.

Need a Professional Writer to Work on this Paper and Give you an A+ 100 % Original Paper? CLICK HERE TO GET THIS PAPER WRITTEN