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.