Excel

C. T. Bauer College of FINA4320
University of Houston Antonio Gargano
Investment Management

Excel Homework 1 (5 Points)

1. Open the Sheet “Assignment_1” in “Excel_Assignment_1_2_3.xlsx”

a. Column A contains Monthly Dates
b. Column B contains the Prices relative to IVV (an ETF that tracks the S&P 500). Data available here

c. Column C contains the Prices relative to IYR (an ETF that tracks the performance of the residential US housing market). Data available here

d. Column E contains the monthly risk-free rate (i.e. the 3-Month Treasury Bill). Data is available here

2. In column F, compute the excess returns for IVV. Use the formula (.

3. In column G, compute the excess returns for IYR. Use the formula (.

4. In cells F2 and F3 compute the average (excel function AVERAGE) and standard deviation (excel function STDEV.P) for the IVV excess returns.

5. In cells G2 and G3 compute the average (excel function AVERAGE) and standard deviation (excel function STDEV.P) for the IYR excess returns.

6. In cell K3 compute the covariance between the excess returns of IVV and IYR, using the COVARIANCE.P excel function.

7. In cell K2 compute the covariance using the formula seen in class: . To this end, follow these steps
a. In column I, compute the terms
b. In column J, compute the terms
c. In column K, compute the terms
d. In cell K3, compute the average of

8. In cell M2 compute the correlation using the formula seen in class

9. In cell M3 compute the correlation using the excel function CORREL

Excel Homework 2 (5 Points)

1. Open the Sheet “Assignment_2” in “Excel_Assignment_1_2_3.xlsx”

a. Cells B3 and C3 contain the expected return and standard deviation for IVV
b. Cells B4 and C4 contain the expected return and standard deviation for IYR
c. Cell B5 contains the expected correlation
d. Cell B6 contains the covariance
e. Cell B7 contains the risk-free rate
f. Cell B8 contains the Risk-Aversion

2. Compute the set of feasible portfolios obtained by combining IVV and IYR. Do it ONLY for the weights in cells B12:B22 and C12:C22. Remember that for us a “portfolio” is just an expected return and a standard deviation. Therefore,
a. Compute the expected returns in cells E12:E22
b. Compute the standard deviations in cells F12:F22

3. For each of these portfolios, compute the associated Mean Variance Utility

4. Highlight in yellow the weights associated with the MINIMUM VARIANCE PORTFOLIO (among the ones you have computed)

5. Highlight in orange the weights associated with the portfolio with the HIGHEST UTILITY
(among the ones you have computed)

6. In cell J11 compute the weight of IVV in the TANGENCY PORTFOLIO

7. In cell J12 compute the weight of IYR in the TANGENCY PORTFOLIO

8. In cell J13 compute the Expected Return of the TANGENCY PORTFOLIO

9. In cell J14 compute the Standard Deviation of the TANGENCY PORTFOLIO

10. In cell J18 compute the optimal allocation between the TANGENCY PORTFOLIO and the Risk-free rate using

11. Compute the CAPITAL ALLOCATION line associated with the TANGENCY PORTFOLIO.
a. In cells N12:N22 compute the Expected Return

b. In cells O12:O22 compute the Standard Deviation

Excel Homework 3 (5 Points)

1. Open the Sheet “Assignment_3” in “Excel_Assignment_1_2_3.xlsx”

a. Use the “Excel solver” to solve the Markowitz minimization problem seen in class. In other words, find the weights associated with the portfolio on the frontier with a target return of 7%. Paste them in cells D42:D54.

b. Solve the same problem but impose the additional constraint of only positive weights (i.e. short sales are NOT allowed). Paste the weights in cells E42:E54