Portfolio Selection

Q1: Generate 1000 random draws

After Add in the Analysis ToolPak, we are able to find the random number generator under data analysis, and can generate 1000 random numbers from the probability distribution given. The random 1000 pickups are discrete.

When we get 1000 numbers filled in A2:A1001(random distributed from label 1-10), we are able to use the VLOOPUP formula to get the net-off of each label from Asset1, Asset2…Asset10 by using the given table of net payoff of each asset.

Q2: The theoretical and simulated expected value and standard deviation of random draws

The mean could be calculated by using the excel Sumproduct formula, and the formulas above give the way to do it mathematically. The theoretical expected value of draw is 4.63 and standard deviation is 2.73785406

Those figures could be calculated by using AVERAGE (A2:A1001), VAR (A2:A1001), SQUR (VAR (A2:A1001)), the expected value of draws is 4.607 and simulated standard deviation is 2.78177808

Q3: the summary statistics of the net payoff using the random sequence of draws generated

Mean=>Expected return Standard Deviation => Volatility/Risk

The way of calculating the expected return and risk of the statics net payoffs is almost the same as the way of calculating draws.

(The theoretical one is just calculated for comparing, and is not required in the assignment)

Variance-Covariance Matrix of payoffs

Where and the matrix is

In excel, the Covar formula can be used to calculate the matrix. As the matrix of 1-10 assets of 1000 labels is set in (B2:B1001, K2:K1001), we could get the first number by using Covar ($B$2:$B$1001, B2:B1001)*1000/999 and can fill the row.

Q4: regression parameters of each