Assignment
Question 1: Use Sheet1 in Portfolios.xlsx to calculate the expected returns of a portfolio and the "risk" of the same portfolio defined in the following way:
An individual investor holds a portfolio of three assets 1,2,3 with expected returns r_i where i=1,2,3.The weights, or the percentages, of these assets in the investor's portfolio are w_iwith i=1,2,3. So the expected return of the portfolio for the investor can be calculated:
r_p=∑_(i=1)^3w_i·r_i .
The "risk" of the portfolio is defined as the variance of the expected return. Formally, if we are given the covariance between two assets i,j (NOTE: i might be equal to j) as cov(i,j), then the risk or the portfolio variance is:
σ_p^2=∑_(i=1)^3∑_(j=1)^3w_i·w_j·cov(i,j) .
As on Sheet1 of Portfolios.xlsx, the investor's portfolio has three assets: Tbills, Bonds, and Shares. We know the expected return for each asset and the covariance between each pair of them (as in the VCV matrix). For example, cell C9 is the covariance between Tbills and Tbills itself. Cell D9 is the covariance between Bonds and Tbills.
Requirements:
Fill in the cells to the right of "Exp Ret," "Std Dev," and "Variance," the cells I13, I14, and I16 using the formula above. They are, respectively, the expected return, standard deviation, and variance of the portfolio.For your information, "Std Dev" is the square root of "Variance."
You can use either Excel's built-in functions or define your own customized functions to calculate the quantities in Requirement 1.
(Hint: Some Excel built-in functions you might find useful: SUMPRODUCT and MMULT.)
Question 2: On Sheet2 of Portfolios.xlsx, you are going to find a similar portfolio with three assets as in Question 1. The difference is that we do not know the weights of the three assets in this case. Your task is to find out the "optimal" weights in the following sense:
A portfolio producing a target return at 7%;
The "risk," or the variance of the portfolio, is minimized.
Requirements: Using the "Solver" feature of Excel to find out the optimal weights and report the weights in cells I9, I10, and I11. The variance and return are defined the same way as in Question 1.
Question 3: On Sheet3 of Portfolios.xlsx you are given another portfolio with two assets: asset 0 and asset 1. The expected returns of these two assets are respectively 1% and 2.1%. The standard deviations are 0 and 0.101, which means that asset 0 is a risk-free asset. Suppose now an investor's benefit from investing in such a portfolio is r_p-0.5·A·σ_p^2, where r_p and σ_p^2 are as defined in Question 1 and A is a risk aversion coefficient. Now the investor is maximizing his or her benefit by choosing the weights w_0 and w_1 in the portfolio. An optimization process gives us the optimal weight for asset 1 being:
w_1=((r_1-r_0 ))⁄((A·σ_1^2 ) ). (1)
In this equation (Equation 1), r_1 is the expected return of asset 1 and r_0 is that of asset 0. σ_1^2 is the variance of asset 1.
Requirements:
Use a user-defined function to calculate the optimal weights of asset 0 and 1.
Call the function and report the results in cell G11 and G12 of Sheet3.
Calculate the value of benefits and report it in cell J8.
Derive Equation (1) using all information given in this assignment.
Attachment:- Portfolios.rar