__Excel Homework 1 (5 Points)__

__ __

__ __

- Open the
**Sheet “Assignment_1”**in “**xlsx”**- Column A contains Monthly Dates
- Column B contains the Prices relative to IVV (an ETF that tracks the S&P 500). Data available here
- Column C contains the Prices relative to IYR (an ETF that tracks the performance of the residential US housing market). Data available here
- Column E contains the monthly risk-free rate (i.e. the 3-Month Treasury Bill). Business & Finance homework help. Data is available here

- In column F, compute the excess returns for IVV. Use the formula (.
- In column G, compute the excess returns for IYR. Use the formula (.
- In cells F2 and F3 compute the average (excel function
*AVERAGE*) and standard deviation (excel function*P*) for the IVV excess returns. - In cells G2 and G3 compute the average (excel function
*AVERAGE*) and standard deviation (excel function*P*) for the IYR excess returns. - In cell K3 compute the covariance between the excess returns of IVV and IYR, using the
*P*excel function. - In cell K2 compute the covariance using the formula seen in class: . To this end, follow these steps
- In column I, compute the terms
- In column J, compute the terms
- In column K, compute the terms
- In cell K3, compute the average of

- In cell M2 compute the correlation using the formula seen in class
- In cell M3 compute the correlation using the excel function
*CORREL*

__ __

__Excel Homework 2 (5 Points)__

__ __

__ __

- Open the
**Sheet “Assignment_2”**in “**xlsx”**- Cells B3 and C3 contain the expected return and standard deviation for IVV
- Cells B4 and C4 contain the expected return and standard deviation for IYR
- Cell B5 contains the expected correlation
- Cell B6 contains the covariance
- Cell B7 contains the risk-free rate
- Cell B8 contains the Risk-Aversion

- 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,
- Compute the expected returns in cells E12:E22
- Compute the standard deviations in cells F12:F22

- For each of these portfolios, compute the associated Mean Variance Utility. Business & Finance homework help.

- Highlight in yellow the weights associated with the MINIMUM VARIANCE PORTFOLIO (among the ones you have computed)
- Highlight in orange the weights associated with the portfolio with the HIGHEST UTILITY

(among the ones you have computed) - In cell J11 compute the weight of IVV in the TANGENCY PORTFOLIO
- In cell J12 compute the weight of IYR in the TANGENCY PORTFOLIO
- In cell J13 compute the Expected Return of the TANGENCY PORTFOLIO
- In cell J14 compute the Standard Deviation of the TANGENCY PORTFOLIO
- In cell J18 compute the optimal allocation between the TANGENCY PORTFOLIO and the Risk-free rate using
- Compute the CAPITAL ALLOCATION line associated with the TANGENCY PORTFOLIO.
- In cells N12:N22 compute the Expected Return

- In cells O12:O22 compute the Standard Deviation

__ __

__ __

__ __

__Excel Homework 3 (5 Points)__

__ __

__ __

- Open the
**Sheet “Assignment_3”**in “**xlsx”**

- 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. - 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. Business & Finance homework help.

- 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