Question

Assume an investor has a coefficient of risk aversion, A = 5, then use the information in the table below (along with Excel) to find

i) the utility-maximizing portfolio weights ii) the expected return of the portfolio iii) the volatility of the portfolio

				Covariance Matrix
Asset	E(R)			AAA	BBB	CCC	DDD	EEE
AAA	5.00%		AAA	0.30	0.10	0.20	0.08	0.10
BBB	7.00%		BBB	0.10	0.25	0.10	0.10	0.20
CCC	12.00%		CCC	0.20	0.10	0.36	0.10	0.22
DDD	14.00%		DDD	0.08	0.10	0.10	0.60	0.20
EEE	16.00%		EEE	0.10	0.20	0.22	0.20	0.40

Answer 1

Putting all the values in Excel.

Following formulas will be used:

Utility = Expected return of Portfolio - 1/2 * Risk Aversion * Variance of Portfolio

Correlation = Covariance(A,B) / (Sigma(A) * Sigma(B))

Expected Return of Portfolio = Summation of W_i * E(r)_i for each asset

Variance of Portfolio = Summation of (W_i * Sigma_i)² for each asset + Summation of 2*W_i*W_j*Sigma_i*Sigma_j*Corr(i,j) for each pair of assets

Since we are doing the problem on excel, hence, we can start with assuming weights and then use Solver under Data tab for maximizing the utility using Weights as variables.

To begin, we have assumed the weights as 20% (1/5) equal for each asset

As we have expected return for each asset, we can reach the Portfolio return using assumed weights which can be used in Utility formula

Next we need Standard deviation of each asset, since, Covariance matrix is given, wherever Column and row asset is same, that is Variance for that asset and square root that variance is standard deviation of the asset

Below is the table showing the Weights assumed and Standard deviation of assets calculated using covariance matrix

Sigma is for example: For AAA asset = (0.30)^1/2 = 54.77%

Now for calculating Volatility of the portfolio, we need to correlation of each pair of assets and we will use the correlation formula for the calculation

For example: For AAA and BBB asset, correlation(AAA,BBB) = Covariance (AAA,BBB) / (Sigma(AAA) * Sigma(BBB)) = 0.1 / (54.77% * 50%) = 0.37

Below is the correlation matrxi calculated using the formula for each pair of assets

Now to calculate Volatility of portfolio, we need (Weights * Sigma)² for each asset

For example: For AAA asset, (Weights * Sigma)² = (20%*54.77%)² = 0.0120

Below is the column added for calculation of (Weights * Sigma)² for each asset

Next we need to calculate 2*W_i*W_j*Sigma_i*Sigma_j*Corr(i,j) for each pair of asset to complete the Volatility formula

We will create a matrix similar to Covariance and Correlation, however, wherever Assets are same, it should be 0. We will calculate 2*W_i*W_j*Sigma_i*Sigma_j*Corr(i,j) for pairs where both assets are different

For example: for AAA and BBB, 2*W_i*W_j*Sigma_i*Sigma_j*Corr(i,j) = 2*20%*20%*54.77%*50%*0.37 = 0.008

Below is the matrix of 2*W_i*W_j*Sigma_i*Sigma_j*Corr(i,j):

Summing the columns of (Weights * Sigma)² with sum of 2*W_i*W_j*Sigma_i*Sigma_j*Corr(i,j), we will get Variance of the portfolio. Square root of this variance will be the Volatility or Standard Deviation

Now once, we have calculated Return and Variance of the portfolio using the assumed weights, we can calculate the utility.

NOTE: All the above calculations of Portfolio return, Volatility and Utility are using assumed weights of 20%.

Since we need maximized utility weights, we will maximize the Utility using Solver under Data tab in Excel (It can be added by going into Options, the Com Addins and tick mark Solver Add-in)

In solver, Utility will be our Objective function, Changing cells will be all cells in Weights column and Constraint will that All weights should sum to 1.

Below is how Solver screen will look like:

Now clicking on Solve, we will get the maximized utility, weights, Portfolio returns and Volatility of the portfolio

The output of the Solver is 30% weight for AAA, 57% weight for BBB and 14% weight for DDD asset. Remaining assets have 0% allocations.

With these weights, Maximized utility = -0.50, Expected Portfolio return = 7.35% and Volatility of Portfolio = 47.86%

Below is the output of Solver: