Question

Homework (Due on Mar. 23, 2023)

A.Use Excel to draw a figure showing portfolios of two assets with different correlations.
B.The basic information of two assets:
1.Asset 1: mean return =0.15, volatility =0.20
2.Asset 2: mean return =0.25, volatility =0.30
3.Asset correlation: Use spin bottom to adjust correlations.
C.Print your excel sheet. Also print figures of portfolios when the correlation of two assets is -0.8 , −0.4,0,0.4,0.8.

Answer 1

To draw the figure showing portfolios of two assets with different correlations using Excel, you need to set up the Excel sheet, calculate the portfolios, draw the figure, and then print the sheet and figures.

Step-by-step explanation:

Step 1: Set up the Excel sheet

First, set up your Excel sheet with the basic information of the two assets. In column A, enter the correlation values (-0.8, -0.4, 0, 0.4, 0.8). In columns B and C, enter the mean return and volatility values for Asset 1 and Asset 2, respectively. In column D, use the Excel function CORREL to calculate the covariance between the two assets based on the correlation value.

Step 2: Calculate the portfolios

Next, set up your formulas in column E to calculate the portfolios for each correlation value. Use the formula E= w1 * B2 + w2 * C2, where w1 and w2 are the weights of Asset 1 and Asset 2, respectively.

Step 3: Draw the figure

Create a scatter plot in Excel with the correlation values on the x-axis and the portfolios on the y-axis. Add labels and titles to make it clear and presentable.

Step 4: Print the Excel sheet and figures

Print your Excel sheet, including the portfolios and figures, for submission.